hi y'all,
I have a wee problem with my code.
It seems my ADO connection script is ok for grabbing files, but not ok
for creating a new record in that database...
i'm perplexed...please help!
the code:


'-------------------------------------------
' MAIN CONNECTION STRING TO DATABASE...these are includes
'-------------------------------------------
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set DataConn = Server.CreateObject("ADODB.Connection")
pathtodatabase = "../private/website.mdb"
DataConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source="&server.mappath(pathtodatabase)&";" & _
"Jet OLEDB:Database"
cursortype = 3

Sub ExecuteSQL(QueryText,cursortype,recordsetname)
cmdTemp.CommandText = QueryText
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = DataConn
recordsetname.Open cmdTemp, , 1, cursortype
End Sub

firstname=request.form("firstname")
lastname=request.form("lastname")
email=request.form("email")
user=request.form("user")
pass1=request.form("pass1")
pass2=request.form("pass2")

'------------------------------------------
'CHECKING PASSWORD INTEGRITY
'------------------------------------------
if pass1 <> pass2 then
response.redirect("newAccount.asp?error=pw")
end if

'------------------------------------------
'CHECKING USERNAME ISN'T ALREADY IN USE....this works
'------------------------------------------
SET rs1 = server.createObject("ADODB.Recordset")
sqlQuery = "SELECT * FROM accounts WHERE user ='"&user&"'"
CALL executeSQL(sqlQuery,cursortype,rs1)
intNumRecs = rs1.recordCount
rs1.Close

if intNumRecs = 0 then
'------------------------------------------
'INSERTING INTO DATABASE....and this doesn't work
'------------------------------------------
accountActive ="n"

datedelimiter="#"
SET rs2 = server.createObject("ADODB.Recordset")
sqlQuery="INSERT INTO accounts (firstname, lastname, email, user, pass,
accountActive, lastUpdated) VALUES ('" & firstname & "','" & lastname &
"','" &email & "','" & user & "','" & pass1 & "','" & accountActive &
"'," & datedelimiter & now() & datedelimiter &")"

CALL executeSQL(sqlQuery,cursortype,rs2)
rs2.close

Steve,
The "INSERT INTO" does not return a recordset, so you call to ExecuteSQL
is not happy.  Also, if the user enters a single quote in one of the
inputs, is will screw up your SQL string.  You should use a function to
take replace single quotes.  Like so:

Function sql_quote(str)
	str = Replace(str,"'","''")
	sql_quote = "'"& str & "'"
End Function

Function sql_date(str)
	sql_date = "#"& str & "#"
End Function


SQLStr = "INSERT INTO accounts " & _
	   "(firstname, lastname, email, user, pass, accountActive,
lastUpdated) VALUES (" & _
	   sql_quote(firstname) & "," & _
	   sql_quote(lastname) & "," & _
	   sql_quote(email) & "," & _
	   sql_quote(user) & "," & _
	   sql_quote(pass1) & "," & _
	   sql_quote(accountActive) & "," & _
	   sql_date( now() ) & ")"

DataConn.Execute(SQLStr)


Hope this helps.
Pete


> -----Original Message-----
> From: Steve Westwood [mailto:steve@m...]
> Sent: Friday, March 22, 2002 7:42 AM
> To: ASP Databases
> Subject: [asp_databases] INSERT INTO doesn't work!!!
> 
> 
> hi y'all,
> I have a wee problem with my code.
> It seems my ADO connection script is ok for grabbing files, but not ok
> for creating a new record in that database...
> i'm perplexed...please help!
> the code:
> 
> 
> '-------------------------------------------
> ' MAIN CONNECTION STRING TO DATABASE...these are includes
> '-------------------------------------------
> Set cmdTemp = Server.CreateObject("ADODB.Command")
> Set DataConn = Server.CreateObject("ADODB.Connection")
> pathtodatabase = "../private/website.mdb"
> DataConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source="&server.mappath(pathtodatabase)&";" & _
> "Jet OLEDB:Database"
> cursortype = 3
> 
> Sub ExecuteSQL(QueryText,cursortype,recordsetname)
> cmdTemp.CommandText = QueryText
> cmdTemp.CommandType = 1
> Set cmdTemp.ActiveConnection = DataConn
> recordsetname.Open cmdTemp, , 1, cursortype
> End Sub
> 
> firstname=request.form("firstname")
> lastname=request.form("lastname")
> email=request.form("email")
> user=request.form("user")
> pass1=request.form("pass1")
> pass2=request.form("pass2")
> 
> '------------------------------------------
> 'CHECKING PASSWORD INTEGRITY
> '------------------------------------------
> if pass1 <> pass2 then
> response.redirect("newAccount.asp?error=pw")
> end if
> 
> '------------------------------------------
> 'CHECKING USERNAME ISN'T ALREADY IN USE....this works
> '------------------------------------------
> SET rs1 = server.createObject("ADODB.Recordset")
> sqlQuery = "SELECT * FROM accounts WHERE user ='"&user&"'"
> CALL executeSQL(sqlQuery,cursortype,rs1)
> intNumRecs = rs1.recordCount
> rs1.Close
> 
> if intNumRecs = 0 then
> '------------------------------------------
> 'INSERTING INTO DATABASE....and this doesn't work
> '------------------------------------------
> accountActive ="n"
> 
> datedelimiter="#"
> SET rs2 = server.createObject("ADODB.Recordset")
> sqlQuery="INSERT INTO accounts (firstname, lastname, email, 
> user, pass,
> accountActive, lastUpdated) VALUES ('" & firstname & "','" & 
> lastname &
> "','" &email & "','" & user & "','" & pass1 & "','" & accountActive &
> "'," & datedelimiter & now() & datedelimiter &")"
> 
> CALL executeSQL(sqlQuery,cursortype,rs2)
> rs2.close
> 
> 
> 
> 
> 
>