• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

DLookup funciton not working properly

Below is the code for an enhanced MS Access DLookup function.
 
My ultimate goal is to enable this function to return any type of data from a given database, my imediate goal is to get it to return binary data.

My two big problems are that:
 (1) Even though I can detect that command.ExecuteScalar.GetType.name is "Byte[]" I get an
       InvalidCastException "Conversion from type 'Byte()' to type 'Byte' is not valid."
 (2) I get the warning "Function 'Dlookup' doesn't return a value on all code paths. A null reference
       exception could occur at run time when the result is used."

CODE:

    Public Function Dlookup(ByVal Field As String, ByVal Table As String, ByVal Condition As String) As Object

        Dim commandText As String = "SELECT " & Field & " FROM " & Table & " WHERE " & Condition
        Try
                Using command As New OleDbCommand(commandText, dbConn)
                    If IsDBNull(command.ExecuteScalar()) Then
                        Return ""
                    Else
                        If command.ExecuteScalar.GetType.Name = "Byte[]" Then
                            Dim ByteData As Byte = command.ExecuteScalar()
                            Return ByteData
                        ElseIf command.ExecuteScalar.GetType.Name = "String" Then
                            Dim StringData As String = command.ExecuteScalar()
                            Return StringData
                        End If
                    End If
                End Using
        Catch ex As InvalidCastException

        End Try
    End Function
0
NevSoFly
Asked:
NevSoFly
  • 5
  • 4
1 Solution
 
VBRocksCommented:
Just as an immediate thought, this error is correct:  "Conversion from type 'Byte()' to type 'Byte'

A Byte() is an array of bytes..  A Byte is only 1 byte.

Why don't you try this:
    Dim ByteData As Byte() = command.ExecuteScalar()


0
 
NevSoFlyAuthor Commented:
sorry for the delayed response.

I did as you suggested and it brings up an Error on the line: Return BytData saying that "Value of type '1-dimensional array of Byte' cannot be converted to String."
0
 
NevSoFlyAuthor Commented:
I'm guessing I should return the entire array.  Does this make any sense to you?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SanclerCommented:
I'm not wholly following this.  As your function returns Object, why do you find it necessary to Dim a specifically typed variable to return?  What would be wrong with (after a test for DBNull if you want) just

     Return command.ExecuteScalar()

The warning is because if it goes out of the Try block into the Catch block there is no return statement.

Roger
0
 
NevSoFlyAuthor Commented:
Thank you for answering my warning question.

I originally did only use "Return command.ExecuteScalar()" after checking for DBNull but it wouldn't return anything at all.
0
 
SanclerCommented:
This has just worked for me

Imports System.Data.OleDb

Public Class Form1


    Private conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\test.mdb"
    Private WithEvents dbConn As New OleDbConnection(conString)


    Public Function Dlookup(ByVal Field As String, ByVal Table As String, ByVal Condition As String) As Object

        Dim commandText As String = "SELECT " & Field & " FROM " & Table & " WHERE " & Condition
        Try
            Using command As New OleDbCommand(commandText, dbConn)
                If IsDBNull(command.ExecuteScalar()) Then
                    Return ""
                Else
                    Return command.ExecuteScalar
                End If

            End Using
        Catch ex As InvalidCastException
            Return ""
        Catch ex As Exception
            Return ""
        End Try
    End Function


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dbConn.Open()
        Dim obj As Object = Dlookup("mydate", "TestTable", "ID = 1")
        MsgBox(obj.GetType.ToString)
        dbConn.Close()
    End Sub
End Class

on string, datetime and integer datatypes.  I don't have a table to hand with binary data in it to test.  I suppose you _are_ opening the connection before you call the function ;-)?

Roger
0
 
NevSoFlyAuthor Commented:
I tried your code.  It works fine for String types or anything that can be converted to String type.

When I used it on a field that has binary data stored in it I got:
     "InvalidCastException was caught"
     "Conversion from type Byte to type String is not valid."

That's why I was attempting to use the below code:
     If command.ExecuteScalar.GetType.Name = "Byte[]" Then
          Dim ByteData As Byte = command.ExecuteScalar()
          Return ByteData
     Else
          etc................
     End If

I too thought that using object type for the function return should work with everything.  The original code that I posted worked fine for String type.  I just can't figure out what type BinaryData should be to accept Binary array data.
0
 
SanclerCommented:
Here's a revised version

Imports System.Data.OleDb

Public Class Form1


    Private conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MSOffice\Access\Samples\Northwind.mdb"
    Private WithEvents dbConn As New OleDbConnection(conString)


    Public Function Dlookup(ByVal Field As String, ByVal Table As String, ByVal Condition As String) As Object

        Dim commandText As String = "SELECT " & Field & " FROM " & Table & " WHERE " & Condition
        Try
            Using command As New OleDbCommand(commandText, dbConn)
                If IsDBNull(command.ExecuteScalar()) Then
                    Return ""
                Else
                    Return command.ExecuteScalar
                End If

            End Using
        Catch ex As InvalidCastException
            Return ""
        Catch ex As Exception
            Return ""
        End Try
    End Function


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dbConn.Open()
        Dim obj As Object = Dlookup("Photo", "Employees", "EmployeeID = 1")
        MsgBox(obj.GetType.ToString)
        dbConn.Close()
    End Sub
End Class

You will see that I changed the database to Northwind and got the Photo field from the Employees table.  That's a binary field and the messagebox said System.Byte[].

So it's working for me.

Roger
0
 
SanclerCommented:
If you still want, within the sub, to test the datatype use

     If command.ExecuteScalar.GetType.Name = "SystemByte[]" Then

rather than

     If command.ExecuteScalar.GetType.Name = "Byte[]" Then

Roger
0
 
NevSoFlyAuthor Commented:
no need to go the GetType.Name route I had s small  syntax problem when I was calling the function.  Basically I was checking to see if what was being returned by the function was ="" or not.  This didn't jive with the datatype that was being returned.

Thank you for help getting me through that.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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