SQL Update using variables

Am having trouble using a variable with update function. Currently i have:

objCommand.CommandText = "UPDATE Table1 " &
  "SET Games= " & P & " " & _
  "Where Team= 'Chelsea' "

But would like to have the variable Team A where chelsea is currently.

more ▼

asked Nov 19, 2009 at 02:25 PM in Default

avatar image

user-617 (yahoo)
11 1 1 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Instead of concatenating your query together, you can use ? as placeholders within your query, then supply your variables as parameters. This takes care of SQL injection worries, as well keeping your query a little easier to read

objCommand.CommandText = "UPDATE Table1 SET Games = ? WHERE Team = ?"
objCommand.Parameters.Append objCommand.CreateParameter("@p", adVarChar, adParamInput, 30, P)
objCommand.Parameters.Append objCommand.CreateParameter("@TeamA", adVarChar, adParamInput, 30, TeamA)

Be sure TeamA = "Chelsea" in order to get a match with the where clause

W3Schools has a good rundown on CreateParameter - http://www.w3schools.com/ado/met_comm_createparameter.asp

more ▼

answered Nov 20, 2009 at 01:52 AM

avatar image

25k 3 13 20

(comments are locked)
10|1200 characters needed characters left

I believe this is what you want: "WHERE Team = '" & TeamA & "'"

That's the basic, unsafe, straightforward method. You shouldn't really do this because it presents the risk of SQL injection. Assuming you are using ADO or ADO.NET, it's better to use parameters with your command object. Alternatively, you could escape any single-quotes (i.e. replace single quotes with a pair of single quotes).

more ▼

answered Nov 19, 2009 at 02:40 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left

Could you clarify this slightly?

First, what language are you using? That looks like VisualBasic to me, but with such a small snippet it is hard to tell for certain.

Next, it looks like you are dynamically creating sql. When doing that, you have a few choices, depending on the language. The easiest is to simply insert the value you want into the text string you are getting ready to pass to the server (remember to keep the single quotes around that value if appropriate). This of course can in some cases have a risk of sql injection, but it is normally the easiest and works with any language.

With some languages you can paramaterize the query and pass the query and values separately. This reduces the security risks involved and can, in some cases, help with execution plan reuse.

more ▼

answered Nov 19, 2009 at 02:45 PM

avatar image

15.6k 22 57 38

AM using visual basic, tried doing the "WHERE Team = '" & TeamA & "'" but had no effect on the database

Nov 19, 2009 at 02:48 PM user-617 (yahoo)

Be sure to trim the variable. Is your database case-sensitive?

Nov 19, 2009 at 03:00 PM Tom Staab ♦

Yes it is case sensitive

Nov 19, 2009 at 03:02 PM user-617 (yahoo)
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 19, 2009 at 02:25 PM

Seen: 2463 times

Last Updated: Nov 20, 2009 at 01:59 PM

Copyright 2018 Redgate Software. Privacy Policy