INSERT INTO statement

[buddyel]

I am trying to find the proper syntax for my INSERT INTO statement. I am trying to take three variables (part,order,cust) and add them to a table (tblHistory) in the fields (partno,orderno,cust). This is what my code looks like so far...

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
Dim sConnString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\MSTRHIST.mdb;" & _
"User ID=Admin;" & _
"Password="
Dim oOleDbConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(sConnString)
Dim objCommand As New OleDb.OleDbCommand()
Dim strSQL As String

strSQL = "INSERT INTO tblHistory (catno, wo, cust_name) VALUES (part, work, cust)"


oOleDbConnection.Open()
With objCommand
.Connection = objDBConnection
.CommandText = strSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
objDBConnection.Close()

oOleDbConnection = Nothing
objCommand = Nothing
End Sub



I know the code opens the database, but after that, it doesnt do add anything. Any suggestion would be greatly appreciated. Thanks

 

[CookieNZ]

strSQL = "INSERT INTO tblHistory (catno, wo, cust_name)" & " VALUES ('" & part & "','" & work & "','" & cust &"')"

If part is the name of the text field it should be part.text, work.text and cust.text

 

[buddyel]

Thank you so much, that seems to have done it. Now, do you know how I could code it to check the catno column before saving a new record to make sure the number does not already exists, and if it does prompt the user to overwrite?

 

[Tarwn]

To do a read ahead for duplicate values, you can simply execute a seperate query ahead of time like thus:

"SELECT * FROM tblHistory WHERE catno = '"&part&"'"

Then take your resulting recordset and check if it is EOF (or record count < 1), if so then your value is not already in the database.

-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[link9]

If you just want a record count, then the .executeScalar() method of the command object is your best bet. It returns a 1X1 result set (i.e. a number or string), so there's no need for a dataset, datatable, or even a datareader.

SELECT COUNT(*) FROM ....

 

[sjn78]

I know this was posted a fair while ago, but it has solved all of my problems. Thanks