What is wrong with this SQL Update Code?

T

Terry Olsen

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
 
E

Elton W

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
(e-mail address removed)
 
D

Dave Fancher

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
 
T

Terry Olsen

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 said:
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 said:
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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top