Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writting to a database troubles

    Hi,
    I have a database with a table called CDTest and the fields CDName, CDPrice, DateAdded and UName. I’m having trouble with my code, I cant quite figure out whats wrong and I was hoping someone could help. Its supposed to take CDIDString and find a match in the database and allow the user to update CDPrice and UName. Here’s the code…

    Code:
    Dim cnn As Data.SqlClient.SqlConnection
    Dim cmd As Data.SqlClient.SqlCommand
    Dim param As Data.SqlClient.SqlParameter
    Dim strSQL As String
    strSQL = "Insert Into CDTestTBL(CDPrice, UName) Values(@CDPrice, @UName) WHERE CDID = '" & CDIDString & "'"
    Dim conn As String
    conn = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\aspnetdb.mdf"";Integrated Security=True;User Instance=True"
    
    cnn = New Data.SqlClient.SqlConnection(conn)
    cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)
    
    param = New Data.SqlClient.SqlParameter("@CDPrice",Data.SqlDbType.VarChar)
    param.Value = CDPriceString
    cmd.Parameters.Add(param)
                
    param = New Data.SqlClient.SqlParameter("@UName",Data.SqlDbType.VarChar)
    param.Value = UNameString
    cmd.Parameters.Add(param)
    
    cnn.Open()
    cmd.ExecuteNonQuery()
    cnn.Close()
    An error appears when I run the code saying “Incorrect syntax near the keyword 'WHERE'.” and points to…
    Code:
     cmd.ExecuteNonQuery()
    Could anyone lend a hand?

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    When using real parameters, you don't need single quotes.
    That's part of the beauty of them.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your right I've changed it now and I've also changed it from Insert to Update. It seems to update the data fine at the moment. Thanks for the help.

  • #4
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Don't believe that you need the where clause on an insert as you are inserting a new record. Now if you were doing an UPDATE then the where clause would be valid.

    Code:
    strSQL = "Insert Into CDTestTBL(CDPrice, UName) Values(@CDPrice, @UName) WHERE CDID = '" & CDIDString & "'"
    should probably be

    Code:
    strSQL = "Update Into CDTestTBL(CDPrice, UName) Values(@CDPrice, @UName) WHERE CDID = '" & CDIDString & "'"
    semper fi...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •