Add record does not add to DB

[jsavoor]

Hi Everyone

I am new to this forum and ASP programming in general. I am writing a warranty registration system and have written the data retrieval modules successfully. I am having problems with the ADD RECORD module.

I am using a ACCESS 2003 MDB file. I do not get any error at all... but the new record just is not created.

<%

Dim s_cno, s_email
Dim s_var
Dim s_dop
Dim s_invno
Dim s_model
Dim s_sno
Dim s_units
Dim mySQL, myData
Dim objRS, objRS2


s_email=Request.Form("f_email")
s_var=request.Form("f_var")
s_dop=request.Form("f_dop")
s_invno=request.Form("f_invno")
s_model=request.Form("f_model")
s_sno=request.Form("f_sno")
s_units=request.Form("f_units")

' Declare values

mySQL = "select * from cust where email='"& s_email &"'"

' OPEN DATABASE!!!!

Set dbCONN=Server.CreateObject("ADODB.Connection")
dbCONN.ConnectionTimeout=60
dbCONN.Open"DSN=wreg"

' Open the recordset

Set objRS=Server.CreateObject("ADODB.RecordSet")
call objRS.open( mySQL,dbConn, 0, 1 )

' Get Customer Number

s_cno = objRS.Fields("cno").value

' Now open the SERIAL table for write

myData = "Select * from serial"

Set objRS2=Server.CreateObject("ADODB.RecordSet")
' objRS2.open "SELECT * from Serial", dbCONN, adOpenStatic, adLockBatchOptimistic, adCmdText
objRS2.open "SELECT * from Serial", dbCONN, adOpenDynamic, adLockBatchOptimistic, adCmdText

objRS2.MoveLast
objRS2.AddNew

objRS2("cno") = s_cno
objRS2("var") = s_var
objRS2("dop") = s_dop
objRS2("invno") = s_invno
objRS2("model") = s_model
objRS2("sno") = s_sno
objRS2("units") = s_units

objRS2.Update

%>

I am not sure what is wrong.

Would appreciate your assistance.

[jsavoor]

I have now put a small ON ERROR code in just before the UPDATE command.

On Error Resume Next
objRS2.Update

If ERR <> 0 then
Response.Write("ERROR: "& ERR)
End if

The error it prints is: ERROR: -2147467259

What is this ??

[jsavoor]

I have tried another method using INSERT.

But now the error trap tells me that I do not have permission to update the databse. I have already set the security in IIS to LOW for the DB directory.

The code is as follows:

<%

Dim s_cno, s_email
Dim s_var
Dim s_dop
Dim s_invno
Dim s_model
Dim s_sno
Dim s_units
Dim mySQL, myData
Dim objRS, objRS2


s_email=Request.Form("f_email")
s_var= request.Form("f_var")
s_dop= request.Form("f_dop")
s_invno= request.Form("f_invno")
s_model= request.Form("f_model")
s_sno= request.Form("f_sno")
s_units= request.Form("f_units")

' Declare values

mySQL = "select * from cust where email='"& s_email &"'"

' OPEN DATABASE!!!!

Set dbCONN=Server.CreateObject("ADODB.Connection")
dbCONN.ConnectionTimeout=60
dbCONN.Open"DSN=wreg"

' Open the recordset

Set objRS=Server.CreateObject("ADODB.RecordSet")
call objRS.open( mySQL,dbConn, 0, 1 )

' Get Customer Number

s_cno = objRS.Fields("cno").value

' Now open the SERIAL table for write

myData = "Select * from serial"
Set objRS2=Server.CreateObject("ADODB.RecordSet")
objRS2.open "SELECT * from Serial", dbCONN

MySQL = "INSERT INTO SERIAL (cno, var, dop, invno, model, sno, units) VALUES (s_cno, s_var, s_dop, s_invno, s_model, s_sno, s_units)"

on error resume next
dbCONN.Execute MySQL,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
objRS.close
set objRS=Nothing
objRS2.close
set objRS2=Nothing
dbCONN.Close
Set dbCONN=Nothing
%>

Appreciate some help please.

[mwood]

If this is on a windows server using iis then the IUSR_ServerName has to have permission to read and write to the database.

[vijaydiwakar]

I have tried another method using INSERT.

But now the error trap tells me that I do not have permission to update the databse. I have already set the security in IIS to LOW for the DB directory.

The code is as follows:

<%

Dim s_cno, s_email
Dim s_var
Dim s_dop
Dim s_invno
Dim s_model
Dim s_sno
Dim s_units
Dim mySQL, myData
Dim objRS, objRS2


s_email=Request.Form("f_email")
s_var= request.Form("f_var")
s_dop= request.Form("f_dop")
s_invno= request.Form("f_invno")
s_model= request.Form("f_model")
s_sno= request.Form("f_sno")
s_units= request.Form("f_units")

' Declare values

mySQL = "select * from cust where email='"& s_email &"'"

' OPEN DATABASE!!!!

Set dbCONN=Server.CreateObject("ADODB.Connection")
dbCONN.ConnectionTimeout=60
dbCONN.Open"DSN=wreg"

' Open the recordset

Set objRS=Server.CreateObject("ADODB.RecordSet")
call objRS.open( mySQL,dbConn, 0, 1 )

' Get Customer Number

s_cno = objRS.Fields("cno").value

' Now open the SERIAL table for write

myData = "Select * from serial"
Set objRS2=Server.CreateObject("ADODB.RecordSet")
objRS2.open "SELECT * from Serial", dbCONN

MySQL = "INSERT INTO SERIAL (cno, var, dop, invno, model, sno, units) VALUES (s_cno, s_var, s_dop, s_invno, s_model, s_sno, s_units)"

on error resume next
dbCONN.Execute MySQL,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
objRS.close
set objRS=Nothing
objRS2.close
set objRS2=Nothing
dbCONN.Close
Set dbCONN=Nothing
%>

Appreciate some help please.

I think in the qry
MySQL = "INSERT INTO SERIAL (cno, var, dop, invno, model, sno, units) VALUES (s_cno, s_var, s_dop, s_invno, s_model, s_sno, s_units)"

u've not concanected the values this qry should be like this try this
MySQL = "INSERT INTO SERIAL (cno, var, dop, invno, model, sno, units) VALUES (" & s_cno &"," & s_var & "," & s_dop &"," & s_invno & "," s_model &"," & s_sno &"," & s_units &")"