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

Answer 1

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