How to insert a row to MS Sql

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

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
 
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
 
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
 
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?
 
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)"
 
let me try that again - here is the correct syntax:

Dim mySelectQuery As String = "INSERT INTO tblInventory (ItemID,
Quantity) VALUES('" + Me.txtDcdData.Text + "', 10)"
 
Yes, you are correct!! Thank you very much!


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

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