Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > What is wrong with this SQL Update Code?

Reply
Thread Tools

What is wrong with this SQL Update Code?

 
 
Terry Olsen
Guest
Posts: n/a
 
      04-24-2005
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("C onnectionString"))

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("C onnectionString"))

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




 
Reply With Quote
 
 
 
 
Elton W
Guest
Posts: n/a
 
      04-24-2005
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
http://www.velocityreviews.com/forums/(E-Mail Removed)


>-----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
>
>
>
>
>.
>

 
Reply With Quote
 
 
 
 
Dave Fancher
Guest
Posts: n/a
 
      04-24-2005
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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("C onnectionString"))
>
> 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("C onnectionString"))
>
> 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
>
>
>
>



 
Reply With Quote
 
Terry Olsen
Guest
Posts: n/a
 
      04-25-2005
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("C onnectionString"))

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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("C onnectionString"))
> >
> > 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("C onnectionString"))
> >
> > 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
> >
> >
> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update On The Windows Phone 7 Update Update Lawrence D'Oliveiro NZ Computing 2 02-25-2011 08:03 AM
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Is XML Doc wrong or is Schema wrong? (or both) Matthew XML 7 01-07-2005 10:05 PM
Simple SQL question. Can you check what am i doing wrong? Thank You. Miguel Dias Moura ASP .Net 1 06-18-2004 09:33 AM
What is wrong in this SQL code? Miguel Dias Moura ASP .Net 3 06-07-2004 08:11 PM



Advertisments