Re: Parameters in INSERT query problem

From: Daniel Crichton (msnews_at_worldofspack.co.uk)
Date: 03/22/05

  • Next message: me: "ADO and Win32 Services" 
    Date: Tue, 22 Mar 2005 11:56:38 -0000

    MJS wrote on Mon, 21 Mar 2005 15:27:22 -0600:

    > I have a query that works.... but I want to clean it up, using ?'s for the
    > parameters in the query... but I am having a problem, I keep getting an error:
    > "Run-time error '3001': Arguments are of the wrong type, are out of
    > acceptable range, or are in conflict with one another.".
    >
    > Like I stated... the query in this format works:
    >
    > cmd.CommandText = "INSERT INTO MyTable(SomeString1,
    > SomeInt, SomeString2) VALUES('" & _
    > str1 & "'," & _
    > int1 & ",'" & _
    > str2 & "')"
    >
    > But I want to simplify the CommandText string... and use queries like the
    > following... some of my queries will have many params.
    > cmd.CommandText = "INSERT INTO MyTable(SomeString1, SomeInt,
    > SomeString2) VALUES(?,?,?)"
    > Set rst = cmd.Execute(Parameters:=Array(str1, int1, str2))
    >
    > I am able to do this successfully if I have only one ?, but when I have
    > multiple, it doesn't want to work.
    >
    > Any Ideas?

    The Array function always returns an array of elements that have the same
    intrinsic data type. Therefore, an implicit conversion is going on to make
    all of the elements the same type.

    How about creating the parameters explicitly using the CreateParameter
    method so you can define the type and it's value knowing that it won't get
    converted? I'm not sure if this will work as I haven't tested it, and you
    might need to change the string types and size to match your field
    definitions.

    cmd.CommandText = "INSERT INTO MyTable(SomeString1, SomeInt, SomeString2)
    VALUES(?,?,?)"
    cmd.Parameters.Append cmd.CreateParameter(,adVarChar,,255,str1)
    cmd.Parameters.Append cmd.CreateParameter(,adInteger,,,int1)
    cmd.Parameters.Append cmd.CreateParameter(,adVarChar,,255,str2)
    cmd.Execute

    Btw, why are you creating a recordset from the Execute method of the Command
    object? You'll only get an empty recordset, right?

    Dan

  • Next message: me: "ADO and Win32 Services"

    Relevant Pages

    • Re: SQL statement with extra characters
      ... you cannot use the Execute method with a Select Query. ... > Dim db As Database ... >> MS Access MVP ...
      (microsoft.public.access.externaldata)
    • passing AND in ado.execute to Oracle
      ... and use the execute method to run the query. ... and paste it into a sql command line in sql+ or sql worksheet, ... somehow not being passed correctly into Oracle with the execute method. ... Single parameter code works properly through the same program, ...
      (microsoft.public.vb.database.ado)
    • Re: conecting with a MsAcces DB by dao
      ... def append_from_Access: ... def Update(self, query, function): ... , for example, no problem, Access can retrieve a not empty recordset ... Access can retrieves a not empty recordset but my python code ...
      (comp.lang.python)
    • Re: error message 2501 the runcommand action was cancelled
      ... Cancel = true ... Because an action query doesn't return any records, ... Use the RecordsAffected property of the Connection, Database, or QueryDef object to determine the number of records affected by the most recent Execute method. ... In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. ...
      (microsoft.public.access.formscoding)
    • Re: Move Records to another table
      ... the Execute method unless you use dbFailOnError. ... > I don't run the Delete query, is it normal to get an error message stating ... >> transaction lets you roll the entire operation back if something does ...
      (microsoft.public.access.formscoding)