Re: VBA + MS SQL

From: Val Mazur (group51a_at_hotmail.com)
Date: 07/29/04 

Date: Wed, 28 Jul 2004 21:36:00 -0400

Hi,

There are couple of mistakes in your code
1. Do not use adBSTR datatype (code 8). You need to define appropriate
datatype, otherwise provider could be confused. In your case it should be
adVarChar or adChar, depending on how the fields defined in your table
2.You did not specify size for your parameters. In a case of numeric
parameters you do not specify size because all numeric types are predefined
and have specific size, but in a case of strings, you have to do this,
because string fields in a table could be defined different way.
You code should look like (assuming that fields should be maximum 10
characters long)

With CommandObj.Parameters
    .Append CommandObj.CreateParameter("P1", adVarChar, adParamInput, 10)
    .Append CommandObj.CreateParameter("P2", adVarChar, adParamInput, 10)
End With

-
Val Mazur
Microsoft MVP

"Piglet" <piglet@box44.gnet.pl> wrote in message
news:ce7jbd$cnq$1@nemesis.news.tpi.pl...
>I insert data into table in SQL Server 2000
> I use ADODB.Command object with parameters like this:
>
>
> Set CommandObj = CreateObject("ADODB.Command")
>
> CommandObj.CommandText = "INSERT INTO TABLE1 VALUES (?,?)"
> With CommandObj.Parameters
> .Append CommandObj.CreateParameter("P1", 8, 1)
> .Append CommandObj.CreateParameter("P2", 8, 1)
> End With
>
> CommandObj.Prepared=True
>
> With CommandObj
> .Parameters("P1")="QWERTY"
> .Parameters("P2")="ABC"
> .Execute
> End With
>
>
> and after executing that code the table in MS SQL looks like this:
>
> P1 | P2
> -------------
> Q | A
>
>
> and where the rest of those strings?
> I have no problems with other data types (numeric,date etc)
>
> Thank you for any help
>
> --
> Piglet
>
>