Inserting Dates into MS-Access ?!?!?

[Dean]

This seems like it should be easy but I am stumped. I am trying take
a variable, add to it, and insert the result as a new record into
Access.



The ending result is:

INSERT INTO Period (CoID, CkDate) Values 1, #2007/01/26#)

My code is Dim RecID
Dim Conn
Dim sDate, eDate, ckDate
Dim tSQL

RecID=ccdlookup("PeriodID","MaxPeriod","CoID=" &
request.Cookies("myco"), DBaccesspayroll)
ckDate=ccdlookup("CkDate","Period","PerID=" & RecID,dbaccesspayroll)

ckDate=ccformatdate(DateAdd("d",AddPayroll.RadioButton1.value,ckDate),Array("yyyy",
"/", "mm", "/", "dd"))

tSQL = "INSERT INTO Period (CoID, CkDate)" & " Values "&
request.Cookies("myco") & ", #" & ckDate &"#)"
response.Write tSQL
Response.end
Set conn = new clsdbaccesspayroll
conn.execute(tSQL)
'conn.close

'End Custom Code

The error message I am getting is:

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either
closed or invalid in this context.




Any ideas?

 

[Bob Barrows [MVP]]

This seems like it should be easy but I am stumped. I am trying take
a variable, add to it, and insert the result as a new record into
Access.



The ending result is:

INSERT INTO Period (CoID, CkDate) Values 1, #2007/01/26#)

In addition to what Jan said, further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Dean]

My tSQL variable now yields this result:
INSERT INTO Period (CoID, CkDate) Values (1, #2007/01/26#)

but I still get "ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either
closed or invalid in this context.
"

error.

I am going to review the comments from Bob.

 

[Bob Lehmann]

Show the code for this class 'clsdbaccesspayroll'

Bob Lehmann

 

[Dean]

I think I almost got what Bob was saying on the previous post.


I am looking at using an Append query in Access then run that query
from ASP. The one article from Bob says this is even faster.

So I have created my Append Query in Access called AddDed:

INSERT INTO PayrollDetail ( PayFld, PayFldType, PayHeaderID )
SELECT EmployeeDeduction.Deduction, "DedEE" AS txt, [Which Header] AS
Hdr
FROM EmployeeDeduction
WHERE (((EmployeeDeduction.EmployeeID)=[Which Employee]));


The prompts for Which Header and Which Employee.

My ASP ends up being:

Dim Conn
Dim P1
Dim P2
P1= ccGetParam("HeaderID",-1)'This functions perfectly
P2= ccGetParam("EmpId",-1)'This Functions perfectly

Set conn = new clsdbaccesspayroll
Conn.open
Conn.AddDed P1,P2

Conn.close


I get an error saying:
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Conn.AddDed'


It seems I am so close but so far. Any ideas.

 

[Bob Barrows [MVP]]

I think I almost got what Bob was saying on the previous post.


I am looking at using an Append query in Access then run that query
from ASP. The one article from Bob says this is even faster.

So I have created my Append Query in Access called AddDed:

INSERT INTO PayrollDetail ( PayFld, PayFldType, PayHeaderID )
SELECT EmployeeDeduction.Deduction, "DedEE" AS txt, [Which Header] AS
Hdr
FROM EmployeeDeduction
WHERE (((EmployeeDeduction.EmployeeID)=[Which Employee]));


The prompts for Which Header and Which Employee.

My ASP ends up being:

Dim Conn
Dim P1
Dim P2
P1= ccGetParam("HeaderID",-1)'This functions perfectly
P2= ccGetParam("EmpId",-1)'This Functions perfectly

Set conn = new clsdbaccesspayroll
Conn.open
Conn.AddDed P1,P2

Conn.close


I get an error saying:
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Conn.AddDed'


It seems I am so close but so far. Any ideas.

Are you using ODBC or OLEDB? See:
http://www.aspfaq.com/show.asp?id=2126

 

[Dean]

Are you using ODBC or OLEDB? See:http://www.aspfaq.com/show.asp?id=2126

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

I am using OLEDB. I did figure it out or I figured something out. I
created a string (qSQL) then appended the name of the query with the
parameters.

Dim Conn
Dim P1
Dim P2
Dim qSQL
P1= ccGetParam("HeaderID",-1)
P2= ccGetParam("EmpId",-1)
qSQL = "AddDed '" & P1 & "','" & P2 &"'"
Set conn = new clsdbaccesspayroll
Conn.open

Conn.Execute(qSQL)

Conn.close

I'm going to do some more testing, but this seems to work GREAT!!! I
don't know what made me think of it. This will be 1,000 times easier
than manually writing my own sql strings especially when some the sql
command gets to be complicated. I am assuming I can use with a delete
query and update query as well.