problem with SQL

[Goomba79]

Hello (again)

I have a problem with my SQL, all the other statements i have written work so I can't understand why this one isn't!!

the sql is...
sql = "Select * From Skills Where StampNo = " & Stamno & " Cell = " & cell & " And Op-Number = '" & op & "' "

i get this error...
Syntax error (missing operator) in query expression 'StampNo = Test25 Cell = E&M And Op-Number = '80''.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'StampNo = Test25 Cell = E&M And Op-Number = '80''.

I can't see what i have got wrong!!

if anyone an see what I have done wrong it would be much appreciated!

Thanks,

Iain

[hericles]

It looks like you are missing an operator (AND or OR) in your statement.

sql = "Select * From Skills Where StampNo = " & Stamno & " Cell = " & cell & " And Op-Number = '" & op & "' "

This translates to SELECT * FROM skills WHERE StampNo = someValue Cell = cellValue AND Op-Number = opValue.
So you can see you're missing an operator between somevalue and Cell.

[Goomba79]

hello,
Thanks that has got rid of that error

but now i am getting the following error

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:

Line 20: Dim DS As System.Data.DataSet = New System.Data.DataSet
Line 21: 'fill data set with returned data
Line 22: DA.Fill(DS)
Line 23:
Line 24:

I have the code for selecting data in a function that i use various other times and it works fine
so why would it now be failing (all other times the function is called it runs correctly)

If there was no data being returned would i see this error?
but there should be returned data as I am making sure that I am selecting things I know are there!

[Goomba79]

ok so here is my function that i am using to talk to the database

    Function SelectData(sql As String, con As System.Data.IDbConnection) As Data.DataSet

        'create new database command
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        'give command string value of sql vairiable
        dbCommand.CommandText = sql
        'what database to connect to
        dbCommand.Connection = con


        'create new Data Adapter
        Dim DA As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        'connect to the datbase and run sql query
        DA.SelectCommand = dbCommand
        'create new data set to hold rturned data
        Dim DS As System.Data.DataSet = New System.Data.DataSet
        'fill data set with returned data
        DA.Fill(DS)

        Return DS

    End Function

it will work with an sql command like " select * from skills where StampNo = " & stamno & " "

but if i try searching by 2 parameters ie " select * from skills where StampNo = " & stamno & " And cell = " & electromech &" " it fails at the point in my previous post

[Goomba79]

I changed the name of some of my vairables and column names and it now works!

sql = "Update Skills Set Competence = " & comp & " AND DateAchieved = '" & Adate & "' Where RecordNo = " & record & " "