5 Replies - 324 Views - Last Post: 16 February 2013 - 04:41 PM Rate Topic: -----

#1 shafran  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 16-February 13

error in update statement..how to correct it?

Posted 16 February 2013 - 03:50 AM

 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        con1.Open()
        cmd.Connection = con1
        cmd.CommandText = "update Employee set Emp_Name='" & TextBox2.Text & "',Address='" & TextBox3.Text & "',Position='" & TextBox4.Text & "',NIC_No='" & TextBox5.Text & "',Marital_Status='" & TextBox6.Text & "',Department='" & TextBox7.Text & "',Date_of_Join='" & TextBox8.Text & "',Contact_No=" & TextBox9.Text & " where Emp_ID='" & Textbox1.Text & "'"
        x = cmd.ExecuteNonQuery()
        If (x = 1) Then
            MsgBox("Update Success")
        End If
        con1.Close()
        clear()



Is This A Good Question/Topic? 0
  • +

Replies To: error in update statement..how to correct it?

#2 tycos  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 53
  • Joined: 12-February 13

Re: error in update statement..how to correct it?

Posted 16 February 2013 - 04:42 AM

You have not said what your error is?

You SQL comes out as:

update Employee set 
Emp_Name='value',
Address='value',
Position='value',
NIC_No='value',
Marital_Status='value',
Department='value'
,Date_of_Join='value',
Contact_No=value 
where Emp_ID='value'


Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3467
  • View blog
  • Posts: 11,768
  • Joined: 12-December 12

Re: error in update statement..how to correct it?

Posted 16 February 2013 - 04:43 AM

What error messages do you receive?
In what sense does it not work?
Use parameterized queries or prepared statements.
Print out the CommandText - check it and try and execute it in SS Management Studio.

This post has been edited by andrewsw: 16 February 2013 - 04:45 AM

Was This Post Helpful? 0
  • +
  • -

#4 tycos  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 53
  • Joined: 12-February 13

Re: error in update statement..how to correct it?

Posted 16 February 2013 - 04:51 AM

As Andrew said reading values in from a textbox to a line of SQL is bad practice:

Why not create a function to handle your SQL statements which you can call:

' Generic Function to handle Queries 

 Public Shared Function execSQLQuery(ByVal cmd As MySqlCommand) As Boolean
        Try
            Dim myConnection As MySqlConnection

            myConnection = New MySqlConnection(ConfigurationSettings.AppSettings("ConnString"))
            cmd.CommandType = CommandType.Text
            cmd.Connection = myConnection

            myConnection.Open()
            reader = cmd.ExecuteNonQuery
            myConnection.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function 

' A Call to the function with Parameters
' cmd.Parameters.Add("?fieldname", datatype, length).Value = Textbox.text

        Dim cmd As New MySqlCommand
        Try
            cmd.CommandText = "UPDATE news set Headline=?title, content=?news, postedBy=?postedBy, shortdesc=?shortdesc, image_id=?image_id where ID =?id"
            cmd.Parameters.Add("?id", MySqlDbType.Int32, 11).Value = id
            cmd.Parameters.Add("?postedBy", MySqlDbType.VarChar, 255).Value = postedBy
            cmd.Parameters.Add("?title", MySqlDbType.VarChar, 255).Value = title
            cmd.Parameters.Add("?news", MySqlDbType.Text).Value = news
            cmd.Parameters.Add("?image_id", MySqlDbType.Int32, 11).Value = image_id
            cmd.Parameters.Add("?shortdesc", MySqlDbType.VarChar, 255).Value = shortdesc
            Return execSQLQuery(cmd)
        Catch ex As Exception
            Return False
        End Try


Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: error in update statement..how to correct it?

Posted 16 February 2013 - 02:46 PM

Also, I believe Position will need to be enclosed in square brackets as I think it might be a keyword.
Sorry, don't have a way to check that right now but try:

...[Position]=...
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3467
  • View blog
  • Posts: 11,768
  • Joined: 12-December 12

Re: error in update statement..how to correct it?

Posted 16 February 2013 - 04:41 PM

http://msdn.microsof...y/ms189822.aspx

Position is a reserved word in ODBC. Personally, if I am suspicious of a particular word I might enclose it in brackets. Preferring compound, as opposed to single, words and abbreviated forms such as Posn prevents such concerns, although some people prefer not to use abbreviated versions.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1