CODEWITHSUNDEEP
sql-servervb.net
shreeya99
shreeya99

Reputation: 1

Error: Fill: selectcommand.connection property has not been

I'm trying to retrieve binary data from a database.

I got this error: "Error: Fill: selectcommand.connection property has not been". I can't locate the error.

Public Shared Function BinaryData(ByVal sFileName As String) As Byte()
            Dim strSql As String
            Dim binaryFile As Byte() = Nothing
            Dim dt As DataTable
            Dim myCommand As New SqlCommand
            Dim sqlConn As New SqlConnection
            sqlConn = New SqlConnection("Data Source=xxx;Initial Catalog=xx;Persist Security Info=True;User ID=wxx;Password=xx;MultipleActiveResultSets=True;Application Name=EntityFramework")
            sqlConn.Open()
            myCommand.Connection = sqlConn
            strSql = "SELECT Data  FROM tbldrive WHERE Filename = '" + sFileName + "'"
            Dim scmd As New SqlCommand(strSql, sqlConn)

            dt = DataComponent.DataTableQuery(DataComponent.SqlConn, strSql)

            If dt.Rows.Count > 0 Then
                Try
                    binaryFile = DirectCast(dt.Rows(0).Item("Data"), Byte())
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

            End If
            Return binaryFile
        End Function

Upvotes: 0

Views: 56

Answers (2)

Andrew Morton
Andrew Morton

Reputation: 25033

It looks like you've tried a few things in that code but accidentally left the remains of some attempts in there.

There are some things you could do a bit differently: as you're only after one item from the database, you can use ExecuteScalar; and when the code has finished with the SQL connection and command, they should have .Dispose() called on them - the Using statement will take care of that for you even if something goes wrong. Finally, you should always use SQL parameters to pass parameters to an SQL query - it makes it more secure and avoids having to worry about things like apostrophes in the value.

Public Shared Function BinaryData(ByVal sFileName As String) As Byte()
    Dim sql As String = "SELECT Data FROM tbldrive WHERE Filename = @fname"
    Dim connStr = "Data Source=xxx;Initial Catalog=xx;Persist Security Info=True;User ID=wxx;Password=xx;MultipleActiveResultSets=True;Application Name=EntityFramework"

    Dim binaryFile As Byte() = Nothing

    Using conn As New SqlConnection(connStr),
            cmd As New SqlCommand(sql, conn)

        cmd.Parameters.Add(New SqlParameter With {
                           .ParameterName = "@fname",
                           .SqlDbType = SqlDbType.NVarChar,
                           .Size = 255,
                           .Value = sFileName})

        conn.Open()

        Dim obj As Object = cmd.ExecuteScalar()

        If obj IsNot Nothing Then
            Try
                binaryFile = DirectCast(obj, Byte())
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If

    End Using

    Return binaryFile

End Function

(You may need to adjust the .SqlDbType and .Size parameters: they need to match the column type and size in the database. Also, you probably don't need MultipleActiveResultSets.)

Upvotes: 1

Kate
Kate

Reputation: 1836

The problem seems to be that you have two SqlCommand objects:

Dim myCommand As New SqlCommand
...
myCommand.Connection = sqlConn

It's assigned but not used.

Then you have defined another one:

Dim scmd As New SqlCommand(strSql, sqlConn)

that is not used either.

And I don't know why you have this:

dt = DataComponent.DataTableQuery(DataComponent.SqlConn, strSql)

Do you even need a SqlCommand if you are not using it ? Clean up your code by removing unused variables.

Upvotes: 0

Related Questions