[2008] Using variables in an SQL string

It is possible to use a variable in the SQL string below?
Because the string is in quotes, I didn't know if it would recognize it as a variable.

Just for some background, 'bfguide' is the name of the database, 'rate' is the name of the table, and 'count' is the name of a field in the table.

VB Code:

myConnection = New MySqlConnection("server=localhost; user id=admin; password=******; database=bfguide; pooling=false;")
 
 --->   strSQL = "UPDATE rate SET count =  **variable** WHERE ID = 1;"
 
        myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
        myDataSet = New DataSet()
        myDataAdapter.Fill(myDataSet, "bfguide")
 
        MySQLDataGrid.DataSource = myDataSet
        MySQLDataGrid.DataBind()

I am somewhat of a newby, but in the past, I have done it like this:


strSQL = "UPDATE rate SET count = " + variable + " WHERE ID = 1;"

I break up the string into parts and insert the variables in between. The variable will need to be converted to a string (variable.tostring).

Hope this helps!

You will also need to put brackets around count....COUNT is a reserved word in SQL. If you have the ability, I would rename that field to something else to avoid any potential problems.

One point to keep in mind is that if you pull any values from a user entered text field you open yourself to sql injection attacks. I.e. " Update mytable set username = '" + txtuser.text + "' where id = '1'".

The way to prevent this and help with readability of the string is to use paramteters. Depending on what db you are using they syntax would differ but something similar to this:

Code:

---

dim objsql_str as sqlcommand
objsql_str.cmdtext ( Update Mytable set username = @username where id = '1')

Dim paramUser as parameter
paramUser.value = txtuser.text
objsql_str.parameters.add(paramUser)

---

There is much documentation here on the forums. This method has really helped me to cleanup my sql strings and make sure that I am not open to injection since a parameter is seen as text only and escape characters are not used as commands.

Hope this helps

And.... (I'm surprised Hack didn't mention this) if you are doing Inserts, Updates or Delete queries (also known as Action Queries) you should use ExecuteNonQuery and not the Fill method.... this is because action queries don't return a proper resultset, so there's no telling what you'll get.

-tg

So much to learn! So little time! :confused:

Yeah, wow I completely overlooked the "UPDATE rate SET count = " & variable & "WHERE ID = 1" method. I've been using VB for quite some time now, and have used that before; I don't know why it didn't come to me earlier.

Quote:

---

Originally Posted by techgnome

And.... (I'm surprised Hack didn't mention this) if you are doing Inserts, Updates or Delete queries (also known as Action Queries) you should use ExecuteNonQuery and not the Fill method.... this is because action queries don't return a proper resultset, so there's no telling what you'll get.

-tg

---

What is it exactly that you're saying? I'm new with SQL and just copied and pasted that code from the web, so what should I change for the UPDATE part?

What I'm saying is that to update data in the database, do not use the .Fill method. Fill is used to get data out of the database (as the result of a SELECT). ExecuteNonQuery means "Execute this SQL, and I'm not expecting any data back" .

I suggest starting with this: http://www.vbforums.com/showthread.php?t=337051 it's our home-grown FAQ and tutorial for VB & databases... people have had success starting with that.

-tg