ASP .NET SQL Statement

0 pts.
Tags:
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?
ASKED: March 2, 2007  8:21 AM
UPDATED: March 5, 2007  8:47 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Wolfman24
    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.
    0 pointsBadges:
    report
  • CharlesJC
    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?
    0 pointsBadges:
    report
  • SheldonLinker
    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;
    30 pointsBadges:
    report
  • Wolfman24
    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!
    0 pointsBadges:
    report
  • CharlesJC
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following