CODEWITHSUNDEEP
sqlvb.netms-access
Terrence McGinnis
Terrence McGinnis

Reputation: 136

Retrieving an AutoNumber ID that has an alphanumeric format

How do I retrieve an ID field that is an AutoNumber and has been given an alphanumeric Format, e.g., 'ER001'?

I'm using Access for backend and VB 2010.

My code so far only returns the last number of the ID Column, e.g., 1 instead of ER001. 

Dim SQL As String = "SELECT ID FROM ReqItemList WHERE ReqItem = " & inputin & " "

Upvotes: 3

Views: 1358

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123619

I believe that the confusion here is because the AutoNumber field "has alphanumeric format". If the table design looks like this

DesignView.png

then the Format property "ER"000 for the ID field will cause it to appear in Access forms and datasheet views as something like ER001.

DatasheetView.png

However, in Design View (the first screenshot) notice that the field is still an AutoNumber field and its "Field Size" is Long Integer. The values themselves are just numbers; they are merely being formatted as alphanumeric in the Access user interface.

So the behaviour you are seeing in your VB.NET application is "normal". If you run the query

Dim SQL As String = "SELECT ID FROM ReqItemList WHERE ReqItem=?"
Using cmd = New OleDbCommand(SQL, con)
    cmd.Parameters.Add("?", OleDbType.VarWChar).Value = "foo"
    Dim rtn = cmd.ExecuteScalar()
End Using

then you will get the Integer value 1, not the String value "ER001". If you want to have the value appear in your VB.NET forms as ER001 you will need to apply the formatting in your VB.NET code.

Similarly, if you want to search by ID then you will have to supply the unformatted numeric value. That is 

Dim SQL As String = "SELECT ReqItem FROM ReqItemList WHERE ID=?"
Using cmd = New OleDbCommand(SQL, con)
    cmd.Parameters.Add("?", OleDbType.Integer).Value = 1
    Dim rtn As String = cmd.ExecuteScalar()
End Using

will return foo, whereas 

Dim SQL As String = "SELECT ReqItem FROM ReqItemList WHERE ID=?"
Using cmd = New OleDbCommand(SQL, con)
    cmd.Parameters.Add("?", OleDbType.VarWChar).Value = "ER001"
    Dim rtn As String = cmd.ExecuteScalar()
End Using

will fail with "Data type mismatch in criteria expression" because ID is really a number, not a text value.

Upvotes: 3

Related Questions