By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

Inserting Dates into MS-Access ?!?!?

P: n/a
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.RadioBu tton1.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?

Feb 10 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
missing "("

INSERT INTO Period (CoID, CkDate) Values (1, #2007/01/26#)
"Dean" <no*****@coveyaccounting.comwrote in message news:11**********************@m58g2000cwm.googlegr oups.com...
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.RadioBu tton1.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?

Feb 10 '07 #2

P: n/a
Dean wrote:
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/...e36562fee7804e

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

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl


--
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"
Feb 10 '07 #3

P: n/a
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.


Feb 10 '07 #4

P: n/a
Show the code for this class 'clsdbaccesspayroll'

Bob Lehmann

"Dean" <no*****@coveyaccounting.comwrote in message
news:11*********************@q2g2000cwa.googlegrou ps.com...
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.




Feb 10 '07 #5

P: n/a
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.

Feb 15 '07 #6

P: n/a
Dean wrote:
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

--
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.
Feb 15 '07 #7

P: n/a
>
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.


Feb 15 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.