Inserting Dates into MS-Access ?!?!?

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.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 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?

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

missing "("

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


"Dean" wrote in message news:1171116698.821834.148280@m58g2000cwm.googlegroups.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.RadioButton1.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 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?
>

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.inetser ver.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&sel m=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"

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.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.

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

Show the code for this class 'clsdbaccesspayroll'

Bob Lehmann

"Dean" wrote in message
news:1171124222.135583.72410@q2g2000cwa.googlegroups.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.
>
>
>
>
>
>
>
>

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 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.

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 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.

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("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.