When Using SQL in VB6 to search a database Error is displayed " Data Type mismatch in criteria expression.?

Question

I have a text box where The invoice number is entered to be used in the search for the record. - txtSearchID


Here is my code-

Private Sub cmdSearchID_Click()

Dim SearchID As String
SearchID = txtSearchID.Text
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\BeanBag.mdb"

Set SalesRS = New ADODB.Recordset
SalesRS.Open "SELECT* FROM Sales Where InvoiceNo = '" & txtSearchID.Text & "'", con, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = SalesRS
End Sub

Please help? When I replace [ Where InvoiceNo = '" & txtSearchID.Text & "'",] with [Where InvoiceNo = '" 000001 & "'",] I obtain the required result, just having an issue with using the TextBox to provide the Search parameter.

Thank you

Answer 1

Hi,



SalesRS.Open "SELECT* FROM Sales Where InvoiceNo = " & trim(txtSearchID.Text) & ", con, adOpenKeyset, adLockOptimistic



Hope it works for you

Answer 2

It can be traditionally a trouble together with your the place clause. You're pronouncing "where 'something' equals 'some thing else'. These two values must be immediately comparable, both given that they are the same data variety (e.G. Each numbers), or they're close enogh for an implicit conversion. Double verify your whole '=' and make certain the 2 objects are the equal data type.

Answer 3

hard step browse over a search engine it can help

Answer 4

you did this at the start

SearchID = txtSearchID.Text

so why not do that again and use SearchID or some other STRING variable

txtSearchID.Text is probably NOT a STRING by TYPE even if it does contain one

Answer 5

txtSearchID.Text is just that - text. You have to convert that string to an integer value. That's why it worked when you simply plugged in 00001.