SQL Server Update command syntax :(

[Desolator144]

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)

[brucevde]

Update TableName Set 
col1name = Value1, 
col2name = Value2
Where col1 = 5

[Desolator144]

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"

[szlamany]

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?

[Desolator144]

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

[szlamany]

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

Once you do that we can really help with your problem.

[Desolator144]

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.

[xoro]

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:

Dim strSql As String = "UPDATE News SET Headline='" & strHeadline & "', Message='" _
                & strMessage & "', Post_Date='" & strDate & "' WHERE Headline='" _
                & newsListBox.SelectedItem.ToString() & "'"