Erica Erica - 3 months ago 16
Vb.net Question

INSERT Query with Parameters in VS2010

I do not understand why it's not inserting to my database whenever I include an apostrophe in my

txtParticulars.Text
and
txtPayTo.Text
.

The error is this:
Syntax error (missing operator) in query expression ''Joy's Boutique','Top's,'Issued')'.


My textbox values are:

txtPayTo.Text
> Joy's Boutique
txtParticulars
> Top's
cmbRemarks.SelectedItem
> Issued

But whenever my
txtParticulars
and
txtPayTo
values does not have an apostrophe, my data saves.

The following is my code:

sql1 = "INSERT INTO Table1(Check_No,Voucher_No,Issue_Date,Company_Name,Bank_Type,Amount_in_Figure,Amount_in_Words,Payee,Particulars,Remarks) VALUES(@CheckNo,@VoucherNo,@Date,@CompName,@BankType,@AmtInFigure,@AmtInWords,@PayTo,@Particulars,@Remarks)"

Dim cmd1 As OleDbCommand = New OleDbCommand(sql1, myConnection)

cmd1.Parameters.AddWithValue("@CheckNo", txtCheckNo.Text)
cmd1.Parameters.AddWithValue("@VoucherNo", txtVoucherNo.Text)
cmd1.Parameters.AddWithValue("@Date", dtpDate.Text)
cmd1.Parameters.AddWithValue("@CompName", txtCompName.Text)
cmd1.Parameters.AddWithValue("@BankType", txtBankType.Text)
cmd1.Parameters.AddWithValue("@AmtInFigure", txtAmtInFigure.Text)
cmd1.Parameters.AddWithValue("@AmtInWords", txtAmtInWords.Text)
cmd1.Parameters.AddWithValue("@PayTo", txtPayTo.Text)
cmd1.Parameters.AddWithValue("@Particulars", txtParticulars.Text)
cmd1.Parameters.AddWithValue("@Remarks", cmbRemarks.SelectedItem)

cmd1.ExecuteNonQuery()

Answer

Use Add instead of AddWithValue.
The latter has to guess the correct database type by the value passed in.
The Add method is more reliant on that (as long as you donĀ“t use the Add(string, object) overload).

Based on your example:

cmd1.Parameters.Add("@PayTo", SqlDbType.Varchar)
cmd1.Parameters("@PayTo").Value = txtPayTo.Text

or as one line (thanks to Plutonix):

cmd1.Parameters.Add("@PayTo", SqlDbType.Varchar).Value = txtPayTo.Text