View Full Version : ASP/SQL (help me with my script plz)

22-02-2003, 02:37 AM
ok i'v been trying for ever and i cant see whats wrong
i fexed quite a bit and i'm still getting problems
if you could help or show me a snipit of one of your SQL statments it wold be VERY apreciated

ok i'm trying to insert into a Access Database using SQL

set rsObj = MyConn.execute("SELECT MAX(MemIdNumber) AS MemIdNumber FROM Mem") '''PROBLEM HERE with MemIdNumber'''
a = CInt(rsObj("MemIdNumber")) '''PROBLEM HERE with CInt'''
a = a + 1 '''will be memID'''
set rsObjMemberCheck = nothing
set rsObj = connObj.execute("INSERT INTO members(user_id, username, user_pass, ship_addr, phone, email, fname, lname) VALUES ("& a & ",'" & b & "','" & c &"','" & d & "','" & e & "','" & f & "','" & g & "','" & h & "')") ''''BIG PROB HERE'''

i think it would be easyist if i could see someone elses SQL Insert


Dolby Digital
22-02-2003, 12:42 PM
What is the error?

22-02-2003, 12:43 PM
It's been awhile since I did asp/sql but here goes .....

I assume all your database connections are valid and have the necessary priviliges. :)
From what I can remember you don't need to use the CInt, CStr, etc statements with asp. So, just use:
a = rsObj("MemIdNumber")

Is there a need to use an alias here: ?
set rsObj = MyConn.execute("SELECT MAX(MemIdNumber) AS MemIdNumber FROM Mem")
Your rsObj will have only one result in it, so my pick is that the alias is causing the problem. Try:
set rsObj = MyConn.execute("SELECT MAX(MemIdNumber) FROM Mem") with
a = rsObj or:
a = rsObj(0)

Your insert statement looks fine. Is the user_id field the primary key for that table? Is so, does the database create the key itself? If so, this could be your problem.
Or is it linked to MemIdNumber from the Mem table? Again, if so, and you're not simultaneously creating the data in the Mem table, this will cause big problems.

With insert statements, I like to start small and build them up a step at a time so you know it works; although often tables are not setup to allow null entries.
connObj.execute("INSERT INTO table (fieldName) VALUES (' " & x & " ')") then:
connObj.execute("INSERT INTO table (fieldName, nextFieldName) VALUES (' " & x & " ',' " & y & " ')") etc.

23-02-2003, 03:40 PM
I've written the following script out, I haven't tested it or anything and I'm a little out of practice. The biggest error you had was in your SQL statement.

Before running the script open your Access database and set the user_id field to primary with an autonumber. This removes the need to run the MAX function and manually incriment the returned value.

You should really search the db to see if the person exists before entering their details into the db again. Also you should filter user input to prevent security breaches.

Ensure you have you Access db in a place where the ASP process can open and edit the file. If security is an issue I would recommend using mySQL (www.mysql.com) or a similar database server (postgres, SQLServer) as it offers greater flexibility with user priviledges and data security.

Anyways here's my code written off the cuff without any testing and without writing asp code for a few years.....

'Establish connection MyConn then...

'Get form values
Dim strUser, strPass, strFName, strLName
Dim strStreet, strCity, strCountry
Dim strTitle, strEmail, strPhone
strUser = request.form("user")
strPass = request.form("pass")
strFName = request.form("fname")
strLName = request.form("lname")
strStreet = request.form("addstreet")
strCity = request.form("addcity")
strCountry = request.form("addcountry")
strTitle = request.form("title")
strEmail = request.form("email")
strPhone = request.form("phone")

'Build ship_addr value as in your SQL statement:
'I'm assuming you have one db field for your address
' - this is what your SQL implies.
Dim strShip = strStreet & ", " & strCity & ", " & strCountry

'Note: You should filter the text before entering it into a db
'This will prevent security problems - details of such filters
'can be found in most asp sites

'Build the SQL statement
Dim SQLInsert, SQLValues, SQL
SQLInsert = "INSERT INTO members(username, user_pass, ship_addr, phone, email, fname, lname)"
SQLValues = " VALUES ("
SQLValues = SQLValues & "'" & strUser & "'"
SQLValues = SQLValues & ", '" & strPass & "'"
SQLValues = SQLValues & ", '" & strShip & "'"
SQLValues = SQLValues & ", '" & strPhone & "'"
SQLValues = SQLValues & ", '" & strEmail & "'"
SQLValues = SQLValues & ", '" & strFName & "'"
SQLValues = SQLValues & ", '" & strLName & "'"
SQL = SQLInsert & SQLValues
Dim rs
'I'm sure there is a specific INSERT function related to the ADO Connection object that returns an int
rs = MyConn.execute(SQL)
set rs = Nothing

Set MyConn = nothing

23-02-2003, 03:43 PM
Definitely don't use small, incimental inserts as you end up hitting your db far to many times.
This may work in a single user environment but once you get numerous simultaneous operations the hits on the database increase dramatically.

As a rule of thumb hit the db as few times and as quickly as possible.


24-02-2003, 11:56 AM
> Definitely don't use small, incimental inserts as you end up hitting your db far to many times

Agreed, I should have stated that I meant it can be handy while debugging. :)