What is wrong with this SQL Update Code?

Discussion in 'ASP .Net' started by Terry Olsen, Apr 24, 2005.

  1. Terry Olsen

    Terry Olsen Guest

    I've got 2 different web pages, both updating the same SQL database. One is
    for the Technician and one is for the Manager. The technician's update page
    works fine but the Manager's update page doesn't. They are both the
    same..as far as I can tell...

    ---------------------
    This code works fine, updating the SQL database with the edited data.

    Code for Tech Page

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnSubmit.Click

    Dim myID As String = Request.QueryString.Get("ID")

    Dim myConnection As New
    SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

    Dim strCmd As String = "UPDATE Incidents " & _

    "SET SO_Num = '" & txtSONum.Text & "'," & _

    "HoursWorked = '" & txtHours.Text & "'," & _

    "Comments = '" & txtExistComments.Text & " -- " & txtAddtlComments.Text & "'
    " & _

    "WHERE ID = '" & myID & "'"

    Dim myCommand As New SqlCommand(strCmd, myConnection)

    myConnection.Open()

    myCommand.ExecuteNonQuery()

    myConnection.Close()

    SendMailMessage(myID)

    Response.Redirect("UpdateComplete.html")

    End Sub

    ---------------------------------------------------

    The following code executes with no exceptions. It returns "1" from the
    "ExecuteNonQuery" statement. But the SQL Database is not being updated.
    When I reload the controls after running this code, all the controls revert
    back to what was previously there before I edited. I've sat here staring at
    it for hours and can't see anything wrong...

    Code for Manager's Page

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnSubmit.Click

    Dim myID As String = Request.QueryString.Get("ID")

    Dim myConnection As New
    SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

    Dim strCmd As String = "UPDATE Incidents " & _

    "SET SO_Num='" & txtSONum.Text & "'," & _

    "HoursWorked='" & txtHours.Text & "'," & _

    "Comments='" & txtComments.Text & "'," & _

    "Center='" & txtCenter.Text & "'," & _

    "Date='" & txtDate.Text & "'," & _

    "UserName='" & txtUser.Text & "'," & _

    "TechEmail='" & txtTech.Text & "' " & _

    "WHERE ID='" & myID & "'"

    Dim myCommand As New SqlCommand(strCmd, myConnection)

    myConnection.Open()

    myCommand.ExecuteNonQuery()

    myConnection.Close()

    Session("Edit") = 1 '1 = Edited

    Response.Redirect("IncidentReview.aspx")

    End Sub
    Terry Olsen, Apr 24, 2005
    #1
    1. Advertising

  2. Terry Olsen

    Elton W Guest

    Hi Terry,

    Apparently two pages are not same. In addition to several
    fields to be updated in tech page, there are Center, Date,
    UserName, TechEmail to be updated in Manager page. The
    Date is key word. If you use Date as field name, you need
    use [Date] to explicitly show it's field name.

    I also have two more suggestions:

    1) It's better to use parameterized command to avoid sql
    injection attacks.
    2) Since you use TextBox for Date input, you also need to
    validate Date filed value,


    HTH

    Elton Wang



    >-----Original Message-----
    >I've got 2 different web pages, both updating the same

    SQL database. One is
    >for the Technician and one is for the Manager. The

    technician's update page
    >works fine but the Manager's update page doesn't. They

    are both the
    >same..as far as I can tell...
    >
    >---------------------
    >This code works fine, updating the SQL database with the

    edited data.
    >
    >Code for Tech Page
    >
    >Private Sub btnSubmit_Click(ByVal sender As

    System.Object, ByVal e As
    >System.EventArgs) Handles btnSubmit.Click
    >
    >Dim myID As String = Request.QueryString.Get("ID")
    >
    >Dim myConnection As New
    >SqlConnection(ConfigurationSettings.AppSettings

    ("ConnectionString"))
    >
    >Dim strCmd As String = "UPDATE Incidents " & _
    >
    >"SET SO_Num = '" & txtSONum.Text & "'," & _
    >
    >"HoursWorked = '" & txtHours.Text & "'," & _
    >
    >"Comments = '" & txtExistComments.Text & " -- " &

    txtAddtlComments.Text & "'
    >" & _
    >
    >"WHERE ID = '" & myID & "'"
    >
    >Dim myCommand As New SqlCommand(strCmd, myConnection)
    >
    >myConnection.Open()
    >
    >myCommand.ExecuteNonQuery()
    >
    >myConnection.Close()
    >
    >SendMailMessage(myID)
    >
    >Response.Redirect("UpdateComplete.html")
    >
    >End Sub
    >
    >---------------------------------------------------
    >
    >The following code executes with no exceptions. It

    returns "1" from the
    >"ExecuteNonQuery" statement. But the SQL Database is not

    being updated.
    >When I reload the controls after running this code, all

    the controls revert
    >back to what was previously there before I edited. I've

    sat here staring at
    >it for hours and can't see anything wrong...
    >
    >Code for Manager's Page
    >
    >Private Sub btnSubmit_Click(ByVal sender As

    System.Object, ByVal e As
    >System.EventArgs) Handles btnSubmit.Click
    >
    >Dim myID As String = Request.QueryString.Get("ID")
    >
    >Dim myConnection As New
    >SqlConnection(ConfigurationSettings.AppSettings

    ("ConnectionString"))
    >
    >Dim strCmd As String = "UPDATE Incidents " & _
    >
    >"SET SO_Num='" & txtSONum.Text & "'," & _
    >
    >"HoursWorked='" & txtHours.Text & "'," & _
    >
    >"Comments='" & txtComments.Text & "'," & _
    >
    >"Center='" & txtCenter.Text & "'," & _
    >
    >"Date='" & txtDate.Text & "'," & _
    >
    >"UserName='" & txtUser.Text & "'," & _
    >
    >"TechEmail='" & txtTech.Text & "' " & _
    >
    >"WHERE ID='" & myID & "'"
    >
    >Dim myCommand As New SqlCommand(strCmd, myConnection)
    >
    >myConnection.Open()
    >
    >myCommand.ExecuteNonQuery()
    >
    >myConnection.Close()
    >
    >Session("Edit") = 1 '1 = Edited
    >
    >Response.Redirect("IncidentReview.aspx")
    >
    >End Sub
    >
    >
    >
    >
    >.
    >
    Elton W, Apr 25, 2005
    #2
    1. Advertising

  3. Terry Olsen

    Dave Fancher Guest

    Is myID being set properly on the manager page? As the other poster
    suggested, you may want to look at the Date column. You can try enclosing
    it in [] to see if that corrects the issue ( [Date] = ... )

    As a side note, you should switch to SqlParameters instead of using string
    concatenation for your queries. For example:
    [C# Example, the syntax for VB will be slightly different but the SQL will
    be the same]

    SqlCommand cmd = "UPDATE Incidents SET SO_Num = @SoNum, HoursWorked = @Hrs,
    Comments = @Cmts, Center = @Center, Date = @Dte, UserName = @Usr, TechEmail
    = @TechEMail WHERE ID = @MyID", MyConnection);

    Then, to set the parameter values, you'll add parameters to the command's
    parameters collection like the following example:
    [C# again, sorry]
    cmd.Parameters.Add(new SqlParameter("@SoNum", SqlDbType.VarChar, 15)).Value
    = txtSONum.Text;
    // Repeat for each parameter

    Obviously you'll need to replace the SqlDbType and length parameters with
    the type that your database uses. Using this will not only help you debug
    but will also result in more secure code since you are restricting what data
    is allowed. This is especially important in situations like this where you
    are accepting input directly from text boxes and the query string.

    HTH
    ----------------
    Dave Fancher
    http://davefancher.blogspot.com

    "Terry Olsen" <> wrote in message
    news:%...
    > I've got 2 different web pages, both updating the same SQL database. One
    > is
    > for the Technician and one is for the Manager. The technician's update
    > page
    > works fine but the Manager's update page doesn't. They are both the
    > same..as far as I can tell...
    >
    > ---------------------
    > This code works fine, updating the SQL database with the edited data.
    >
    > Code for Tech Page
    >
    > Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles btnSubmit.Click
    >
    > Dim myID As String = Request.QueryString.Get("ID")
    >
    > Dim myConnection As New
    > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    >
    > Dim strCmd As String = "UPDATE Incidents " & _
    >
    > "SET SO_Num = '" & txtSONum.Text & "'," & _
    >
    > "HoursWorked = '" & txtHours.Text & "'," & _
    >
    > "Comments = '" & txtExistComments.Text & " -- " & txtAddtlComments.Text &
    > "'
    > " & _
    >
    > "WHERE ID = '" & myID & "'"
    >
    > Dim myCommand As New SqlCommand(strCmd, myConnection)
    >
    > myConnection.Open()
    >
    > myCommand.ExecuteNonQuery()
    >
    > myConnection.Close()
    >
    > SendMailMessage(myID)
    >
    > Response.Redirect("UpdateComplete.html")
    >
    > End Sub
    >
    > ---------------------------------------------------
    >
    > The following code executes with no exceptions. It returns "1" from the
    > "ExecuteNonQuery" statement. But the SQL Database is not being updated.
    > When I reload the controls after running this code, all the controls
    > revert
    > back to what was previously there before I edited. I've sat here staring
    > at
    > it for hours and can't see anything wrong...
    >
    > Code for Manager's Page
    >
    > Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles btnSubmit.Click
    >
    > Dim myID As String = Request.QueryString.Get("ID")
    >
    > Dim myConnection As New
    > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    >
    > Dim strCmd As String = "UPDATE Incidents " & _
    >
    > "SET SO_Num='" & txtSONum.Text & "'," & _
    >
    > "HoursWorked='" & txtHours.Text & "'," & _
    >
    > "Comments='" & txtComments.Text & "'," & _
    >
    > "Center='" & txtCenter.Text & "'," & _
    >
    > "Date='" & txtDate.Text & "'," & _
    >
    > "UserName='" & txtUser.Text & "'," & _
    >
    > "TechEmail='" & txtTech.Text & "' " & _
    >
    > "WHERE ID='" & myID & "'"
    >
    > Dim myCommand As New SqlCommand(strCmd, myConnection)
    >
    > myConnection.Open()
    >
    > myCommand.ExecuteNonQuery()
    >
    > myConnection.Close()
    >
    > Session("Edit") = 1 '1 = Edited
    >
    > Response.Redirect("IncidentReview.aspx")
    >
    > End Sub
    >
    >
    >
    >
    Dave Fancher, Apr 25, 2005
    #3
  4. Terry Olsen

    Terry Olsen Guest

    I think I found my problem. And it probably exists in the Tech page as
    well, I just haven't triggered it yet becase on entry, the HoursWorked and
    SO_Num text boxes are empty.

    On page load, I load the controls with the following routine:
    ------------------------------------------------------
    Private Sub LoadControls()

    Dim myID As String = Request.QueryString.Get("ID")

    Dim myConnection As New
    SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

    Dim myCommand As New SqlCommand("SELECT * FROM Incidents WHERE ID='" & myID
    & "'", myConnection)

    myConnection.Open()

    Dim myDR As SqlDataReader = myCommand.ExecuteReader()

    myDR.Read()

    lblID.Text = myDR.Item("ID")

    txtCenter.Text = myDR.Item("Center")

    txtDate.Text = myDR.Item("Date")

    txtTech.Text = myDR.Item("TechEmail")

    txtUser.Text = myDR.Item("UserName")

    txtHours.Text = myDR.Item("HoursWorked")

    txtSONum.Text = myDR.Item("SO_Num")

    txtComments.Text = myDR.Item("Comments")

    myConnection.Close()
    ----------------------------------------------------------------------------
    --

    It's acting like when I click the Submit button, the textboxes are being
    reloaded BEFORE the SQL Update command. So now i'll try something like:

    If not IsPostBack then LoadControls()

    and see if that works...

    "Dave Fancher" <> wrote in message
    news:...
    > Is myID being set properly on the manager page? As the other poster
    > suggested, you may want to look at the Date column. You can try enclosing
    > it in [] to see if that corrects the issue ( [Date] = ... )
    >
    > As a side note, you should switch to SqlParameters instead of using string
    > concatenation for your queries. For example:
    > [C# Example, the syntax for VB will be slightly different but the SQL will
    > be the same]
    >
    > SqlCommand cmd = "UPDATE Incidents SET SO_Num = @SoNum, HoursWorked =

    @Hrs,
    > Comments = @Cmts, Center = @Center, Date = @Dte, UserName = @Usr,

    TechEmail
    > = @TechEMail WHERE ID = @MyID", MyConnection);
    >
    > Then, to set the parameter values, you'll add parameters to the command's
    > parameters collection like the following example:
    > [C# again, sorry]
    > cmd.Parameters.Add(new SqlParameter("@SoNum", SqlDbType.VarChar,

    15)).Value
    > = txtSONum.Text;
    > // Repeat for each parameter
    >
    > Obviously you'll need to replace the SqlDbType and length parameters with
    > the type that your database uses. Using this will not only help you debug
    > but will also result in more secure code since you are restricting what

    data
    > is allowed. This is especially important in situations like this where

    you
    > are accepting input directly from text boxes and the query string.
    >
    > HTH
    > ----------------
    > Dave Fancher
    > http://davefancher.blogspot.com
    >
    > "Terry Olsen" <> wrote in message
    > news:%...
    > > I've got 2 different web pages, both updating the same SQL database.

    One
    > > is
    > > for the Technician and one is for the Manager. The technician's update
    > > page
    > > works fine but the Manager's update page doesn't. They are both the
    > > same..as far as I can tell...
    > >
    > > ---------------------
    > > This code works fine, updating the SQL database with the edited data.
    > >
    > > Code for Tech Page
    > >
    > > Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
    > > System.EventArgs) Handles btnSubmit.Click
    > >
    > > Dim myID As String = Request.QueryString.Get("ID")
    > >
    > > Dim myConnection As New
    > > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    > >
    > > Dim strCmd As String = "UPDATE Incidents " & _
    > >
    > > "SET SO_Num = '" & txtSONum.Text & "'," & _
    > >
    > > "HoursWorked = '" & txtHours.Text & "'," & _
    > >
    > > "Comments = '" & txtExistComments.Text & " -- " & txtAddtlComments.Text

    &
    > > "'
    > > " & _
    > >
    > > "WHERE ID = '" & myID & "'"
    > >
    > > Dim myCommand As New SqlCommand(strCmd, myConnection)
    > >
    > > myConnection.Open()
    > >
    > > myCommand.ExecuteNonQuery()
    > >
    > > myConnection.Close()
    > >
    > > SendMailMessage(myID)
    > >
    > > Response.Redirect("UpdateComplete.html")
    > >
    > > End Sub
    > >
    > > ---------------------------------------------------
    > >
    > > The following code executes with no exceptions. It returns "1" from the
    > > "ExecuteNonQuery" statement. But the SQL Database is not being updated.
    > > When I reload the controls after running this code, all the controls
    > > revert
    > > back to what was previously there before I edited. I've sat here

    staring
    > > at
    > > it for hours and can't see anything wrong...
    > >
    > > Code for Manager's Page
    > >
    > > Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
    > > System.EventArgs) Handles btnSubmit.Click
    > >
    > > Dim myID As String = Request.QueryString.Get("ID")
    > >
    > > Dim myConnection As New
    > > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    > >
    > > Dim strCmd As String = "UPDATE Incidents " & _
    > >
    > > "SET SO_Num='" & txtSONum.Text & "'," & _
    > >
    > > "HoursWorked='" & txtHours.Text & "'," & _
    > >
    > > "Comments='" & txtComments.Text & "'," & _
    > >
    > > "Center='" & txtCenter.Text & "'," & _
    > >
    > > "Date='" & txtDate.Text & "'," & _
    > >
    > > "UserName='" & txtUser.Text & "'," & _
    > >
    > > "TechEmail='" & txtTech.Text & "' " & _
    > >
    > > "WHERE ID='" & myID & "'"
    > >
    > > Dim myCommand As New SqlCommand(strCmd, myConnection)
    > >
    > > myConnection.Open()
    > >
    > > myCommand.ExecuteNonQuery()
    > >
    > > myConnection.Close()
    > >
    > > Session("Edit") = 1 '1 = Edited
    > >
    > > Response.Redirect("IncidentReview.aspx")
    > >
    > > End Sub
    > >
    > >
    > >
    > >

    >
    >
    Terry Olsen, Apr 25, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Miguel Dias Moura

    What is wrong in this SQL code?

    Miguel Dias Moura, Jun 7, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    387
    Miguel Dias Moura
    Jun 7, 2004
  2. Guest
    Replies:
    1
    Views:
    3,531
    =?Utf-8?B?VGFtcGEgLk5FVCBLb2Rlcg==?=
    Dec 21, 2004
  3. Daves
    Replies:
    1
    Views:
    362
  4. Matthew
    Replies:
    7
    Views:
    652
    Priscilla Walmsley
    Jan 7, 2005
  5. ecoolone
    Replies:
    0
    Views:
    743
    ecoolone
    Jan 3, 2008
Loading...

Share This Page