Must declare the scalar variable

[johan1974]

Hello,

I try to put an image into a sql db, and i think i am close but now i still get the message

[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@FORM_SCAN".

How can i declare this variable? When i try to do this in sql db it tells me

The text, ntext, and image data types are invalid for local variables.

How can i solve this?

Dim mStream As New ADODB.Stream
        With mStream
            .Type = adTypeBinary
            .Open
            .LoadFromFile TheData.ImageFiles.ReleasedDirectory
        End With
   
        Dim MyParameter As ADODB.Parameter
               
        'Set MyParameter = cmd.CreateParameter("rows", adInteger, adParamOutput)
        Set MyParameter = cmd.CreateParameter("@FORM_SCAN", adLongVarBinary, adParamInput, mStream.Size)
        cmd.Parameters.Append MyParameter
        cmd.ActiveConnection = cnn
        cmd.CommandText = "INSERT INTO deelnemer_aanw_scan VALUES (" & sbatchID & ", @FORM_SCAN)"
        cmd.Parameters(0).Value = mStream.Read
        cmd.Execute

[si_the_geek]

You need to set the CommandText before adding parameters.

Note that there is a tutorial in our Database FAQs which does something similar - it may contain useful info for you.

[brucevde]

Does the ODBC provider support named parameters? Try setting the NamedParameters property of the Command object to True or change the insert statement to

cmd.CommandText = "INSERT INTO deelnemer_aanw_scan VALUES (" & sbatchID & ", ?)"

[johan1974]

Hi,

Tried your suggestions only with the same result.

 Dim mStream As New ADODB.Stream
        With mStream
            .Type = adTypeBinary
            .Open
            .LoadFromFile TheData.ImageFiles.ReleasedDirectory
        End With
   
        Dim MyParameter As ADODB.Parameter
               
        'Set MyParameter = cmd.CreateParameter("rows", adInteger, adParamOutput)
        Set MyParameter = cmd.CreateParameter("@FORM_SCAN", adLongVarBinary, adParamInput, mStream.Size)
        cmd.NamedParameters = True
        cmd.CommandText = "INSERT INTO deelnemer_aanw_scan VALUES (" & sbatchID & ", @FORM_SCAN)"
        cmd.Parameters.Append MyParameter
        cmd.ActiveConnection = cnn
        
        cmd.Parameters(0).Value = mStream.Read
        cmd.Execute

[brucevde]

NamedParameters might only be supported with Stored Procedures or specific Providers. Did you try using the ? as the parameter placeholder? That should work no matter the provider.

[johan1974]

Can u pleae tell me how this works? I mean should i work these ? in order of parameter in the sql statement. Something like this

insert into table values ( ? , ? ) = insert into table values ( parameter1 , parameter2 )

[brucevde]

A parameter must be added to the parameters collection in the order they appear in the sql statement. So cmd.Parameters(0) applies to the first ?, cmd.Parameters(1) to the second and so on...

[johan1974]

It seems to work perfectly, tomorrow i will do some more testing with it. But i allready could read an image inside the sql db.

Thank you all for the help!