How to insert a row to MS Sql

[Guest]

HI,
I am developing a Smart Device app to run on a handheld scanner. I can
SELECT rows with the following:

Dim mySelectQuery As String = "SELECT ProductName FROM Products
Where ProductID = " & Me.txtDcdData.Text
Dim myConnection As New SqlConnection("User
ID=sa;Password=xxxg;Initial Catalog=PSC;Data Source=CSIREPL;")
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()

myReader.Close()
myConnection.Close()

I need to INSERT a row, I can't find how to do that.
Can anyone help please?
Thanks

 

[Darren Shaffer]

SqlCommand.ExecuteNonQuery()

 

[Guest]

Hello,
I get an error on the close when I tried that, I had

Dim mySelectQuery As String = "INSERT INTO tblInventory (ItemID,
Quantity) VALUES(Me.txtDcdData.Text, 10)"
Dim myConnection As New SqlConnection("User
ID=sa;Password=xxg;Initial Catalog=PSC;Data Source=CSIREPL;")
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

On the Close() I get,

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in System.Data.SqlClient.dll

Steve

 

[Guest]

Darren,
Your post was correct, my problem is with the Me.txtDcdData.text not having
a value, when I hardcoded a value in for ItemID, the Insert worked.

Guess I really need to know how to check the SQL code after the call to see
what the error is.

Thanks,
Steve

 

[Guest]

Ha! I was wrong again, Me.txtDcdData.text does have the value from the
field. I debugged it, and it has a "4" in it. When I hardcode a 4 in the
INSERT statement, it works fine, but when I have me.txtDcdData.Text there, it
fails. I have changed the table column from both type numeric and char,
fails both times

any idea?

 

[Darren Shaffer]

the issue is you are not qualifying the value of your textbox in your
SQL statement.

Dim mySelectQuery As String = "INSERT INTO tblInventory (ItemID,
Quantity) VALUES(Me.txtDcdData.Text, 10)"

needs to become

Dim mySelectQuery As String = "INSERT INTO tblInventory (ItemID,
Quantity) VALUES(' + Me.txtDcdData.Text + ', 10)"

 

[Darren Shaffer]

let me try that again - here is the correct syntax:

Dim mySelectQuery As String = "INSERT INTO tblInventory (ItemID,
Quantity) VALUES('" + Me.txtDcdData.Text + "', 10)"

 

[Guest]

Yes, you are correct!! Thank you very much!

let me try that again - here is the correct syntax:

Dim mySelectQuery As String = "INSERT INTO tblInventory (ItemID,
Quantity) VALUES('" + Me.txtDcdData.Text + "', 10)"