Insert command incorrect syntaz near '?'

S

sparkle

Hi everyone,

I have successfully used this same command in msAccess using an
oledbCommand, but can't get it to work in sqlServer 2000.

I have simplified everything down to one column to fill just to see
where I am going wrong.

Here is my code for loading the form:

dbCONN.Open()
With daPermits
SQLStr = "SELECT * FROM Permits"
cmd = New System.Data.SqlClient.SqlCommand(SQLStr, dbCONN)
cmd.CommandType = CommandType.Text
.SelectCommand = cmd
.Fill(dsPermits)
.Dispose()
End With
dsPermits.AcceptChanges()
PermitsTable = dsPermits.Tables.Item(0)
dsPermits.Dispose()
dbCONN.Close()

'''''then I have the user click an add button, which clears the form,
then the user types a permitnumber into a textbox and clicks SAVE.

Here is the code for the SAVE:
Sub UpdateDB()
cmdInsertPermits.Parameters("@param1").Value =
txtPermitNumber.Text
dbCONN.Open()

Try
cmdInsertPermits.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Clipboard.SetDataObject(ex.ToString, True)
End Try
dbCONN.Close()
DsPermits.Clear()

LoadForm()
End Sub

Here is the code for the cmdInsertPermits:
INSERT INTO Permits (PermitNumber) VALUES (?)

What gives? I can see that the data is noticed in the autos window, so
why do I get the incorrect syntaz near '?'

Also, it works when I configure the cmdInsertPermits control...

Thanks for any help
 
M

Marina

The sql server SqlClient wrapper does not support unnamed parameters in the
form of question marks. You must give parameters names such as
@permitNumber, etc.

sparkle said:
Hi everyone,

I have successfully used this same command in msAccess using an
oledbCommand, but can't get it to work in sqlServer 2000.

I have simplified everything down to one column to fill just to see
where I am going wrong.

Here is my code for loading the form:

dbCONN.Open()
With daPermits
SQLStr = "SELECT * FROM Permits"
cmd = New System.Data.SqlClient.SqlCommand(SQLStr, dbCONN)
cmd.CommandType = CommandType.Text
.SelectCommand = cmd
.Fill(dsPermits)
.Dispose()
End With
dsPermits.AcceptChanges()
PermitsTable = dsPermits.Tables.Item(0)
dsPermits.Dispose()
dbCONN.Close()

'''''then I have the user click an add button, which clears the form,
then the user types a permitnumber into a textbox and clicks SAVE.

Here is the code for the SAVE:
Sub UpdateDB()
cmdInsertPermits.Parameters("@param1").Value =
txtPermitNumber.Text
dbCONN.Open()

Try
cmdInsertPermits.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
Clipboard.SetDataObject(ex.ToString, True)
End Try
dbCONN.Close()
DsPermits.Clear()

LoadForm()
End Sub

Here is the code for the cmdInsertPermits:
INSERT INTO Permits (PermitNumber) VALUES (?)

What gives? I can see that the data is noticed in the autos window, so
why do I get the incorrect syntaz near '?'

Also, it works when I configure the cmdInsertPermits control...

Thanks for any help
Click to expand...
 
J

Jon Skeet [C# MVP]

sparkle said:
I have successfully used this same command in msAccess using an
oledbCommand, but can't get it to work in sqlServer 2000.
Click to expand...

Are you still using OleDbCommand, or are you now using SqlCommand? If
it's the latter, you should have @param1 in your SQL string rather than
? - you need named parameters rather than just positional ones.
 
C

Cor Ligthert

Hi Jon,
Are you still using OleDbCommand, or are you now using SqlCommand? If
it's the latter, you should have @param1 in your SQL string rather than
? - you need named parameters rather than just positional ones.
Click to expand...

This sentence in the message from sparkle shows that the SqlCommand is used.

I am glad that I could clear that for you.

Cor
 
J

Jon Skeet [C# MVP]

Cor Ligthert said:
This sentence in the message from sparkle shows that the SqlCommand is used.
Click to expand...

Whoops - I looked for a declaration, but failed to find it...

The use of positional (rather than named) parameters is almost
certainly the problem then.
 
Top