Link to home
Start Free TrialLog in
Avatar of FINDERSOL
FINDERSOL

asked on

Must declare the scalar variable Error

I am getting an error 'Must declare the scalar variable @Blob" when I run the following code VB >net 2005 against MS SQL Server 2008.  This worked fine when run against a MS Access database.

Can someone tell me how to fix the problem?

Thanks
Friend Sub SendToBLOBNew(ByRef dbConnection As System.Data.IDbConnection, _
                             ByRef lVersionNo As Long, _
                             ByRef strFile As String, _
                             ByRef strFolder As String)
 
        Dim cmd As New OleDbCommand()
 
        cmd.CommandText = _
        "INSERT INTO tbl_Object(VersionId,Component,Blob) VALUES (" + CStr(lVersionNo) + "," + _
                                                                  "'" & strFile & "'," + _
                                                                  "@BLOB)"
 
        cmd.Connection = dbConnection
 
        Dim fs As New System.IO.FileStream(strFolder + strFile, IO.FileMode.Open, IO.FileAccess.Read)
 
        Dim b(fs.Length() - 1) As Byte
 
        fs.Read(b, 0, b.Length)
        fs.Close()
 
        fs = Nothing
 
        ' ***** Compress file before submitting to source control *****
        Dim byteCompressedByteArray As Byte()
 
        byteCompressedByteArray = mobjCompression.ZipFile(, b)
 
        Dim P As New OleDbParameter()
 
        With P
            .ParameterName = "@BLOB"
            .OleDbType = OleDbType.LongVarBinary
            .Size = byteCompressedByteArray.Length
            .Direction = ParameterDirection.Input
            .IsNullable = False
            .Precision = 0
            .Scale = 0
            .SourceColumn = Nothing
            .SourceVersion = DataRowVersion.Current
            .Value = byteCompressedByteArray
        End With
 
        cmd.Parameters.Add(P)
 
        dbConnection.Open()
        cmd.ExecuteNonQuery()
        dbConnection.Close()
 
    End Sub

Open in new window

Avatar of ezraa
ezraa

instead of OleDbParameter use SQLParameter

Trust the message, @Blob is not defined. Your problem seems to be here:
 cmd.CommandText = _
        "INSERT INTO tbl_Object(VersionId,Component,Blob) VALUES (" + CStr(lVersionNo) + "," + _
                                                                  "'" & strFile & "'," + _
                                                                  "@BLOB)"

 Try defining the blob variable, and then change the insert statement. (Because blob will be a string, then you need to make sure you have in between quotes.

DIM blob as string

cmd.CommandText = _
        "INSERT INTO tbl_Object(VersionId,Component,Blob) VALUES (" + CStr(lVersionNo) + "," + _
                                                                  "'" & strFile & "'," + "''" + blob+ "'')"
                                                             
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial