Re: ADO 2.8 Command INSERT INTO with Date Parameter

Hi Stephen,

I target both Access and SQL Server.

After extensive tests, I found a workaround to my issue. It doesn't seem to
be related with date issue but with either ADO, ADO / Access connection.

To clarify,

1/ First try
cmdtext = "INSERT INTO mytable(sample) VALUES(@sample)"
parameters.append .createparameter("@sample",addbdate,adinput)
execute ,dateserial(2005,02,18)

works fine.

2/ Second try:
cmdtext= "INSERT INTO mytable(sample,fKtable) SELECT @sample,
[table2].[pKTable2] from [table2] WHERE [table2].[field1]=@filter"
parameters.append .createparameter("@sample",addbdate,adinput)
parameters.append .createparameter("@filter",advarchar,adinput,50)
execute ,array(dateserial(2005,02,18),"what_ever"), adexecutenorecords

didn't work at all.

3/ Third Try using a Procedure
cmdtext="CREATE PROCEDURE [procTry] AS INSERT INTO mytable(sample,fKtable)
SELECT @sample, [table2].[pKTable2] from [table2] WHERE
[table2].[field1]=@filter"
execute ,,adexecutenorecord

then

cmdtext="EXECUTE [procTry]"
parameters.append .createparameter("@sample",addbdate,adinput)
parameters.append .createparameter("@filter",advarchar,adinput,50)
execute ,array(dateserial(2005,02,18),"what_ever"),adexecutenorecords

works perfectly.

Strange enough, isn't it?

Best


.

Relevant Pages

  • Re: Problem with Deletes in code
    ... DoCmd.RunSQL to execute the delete query. ... 'All records in rsSourceProjects will be for the same project, ... problem with ADO interacting with the tables, ...
    (microsoft.public.access.modulesdaovba)
  • Re: How do I make the ON DELETE CASCADE clause in the ALTER TABLE CON.
    ... I believe that cascading updates/deletes can only be included in your DDL ... Since the interfaces uses the native Access libraray, ... You have to execute the query from code on an ADO connection. ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO Error - ODBC Microsft Access Driver Toofew parameters. Expecte
    ... .Execute rowsAffected ... I generally use late binding when posting, ... there to be a difference of behaviour between ADO and DAO. ... Doubling up the "offending character" through the replace function, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Return value from recordset
    ... You should use the connection's Execute ... dim sSQL ... The 129 is a combination of two constants: adCmdText which tells ADO ... dim lRecs ...
    (microsoft.public.inetsdk.programming.scripting.vbscript)
  • Re: Error with insert into statement
    ... It tells ADO that you want it to execute a sql statment ... Bob Barrows ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)