Syntax error

Dear All,

I have the following code:

Imports System.Data.OleDb

Public Class frmBrucS19Calc
Inherits System.Windows.Forms.Form

Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=|DataDirectory|\VaccineInfo.accdb;")

Dim ID As Integer = 0

Dim Ds As New DataSet()
Dim Nextb As Long
Dim PrevB As Long
Dim TotalC As Long
Dim dt As New DataTable("tblBrucellaS19")
Dim rs As OleDb.OleDbDataAdapter

Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click


con.Open()

Dim rs As New OleDb.OleDbCommand(
"INSERT INTO tblBrucellaS19 (MFCSBatchNo, FinalBatchNo, Doses, TASBatchNo) VALUES ('" & txtMFCS.Text & "','" & txtFinBatchNum.Text & "','" &
txt220.Text & "','" & txtTASBatch.Text & ")", con)

rs.ExecuteNonQuery()
con.Close()
LoadGrid("")


End Sub


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I get the following error after clicking cmdsave to insert data into the database:

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217900
Message=Syntax error in string in query expression ''80)'.
Source=Microsoft Office Access Database Engine

Here is something obvious wrong, yet I cannot see it....''80)' refers to the value of txtTASBatch.Text. It is supposed to be "80".

Any ideas?

TIA

If you step through the code in a debugger what does the sql statement for the OleDbCommand look like before it is executed?

Rather than concatenate strings to generate a sql statement you might want to look at using a parametrised query as this can both make this kind of error less likely and give a more secure system.

Dear PlausiblyDamp,

Thank you for your reply. I have rewritten the code to exclude concatenation (I think):

Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click

Dim add1 As String
Dim add2 As String
Dim add3 As String
Dim add4 As String
Dim add5 As String

add1 = txtMFCS.Text
add2 = txtFinBatchNum.Text
add3 = txt220.Text
add4 = txtTASBatch.Text
add5 = txtCMC.Text

con.Open()

Dim rs As New OleDb.OleDbCommand(
"INSERT INTO tblBrucellaS19 (MFCSBatchNo, FinalBatchNo, Doses, TASBatchNo,CMCBatchNo) VALUES (add1,add2,add3,add4,add5, con)")

rs.ExecuteNonQuery()
con.Close()
LoadGrid("")

End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

This error appears at line rs.ExecuteNonQuery()

System.InvalidOperationException was unhandled
Message=ExecuteNonQuery: Connection property has not been initialized.
Source=System.Data

I have also added a screen shot of [Dim rs As New OleDb.OleDbCommand]

TIA

The line

Dim rs As New OleDb.OleDbCommand(
"INSERT INTO tblBrucellaS19 (MFCSBatchNo, FinalBatchNo, Doses, TASBatchNo,CMCBatchNo) VALUES (add1,add2,add3,add4,add5, con)")


isn't doing what you think it is doing unfortunately. You can't just include VB variables inside a SQL string like that.

You might want to take a look at Atma's response in http://www.xtremevbtalk.com/showthread.php?t=314548

Dear PlausiblyDamp,

Thank you for your patience! It is working now.

Here is what I did:

Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click

Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString, strSQL As String

strConnString = ("Provider=Microsoft.ACE.OLEDB.12.0;data source=|DataDirectory|\VaccineInfo.accdb;")

objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()


strSQL = "INSERT INTO tblBrucellaS19 (MFCSBatchNo,FinalBatchNo,Doses,TASBatchNo,CMCBatchNo,FinalPCV) " & "VALUES (@sMFCSBatchNo,@sFinalBatchNo,@sDoses,@sTASBatchNo,@sCMCBatchNo,@sFina lPCV)"


objCmd = New System.Data.OleDb.OleDbCommand(strSQL, objConn)


With objCmd
.Parameters.Add(New OleDbParameter("@sMFCSBatchNo", "A11058"))
.Parameters.Add(New OleDbParameter("@sFinalBatchNo", "663"))
.Parameters.Add(New OleDbParameter("@sDoses", "20s"))
.Parameters.Add(New OleDbParameter("@sTASBatchNo", "38"))
.Parameters.Add(New OleDbParameter("@sCMCBatchNo", "18888"))
.Parameters.Add(New OleDbParameter("@sFinalPCV", txtPCVAfterTAS.Text))
End With

objCmd.ExecuteNonQuery()

objCmd = Nothing
objConn.Close()
objConn = Nothing


MsgBox("Record Insert Sucessful.")

End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

In your first post, your problem is that you are not completing the SQL syntax correctly. You are not closing the final text-tag:

,'" & txtTASBatch.Text & ")", con)


should be:

,'" & txtTASBatch.Text & "')", con)

The only difference is the ' missing in your code, just before the ). See it? Might be hard to spot... ;)