none
syntax error in update statement RRS feed

  • General discussion

  • connection()
                    Dim j As Integer
                    qry = "update member set type='" & cmbmembertype.Text & "', date='" & txtdate.Text & "', expiry='" & txtexpiry.Text & "', amt='" & txtamt.Text & "', deposit='" & txtdeposit.Text & "' where memberid=" & txtmemberid.Text & " "
                    cmd1 = New OleDbCommand(qry, con)
                    j = cmd1.ExecuteNonQuery()
                    MsgBox("Member Renewed Successfully", MsgBoxStyle.Information, "Member Renew")

    Shows syntax error in the above statement. plz help!

    • Edited by Umci Monday, March 25, 2013 7:14 PM
    Monday, March 25, 2013 7:13 PM

All replies

  • connection()
                    Dim j As Integer
                    qry = "update member set type='" & cmbmembertype.Text & "', date='" & txtdate.Text & "', expiry='" & txtexpiry.Text & "', amt='" & txtamt.Text & "', deposit='" & txtdeposit.Text & "' where memberid=" & txtmemberid.Text & " "
                    cmd1 = New OleDbCommand(qry, con)
                    j = cmd1.ExecuteNonQuery()
                    MsgBox("Member Renewed Successfully", MsgBoxStyle.Information, "Member Renew")

    Shows syntax error in the above statement. plz help!

    I don't see anything wrong with the update statement syntax, assuming the values passed are not empty strings and do not contain special characters.  However, I strongly suggest you instead use a parameters instead of building a SQL statement string using string concatenation of user-supplied values.  This method has a number of advantages related to security and performance and also avoids the need to double-up quotes, format dates, use period decimal separators and makes the code easier to read.  I don't know your actual data types but below is an example.

            Dim qry As String = "update member set type=?, date=?, expiry=?, amt=?, deposit=? where memberid=?"
            Dim cmd1 As New OleDbCommand(qry, con)
            cmd1.Parameters.AddWithValue("@type", cmbmembertype.Text)
            cmd1.Parameters.AddWithValue("@date", DateTime.Parse(txtdate.Text))
            cmd1.Parameters.AddWithValue("@expiry", DateTime.Parse(txtexpiry.Text))
            cmd1.Parameters.AddWithValue("@amt", Decimal.Parse(txtamt.Text))
            cmd1.Parameters.AddWithValue("@deposit", Decimal.Parse(txtdeposit.Text))
            cmd1.Parameters.AddWithValue("@memberid", Int32.Parse(txtmemberid.Text))
            j = cmd1.ExecuteNonQuery()

    Also, if you intend to use only SQL Server, I suggest you use SqlClient instead of OleDb.  SqlClient is best for SQL Server data access from managed code.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, March 26, 2013 2:25 AM
  • Please change the type of your post from comment to question. Thanks!

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 26, 2013 11:58 AM
  • Hello,

    In addition to Dan's post, depending on the database, date and/or type are reserved words in MS-Access and SQL-Server. Try enclosing date and type in square brackets i.e. [date] [type]. Apostrophes can easily mess things up which goes back to Dan's recommendation for using parameters and for an extra step use Add rather than AddWithValue to be certain the data provider is told the data type.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, March 28, 2013 2:07 PM