SQL Server Update command syntax :(

Printable View

Dec 3rd, 2006, 01:16 AM
Desolator144

SQL Server Update command syntax :(

Apparently SQL Server (the version that comes with VS 05) really doesn't like statements in the form of Update TheTable set (col1name, col2name) = ('value','value') where col1 = 5
so what's the correct syntax for multiple column updates? (and don't say use parameters, those are a pain)
Dec 3rd, 2006, 03:47 AM
brucevde

Re: SQL Server Update command syntax :(

Code:

Update TableName Set col1name = Value1, col2name = Value2 Where col1 = 5
Dec 3rd, 2006, 02:14 PM
Desolator144

Re: SQL Server Update command syntax :(

I think it worked and it returned a value of one row affected but the data in the database wasn't changed at all. Nooooo idea why :( Here's the full, compiled SQL string:
strSQL = "Update Auto set VIN = 'WQ38947894TE87534895WP99',Make = 'Nissan',
Model = 'Altima',AutoYear = 2006, AutoRegExpire = '7/12/2007 12:00:00 AM', CurrentMiles = 3201,
AutoCost = 17750.00, CategoryID = 1 where AutoID = 15"
Dec 3rd, 2006, 04:30 PM
szlamany

Re: SQL Server Update command syntax :(

Do you have Query Analyzer or Management studio?

If so can you copy that statement into a query window and execute it? Does it get an error? Are any of the column names a different color then other column names?
Dec 3rd, 2006, 07:03 PM
Desolator144

Re: SQL Server Update command syntax :(

I did download the free SQL management studio thingy and it was awesome....for my MySQL backups. It had never been able to open an .mdf file! I don't know what the hell microsoft was thinking with that but yeah, it can't open SQL Server files. Unbelievable! I even tried with multiple .mdf files and none worked. It says it doesn't have an interpreter for that filetype or something like that. I don't have any other query analyzer either :( But it does say it affected one row with that statement and the fact that it ran at all makes me think that the only other way something could do wrong is if the code didn't send the correct values but that's impossible because according to the string, those are the new values. I hate databases lol
Dec 3rd, 2006, 07:33 PM
szlamany

Re: SQL Server Update command syntax :(

You should be able to ATTACH the .mdf/.ldf pair in Management Studio...

Once you do that we can really help with your problem.
Dec 3rd, 2006, 10:15 PM
Desolator144

Re: SQL Server Update command syntax :(

I think I uninstalled it lol. I did get one record to stick but it was reloaded onto the wrong record duplicated in another place but only after closing the form once and it wasn't actually saved that way in the database. So this must be either magic, sun spots, or a haunting. I stepped through all my code in that area and it all should have worked absolutely perfectly so I'm thinking it's some sort of glitch with VS or something. I'm going to show it to my teacher ASAP.
Dec 4th, 2006, 12:20 AM
xoro

Re: SQL Server Update command syntax :(
I am having a similar issue with mine. I've gotten it to work if there are no spaces or characters such as "#" or "/" in the fields I am updating, but that isn't very often.

If anybody knows the answer to this problem I'm sure we would both appreciate it. My statement looks like this:
VB Code:
```
Dim strSql As String = "UPDATE News SET Headline='" & strHeadline & "', Message='" _
                & strMessage & "', Post_Date='" & strDate & "' WHERE Headline='" _
                & newsListBox.SelectedItem.ToString() & "'"
```