UPDATE using params, again, deosn't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I move a step forward, then I have to spend a day on 1 little problem. VS isn't impressing me anymore. I have deadlines to meet and it is just too damn easy to get error pages

Enough of the moan, the mistake is me, I know, but why oh why oh why does this statement not update my SQL Server DB

***
Dim str As Strin
Dim con2 As SqlConnectio
Dim cmdUPDATE As SqlComman
Dim intUpdateCount As Intege

con2 = New SqlConnection("xxxxxxx"

str = "UPDATE ClientDetails SET ContactTitle=@Title, ContactFirstName=@FirstName, ContactLastName=@Surname, Tel=@Tel, HospContactPosition=@Position, Fax=@Fax, Add1=@Add1, Add2=@Add2, Add3=@Add3, Add4=@Add4, PostCode=@PostCode, OrgName=@OrgName WHERE Email='" & stateEmail & "'

cmdUPDATE = New SqlCommand(str, con2

cmdUPDATE.Parameters.Add("@Title", SqlDbType.NVarChar, 50).Value = txtTitle.Tex
cmdUPDATE.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = Fname.Tex
cmdUPDATE.Parameters.Add("@Surname", SqlDbType.NVarChar, 50).Value = Sname.Tex
cmdUPDATE.Parameters.Add("@Tel", SqlDbType.NVarChar, 50).Value = Tel.Tex
cmdUPDATE.Parameters.Add("@Position", SqlDbType.NVarChar, 50).Value = txtPosition.Tex
cmdUPDATE.Parameters.Add("@Fax", SqlDbType.NVarChar, 50).Value = Fax.Tex
cmdUPDATE.Parameters.Add("@Add1", SqlDbType.NVarChar, 50).Value = Add1.Tex
cmdUPDATE.Parameters.Add("@Add2", SqlDbType.NVarChar, 50).Value = Add2.Tex
cmdUPDATE.Parameters.Add("@Add3", SqlDbType.NVarChar, 50).Value = Add3.Tex
cmdUPDATE.Parameters.Add("@Add4", SqlDbType.NVarChar, 50).Value = Add4.Tex
cmdUPDATE.Parameters.Add("@PostCode", SqlDbType.NVarChar, 10).Value = PostCode.Tex
cmdUPDATE.Parameters.Add("@OrgName", SqlDbType.NVarChar, 50).Value = txtOrgName.Tex
con2.Open(
cmdUPDATE.ExecuteNonQuery(
con2.Close(

Response.Redirect("PersonalDetailsUpdated.aspx"

please please hel
 
Dazzalondon:

Not sure why you didn't use stateEmail as a Param, but that's the most
likely cause. You only have one condition in your where statement so at
first glance, that's probably the most likely problem. You probably have,
but just to be sure, have you verified the value and that the record exists?
dazzalondon said:
I move a step forward, then I have to spend a day on 1 little problem. VS
Click to expand...
isn't impressing me anymore. I have deadlines to meet and it is just too
damn easy to get error pages.
Enough of the moan, the mistake is me, I know, but why oh why oh why does
Click to expand...
this statement not update my SQL Server DB:
****
Dim str As String
Dim con2 As SqlConnection
Dim cmdUPDATE As SqlCommand
Dim intUpdateCount As Integer

con2 = New SqlConnection("xxxxxxx")

str = "UPDATE ClientDetails SET ContactTitle=@Title,
Click to expand...
ContactFirstName=@FirstName, ContactLastName=@Surname, Tel=@Tel,
HospContactPosition=@Position, Fax=@Fax, Add1=@Add1, Add2=@Add2, Add3=@Add3,
Add4=@Add4, PostCode=@PostCode, OrgName=@OrgName WHERE Email='" & stateEmail
& "'"
 
thanks for your assistance. You've given me the idea to change the condition I use (which is a registered email address) to the member reference number from a DB Column (which is unqiue), but this UPDATE still doesn't work.

Should I be using '' where the condition is ??

Please help. Its midnight here in the UK, and I am still planning to launch this tonight!

Here's my full update code
****
Public Sub butUpdatePersonal_Click(ByVal s As System.Object, ByVal e As System.EventArgs) Handles butUpdatePersonal.Click

'submit to DB
Dim str As String
Dim con2 As SqlConnection
Dim cmdUPDATE As SqlCommand
Dim intUpdateCount As Integer

con2 = New SqlConnection("xxxx")

str = "UPDATE ClientDetails SET ContactTitle=@Title, ContactFirstName=@FirstName, ContactLastName=@Surname, Tel=@Tel, HospContactPosition=@Position, Fax=@Fax, Add1=@Add1, Add2=@Add2, Add3=@Add3, Add4=@Add4, PostCode=@PostCode, OrgName=@OrgName WHERE ClientRef='" & stateRef & "'"
cmdUPDATE = New SqlCommand(str, con2)
cmdUPDATE.Parameters.Add("@Title", txtTitle.Text)
cmdUPDATE.Parameters.Add("@FirstName", Fname.Text)
cmdUPDATE.Parameters.Add("@Surname", Sname.Text)
cmdUPDATE.Parameters.Add("@Tel", Tel.Text)
cmdUPDATE.Parameters.Add("@Position", txtPosition.Text)
cmdUPDATE.Parameters.Add("@Fax", Fax.Text)
cmdUPDATE.Parameters.Add("@Add1", Add1.Text)
cmdUPDATE.Parameters.Add("@Add2", Add2.Text)
cmdUPDATE.Parameters.Add("@Add3", Add3.Text)
cmdUPDATE.Parameters.Add("@Add4", Add4.Text)
cmdUPDATE.Parameters.Add("@PostCode", PostCode.Text)
cmdUPDATE.Parameters.Add("@OrgName", txtOrgName.Text)
cmdUPDATE.Parameters.Add("@stateRef", stateRef)
con2.Open()
cmdUPDATE.ExecuteNonQuery()
con2.Close()

Response.Redirect("PersonalDetailsUpdated.aspx")
End Sub
 
The Where needs to specify a condition that will point to some actual Rows.
Email address for instance needs to be on that is in the DB. The new field
you used is the same thing, it has to point to a real row. Since you are in
a pinch, right beforehand, add another SELECT Statement that Mirrors the
Where clause Exaclty and verify that you are in fact getting rows. If you
won't, the update is worthless. Also, since everything else is
parameterized, I'd recommend that you create a parameter in teh where clause
and add it as well. No need to expose yourself when you put that much
effort into doing things 'by the book'

If the connection is opening and you aren't getting an exception, it's
pretty much pointing to the where.

It's 8:50 PM here, I'll stay up until we get it solved, no worries.

Cheers,

Bill
dazzalondon said:
thanks for your assistance. You've given me the idea to change the
Click to expand...
condition I use (which is a registered email address) to the member
reference number from a DB Column (which is unqiue), but this UPDATE still
doesn't work.
Should I be using '' where the condition is ??

Please help. Its midnight here in the UK, and I am still planning to launch this tonight!

Here's my full update code
****
Public Sub butUpdatePersonal_Click(ByVal s As System.Object, ByVal e As
Click to expand...
System.EventArgs) Handles butUpdatePersonal.Click
'submit to DB
Dim str As String
Dim con2 As SqlConnection
Dim cmdUPDATE As SqlCommand
Dim intUpdateCount As Integer

con2 = New SqlConnection("xxxx")

str = "UPDATE ClientDetails SET ContactTitle=@Title,
Click to expand...
ContactFirstName=@FirstName, ContactLastName=@Surname, Tel=@Tel,
HospContactPosition=@Position, Fax=@Fax, Add1=@Add1, Add2=@Add2, Add3=@Add3,
Add4=@Add4, PostCode=@PostCode, OrgName=@OrgName WHERE ClientRef='" &
stateRef & "'"
 
ok. its 3.10am; I've been to bed, cannot sleep so attempted to do this for a 50th time. still doens't work.

I placed a
intUpdateCount = cmdUPDATE.ExecuteNonQuery()
con2.Close()
Label1.Text = intUpdateCount

at the end. and the label returned with a 1; but the record was not updated.

For your info, i have in the page_load a query which builds the stateEmail variable, and that sucessfully fills various textboxes I have on the screen.

was the first line correct ?? - i mean the following - because i had an issue with this yesterday
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

I am very dubious at having to stay up till 3am to try and work something which has been borhtering me all day. I wouldn't mind if i were making progress but I am not.
 
H

would it have something to do with text which is being updated contains spaces??

if the data trying to be updated is 'Hello There' and the param is

cmdUPDATE.Parameters.Add("@Message", txtMessage.Text

would that cause a problem? if so, how would i resolve it?
 
It shouldn't if that's what is matched in the db. Let me reread your last
post, I read this one first b/c I thought it might have more info. I'll be
right back.
 
Ok, if you are getting one as a value back, something is really really weird
if no update is made Here's the only thing I can think of. Make a Totally
unique record, something with a very distinct value in the column in the
where clause. Then send it through....Verify the Parameter Values
beforehand. If those values are correct and the where clause value is
legit, there's no reason it shouldn't work. I know this is silly, but
verify the database you are writing to as well in the connection string,
make sure you aren't pointing to test and looking at production or some
such. I just mention this b/c this is very weird and it's getting weirder.
Sending an update command is standard far and there's no reason that it
shouldn't work.

Also, did you run the Select statement beforehand? I'm wondering if it's
returning one record like it should be if the where clauses match. And
there's definitely no exceptions beign thrown anywhere right? I'm assuming
that b/c of the records affected but we need to check every avenue.

Don't get discouraged, we'll get it.

Bill
dazzalondon said:
ok. its 3.10am; I've been to bed, cannot sleep so attempted to do this for
Click to expand...
a 50th time. still doens't work.
I placed a
intUpdateCount = cmdUPDATE.ExecuteNonQuery()
con2.Close()
Label1.Text = intUpdateCount

at the end. and the label returned with a 1; but the record was not updated.

For your info, i have in the page_load a query which builds the stateEmail
Click to expand...
variable, and that sucessfully fills various textboxes I have on the screen.
was the first line correct ?? - i mean the following - because i had an issue with this yesterday
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
Click to expand...
System.EventArgs) Handles Button1.Click
I am very dubious at having to stay up till 3am to try and work something
Click to expand...
which has been borhtering me all day. I wouldn't mind if i were making
progress but I am not.
 
Since you are pressed for time, we should probably set up profiler ( I
should have mentioned it earlier) and lets verify what's being sent to the
db.
 
Thought I'd hear back from you, but let me know if it's still not working.
i'm heading to work know, but will be there in about a 45 minutes - I'll
check when I get in .
dazzalondon said:
I move a step forward, then I have to spend a day on 1 little problem. VS
Click to expand...
isn't impressing me anymore. I have deadlines to meet and it is just too
damn easy to get error pages.
Enough of the moan, the mistake is me, I know, but why oh why oh why does
Click to expand...
this statement not update my SQL Server DB:
****
Dim str As String
Dim con2 As SqlConnection
Dim cmdUPDATE As SqlCommand
Dim intUpdateCount As Integer

con2 = New SqlConnection("xxxxxxx")

str = "UPDATE ClientDetails SET ContactTitle=@Title,
Click to expand...
ContactFirstName=@FirstName, ContactLastName=@Surname, Tel=@Tel,
HospContactPosition=@Position, Fax=@Fax, Add1=@Add1, Add2=@Add2, Add3=@Add3,
Add4=@Add4, PostCode=@PostCode, OrgName=@OrgName WHERE Email='" & stateEmail
& "'"
 
Hi
One question is stateEmail is a variable or textbox? if textbox, add .text afterit
One simple thing is just replace all parameters with values, add the following in the beginning of the sub
After: Dim str As Strin
str = "UPDATE ClientDetails SET ContactTitle='" & txtTitle.Text & "', ContactFirstName='" & Fname.Text & "', ContactLastName='" & Sname.Text & "', Tel='" & Tel.Text & "', HospContactPosition='" & txtPosition.Text & "', Fax='" & Fax.Text & "', Add1='" & Add1.Text & "', Add2='" & Add2.Text & "', Add3='" & Add3.Text & "', Add4='" & Add4.Text & "', PostCode='" & PostCode.Text & "', OrgName='" & OrgName.Text & "' WHERE Email='" & stateEmail & "'
Response.Write(str
Exit Su
Then try execute the str value in Query Analyzer to check if the query has any problem

Bin Song, MCP
 
Back
Top