data types

hey all hope u can help me wif this prob

ok i got a variable

Code:

---

Dim intID As Integer

---

then i put a value in it

Code:

---

intID = lvwContacts.SelectedItem.Text

---

then i open a database lookin for that value

Code:

---

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

Code:

---

intID = CInt(lvwContacts.SelectedItem.Text)

---

but i stil get the same error

can some1 plz help

oh also if i do

Code:

---

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

---

put a number instead of variable it works

plz help

tnx :)

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

Code:

---

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.

.

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

Quote:

---

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 :rolleyes:

.

ty all for yor help

removing the single quotes fixed it

tnx :)

It should be:

VB Code:

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