Data Type Mismatch In Criteria Expression [help]

[hawisme000]

this code is from another form before i get to the formload below

xNum = 2
adminlec.show
unload me

Private Sub Form_Load()



lblLECNUM.Caption = xNUM '<----this is a public dim that i wrote on a module as integer 

Set cnn = New ADODB.connection
    Set rs = New ADODB.Recordset
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MAINDB2.mdb" & ";Persist Security Info=False"
    cnn.CursorLocation = adUseServer
    cnn.Open
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseClient
    rs.Source = ("SELECT * from tblLecture where arlNumber = '" & lblLECNUM & "' ")
  
    rs.ActiveConnection = cnn
    

rs.Open '<---------THIS IS HIGHLIGHTED WHEN I GOT THE ERROR
  
  rs.Update
  
  If rs.BOF = True Or rs.EOF = True Then
        
        rs.MoveNext
        Exit Sub
        'If the pointer is at the end of the recordset of
        'at the before the first record, exit the sub and
        'show no data.
    End If

RtxtLec.Text = rs.Fields("arlLec").Value & ""
txtNum.Text = rs.Fields("arlNumber").Value & ""
txtPart.Text = rs.Fields("arlPart").Value & ""


End Sub

there's my code, im having a error "DATA TYPE MISMATCH IN CRITERIA EXPRESSION "
but i cnt and no idea where it is.... and im still finding it

i'm performing to have a connection to my database and as you see my query below
im getting the value of lblLECNUM(which is 2)

("SELECT * from tblLecture where arlNumber = '" & lblLECNUM & "' ")

pls help me!! getting dizzy solving this

[SCBWV]

Just a guess... could it be the field is numeric, where your selection criteria (lblLECNUM ) is text (Caption property)?

[hkdani]

("SELECT * from tblLecture where arlNumber = '" & lblLECNUM & "' ")

SELECT * from tblLecture WHERE alrNumber=" & cstr(lblLECNUM)

Don't think that will make much difference. But it's a little clearer.

there's my code, im having a error "DATA TYPE MISMATCH IN CRITERIA EXPRESSION "
but i cnt and no idea where it is.... and im still finding it

That particular error usually pops up when you have a NULL value coming from your form. Try using an error checking code before you run the SEQUEL statement.

If lblLECNUM.Caption <> vbNullString then
    ' Execute Code for Database
Else
    ' Do not execute Code for Database
Endif

[hawisme000]

@hkdani

ive tried what you have said to me, and it doest hav a null value

BUT,

maybe beacause of extreme LUCK ive manage to solve my problem

my query

("SELECT * from tblLecture where arlNumber = '" & lblLECNUM & "' ")

is the one with error

how i solve ??

i accidentally erase a part of it and became like this

("Select * From tblLecture where arlNumber = " & txtARALNUMBER & " ")

and it works!!!

BUT another BUT.. do you know y it work?? and why does my 1st query wrong???
(need aditional info so that i wont make this error again really this kind of error sucks)

btw ThX for the HELP!!

[hkdani]

BUT another BUT.. do you know y it work?? and why does my 1st query wrong???

It might be the single quotation marks. Try it with and without them.

Hank

[SCBWV]

Single quotes are need with text, not numbers.

[joolyan]

I also have a similar problem, it goes like this
my code is something like this
rs.open"update rented set balance='" &"0"& where transactionid='" &txtid.text& "'" adopendynamic adlockoptimistic
txtid contains a value e.g 62
and 0 is an integer
the same code works with text if the fields are text
what might be the problem?
please assist
thank you.

[hkdani]

the same code works with text if the fields are text
what might be the problem?
please assist
thank you.

It's not the greatest practice to rely on VB6 to convert your variables from one type to another: i.e. text to integer, text to long, long to text, etc.

That's one reason you should set up your code in the options to use Option Explicit or in the Tools Menu choose Options, and in the Editor Tab make sure the Require Variable Declaration checkbox is checked.

If you're working with text boxes or string values, then it's good practice to go ahead and convert those values to the type that you are expecting to use in your database field before assigning them to a field.

Dim intValue as Integer
if isNumeric(txtValue.text) then
[INDENT]intValue = cInt(txtValue.text) [/INDENT]
else
[INDENT]intValue = 0[/INDENT]
End if
rst.Fields("PartNumber") = intValue

You also might want to include some type checking code as above in areas where it might occur that an incorrect type may be used in a field that expects a different value. And as always, it wouldn't be a bad idea to have error checking code in each procedure to handle errors such as you mentioned.

Private sub FillData()
On error goto  FillData_ERROR
...
...
...
Exit sub

FillData_ERROR:
if err.number = 15 ' Data type mismatch
   msgBox "Incorrect Data Type", vbCritical, "Error Message"
else
    debug.print err.description, err. number, err.source
endif
Resume Next

End sub()