Trouble with parameterized INSERT

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using parameters to preform an INSERT. This is my first time. Here is
what I have:

Dim myConnection As New SqlConnection("User ID=sa;Password=sappkg;Initial
Catalog=PSC;Data Source=CSIREPL;")
Dim insertCommand As SqlCommand = myConnection.CreateCommand

insertCommand.CommandText = "INSERT INTO tblInventory (ItemNum,
Quantity) VALUES(?,?)"

insertCommand.Parameters.Add(New SqlParameter("ItemNum",
SqlDbType.NText, 20))
insertCommand.Parameters.Add(New SqlParameter("Quantity",
SqlDbType.NText, 10))

insertCommand.Parameters("ItemNum").Value = Me.txtDcdData.Text
insertCommand.Parameters("Quantity").Value = Me.txtQuantity.Text

Try
myConnection.Open()
insertCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As SqlException
MessageBox.Show("Unable to open connection. Reason: " &
ex.Errors(0).Message)
Exit Sub
End Try

When I run it, it throws an exception that says,

"Incorrect syntax near 'ItemNum'.

I can't get it figured out, any help?

Thanks,
Steve
 
You should use parameter names with SQL Server. Something like this:

insertCommand.CommandText = "INSERT INTO tblInventory (ItemNum, Quantity)
VALUES(@ItemNum, @Quantity)"
insertCommand.Parameters.Add(New SqlParameter("@ItemNum", SqlDbType.NText,
20))
...

You should be able to use desktop samples with SQL Client.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
Ilya,

What did you mean about "desktop samples"?

Thanks again,
Steve

"Ilya Tumanov [MS]" said:
You should use parameter names with SQL Server. Something like this:

insertCommand.CommandText = "INSERT INTO tblInventory (ItemNum, Quantity)
VALUES(@ItemNum, @Quantity)"
insertCommand.Parameters.Add(New SqlParameter("@ItemNum", SqlDbType.NText,
20))
...

You should be able to use desktop samples with SQL Client.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: Trouble with parameterized INSERT
thread-index: AcU1dcZN+GQ6BbzgTxmr/XuIOj4XLQ==
X-WBNR-Posting-Host: 64.72.45.245
From: "=?Utf-8?B?U3RldmVJbkJlbG9pdA==?="
Click to expand...
Subject: Trouble with parameterized INSERT
Date: Wed, 30 Mar 2005 14:14:00 -0800
Lines: 39
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.dotnet.framework.compactframework:74519
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi,

I am using parameters to preform an INSERT. This is my first time. Here is
what I have:

Dim myConnection As New SqlConnection("User ID=sa;Password=sappkg;Initial
Catalog=PSC;Data Source=CSIREPL;")
Dim insertCommand As SqlCommand = myConnection.CreateCommand

insertCommand.CommandText = "INSERT INTO tblInventory (ItemNum,
Quantity) VALUES(?,?)"

insertCommand.Parameters.Add(New SqlParameter("ItemNum",
SqlDbType.NText, 20))
insertCommand.Parameters.Add(New SqlParameter("Quantity",
SqlDbType.NText, 10))

insertCommand.Parameters("ItemNum").Value = Me.txtDcdData.Text
insertCommand.Parameters("Quantity").Value = Me.txtQuantity.Text

Try
myConnection.Open()
insertCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As SqlException
MessageBox.Show("Unable to open connection. Reason: " &
ex.Errors(0).Message)
Exit Sub
End Try

When I run it, it throws an exception that says,

"Incorrect syntax near 'ItemNum'.

I can't get it figured out, any help?

Thanks,
Steve
Click to expand...
Click to expand...
 
That was it!! Thank you very much!
Steve


"Ilya Tumanov [MS]" said:
You should use parameter names with SQL Server. Something like this:

insertCommand.CommandText = "INSERT INTO tblInventory (ItemNum, Quantity)
VALUES(@ItemNum, @Quantity)"
insertCommand.Parameters.Add(New SqlParameter("@ItemNum", SqlDbType.NText,
20))
...

You should be able to use desktop samples with SQL Client.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: Trouble with parameterized INSERT
thread-index: AcU1dcZN+GQ6BbzgTxmr/XuIOj4XLQ==
X-WBNR-Posting-Host: 64.72.45.245
From: "=?Utf-8?B?U3RldmVJbkJlbG9pdA==?="
Click to expand...
Subject: Trouble with parameterized INSERT
Date: Wed, 30 Mar 2005 14:14:00 -0800
Lines: 39
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.dotnet.framework.compactframework:74519
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi,

I am using parameters to preform an INSERT. This is my first time. Here is
what I have:

Dim myConnection As New SqlConnection("User ID=sa;Password=sappkg;Initial
Catalog=PSC;Data Source=CSIREPL;")
Dim insertCommand As SqlCommand = myConnection.CreateCommand

insertCommand.CommandText = "INSERT INTO tblInventory (ItemNum,
Quantity) VALUES(?,?)"

insertCommand.Parameters.Add(New SqlParameter("ItemNum",
SqlDbType.NText, 20))
insertCommand.Parameters.Add(New SqlParameter("Quantity",
SqlDbType.NText, 10))

insertCommand.Parameters("ItemNum").Value = Me.txtDcdData.Text
insertCommand.Parameters("Quantity").Value = Me.txtQuantity.Text

Try
myConnection.Open()
insertCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As SqlException
MessageBox.Show("Unable to open connection. Reason: " &
ex.Errors(0).Message)
Exit Sub
End Try

When I run it, it throws an exception that says,

"Incorrect syntax near 'ItemNum'.

I can't get it figured out, any help?

Thanks,
Steve
Click to expand...
Click to expand...
 
I mean whatever SQLClient code you can find for a big framework should also
work on CF.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: Trouble with parameterized INSERT
thread-index: AcU1fOwSDUYgYpwMT7OHEJafhPYf8Q==
X-WBNR-Posting-Host: 64.72.45.245
From: "=?Utf-8?B?U3RldmVJbkJlbG9pdA==?="
Click to expand...
 
Back
Top