SQl syntax error for search button

[rahul8590]

I have a search a particular filename in my msaccess file .
if i search through ID no , i am able to fetch the output . but if i am searching by comparing filenames then i am getting error

Private Sub cmdSearch_Click()
    Dim key As String, str As String
    key = InputBox("Enter the Employee No whose details u want to know: ")
    Set rs = Nothing
    str = "select * from emp where Filename=  &key "   // my search query is          //giving problems 
    rs.Open str, adoconn, adOpenForwardOnly, adLockReadOnly
    txtNo.Text = rs(0)
    txtName.Text = rs(1)
    txtCity.Text = rs(2)
    Set rs = Nothing
    str = "select * from emp"
    rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
End Sub

[PsychoCoder]

This is not VB.NET code, it appears to be VB6 code. Mods is there a way to move this to the VB forum?

[JRitchie777]

It may be VB6, but if that really is his code then he would be getting an error where he sets str=... He is using C language comment delimiters, not VB. VB will try and interpret that and will give an error.
JR

[PsychoCoder]

I never even noticed that, I just noticed right off the bat that it wasnt VB.NET code. But you're right, he's got C/C++/C# comments with VB6 code, which is going to bomb whenever they try to run it

[rahul8590]

k guys . i do apologies that i posted in wrong forum . i guess you would have known that i am a total newbie to VB . i am not able to get the query right . it will e of great help if i can get string matching happen so i can fetch the corresponding data .

[AndreRet]

Your problem lies in your string syntax -

str = "select * from emp where Filename= &key "

Try -

str = "select * from emp where Filename=" & "'" & key & "'", conn, AdOpenStatic, AdLockOptimistic

This should return the values you are looking for.