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","Ma xPeriod","CoID= " &
request.Cookies ("myco"), DBaccesspayroll )
ckDate=ccdlooku p("CkDate","Per iod","PerID=" & RecID,dbaccessp ayroll)

ckDate=ccformat date(DateAdd("d ",AddPayroll.Ra dioButton1.valu e,ckDate),Array ("yyyy",
"/", "mm", "/", "dd"))

tSQL = "INSERT INTO Period (CoID, CkDate)" & " Values "&
request.Cookies ("myco") & ", #" & ckDate &"#)"
response.Write tSQL
Response.end
Set conn = new clsdbaccesspayr oll
conn.execute(tS QL)
'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?

[Jon Paal]

Re: Inserting Dates into MS-Access ?!?!?

missing "("

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


"Dean" <noreply@coveya ccounting.comwr ote in message news:1171116698 .821834.148280@ m58g2000cwm.goo glegroups.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","Ma xPeriod","CoID= " &
request.Cookies ("myco"), DBaccesspayroll )
ckDate=ccdlooku p("CkDate","Per iod","PerID=" & RecID,dbaccessp ayroll)
>
ckDate=ccformat date(DateAdd("d ",AddPayroll.Ra dioButton1.valu e,ckDate),Array ("yyyy",
"/", "mm", "/", "dd"))
>
tSQL = "INSERT INTO Period (CoID, CkDate)" & " Values "&
request.Cookies ("myco") & ", #" & ckDate &"#)"
response.Write tSQL
Response.end
Set conn = new clsdbaccesspayr oll
conn.execute(tS QL)
'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]]

Re: Inserting Dates into MS-Access ?!?!?

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/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&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.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"

[Dean]

Re: Inserting Dates into MS-Access ?!?!?

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

but I still get "ADODB.Recordse t 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]

Re: Inserting Dates into MS-Access ?!?!?

Show the code for this class 'clsdbaccesspay roll'

Bob Lehmann

"Dean" <noreply@coveya ccounting.comwr ote in message
news:1171124222 .135583.72410@q 2g2000cwa.googl egroups.com...

My tSQL variable now yields this result:
INSERT INTO Period (CoID, CkDate) Values (1, #2007/01/26#)
>
but I still get "ADODB.Recordse t 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.
>
>
>
>
>
>
>
>

[Dean]

Re: Inserting Dates into MS-Access ?!?!?

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 EmployeeDeducti on.Deduction, "DedEE" AS txt, [Which Header] AS
Hdr
FROM EmployeeDeducti on
WHERE (((EmployeeDedu ction.EmployeeI D)=[Which Employee]));


The prompts for Which Header and Which Employee.

My ASP ends up being:

Dim Conn
Dim P1
Dim P2
P1= ccGetParam("Hea derID",-1)'This functions perfectly
P2= ccGetParam("Emp Id",-1)'This Functions perfectly

Set conn = new clsdbaccesspayr oll
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]]

Re: Inserting Dates into MS-Access ?!?!?

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 EmployeeDeducti on.Deduction, "DedEE" AS txt, [Which Header] AS
Hdr
FROM EmployeeDeducti on
WHERE (((EmployeeDedu ction.EmployeeI D)=[Which Employee]));
>
>
The prompts for Which Header and Which Employee.
>
My ASP ends up being:
>
Dim Conn
Dim P1
Dim P2
P1= ccGetParam("Hea derID",-1)'This functions perfectly
P2= ccGetParam("Emp Id",-1)'This Functions perfectly
>
Set conn = new clsdbaccesspayr oll
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.

[Dean]

Re: Inserting Dates into MS-Access ?!?!?

>
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("Hea derID",-1)
P2= ccGetParam("Emp Id",-1)
qSQL = "AddDed '" & P1 & "','" & P2 &"'"
Set conn = new clsdbaccesspayr oll
Conn.open

Conn.Execute(qS QL)

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.