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