SQL Update not working

[GoingUp]

I can read info out of my database fine, i just cant seem to get it to update at all. I havent tried insert yet at all.

Here is the code im using.

Dim Sql As String
Dim Cmd As OleDb.OleDbCommand


Dim Reader As OleDb.OleDbDataReader
Dim Con = New OleDb.OleDbConnection("PROPER SERVER INFO EDITED OUT")

Con.Open()

Sql = "UPDATE CUSTOMER SET First_name = '" & fname & "', Last_name = '" & lname & "', Company= '" & company & "', Address= '" & address & "', City= '" & city & "', State= '" & state & "', Zip= '" & zip & "', Email= '" & email & "', Phone_no= '" & phoneno & "', Online_password= '" & pword & "' WHERE Cust_no ='" & idtext & "'"

Cmd = New OleDb.OleDbCommand(Sql, Con)



Here is the string that my system sees, as output by a label.

UPDATE CUSTOMER SET First_name = 'Jerry ', Last_name = 'Smith ', Company= 'Johnson Controls ', Address= '100 Johnson St ', City= 'Milwaukee ', State= 'WI', Zip= '53212', Email= 'jerry@gmail.com ', Phone_no= '4144565896', Online_password= 'qwerty' WHERE Cust_no ='1001'

Any idea why it might not think the customer number is 1001 and then not update?

 

[bunker]

What kind of field is Cust_no in the database? If it's a numeric and not text type then leave out the single quotes. Is this MS SQL, MySQL, Oracle??

 

[Lint21]

I don't see anything wrong with that update statement other than what bunker pointed out. Check the datatypes of zip, phone_no and cust_no in the database.

 

[GoingUp]

Originally posted by: bunker
What kind of field is Cust_no in the database? If it's a numeric and not text type then leave out the single quotes. Is this MS SQL, MySQL, Oracle??

its numeric! going to try that out now!

Edit: didn't work.

This is what the new sql string even looks like.

UPDATE CUSTOMER SET First_name = 'Jerry ', Last_name = 'smith ', Company= 'Johnson Controls ', Address= '100 Johnson St ', City= 'Milwaukee ', State= 'WI', Zip= '53212', Email= 'jerry@gmail.com ', Phone_no= '4144565896', Online_password= 'qwerty' WHERE Cust_no =1001

 

[GoingUp]

Originally posted by: Lint21
I don't see anything wrong with that update statement other than what bunker pointed out. Check the datatypes of zip, phone_no and cust_no in the database.

wouldnt I at least get some sort of error message if there was a problem? my webpage isnt crashing, and nothing is getting output in my error label.

 

[KLin]

are the zip code and phone_no text fields or are they numeric?


EDIT: if the phone_no field is numeric, what numeric datatype is it? I tried it out in access, and I couldn't insert the phone number into the field wirh a datatype set as Long Integer. I think the limit is 2^31 (aka 2147483648). You may want to consider changing the phone_no field to a text field.

 

[MrChad]

Here's an obvious question.

You are calling Cmd.ExecuteNonQuery() after Cmd = New OleDb.OleDbCommand(Sql, Con), right?

 

[Firus]

Also, I have no experience with VBScript or whatever that is 🙂 and databases, but I know in PHP you have to actually handle the error in order for an error to be displayed on the page.

something like:
if(not successful)
echo $conn->ErrorMsg();

 

[Lint21]

Everything that has been mentioned above are good things to look at.

It looks like there are two general possibilities for the problem.

1. The SQL is bad.

2. The app/script isn't doing what it should be.

You can test #1 by pasting that SQL code directly into the query analyzer (is this MS SQL, MySQL, ?). If it updates the row correctly when bypassing the app, then you know the app isn't doing something right. If the direct SQL update doesn't work, try mucking with the code until it does.

 

[Armitage]

Originally posted by: Firus
Also, I have no experience with VBScript or whatever that is 🙂 and databases, but I know in PHP you have to actually handle the error in order for an error to be displayed on the page.

something like:
if(not successful)
echo $conn->ErrorMsg();

Yep ... you may not see an error message unless you ask for it.

Another bit to check ... does the user your script runs as have update privileges on that particular table?

 

[bunker]

We need to know what database it is, but as long as he's using ASP and has detailed error reporting turned on in IE you'll see exactly what the error is.

 

[GoingUp]

Originally posted by: MrChad
Here's an obvious question.

You are calling Cmd.ExecuteNonQuery() after Cmd = New OleDb.OleDbCommand(Sql, Con), right?

No, I wasnt. And thats what my problem was. Thanks 😀

 

[DT4K]

Originally posted by: Gobadgrs

Originally posted by: MrChad
Here's an obvious question.

You are calling Cmd.ExecuteNonQuery() after Cmd = New OleDb.OleDbCommand(Sql, Con), right?

No, I wasnt. And thats what my problem was. Thanks 😀

Yeah, you have to actually execute the query if you want the query to be executed. 😉

 

[KLin]

Originally posted by: DT4K

Originally posted by: Gobadgrs

Originally posted by: MrChad
Here's an obvious question.

You are calling Cmd.ExecuteNonQuery() after Cmd = New OleDb.OleDbCommand(Sql, Con), right?

No, I wasnt. And thats what my problem was. Thanks 😀

Yeah, you have to actually execute the query if you want the query to be executed. 😉

talk about a a pebkac error. 😉