Some ASP/Sql help please

bribren2001

hey all, any chance you could browse your eye over this and spot any errors please!
it is giving me an error with the "response.write(sSql)" (line24)
thanks

<%
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("db\Game Emporium.mdb") & ";" & _
"Persist Security Info=False"
oConn.Open(sConnection)

M_id= Request("M_id")
M_password= Request("M_password")
M_firstname= Request("M_firstname")
M_secondname= Request("M_secondname")
M_address1= Request("M_address1")
M_address2= Request("M_address2")
M_County= Request("M_County")
M_Country= Request("M_Country")
M_phonenumber= Request("M_phonenumber")
M_email= Request("M_email")
CC_Company= Request("CC_Company")
CC_Number= Request("CC_Number")
CC_Expiry= Request("CC_Expiry")

sSQL = "UPDATE Member SET M_password = '"& M_password & "', M_firstname = '" & M_firstname & "', M_secondname = '"& M_secondname & "', M_address1 = '" & M_address1 & "', M_address2 = '"& M_address2 & "', M_County = '"& M_County & "', M_Country = '"& M_Country & "', M_phonenumber = '"& M_phonenumber & "', M_mobile = '"& M_mobile & "', M_email = '"& M_email & "', CC_Company = '"& CC_Company & "', CC_Number = '"& CC_Number & "', CC_Expiry = '"& CC_Expiry & "', WHERE M_id =" & M_id & " AND M_password = '" & M_password &"'"

oConn.Execute(sSQL)
Response.write(sSQL)
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

Seth Brundle

can't see anything there - one suggestion is to comment out the line
oConn.Execute(sSQL)
and see what it prints

[I wouild also decalre all variables (use OPTION EXPLICIT) & use Request.Form rather than just Request but thats just me]

bribren2001

thanks for that, ill give it a try

bribren2001

i am made them changes.When i click my update button it comes up sSql and nothing else but it does not make any changes in my database

Seth Brundle

it won't update because you commented out the execute line.
Is it printing the sSQL statyement of the 4 characters 'sSQL'?

bribren2001

yeah it prints sSQL when i comment out oconn.execute but when i dont comment it out it gives me an error at line 24 which is response.write(sSql)

also what exactly do you mean by OPTION EXPLICIT?

thanks for your help!

leeroybrown

Adding "Option Explicit" to the top of your code introduces a requirement that all variables be explicitly predefined using a Dim statement. It's a little bit more work but helps avoid problems in the code.

Seth Brundle

<% OPTION EXPLICIT %>
on the 1st line should force you to Dim (declare) all variables

Just double check your code and see if you have
Response.write(sSQL)
or
Response.write("sSQL")
It appears that sSQL is a String rather than a variable.
If you want to email me your code (pref. with db) to info@kbannon.com
Killian

bribren2001

yeah i think ill send them on to you if thats ok
thanks!

Seth Brundle

Try this instead of
sSQL = "Update...
...
oConn.Execute(sSQL)


'open the table & update the data
Dim oRS, sSQL
Set RS = Server.CreateObject("ADODB.Recordset")
theSQL = "UPDATE ....[rest of code snipped]
RS.CursorType = 3
RS.LockType = 3
oRS.Open sSQL, oConn

bribren2001

it was giving me an error with optionexplicit but i sent on the code + db to you.
thanks

Seth Brundle

1. don't store credit card numbers along with the owners name and address!!!
2. You are trying to insert a mobile number without assigning it a value
3. you have a comma after the CC_Expiry = '"& CC_Expiry & "', bit
4. Your M_id is a text datatype - i would use the autonumber feature - easier
5. Based on the code you sent me:-

<% option explicit %>
<%
Dim sConnection, oConn, oRS, sSQL
Dim M_id, M_password, M_firstname, M_secondname, M_address1, M_address2
Dim M_County, M_Country, M_phonenumber, M_email, CC_Company, CC_Number
Dim CC_Expiry, m_mobile


Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Game Emporium.mdb") & ";" & _
"Persist Security Info=False"
oConn.Open(sConnection)

M_id= Request.form("M_id")
M_password= Request.form("M_password")
M_firstname= Request.form("M_firstname")
M_secondname= Request.form("M_secondname")
M_address1= Request.form("M_address1")
M_address2= Request.form("M_address2")
M_County= Request.form("M_County")
M_Country= Request.form("M_Country")
M_phonenumber= Request.form("M_phonenumber")
M_mobile= Request.form("M_mobile")
M_email= Request.form("M_email")
CC_Company= Request.form("CC_Company")
CC_Number= Request.form("CC_Number")
CC_Expiry= Request.form("CC_Expiry")

sSQL = "UPDATE Member SET M_password = '"& M_password & "', M_firstname = '" & M_firstname & "', M_secondname = '"& M_secondname & "', M_address1 = '" & M_address1 & "', M_address2 = '"& M_address2 & "', M_County = '"& M_County & "', M_Country = '"& M_Country & "', M_phonenumber = '"& M_phonenumber & "', M_mobile = '"& M_mobile & "', M_email = '"& M_email & "', CC_Company = '"& CC_Company & "', CC_Number = '"& CC_Number & "', CC_Expiry = '"& CC_Expiry & "' WHERE M_id ='" & M_id & "'"

oConn.Execute(sSQL)
Response.write(sSQL)
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

bribren2001

thanks for looking at that!
im getin d following when i click the update button

it seems as if no values are being taken form the form





UPDATE Member SET M_password = '', M_firstname = '', M_secondname = '', M_address1 = '', M_address2 = '', M_County = '', M_Country = '', M_phonenumber = '', M_mobile = '', M_email = '', CC_Company = '', CC_Number = '', CC_Expiry = '' WHERE M_id =''

Seth Brundle

It worked perfectly on my system
1. that update statement will be visible on the screen as you had the line
Response.Write(sSQL)
2. Have you inserted values into the form before you click the update button?
[edit]3. Are the names of your form's input boxes the same as those you are using Request.Form in front of?[/edit]

bribren2001

ye i double checked all the input box names with those on my form and they were all the same.
just keeps giving the same error as in my previous post

Winters

I would usuallly always declare values using Dim when im working with ASP, just a habbit I have really. There doesnt seem to be any problem in the code by the looks of things, I cant test it out here in collage but might give it a go later when im back on my own PC. By any chance what server are you running it from? IIS 5?

Aswell, seeing as though your working on a database with passwords and credit card numbers you REALLY should password the database and possibly have a script to encript he credit card numbers. Nobody would like the possibility of thir credit card number being stored in a database, in particular a unencripted/passworded one.

You could also try cutting down on the amount of things your adding to the database, possibly have two pages. One with the users details and the other with credit card details. And if you havent already changed it, use Autonumber in the ID field.

bribren2001

im using the slooooooooow brinkster web server.

Karoma

Originally posted by bribren2001
im using the slooooooooow brinkster web server.

what OS do you have?
Make life easier for yourself,spend 5 mins installing iis locally:)

Seth Brundle

I built a form with 14(I think) boxes (each with the appropriate name) and a submit button. I used the POST method and it was sent to the page containing the update code. It updated the table fully.

One thing I forgot to change back to yours when I posted the code was the path to the DB
mine:- Data Source=" & Server.MapPath("Game Emporium.mdb") & ";" & _
yours:- [Data Source=" & Server.MapPath("db\Game Emporium.mdb") & ";" & _[/I]

@winters - I would not store the CC numbers at all - nothing can be protected fully!!!

@bribren2001 - if you are taking CC details can you secure the pages on brinkster [or is this just a test server]?

bribren2001

thanks for the help kbannon and everyone else- it doesnt matter about securing web pages because it is a project for a degree course and they expect us to just be able to do the basics such as login,register update,delete and view pages !


i changed that path for game emporium but to no joy,
just my look i expect!

Seth Brundle

email your form page, the code you currently have and the DB to me again.
K.

Winters

If you are using Brinkster you will need to locate the database in the db folder. This is the only folder which allows reading/writing of databases.

Also in the Server.MapPath you will need to have it set to: Server.MapPath("\username\db\Game Emporium.mdb"). Id also stay away from using spaces in names when developing websites. Can get annoying, try using an underscore or something. Brinkster have a policy of havign you use the /username/db/database.mdb [or \username\db\database.mdb ... cant remember which way the /'s go].

bribren2001

bingo!
just after getin it to work
one minute it wasnt working and i changed somethind and it worked

thanks a million kbannon and winters!

really appreciate your help!!!!!!

Seth Brundle

Originally posted by bribren2001
bingo!
just after getin it to work
one minute it wasnt working and i changed somethind and it worked

what did you change?

bribren2001

i changed a few things as you will see below
thanks again for the help!



<html>
<head>
</head>
<title>Update</title>

<%
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db\Game Emporium.mdb") & ";" & _
"Persist Security Info=False"
oConn.Open(sConnection)

Cust_Username = Session("Cust_Username")

strM_id = Request("M_id")
strM_password = Request("M_password")
strM_firstname = Request("M_firstname")
strM_secondname = Request("M_secondname")
strM_address1= Request("M_address1")
strM_address2= Request("M_address2")
strM_county = Request("M_county")
strM_country = Request("M_country")
strM_phonenumber = Request("M_phonenumber")
strM_mobile = Request("M_mobile")
strM_email = Request("M_email")
strCC_Company= Request("CC_Company")
strCC_Number = Request("CC_Number")
strCC_Expiry = Request("CC_Expiry")



sSQL = "UPDATE Member SET M_password = '" & strM_password & "', M_firstname = '" & strM_firstname & "', M_secondname = '" &_
strM_secondname & "', M_address1 = '" & strM_address1 & "', M_address2= '" & strM_address2 & "', M_County = '" & strM_County & "' , M_Country = '" & strM_Country & "', M_phonenumber= '" & strM_phonenumber & "', M_mobile = '" & strM_mobile & "', M_email= '" & strM_email & "', CC_Company= '" & strCC_Company & "', CC_Number= '" & strCC_Number & "', CC_Expiry= '" & strCC_Expiry & "' WHERE M_id = '" & strM_id & "'"


oConn.Execute(sSQL)


Response.Redirect "updatesuccess.html"
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

</html>