Persistance UPDATE problem - revised Q for clarity

[Guest]

I'd like to clear up a very long-standing problem I have had only because I have done a little more testing, but the problem is still there.

On Page_Load event, the textfields on my page fills with the personal data stored in a SQL DB, as follows:


Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader

cn = New SqlConnection("xxx")

cn.Open()
cmd = New SqlCommand("SELECT SName FROM ClientDetails WHERE clientRef=100", cn)
rdr = cmd.ExecuteReader()
rdr.Read()

'fill the surname textbox

txtSName.Text = rdr("ColSurName")

cn.Close()


e.g. pretend the value of the colSurNamel in the DB is 'Smith'


Then on the form my clients can update their details by simply overwriting the text in the textboxes and hitting the UPDATE button.
Assume I have now updated the textbox with 'Bloggs' and I've hit the UPDATE button (code as follows):


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conCOMP As SqlConnection
Dim cmdUPDATECOMP As SqlCommand

conCOMP = New SqlConnection("xxxxxx")
strCOMP = "UPDATE ClientDetails SET colSurName= '" & txtSName.Text & "' WHERE ClientRef = 100"
cmdUPDATECOMP = New SqlCommand(strCOMP, conCOMP)

Label4.Text = strCOMP

conCOMP.Open()
cmdUPDATECOMP.ExecuteNonQuery()
conCOMP.Close()

End Sub


I've added a label here to view the value of the UPDATE command when it is passed BUT it gives the value already stored in the DB / or rather the value it already got in the Page_Load routine:

the text in Label4 is: " UPDATE ClientDetails SET colSurName = 'Smith' WHERE ClientRef = 100 "



I think it is obviously the text box variable: having got a value for when the Page_Load event occurs. But how can my clients change the text boxes to update their details and hit UPDATE??????


PLEASE PLEASE HELP. This seriously has been BUGGing me for a month. My Client is not happy

 

[Raterus]

Page_Load will process on EVERY page load, even on postbacks...

If you need something to happen only the first time a page loads, then surround it with this in page_load

If Not Page.IsPostBack then
'Do stuff like fill textboxes for the first time.
End if

Most common newbie problem I see with asp.net
--Michael

 

[William Ryan eMVP]

I'd like to clear up a very long-standing problem I have had only because

I have done a little more testing, but the problem is still there.

Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader

cn = New SqlConnection("xxx")

cn.Open()
cmd = New SqlCommand("SELECT SName FROM ClientDetails WHERE clientRef=100", cn)
rdr = cmd.ExecuteReader()
rdr.Read()

This is a very inefficient way to do this. You don't need a whole reader,
use executeScalar or an output parameter instead

'fill the surname textbox

txtSName.Text = rdr("ColSurName")

cn.Close()


e.g. pretend the value of the colSurNamel in the DB is 'Smith'


Then on the form my clients can update their details by simply overwriting

the text in the textboxes and hitting the UPDATE button.

Assume I have now updated the textbox with 'Bloggs' and I've hit the

UPDATE button (code as follows):

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click

Dim conCOMP As SqlConnection
Dim cmdUPDATECOMP As SqlCommand

conCOMP = New SqlConnection("xxxxxx")
strCOMP = "UPDATE ClientDetails SET colSurName= '" & txtSName.Text & "' WHERE ClientRef = 100"
cmdUPDATECOMP = New SqlCommand(strCOMP, conCOMP)

Label4.Text = strCOMP

conCOMP.Open()
cmdUPDATECOMP.ExecuteNonQuery()
conCOMP.Close()

End Sub


I've added a label here to view the value of the UPDATE command when it is

passed BUT it gives the value already stored in the DB / or rather the value
it already got in the Page_Load routine:

the text in Label4 is: " UPDATE ClientDetails SET colSurName = 'Smith' WHERE ClientRef = 100 "



I think it is obviously the text box variable: having got a value for when

the Page_Load event occurs. But how can my clients change the text boxes to
update their details and hit UPDATE??????

PLEASE PLEASE HELP. This seriously has been BUGGing me for a month. My

Client is not happy

YOu have to check the value of the update statement. I can't tell you're
verifiying the update but you need to verify the parameter is correct.
Using Dynamic SQL Here instead of paramaters is suicide and I'd really
recommend using parameters http://www.knowdotnet.com/articles/dynamisql.html

If your update command is good then it will change in the db. You'll need
to requery it to verify that but if the update statement is valid and you
aren't throwing an exception, it should work. StrCompt, if the textbox text
is the changed value should reflect that. Step through it with the debugger
and verify the value it's getting. It should be changed. You may want to
set a module level varialbe and set it at page_load the same way you do the
textbox. Then use a Debug.Assert(txtSName.Text <> ModuleVar, "Values
Haven't Changed") and see if the assertion fails (you'll neeed to step
through since the assertion box isn't going to pop up in a web app. Also,
run a trace on SQL Server and verify that the wrong command is being sint.
--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/