data types

[matrixau]

hey all hope u can help me wif this prob

ok i got a variable

Dim intID As Integer

then i put a value in it

intID = lvwContacts.SelectedItem.Text

then i open a database lookin for that value

RS.Open "Select * from tblPcontacts where id='" & intID & "'", CN, adOpenStatic, adLockOptimistic

NOW

the problem i am having is:

"Data type mismatch in criteria expression"

now the data type in the database is "auto number"

i have tried doing this

intID = CInt(lvwContacts.SelectedItem.Text)

but i stil get the same error

can some1 plz help

oh also if i do

RS.Open "Select * from tblPcontacts where id=1", CN, adOpenStatic, adLockOptimistic

put a number instead of variable it works

plz help

tnx

[honeybee]

Just get rid of the single quotes in your query string.

RS.Open "Select * from tblPcontacts where id = " & intID, CN, adOpenStatic

Also ideally you should declare the intID variable as String, because you are storing a Text property to it.

.

[boylesw]

try this....

[CODE]
Dim strID as string
strID = lvwContacts.SelectedItem.Text
RS.Open "Select * from tblPcontacts where id= '" & intID & "' ORDER BY id", CN, adOpenStatic, adLockOptimistic

[honeybee]

Originally posted by boylesw
try this....

[CODE]
Dim strID as string
strID = lvwContacts.SelectedItem.Text
RS.Open "Select * from tblPcontacts where id= '" & intID & "' ORDER BY id", CN, adOpenStatic, adLockOptimistic

That will fail, because Autonumber fields are of Long Integer data type, at least in Access. When you include the single quotes in your query around the ID variable's value, the query will interpret it as a string value, because in SQL a string value is delimited in single quotes. Since the Autonumber really is a long integer, using a string value for comparison will produce the Data Type Mismatch error.

Also you are using strID in the declaration, but intID in the query

.

[matrixau]

ty all for yor help

removing the single quotes fixed it

tnx

[Wokawidget]

It should be:

Dim lngID   As Long
 
   lngID = CLng(lvwContacts.Selectedtem.Text)

If ID is an int in the database, then in fact it's a LONG in VB, not an interger.
Just because you store the varible in a text field does not mean you define it as strID As String, very bad, since it is actually a long...

Woka