On Tue, 18 Oct 2005 10:32:07 -0700, "RG" <rg@medworks.com> wrote:

"Paul Clement" wrote:

> On Tue, 18 Oct 2005 10:32:07 -0700, "RG" <rg@medworks.com> wrote:
>
> ¤ I need to do a SQL INSERT statement into an Access table, and I’d like to use
> ¤ the exact technique described in the Help file example for
> ¤ OleDbCommand.Prepare Method.
> ¤
> ¤ When I follow the example exactly I get the unhandled exception error message:
> ¤
> ¤ “OleDbCommand.Prepare Method requires all parameters to have an explicitly
> ¤ set type”.
> ¤
> ¤ In the “Remarks” of the Help file it warns about this, saying:
> ¤
> ¤ Remarks
> ¤ Before you call Prepare, specify the data type of each parameter in the
> ¤ statement to be prepared. For each parameter that has a variable length data
> ¤ type, you must set the Size property to the maximum size needed. Prepare
> ¤ returns an error if these conditions are not met.
> ¤
> ¤ That’s nice, but I can’t understand HOW to indicate the size of the
> ¤ parameters. I just got confused & lost trying to figure it out.
> ¤
> ¤ The example DOES NOT indicate any sizes, it just says:
> ¤
> ¤ Dim id As Integer = 20
> ¤ Dim desc As String = "myFirstRegion"
> ¤ ...
> ¤ ' Create and prepare an SQL statement.
> ¤ command.CommandText = "insert into Region (RegionID, RegionDescription)
> ¤ values (@id, @desc)"
> ¤ command.Parameters.Add("@id", id)
> ¤ command.Parameters.Add("@desc", desc)
> ¤ command.Prepare() ' Calling Prepare after having set the Commandtext and
> ¤ parameters.
> ¤ command.ExecuteNonQuery()
> ¤
> ¤
> ¤ I even got the same error when I just tried using an Integer variable (such
> ¤ as ‘id’ above); and Integer is a fixed data type at 4 bytes, not variable
> ¤ (like String).
> ¤
> ¤ This is exactly the technique I need to use in my application so I’d
> ¤ appreciate anyone’s insight into this.
>
> I don't see in your code where you specify the data type. In any event, I would specify the type and
> size when you create a new Parameter object and then add it to the Parameters collection.
>
> Dim QueryParameter As New OleDbParameter("@SomeVal", OleDbType.Numeric)
> QueryParameter.Value = 5
> AccessCommand.Parameters.Add(QueryParameter)
>
> Dim QueryParameter2 As New OleDbParameter("@testVal", OleDbType.VarWChar, SizeValue)
> QueryParameter.Value = "Testing"
> AccessCommand.Parameters.Add(QueryParameter2)
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)