UPDATE statement 1

[Airpan]

I have built an ASP page that utilizes a form to allow for editing information. I ran this SQL in access, that I wrote myself with my textbook (not the wizard) and it works great.

UPDATE cust_logins SET CustName = 'Double Vision Media Group', CUSTUSER = 'dvmg', CUSTPASS = 'dvmg', Email = 'info@dvmg.com'
WHERE CustID=1265;

However, I am getting a syntax error when I try to put it in the asp page.
Syntax error (missing operator) in query expression '[CustID]='.
Here is the code I wrote for the ASP page:

SQL = SQL = "UPDATE cust_logins SET CustName='"&custname&"',CUSTUSER='"&custuser&"',CUSTPASS='"&custpass&"',Email='"&custemail&"' WHERE [CustID]="& customer &""

If anyone needs to see more of my code I can supply it, but thought this would be a good place to start. THe CustID is an integer, BTW.

 

[bborissov]

What type is customer variable? Is it some kind of numeric?
Also why you have
SQL = SQL = ......?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[sammyc]

something isn't what it seems with CustID's value. Off hand I'd say customer = NOTHING

sammy at Personafile

 

[Airpan]

bborisov,
the double sql's were a copy and paste error that is not actually in my code. CustID is an integer, so it is numeric.
Sammyc,
I think you are right, CustId appears to be throwing the whole thing off. I will try your suggestion and post back.

 

[Airpan]

bborisov,
i am sorry i misread your question. the customer variable is a numeric, as it is the CustID. I am trying to not allow the end user to edit that, so I thought I would try to capture it as a means with which to have the SQL statement update only that particular record.

 

[Airpan]

Here is what the response.write(sql) statement yielded.

UPDATE cust_logins SET CustName='',CUSTUSER='',CUSTPASS='',Email='' WHERE [CustID]= NOTHING

 

[SQLSister]

it looks as if you have not populated your variables with any data.

Questions about posting. See faq183-874

 

[bborissov]

If the variable is some kind of numeric you must convert it before you build the string

SQL = "UPDATE cust_logins SET CustName='"&custname&"',CUSTUSER='"&custuser&"',CUSTPASS='"&custpass&"',Email='"&custemail&"' WHERE [CustID]="& ToStr$(customer)

or whatever the syntax it is.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Airpan]

bborisov,
Here is the complete code:

<%
customer=Request.QueryString("customer") 
frmcustname=Request.Form("custname")
frmcustuser=Request.Form("custuser")
frmcustpass=Request.Form("custpass")
frmcustemail=Request.Form("custemail")
if request.form("submit") <> "" Then
Set Con = Server.CreateObject("ADODB.Connection")
	Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")
'SQL = "INSERT INTO cust_logins (CUSTUSER,CUSTPASS,CustName,email) VALUES ('"& frmcustuser &"','"& frmcustpass &"','"& frmcustname &"','"& frmcustemail &"')"
SQL = "UPDATE cust_logins SET [CustName]='"&frmcustname&"',[CUSTUSER]='"&frmcustuser&"',[CUSTPASS]='"&frmcustpass&"',[Email]='"&frmcustemail&"' WHERE [CustID]= "& customer &" "
	   'response.write(sql)
	   Con.execute(sql)
	   End if

Con.Close  
%>

The variable customer is a querystring which you will see at the top. As you can see, I commented out the insert string that works on my addcust page.

 

[bborissov]

Your insert statement works because customer is not involved there. Run SQL Profiler and see what is actual code you write at SQL Server side.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Airpan]

bborissov,
i don't have SQL Server... I am running this on IIS with XP. I posted the SQL that I ran in Access which was able to populate the database. I know that it is having an issue with the "customer" part of it, and I think it is because I am using it to capture the customer id from the previous page. After looking closely at my other pages that seem to work, I noticed (as you stated) that it does not utilize the query string. So, I think I will start by removing that from the page and start working from there. I will post back if I run into trouble. Thank you for your help.

 

[AlexCuse]

Airpan -

You should ask in Access forums, as it and SQL Server are completely different animals.

I ran this SQL in access, that I wrote myself with my textbook (not the wizard) and it works great.

Is this homework?

Good Luck,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[Airpan]

No it isn't homework... and I have already read the policy here for that. I am working for a media group and building admin side pages to access their customer database. I am using a text book that I had when I went to school for SQL, Oracle, and some other programming languages back in 2000. I don't believe in cheating.
I got it to work bborissov. I realized after you pointed that out in your last post that I was trying to utilize the query string when I didn't need to, not to mention, I didn't include the customer id field in my form which was not allowing the value to be passed. At any rate, thanks for pointing it out and I got it.
I normally post in the ASP side but figured since I was having problems with the SQL end of it I would post here. Sorry, won't happen again.

 

[bborissov]

Glad you found a solution

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP