Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1368
  • Last Modified:

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

0
FINDERSOL
Asked:
FINDERSOL
  • 2
1 Solution
 
ezraaCommented:
instead of OleDbParameter use SQLParameter
0
 
ezraaCommented:
0
 
ralmadaCommented:

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+ "'')"
                                                             
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with oledb, you don't write @var in the sql:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx
  cmd.CommandText = _
        "INSERT INTO tbl_Object(VersionId,Component,Blob) VALUES (" + CStr(lVersionNo) + "," + _
                                                                  "'" & strFile & "', ? )"
 
        cmd.Connection = dbConnection
 

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now