Unable to save recordset into an access database

[Anand]

Hi
I am trying to make a page where i simply collect data from a form and
update it in the database. i am useing a recodset to do the above. The
vars are being passed into the script and everythign seems to be
working fine, but when i do the recorset.Update command i dont think
the data is actualy being saved in the database i have also tried
beginTrans and commitTrans they dont work either.....i would really
appreciate if u could give me any sugestions...here's the code:
<code>

<%
' Connect to database
Set conn_add = Server.CreateObject("ADODB.Connection")
'conn_add.mode = 3 'readWrite mode
conn_add.Open "DSN=semda; UserID=USERIDSTRING; pwd=PWDSTRING"

'conn_add.BeginTrans

if ucase(TypeName(conn_add)) = "CONNECTION" then
response.Write("CONNECTION MADE!")
end if


If Request.Form("DescShort") = "" Or Request.Form("DescLong") = ""
Then
ErrStr = "You didn't fill in all the required fields. Please go back
and enter all required data."
Else 'Insert into database

'pulling out max_job
Set maxJobID = Server.CreateObject("ADODB.Recordset")
maxJobID.open "Select max(JobID) as max_job from JobList", conn_add
Do while not maxJobID.eof
max_job=maxJobID("max_job")
maxJobID.MoveNext
Loop
response.Write("<br>MAX JOB ID IS: "& max_job &"<br>")
max_job=max_job+1
maxJobID.Close
Set maxJobID= Nothing

Set sthRecordset = Server.CreateObject("ADODB.Recordset")
on error resume next

sthRecordset.open "JobList",conn_add,2,3
'2,3 adOpenDynamic=2/adOpenKeyset = 1, adLockOptimistic=3

'check for errors
on error resume next
If conn_add.Errors.count > 0 Then
Set objErr = Server.CreateObject("ADODB.Error")
for each objErr in conn_add.Errors
If objErr.Number <> 0 Then
response.Write("Number: " & objErr.Number & "<p>")
response.Write("Description: " & objErr.Description & "<p>")
response.Write("Source: " & objErr.Source & "<p>")
response.Write("SQLState: " & objErr.SQLState & "<p>")
response.Write("NativeError: " & objErr.NativeError & "<p>")
End If
next
Else
sthRecordset.AddNew
response.Write("NEW RECORD ADDED")
sthRecordset.Fields("JobID") = max_job
sthRecordset.Fields("DescShort") = Request.Form("DescShort")
sthRecordset.Fields("DescLong") = Request.Form("DescLong")
sthRecordset.Fields("TypeID") = Request.Form("TypeID")
sthRecordset.Fields("PayLow") = Request.Form("PayLow")
sthRecordset.Fields("PayHi") = Request.Form("PayHi")
sthRecordset.Fields("ContactName") = Request.Form("ContactName")
sthRecordset.Fields("Phone") = Request.Form("Phone")
sthRecordset.Fields("Fax") = Request.Form("Fax")
sthRecordset.Fields("Email") = Request.Form("Email")
sthRecordset.Fields("Web") = Request.Form("Web")
sthRecordset.Fields("Verified") = "No"
If Request.Form("ExpireDate") = "" Then
sthRecordset.Fields("ExpireDate") = Date()+30
Else
sthRecordset.Fields("ExpireDate") = Request.Form("ExpireDate")
End if
sthRecordset.Fields("EnterDate") = Now()

If sthRecordset.Fields("ExpireDate") = "" Then
ErrStr = "There was a problem converting the expiration date you
entered. " &_
"Please go back and make sure you entered a date in the format
specified."
response.Write("<br>IN HERE<br>")
sthRecordset.CancelUpdate
'conn_add.RollbackTrans
Else
sthRecordset.Update
'conn_add.CommitTrans
JobID = sthRecordset.Fields("JobID")
response.Write("<br>NEW RECORD ADDED TOTALY with job id: "& JobID)
EXPdate = sthRecordset.Fields("ExpireDate")
response.Write("<br>EXP DATE: "& EXPdate)
End If
sthRecordset.Close
Set sthRecordset = Nothing
End If
End If
%>

</code>

[Bob Barrows]

Anand wrote:

> Hi
> I am trying to make a page where i simply collect data from a form and
> update it in the database. i am useing a recodset to do the above. The
> vars are being passed into the script and everythign seems to be
> working fine, but when i do the recorset.Update command i dont think
> the data is actualy being saved in the database i have also tried
> beginTrans and commitTrans they dont work either.....i would really

No error messages? Have you made sure the IUSR account has NTFS Change
permissions on the folder containing your database?

[url]http://www.aspfaq.com/show.asp?id=2062[/url] - updatable cursor
[url]http://www.aspfaq.com/show.asp?id=2009[/url] - 80004005 errors

> appreciate if u could give me any sugestions...here's the code:
> <code>
>
> <%
> ' Connect to database
> Set conn_add = Server.CreateObject("ADODB.Connection")

"conn_add"? Why not just "cn"? :-)
Save yourself some typing, and make your code a little easier to read ...
:-)
And actually, I see in the code below that you are using this connection to
do more than simply add a record, so tacking on that "_add" may be
misleading.

> 'conn_add.mode = 3 'readWrite mode

Not necessary.

> conn_add.Open "DSN=semda; UserID=USERIDSTRING; pwd=PWDSTRING"
>

Don't use ODBC. See [url]www.able-consulting.com/ado_conn.htm[/url] for examples of
connection strings using the native OLEDB Provider for Microsoft Jet.

Why are you providing a username and password? Is the database protected
using a workgroup file?

> 'conn_add.BeginTrans
>
> if ucase(TypeName(conn_add)) = "CONNECTION" then
> response.Write("CONNECTION MADE!")
> end if
>
>
> If Request.Form("DescShort") = "" Or Request.Form("DescLong") = ""
> Then
> ErrStr = "You didn't fill in all the required fields. Please go back
> and enter all required data."
> Else 'Insert into database
>
> 'pulling out max_job
> Set maxJobID = Server.CreateObject("ADODB.Recordset")

Again, less-than-optimal naming convention. It is not obvious that you are
creating a recordset variable here, At least, use a prefix, "rs", so people
(including yourself two years from now) who encounter this variable later on
in your code can see that it's a recordset without scrolling back up to find
the CreateObject statement ...

<snip>

You do realize that if two users run this query simultaneously, that they
will both get the same max_job value? If this app will not have a lot of
activity, you may get away with this. However, I recommend that you convert
JobID to an autonumber field and let Jet assign values to it.

>
> Set sthRecordset = Server.CreateObject("ADODB.Recordset")
> on error resume next

Stop here. Do not use a recordset to modify data in your database. Yes, I
know that using a recordset makes it easy for you to code, but it is very
inefficient and will limit the number of concurrent users that your database
will be able to handle.

I suggest you use Access to create a saved parameter query to do this
insert. Here are the steps:

1. Open the database in Access.
2. Go to the Queries tab and click the button to create a new query in
Design mode.
3. Select the JobList table from the dialog to add it to the upper pane of
the Design window, and close the dialog box.
4. Go to the Query menu and select Append Query. In the ensuing dialog,
select the Joblist table again from the dropdown and click OK.
4. Use the Shift key to select/highlight all the fields you wish to insert
data into from the table in the upper pane and drag and drop them into the
first row (the "Field" row) of the first column in the grid. They will all
be added to the grid. In each column, you will see that Access has put the
corresponding field name into the Append To row.
5. Switch to SQL View (use the View menu, or the toolbar button, or the
right-click context menu). You will see something like

INSERT INTO JobList (<columns>)
SELECT <columns> FROM JobList

What you are going to do is replace the SELECT... portion with a VALUES
clause, so it will look like this:

INSERT INTO JobList (<columns>)
VALUES (<columns>)

The easiest thing to do is replace the word "SELECT" with VALUES, put
parentheses around the column names list, and delete the FROM clause.
In the parentheses, change the column names to parameter names. Use a naming
convention to guarantee that the parameter names do not match any of the
actual field names in the table. I usually prefix them with a "p".
It's a good idea to leave the brackets around each parameter name (or put
brackets there if Access did not put brackets around each column name). What
you will end up with should look like this:

INSERT INTO JobList (JobID, DescShort, ...)
VALUES ([pJobID], [pDescShort], ...)

(if you take my advice to convert JobID to an autonumber field, you do not
want to include it in this query: that field is automatically assigned a
value when a record is inserted, including it in the query should cause an
error when you test it)

Save the query, giving it a name such as qInsJobListRecord. Do not switch
back to Design View: in earlier versions of Access doing so would mess up
your SQL.

Now, test it by running it (use the button with the exclamation point (!) in
the toolbar). You will see that Access will prompt you for values for each
of the parameters you created in the VALUES list. After you supply the last
value, you will be prompted to confirm the insert; click Yes and, then go
check your table to verify that the record was added.

Now you have a query which you know will work, and you can now write the
code to execute it in ASP. So, delete the "Set sthRecordset =" line, and
replace the recordset addnew code with these lines of code (I will include
the JobId parameter just in case it is not an autonumber field):

If Request.Form("ExpireDate") = "" Then
dExpDate = Date()+30
Else
dExpDate = CDate(Request.Form("ExpireDate"))
End If
conn_add.qInsJobListRecord max_job, _
Request.Form("DescShort"), Request.Form("DescLong"), _
Request.Form("TypeID"), ..., dExpDate

> JobID = sthRecordset.Fields("JobID")

?
Why not:
JobID = max_job

If you do convert JobID to an autonumber field, you will need to do this to
get the id of the new record:

Set rs=conn_add.Execute("SELECT @@IDENTITY",,1)
if not rs.eof then
JobID = rs(0).value
else
'problem inserting record... an earlier error should have occurred
end if


Make sure you add this line as well:
conn_add.close: set conn_add = nothing

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"