ASP .NET SQL Statement
Desktops, Management, Microsoft Windows, OS, Security, Servers, SQL, SQL Server, Web site design & management
Thanks to SheldonLinker my earlier question has been answered. I continue to have 1 issuse and it is with my conditional SQL statement. I'm thinking someone will be able to correct this very easily. Here's the statement:
If ("SELECT DataID FROM table1") = UserID
Then Update
Else Insert
End If
I don't know if SELECT will actually work in an IF statement so that could be the issue. I want it to check the sql table and compare the DataID to a variable (UserID) entered by a hidden field. If the equal each other an Update is made otherwise an Insert is performed. If I do this: IF "9" = UserID the Update works fine. The problem is with the SELECT statement.
Does anyone know what I'm doing wrong or how to get around it?
Software/Hardware used:
Software/Hardware used:
ASKED:
March 2, 2007 8:21 AM
UPDATED:
March 5, 2007 8:47 AM
Answer Wiki:
Wolfman24,
After reviewing the code posted in your last question, you can do something like this:
Dim lCommand As SqlCommand
Try
lCommand = New SqlCommand("Select data from table where data = """ & UserID & """", conNCIGFDNN)
If lCommand.ExecuteScalar Is Nothing Then
Insert() 'call your insert code
Else
Update() 'call your update code
End If
Catch lEx As Exception
'handle your error
Finally
lCommand = Nothing 'Clean up
End Try
Basically, check the database for an existing record matching you criteria. If it exists call you update code otherwise call you insert code.
The Try-Catch-Finally exception handling is a good thing to get in the habit of doing early on, as it will allow you to control the stability of the application.
I hope this helps.
Charles
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you for your help CharlesJC. Again, I’m a newbie so I’m sure my inexperience is showing. I can’t get it to work properly and I think it’s b/c off wrong syntax.
Try
lCommand = New SqlCommand(“SELECT UserID FROM dnn_KB_XMod_Index_VC500 WHERE UserID = “”" & UserID & “”"”, conNCIGFDN)
If lCommand.ExecuteScalar Is Nothing Then
conNCIGFDN = New SqlConnection(“Server=server;UID=uid;PWD=pass;database=DB”)
strInsert = “INSERT…
conNCIGFDN.Open()
cmdInsert = New SqlCommand(strInsert, conNCIGFDN)
cmdInsert.ExecuteNonQuery()
Else
strUpdate = “UPDATE….
cmdUpdate = New SqlCommand(strUpdate, conNCIGFDN)
cmdUpdate.ExecuteNonQuery()
conNCIGFDN.Close()
End If
….
Any suggestions on how to get this working correctly? I’m very close, thanks to this forum, to getting this to work.
Wolfman24,
Here is your code rearranged a bit:
Try
‘Create the connection first
conNCIGFDN = New SqlConnection(“Server=server;UID=uid;PWD=pass;database=DB”)
‘Opening the coneection is optional.
conNCIGFDN.Open()
‘Create the SQLCommand using the connection you just created. The other SQLCommand objects will use the same SQLConnection object.
lCommand = New SqlCommand(“SELECT UserID FROM dnn_KB_XMod_Index_VC500 WHERE UserID = “”" & UserID & “”"”, conNCIGFDN)
If lCommand.ExecuteScalar Is Nothing Then
strInsert = “INSERT…
cmdInsert = New SqlCommand(strInsert, conNCIGFDN)
cmdInsert.ExecuteNonQuery()
Else
strUpdate = “UPDATE….
cmdUpdate = New SqlCommand(strUpdate, conNCIGFDN)
cmdUpdate.ExecuteNonQuery()
End If
‘if you explicitly open the connection, you should close it.
conNCIGFDN.Close()
What happens now?
Regarding:
If (“SELECT DataID FROM table1″) = UserID
Then Update
Else Insert
End If
That’s 2 queries every time. Do the update (1 query) and see if any rows were affected. If not, do the insert (possible second query).
If you really really want to use an IF, the IF should be:
IF EXISTS(SELECT 1 FROM table1 WHERE dataId=userId) THEN
UPDATE…;
ELSE
INSERT…;
END IF;
Thanks, CharlesJC. Your suggestions along with a few other tweaks have gotten the form up and running. Except for 1 issue. The UPDATE works like it should. The INSERT however does not work. If I submit a new entry it looks like everything completed properly but the data isn’t showing in the SQL table. It’s almost like it tries to update but there’s nothing to update.
Could the problem be with ’1Command IS NOTHING’? It seems like it never finds this to be true and always goes to ELSE. Is there another way to check this?
Again, thanks for all your help!
Wolfman24,
The ExecuteScalar function should return a null reference if the resultset is empty. Since that is not working for some reason, try putting a breakpoint on the start of the If block and check the return value of the ExecuteScalar call, when the record does not exist. Whatever that value is, use that as your condition for inserting.
Charles