|
 |
asp_databases thread: Insert to Access using Autonumber
Message #1 by "Drew, Ron" <RDrew@B...> on Thu, 30 Aug 2001 13:55:58 -0400
|
|
I have an access database with a table called codetable with 4 columns
called apptype, appfilter and appcode with an autonumber key column called
appid.
I am trying to insert new records from a form that posts the 3 fields to my
asp. I have tried using the object command and tried using the addnew..both
fail. My problem is I can not figure out how to get the next appid into it.
Here is my code first using COMMAND ..error says missing parameter 1 appid.
Then followed by addnew...I do not care which way as long as it works...
HELP!!!
<%
set objConn = Server.CreateObject("ADODB.Connection")
set objCommand = Server.CreateObject("ADODB.Command")
objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\InetPub\wwwroot\asptest\ASPCode\aspjavainfo.mdb;" & _
"User Id=admin;" & _
"Password=;"
objCommand.ActiveConnection = objConn
strSQL = "INSERT INTO codetable (appid,apptype, appfilter, appcode)" &_
" VALUES (?,?,?,?)"
objCommand.CommandText = strSQL
objCommand.Parameters.Append objCommand.CreateParameter("appid",adGUID)
objCommand.Parameters.Append objCommand.CreateParameter("apptype",200, ,10)
objCommand.Parameters.Append objCommand.CreateParameter("appfilter",200,
,25)
objCommand.Parameters.Append objCommand.CreateParameter("appcode",200, ,250)
objCommand("apptype") = Request.Form("apptype")
objCommand("appfilter") = Request.Form("appfilter")
objCommand("appcode") = Request.Form("appcode")
objCommand.Execute
set objCommand = Nothing
set objConn = Nothing
%>
now using addnew....
set objRecordset = Server.CreateObject("ADODB.Recordset")
strSQL = "Select * from codetable where 0=1;"
objRecordset.Open strSQL, DB_CONNECTIONSTRING, adOpenKeyset,
adLockPessimistic, adCmdText
objRecordset.AddNew
objRecordset.Fields("apptype") = Request.Form("apptype")
objRecordset.Fields("appfilter") = Request.Form("appfilter")
objRecordset.Fields("appcode") = Request.Form("appcode")
objRecordset.Update
Message #2 by "Ken Schaefer" <ken@a...> on Fri, 31 Aug 2001 13:21:45 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Drew, Ron" <RDrew@B...>
Subject: [asp_databases] Insert to Access using Autonumber
: I am trying to insert new records from a form that posts the 3 fields to
my
: asp. I have tried using the object command and tried using the
addnew..both
: fail.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Why does the .AddNew fail?
Also, when you open a recordset, use an explicitly created connection
object, *NOT* a connection string - especially with Access.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My problem is I can not figure out how to get the next appid into it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The AppId is generated automatically by Access - you can't insert a value
into it.
If you want to get the newly created value out, check out:
http://www.adopenstatic.com/experiments/fastestautonumber.asp
Cheers
Ken
Message #3 by "Drew, Ron" <RDrew@B...> on Fri, 31 Aug 2001 11:09:56 -0400
|
|
Thanks for the response Ken...you seem to really know your stuff. I went to
the link below and modified my code to fit what you have....here is my new
code and the error message I am getting.
................error message.........is..........
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/asptest/ASPCode/addasp1.asp, line 35
..............code is.........
Dim objConn, objRS, intID
set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\InetPub\wwwroot\asptest\ASPCode\aspjavainfo.mdb;" & _
"User Id=admin;" & _
"Password=;"
set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic ' THIS is line
35
.ActiveConnection = Application("adOpenStatic_ConnectionString")
End With
objRS.Open "codetable",,,,adCmdTable
objRS.AddNew
objRS.Fields("apptype") = strtype
objRS.Fields("appkeywork") = strkeyword
objRS.Fields("appshortdesc") = strshortdesc
objRS.Fields("appcode") = strcode
If MsgBox("Save All Changes", vbYesNo) = vbYes Then
objRS.Update
MsgBox "All Changes Updated"
intID = objRS("appid")
Response.Write "<p>Record ID " & intID & " added!!</p>"
Else
objRS.CancelUpdate
MsgBox "Update Cancelled"
End If
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing
set intID = Nothing
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, August 30, 2001 11:22 PM
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Drew, Ron" <RDrew@B...>
Subject: [asp_databases] Insert to Access using Autonumber
: I am trying to insert new records from a form that posts the 3 fields to
my
: asp. I have tried using the object command and tried using the
addnew..both
: fail.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Why does the .AddNew fail?
Also, when you open a recordset, use an explicitly created connection
object, *NOT* a connection string - especially with Access.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My problem is I can not figure out how to get the next appid into it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The AppId is generated automatically by Access - you can't insert a value
into it.
If you want to get the newly created value out, check out:
http://www.adopenstatic.com/experiments/fastestautonumber.asp
Cheers
Ken
Message #4 by Sam Clohesy <sam@e...> on Fri, 31 Aug 2001 16:43:48 +0100
|
|
Have you got adovbs.inc at top of the page?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: 31 August 2001 16:10
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
Thanks for the response Ken...you seem to really know your stuff. I went to
the link below and modified my code to fit what you have....here is my new
code and the error message I am getting.
................error message.........is..........
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/asptest/ASPCode/addasp1.asp, line 35
..............code is.........
Dim objConn, objRS, intID
set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\InetPub\wwwroot\asptest\ASPCode\aspjavainfo.mdb;" & _
"User Id=admin;" & _
"Password=;"
set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic ' THIS is line
35
.ActiveConnection = Application("adOpenStatic_ConnectionString")
End With
objRS.Open "codetable",,,,adCmdTable
objRS.AddNew
objRS.Fields("apptype") = strtype
objRS.Fields("appkeywork") = strkeyword
objRS.Fields("appshortdesc") = strshortdesc
objRS.Fields("appcode") = strcode
If MsgBox("Save All Changes", vbYesNo) = vbYes Then
objRS.Update
MsgBox "All Changes Updated"
intID = objRS("appid")
Response.Write "<p>Record ID " & intID & " added!!</p>"
Else
objRS.CancelUpdate
MsgBox "Update Cancelled"
End If
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing
set intID = Nothing
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, August 30, 2001 11:22 PM
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Drew, Ron" <RDrew@B...>
Subject: [asp_databases] Insert to Access using Autonumber
: I am trying to insert new records from a form that posts the 3 fields to
my
: asp. I have tried using the object command and tried using the
addnew..both
: fail.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Why does the .AddNew fail?
Also, when you open a recordset, use an explicitly created connection
object, *NOT* a connection string - especially with Access.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My problem is I can not figure out how to get the next appid into it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The AppId is generated automatically by Access - you can't insert a value
into it.
If you want to get the newly created value out, check out:
http://www.adopenstatic.com/experiments/fastestautonumber.asp
Cheers
Ken
Message #5 by "Drew, Ron" <RDrew@B...> on Fri, 31 Aug 2001 11:43:53 -0400
|
|
Did not....added the following to the top
<!-- #INCLUDE FILE="include/adovbs.inc" -->
and now line 36 has the error which is...
.ActiveConnection = Application("adOpenStatic_ConnectionString")
-----Original Message-----
From: Sam Clohesy [mailto:sam@e...]
Sent: Friday, August 31, 2001 11:44 AM
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
Have you got adovbs.inc at top of the page?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: 31 August 2001 16:10
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
Thanks for the response Ken...you seem to really know your stuff. I went to
the link below and modified my code to fit what you have....here is my new
code and the error message I am getting.
................error message.........is..........
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/asptest/ASPCode/addasp1.asp, line 35
..............code is.........
Dim objConn, objRS, intID
set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\InetPub\wwwroot\asptest\ASPCode\aspjavainfo.mdb;" & _
"User Id=admin;" & _
"Password=;"
set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic ' THIS is line
35
.ActiveConnection = Application("adOpenStatic_ConnectionString")
End With
objRS.Open "codetable",,,,adCmdTable
objRS.AddNew
objRS.Fields("apptype") = strtype
objRS.Fields("appkeywork") = strkeyword
objRS.Fields("appshortdesc") = strshortdesc
objRS.Fields("appcode") = strcode
If MsgBox("Save All Changes", vbYesNo) = vbYes Then
objRS.Update
MsgBox "All Changes Updated"
intID = objRS("appid")
Response.Write "<p>Record ID " & intID & " added!!</p>"
Else
objRS.CancelUpdate
MsgBox "Update Cancelled"
End If
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing
set intID = Nothing
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, August 30, 2001 11:22 PM
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Drew, Ron" <RDrew@B...>
Subject: [asp_databases] Insert to Access using Autonumber
: I am trying to insert new records from a form that posts the 3 fields to
my
: asp. I have tried using the object command and tried using the
addnew..both
: fail.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Why does the .AddNew fail?
Also, when you open a recordset, use an explicitly created connection
object, *NOT* a connection string - especially with Access.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My problem is I can not figure out how to get the next appid into it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The AppId is generated automatically by Access - you can't insert a value
into it.
If you want to get the newly created value out, check out:
http://www.adopenstatic.com/experiments/fastestautonumber.asp
Cheers
Ken
Message #6 by "Drew, Ron" <RDrew@B...> on Fri, 31 Aug 2001 11:55:36 -0400
|
|
after adding the adovbs.inc to the top I ran and got error...I
took the With objRS out and replaced the objRS Open with
objRS.Open "codetable",objConn,adOpenForwardOnly,adLockOptimistic,adCmdTable
changed the typo of keyword, took the msgbox stuff out for testing and bha
la it worked.
Thanks for your response Sam
Ron
-----Original Message-----
From: Sam Clohesy [mailto:sam@e...]
Sent: Friday, August 31, 2001 11:44 AM
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
Have you got adovbs.inc at top of the page?
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: 31 August 2001 16:10
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
Thanks for the response Ken...you seem to really know your stuff. I went to
the link below and modified my code to fit what you have....here is my new
code and the error message I am getting.
................error message.........is..........
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/asptest/ASPCode/addasp1.asp, line 35
..............code is.........
Dim objConn, objRS, intID
set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=c:\InetPub\wwwroot\asptest\ASPCode\aspjavainfo.mdb;" & _
"User Id=admin;" & _
"Password=;"
set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic ' THIS is line
35
.ActiveConnection = Application("adOpenStatic_ConnectionString")
End With
objRS.Open "codetable",,,,adCmdTable
objRS.AddNew
objRS.Fields("apptype") = strtype
objRS.Fields("appkeywork") = strkeyword
objRS.Fields("appshortdesc") = strshortdesc
objRS.Fields("appcode") = strcode
If MsgBox("Save All Changes", vbYesNo) = vbYes Then
objRS.Update
MsgBox "All Changes Updated"
intID = objRS("appid")
Response.Write "<p>Record ID " & intID & " added!!</p>"
Else
objRS.CancelUpdate
MsgBox "Update Cancelled"
End If
objRS.Close
set objRS = Nothing
objConn.Close
set objConn = Nothing
set intID = Nothing
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, August 30, 2001 11:22 PM
To: ASP Databases
Subject: [asp_databases] Re: Insert to Access using Autonumber
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Drew, Ron" <RDrew@B...>
Subject: [asp_databases] Insert to Access using Autonumber
: I am trying to insert new records from a form that posts the 3 fields to
my
: asp. I have tried using the object command and tried using the
addnew..both
: fail.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Why does the .AddNew fail?
Also, when you open a recordset, use an explicitly created connection
object, *NOT* a connection string - especially with Access.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: My problem is I can not figure out how to get the next appid into it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The AppId is generated automatically by Access - you can't insert a value
into it.
If you want to get the newly created value out, check out:
http://www.adopenstatic.com/experiments/fastestautonumber.asp
Cheers
Ken
|
|
|
View Cart
