Solved

Must declare the scalar variable  Error

Posted on 2009-05-11
4
1,340 Views
Last Modified: 2012-05-06
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
Comment
Question by:FINDERSOL
  • 2
4 Comments
 
LVL 9

Expert Comment

by:ezraa
ID: 24356683
instead of OleDbParameter use SQLParameter
0
 
LVL 9

Expert Comment

by:ezraa
ID: 24356686
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24357006

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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24357423
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question