ADO.NET VB2008 - Adding Text with ' in it.

[Wesley008]

Hi. I'm successfully adding Last Names to my List and Database but when I come across a Last or First Name with the Character ' in it, It tends to error out. Here's my code for the SQL Statement.

SQL = "INSERT INTO [Course_" & CourseName & "] (LastName, FirstName, Gender, HomeRoom, StudentNumber) VALUES ('" & DR("Last Name") & "','" & DR("First Name") & "','" & TempGender & "','" & DR("Home Room/Form") & "','" & DR("Special Use") & "')"
                        Command = New OleDbCommand(SQL, Connection)
                        Command.Connection.Open()
                        Command.ExecuteNonQuery()
                        Command.Connection.Close()

[Jenner]

You need to use a Parameter. Parameters don't care about ' or any other special characters.

Here's how:

                              SQL = "INSERT INTO [MyTable] (FirstName) VALUES (?)"
                              Command = New OleDbCommand(SQL, Connection)
                              Command.Parameters.AddWithValue("FirstName", DR("First Name"))
                              Command.Connection.Open()
                              Command.ExecuteNonQuery()
                              Command.Connection.Close()

I assume you're using Access as your database. Go HERE for examples. You REALLY REALLY want to use Parameters. Stringing together an SQL string is a time-bomb for bad things to happen. The only thing special with Access databases is the ORDER you have your parameters in your SQL MUST be the same order you call them in the code below.

[Wesley008]

Should I be doing this for all my Statements?

[Wesley008]

Hmm, Oki, New Problem with the statement I used:

Heres my Code:

SQL = "INSERT INTO [Course_" & CourseName & "] (Gender, HomeRoom, StudentNumber) VALUES ('" & TempGender & "','" & DR("Home Room/Form") & "','" & DR("Special Use") & "')"
                        Command = New OleDbCommand(SQL, Connection)
                        Command.Parameters.AddWithValue("LastName", DR("Last Name"))
                        Command.Parameters.AddWithValue("FirstName", DR("First Name"))
                        Command.Connection.Open()
                        Command.ExecuteNonQuery()
                        Command.Connection.Close()

When I exit out of the Program & then Open it back Up, It then Loads up the Data in the DB File, But for some reason, their all

,

And For that Reason, its because its not Adding to Database, It adds everything else except the first and last name

[Jenner]

Yes. Absolutely. Definitely.

Only amateur programmers don't use parameters in their statements. I can't stress this enough. Most new database programmers start off exactly like you did (I know I did as well!) because it seems to work just fine. Then, they get someone trying to put in a ' or a \ or something even worse like japanese text and the whole thing blows up on them and they realize they have 300 SQL statements that are ticking timebombs to a bad character.

Parameters fix all that. They make the system idiot-proof. Also, they're more secure since they make the database call less hackable (there are methods known as SQL injection to hack a database that doesn't use parameters).

So yup, you should definately be using parameters for all your statements.

[Jenner]

You forgot the ?. The ? symbol tells Access "Parameter goes here".

Wherever you have a " & blahstring & ", replace it with a ?. Then add a parameter for that position below.

If you got 10 things, you'll have an SQL looking like: ..."VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
and 10 Parameters.AddWithValue(name, value) below.

[Wesley008]

Number of query values and destination fields are not the same.

With this Code:

SQL = "INSERT INTO [Course_" & CourseName & "] (Gender, HomeRoom, StudentNumber) VALUES ('" & TempGender & "','" & DR("Home Room/Form") & "','" & DR("Special Use") & "')"
Command = New OleDbCommand(SQL, Connection)
Command.Connection.Open()
Command.ExecuteNonQuery()
SQL = "INSERT INTO [Course_" & CourseName & "] (LastName, FirstName) VALUES (?)"
Command = New OleDbCommand(SQL, Connection)
Command.Parameters.AddWithValue("LastName", DR("Last Name"))
Command.Parameters.AddWithValue("FirstName", DR("First Name"))
Command.ExecuteNonQuery()
Command.Connection.Close()

[Jenner]

Try this:

SQL = "INSERT INTO [Course_" & CourseName & "] (LastName, FirstName) VALUES (?, ?)"
Command = New OleDbCommand(SQL, Connection)
Command.Parameters.AddWithValue("LastName", DR("Last Name"))
Command.Parameters.AddWithValue("FirstName", DR("First Name"))
Command.ExecuteNonQuery()
Command.Connection.Close()

If that works, then do this:

SQL = "INSERT INTO [?] (LastName, FirstName) VALUES (?, ?)"
Command = New OleDbCommand(SQL, Connection)
Command.Parameters.AddWithValue("Table", "Course_" & CourseName)
Command.Parameters.AddWithValue("LastName", DR("Last Name"))
Command.Parameters.AddWithValue("FirstName", DR("First Name"))
Command.ExecuteNonQuery()
Command.Connection.Close()

And finally:

SQL = "INSERT INTO [?] (LastName, FirstName, Gender, HomeRoom, StudentNumber) VALUES (?, ?, ?, ?, ?); "
Command = New OleDbCommand(SQL, Connection)
Command.Parameters.AddWithValue("Table", "Course_" & CourseName)
Command.Parameters.AddWithValue("LastName", DR("Last Name"))
Command.Parameters.AddWithValue("FirstName", DR("First Name"))
Command.Parameters.AddWithValue("Gender", TempGender)
Command.Parameters.AddWithValue("HomeRoom", DR("Home Room/Form"))
Command.Parameters.AddWithValue("StudentNumber", DR("Special Use"))
Command.ExecuteNonQuery()
Command.Connection.Close()

It's been a LONG time since I worked with Access, so if I'm a little off, I'm sure someone will come and correct me.

[Wesley008]

So let me get this, "?" is the Parameters.AddWithValue, and its in Order?

Command.Parameters.AddWithValue("Table", "Course_" & CourseName)

Why did you use "Table" in it?

[Wesley008]

Jenner, Is their any way I can get in contact with you? Via IM? MSN or AIM be great, I got some other questions and some stuff I'd like to discuss on how I can improve my Code. I guess I got my works cut out for me on rewriting all my code with Parameters.

[masfenix]

Just to advise you and stress a little more why you should use parameters, is because of something called SQL Injection.

Consider this simple Select statement:

SELECT FROM users WHERE username = 'textbox-value' AND password='textbox2-value'

Seems pretty simple, just supply username and password, and boom your in, if the password dosnt match, the query fails.

HOWEVER, consider this possible value in the password textbox.

' OR 1 = 1

So when your when you pass your values along to SQL statement, plug them in and see what comes out:

SELECT * FROM users WHERE username='valid user' AND password ='' OR 1 =1

The OR will return true at all counts, giving you the information about that user and/or logging you in as an admin..

[jmcilhinney]

Assuming that you're using Access I suggest using names for your parameters. The ADO.NET provider ignores them and only uses position but the names make it clearer to YOU what you're doing. So, you can do this:

myCommand.CommandText = "INSERT INTO MyTable (Column1, Column2) VALUES (?, ?)"
 
myCommand.Parameters.AddWithValue("Column1", value1)
myCommand.Parameters.AddWithValue("Column2", value2)

The "?" are just palce-holders. They tell the ADO.NET provider to replace them with parameter values from the command. The first "?" will be replaced with the value of the first parameter that you add, the second "?" will be replaced with the second parameter value and so on. That means that, when the command gets executed, value1 and value2 actually get substituted into the command where the "?" symbols are.

If you did this:

myCommand.CommandText = "INSERT INTO MyTable (Column1, Column2) VALUES (?, ?)"
 
myCommand.Parameters.AddWithValue("Column2", value2)
myCommand.Parameters.AddWithValue("Column1", value1)

you would find that your values ended up in the wrong columns. That's because, regardless of what you actually intended, the first parameter you added contains value2 and it will be substituted for the first "?" in the SQL code. Likewise for value1 and the second "?" symbol.

Now, I suggest that you do this:

myCommand.CommandText = "INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2)"
 
myCommand.Parameters.AddWithValue("@Column1", value1)
myCommand.Parameters.AddWithValue("@Column2", value2)

Like I said before, the ADO.NET provider actually ignores the names and still uses position to decide what value goes where. Using these names though, it's easier for you to see exactly which parameter corresponds to exactly which place-holder and to make sure that they are in the same order where you add them as they are in in the SQL code.

[Jenner]

I didn't know Access supported @para style callouts. I thought it only supported ?. If it does support @para style syntax, then absolutely you should use them.

You're free to PM me but it's better to just make posts on these boards for this kinda stuff. It's helped me immensely and the users are very friendly provided you're not some whiny college kid begging the forums to do your homework for you.

[jmcilhinney]

I didn't know Access supported @para style callouts. I thought it only supported ?. If it does support @para style syntax, then absolutely you should use them.

It supports them in that it will accept them as place-holders but, as I said, it still ignores the names you use and uses position to decide on substitutions. For instance, if you did this with an SqlCommand:

myCommand.CommandText = "INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2)"
 
myCommand.Parameters.AddWithValue("@Column2", value2)
myCommand.Parameters.AddWithValue("@Column1", value1)

then you'd end up with value1 in Column1 and value2 in Column2, as is obviously intended. If you did it with Access and an OleDbCommand though, your values would end up the other way around. With SqlClient it is parameter name that decides what values get substituted where. With OleDb and the Jet provider it is position that determines the parameter substitutions, whether names are used or not. Note that this is a function of the Jet provider for OLEDB, not of .NET OleDb specifically.

[Rory Butler]

Hi

I've found this thread incredibly helpful and i can now add new records to my database very nicely (with apostrophes in the fields). Now I'm wanting to do updates on the tables, again with data containing the oddd apostrophe.

It seems to me that I cannot use the wonderful .parameters.addwithvalue process. It seems that I must use

"UPDATE [Mytable] SET [Field1] = " & sDetails(1) & " WHERE etc..."

or should it be

"UPDATE [Mytable] SET [Field1] = '" & sDetails(1) & "' WHERE etc..."

(i.e. surround the text with apostrophes)

or perhaps

"UPDATE [Mytable] SET [Field1] = '" & Twinapostrophes(sDetails(1)) & "' WHERE etc..."

where the twinapostrophes function replace each single apostrophe with a double one.

Can I have your advice please???? Thanks in advance...

Rory

[jmcilhinney]

Hi

I've found this thread incredibly helpful and i can now add new records to my database very nicely (with apostrophes in the fields). Now I'm wanting to do updates on the tables, again with data containing the oddd apostrophe.

It seems to me that I cannot use the wonderful .parameters.addwithvalue process. It seems that I must use

"UPDATE [Mytable] SET [Field1] = " & sDetails(1) & " WHERE etc..."

or should it be

"UPDATE [Mytable] SET [Field1] = '" & sDetails(1) & "' WHERE etc..."

(i.e. surround the text with apostrophes)

or perhaps

"UPDATE [Mytable] SET [Field1] = '" & Twinapostrophes(sDetails(1)) & "' WHERE etc..."

where the twinapostrophes function replace each single apostrophe with a double one.

Can I have your advice please???? Thanks in advance...

Rory

It doesn't matter what the SQL code contains. Parameters are inserted exactly the same way regardless. Does my previous post imply the type of the values being inserted in any way? No, it doesn't. Any type of data can be inserted this way. You only need to use single quotes in SQL code to denote a text literal. If you're using parameters you don't use single quotes. Think about VB code. To denote a string literal you must use double quotes, right? When you join string variables do you have to use double quotes? Of course not. The same goes fro SQL. So, in short, construct your command in EXACTLY the same way as I showed before.