INSERT Query with Parameters in VS2010

Question

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()

Please edit your question to tell us *which* TextBox, and if you get an error message then include the text of that too. — Andrew Morton, Sep 06 '16 at 10:22
Use `Add` instead of `AddWithValue`. Like `cmd1.Parameters.Add("@PayTo", SqlDbType.Varchar) cmd1.Parameters("@PayTo").Value = txtPayTo.Text` — Alex B., Sep 06 '16 at 10:55
you can `Add` in one line: `cmd1.Parameters.Add("@PayTo", SqlDbType.Varchar).Value = txtPayTo.Text` — Ňɏssa Pøngjǣrdenlarp, Sep 06 '16 at 14:43
OMG it worked!!! Thank you so much mister!! :D I wish I could vote tho. But thanks a lot! :) @AlexB. — Erica, Sep 07 '16 at 02:55
No problem. But you could accept my answer by clicking on the arrow on the left — Alex B., Sep 08 '16 at 08:28
I did but it fades and says that they require 15 reputation so i can vote for an answer :) @AlexB. — Erica, Sep 08 '16 at 08:29
[How to accept an answer](http://meta.stackexchange.com/a/5235). You don't need any rep to accept. — Alex B., Sep 08 '16 at 08:35
oh sorry. I thought you meant the arrow. Sorry mister, I just accepted it. Thanks so much! :) @AlexB. — Erica, Sep 08 '16 at 08:39
Yes sorry I meant the mark. You're welcome glad to help you. — Alex B., Sep 08 '16 at 08:41

score 0 · Accepted Answer · answered Sep 07 '16 at 12:03

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

INSERT Query with Parameters in VS2010

1 Answers1