Welcome to WebmasterWorld Guest from 3.209.80.87

Forum Moderators: ocean10000

Message Too Old, No Replies

trouble with update statement

update statement fails

     
1:10 am on May 25, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


I have an update statement that works only if I hard code in what I want. I want to use a variable, but I don't get an error, it just never works. I kid you not when I say that I've spent 5 hours on this. I've given up... please help!



This is the code for on page load -->


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Session("securelogin") <> True Then
Response.Redirect("loggedout.aspx")

Else




MultiView1.SetActiveView(View1)
strid = Request.QueryString("id")
strvl = Request.QueryString("vl")



Dim cmsData As New DataTable
Dim cmsCt As New SqlConnection(ConnectionString)
Dim cmsAdapt As New SqlDataAdapter

'Try

Dim cmsQuery As String = "SELECT * FROM tblPages WHERE ID = @ID AND VIEWER_LEVEL = @VIEWER_LEVEL "
cmsAdapt.SelectCommand = New SqlCommand(cmsQuery, cmsCt)
cmsAdapt.SelectCommand.Parameters.Add(New SqlParameter("@ID", strid))
cmsAdapt.SelectCommand.Parameters.Add(New SqlParameter("@VIEWER_LEVEl", strvl))

cmsAdapt.Fill(cmsData)
cmsCt.Close()
' Catch ex As Exception
' lblErrors.Text = "Could not connect to CMSWebData"
'Exit Sub
' End Try

If cmsData.Rows.Count > 0 Then
Dim dr As DataRow = cmsData.Rows(0)
strCont = Utils.CheckForNull_Text(dr.Item("PAGE_CONTENT"), "")

txtInfo.Text = strCont

strmessage = txtInfo.Text


Else

lblError.Text = "***Error. No page to update based on the QueryString. Please try again. <br/>"

End If

cmsData.Clear()

End If
End Sub

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

this is on the button click -->


Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click


Response.Write(strmessage)
'the above line does work... it displays the message, but a few lines below... NOTHING.


strQuery = "Update tblPages set PAGE_CONTENT=@PAGE_CONTENT WHERE ID=@ID"

cmd = New SqlCommand(strQuery, Connection)

cmd.Parameters.Add(New SqlParameter("@PAGE_CONTENT", SqlDbType.Text)).Value = strmessage
'note - if I use the line below instead of the one above it will insert the word anything.
'cmd.Parameters.Add(New SqlParameter("@PAGE_CONTENT", SqlDbType.Text)).Value = "ANYTHING"

cmd.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int)).Value = strid


Connection.Open()
cmd.ExecuteNonQuery()
Connection.Close()




MultiView1.SetActiveView(View2)
End Sub
1:37 am on May 25, 2010 (gmt 0)

Administrator

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month

joined:Jan 14, 2004
posts:864
votes: 3


Try the following.

cmd.Parameters.Add(New SqlParameter("@PAGE_CONTENT", SqlDbType.Text)).Value = txtInfo.Text
cmd.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int)).Value = strid
12:26 pm on May 25, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


Still no luck. Below I am pasting the updated code so you or anybody can see....



Dim dr As DataRow = cmsData.Rows(0)
txtInfo.Text = Utils.CheckForNull_Text(dr.Item("PAGE_CONTENT"), "")

' txtInfo.Text = strCont

'strmessage = txtInfo.Text


Else

lblError.Text = "***Error. No page to update based on the QueryString. Please try again. <br/>"

End If

cmsData.Clear()

End If
End Sub

Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click


' Response.Write(strmessage)
Response.Write(txtInfo.Text)


strQuery = "Update tblPages set PAGE_CONTENT=@PAGE_CONTENT WHERE ID=@ID"

cmd = New SqlCommand(strQuery, Connection)

cmd.Parameters.Add(New SqlParameter("@PAGE_CONTENT", SqlDbType.Text)).Value = txtInfo.Text
cmd.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int)).Value = strid
1:49 pm on May 25, 2010 (gmt 0)

Administrator

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month

joined:Jan 14, 2004
posts:864
votes: 3


Response.Write(strid) <-- I am curious to see if this has a value or not. If it is blank or zero (aka no matching record to update) would explain why its not updating the record you are trying.
2:30 pm on May 25, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


That actually does show. It will show a 3, 2, or 1 depending upon the link I click on beforehand. It displays it on the page. The value I need to update just gets lost... If I hard code in what I want, that works, but I need it to update it based on a variable.
3:31 pm on May 25, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 1, 2005
posts:733
votes: 0


It looks like you're resetting the value in txtInfo.Text on Page_Load.

I think you need to check for IsPostBack on Page_Load, if it's a PostBack, then don't reload the data from the Database.


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Session("securelogin") <> True Then
Response.Redirect("loggedout.aspx")
Else
If Not Page.IsPostBack Then
' Load data here
End If

End If
End Sub


* Edit: Some extra info

You might want to check out the ASP.Net Page Lifecycle [msdn.microsoft.com], in your code the following is most likely happening:

1: The page loads for the first time, the Database is accessed and txtInfo.Text gets filled with PAGE_CONTENT.
2: User updates the content of txtInfo.Text and clicks on the btnUpdate button.
3: The page loads for the second time, the Database is accessed and txtInfo.Text gets filled with PAGE_CONTENT again, overwriting the text entered by the user.
4: btnUpdate_Click fires, writing the values to the database, but, as the fields were reset on Page_Load, you are overwriting the DB values with the originals.
5:39 pm on May 25, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


Postback was the problem!

Marcel and Ocean10000 thanks for your responsiveness and help.

I was about to pull what little hair I have left out of my head. ha ha

[edited by: marcel at 11:34 am (utc) on May 26, 2010]