single quotes in SQL statement

[vbdotnetboy]

if i have a value that has to be insterted into a field in Access but the value has a single quote, ie. Example's. if i write the instert statement the following way would it work?

dim sql as string = "insert into THETABLE (THEFIELD) values(" & """example's""" & ")"

[SeanGrebey]

I usually do replaces on all my SQL statements that don't use parameters just in case.

Replace(strSQL,"'","''") that is one single quote with two single quotes.

[salvelinus]

That's how I do it too.

[Redth]

Might i recommend instead of doing it this way, .NET has a much better way of adding parameters to a sql query.

For instance, try this:

Dim sql as string = "insert into THETABLE (THEFIELD) values (@thefield)"

Dim objCmd As OleDbCommand = New OleDbCommand(sql, objCon)

objCmd.Parameters.Add("@thefield", textbox1.Text)


Notice that if i do it this way i don't have to worry about any single quotes surrounding a string to be insertted into a Text field or anything, or even replacing single quotes that would mess up the sql query. This method does that all for you, and better yet, it even checks for and stops SQL Injection attacks!

This is the new recommended way to do things

[vbdotnetboy]

thanx Redth...did the trick....still learning or converting over to the .NET side of things...thanx again

[MrGTI]

That was an INSERT statement. How do you do it when it's a SELECT statement? For example,...

SELECT * FROM tbl_One WHERE Field = 'userdatehere';

The SQL statement is passed into a SqlDataAdapter when created, so you can't use the SqlCommand object. So how do you use SqlParameter method with a SELECT statement?

[TLord]

Guys guys,
when you use SQl server, OLEDB objects are irrelevant.
This is the perfect code for adding parameters (assuming that you previously declared SqlConnection(Conn) and SqlDataAdapter(DAdap) objects, in order to use these references you MUST import the System.Data.SqlClient Class):

DAdap.InserCommand = New SqlCommand("insert into THETABLE (THEFIELD) values (@Val)", Conn)
Dim Prm as SqlParameter = DAdap.InsertCommand.Parameters.Add(New SqlParameter("@Val", SqlDbType.Char))
Prm.SourceColumn = "THEFIELD"
Prm.SourceVersion = DataRowVersion.Current