asked on

SQL does not return in VB Code but will run and reuturn results SQL Server

I wrote a function for VB that is supposed to mirror the functionality of DLookUp From access.

There was an example of i found on this site that did something close.
https://www.experts-exchange.com/questions/28407003/How-Do-I-execute-a-DLookUp-function-in-VB-Net.html

When i run my code i have added debug code to print out what each where is and its type. I print each of these out and then i print out the SQL statement. That debug text is as follows:

where clause is: CREATE_DATE = 9/11/2014 2:26:08 PM
Data type is DateTime
Its @value will be: @CreateDate
@CreateDate = 9/11/2014 2:26:08 PM

where clause is: CREATE_USER = 1
Data type is Int
Its @value will be: @CreateUser
@CreateUser = 1

where clause is: HOST_NAME = Debra
Data type is NVarChar
Its @value will be: @HostName
@HostName = Debra

SELECT LIST_ID FROM dbo.EVENTS WHERE CREATE_DATE = @CreateDate AND CREATE_USER = @CreateUser AND HOST_NAME = @HostName

/end debug text

If i take that SQL Select Statment and i run it in SQL Server DBMS and replace the @values with the actual values listed above in the debug text returns the value i am looking for, but when it runs in line, it returns nothing...

It creates and runs the SQL wihtout warnings or Error in code, but the return is nothing, what am i missing? Please help.

public sub name()
 Dim where() As DMT_SQLWhere
Dim wClause As DMT_SQLWhere
Dim inSQL As String

 'set first clause
            wClause = New DMT_SQLWhere
            wClause.FieldName = "CREATE_DATE"
            wClause.FieldValue = loadTime
            wClause.valueType = SqlDbType.DateTime
            wClause.atName = "@CreateDate"
            wClause.join = DMT_SQLWhere.jType.First
            where(0) = wClause

            'set second clause
            wClause = New DMT_SQLWhere
            wClause.FieldName = "CREATE_USER"
            wClause.FieldValue = Frm_Home.activeUser.UN_ID
            wClause.valueType = SqlDbType.Int
            wClause.atName = "@CreateUser"
            wClause.join = DMT_SQLWhere.jType.A
            where(1) = wClause

            'set third clause
            wClause = New DMT_SQLWhere
            wClause.FieldName = "HOST_NAME"
            wClause.FieldValue = Sponsor_List.Text
            wClause.valueType = SqlDbType.NVarChar
            wClause.atName = "@HostName"
            wClause.join = DMT_SQLWhere.jType.A
            where(2) = wClause

            'set Query
            inSQL = "SELECT LIST_ID FROM dbo.EVENTS WHERE"

            'event ID did not return
            '
            '
            '
            '
            '
            '

            eventID = CInt(DLookUpnWhereSQL(where, inSQL, "Frm_Setup_Events:Create_Event_Click"))
end sub

Public Function DLookUpnWhereSQL(where() As DMT_SQLWhere, inSQL As String, CallingProd As String) As Object
        Dim conStr As String, runSQL As String, strLen As Int16
        Dim retVal As Object
        Dim con As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand

        conStr = "PROPER SQL CONNECTION STRING"

        retVal = Nothing

        'make SQL String
        runSQL = inSQL
        strLen = Len(runSQL)
        'check for space after where
Check_Right_One:
        If Right(runSQL, 1).CompareTo(" ") = 0 Then
            'remvoe this space and run again
            runSQL = Left(runSQL, (strLen - 1))
            GoTo Check_Right_One

        ElseIf ((Right(runSQL, 5).CompareTo("WHERE") = 0) Or (Right(runSQL, 5).CompareTo("where") = 0) Or (Right(runSQL, 5).CompareTo("Where") = 0)) Then
            'this is the proper end to the SQL statement we are ready to add our mumbo jumbo
            'i gave you three spellings of where
            'continue

        Else
            'this sadly is a fail condidtion. There is something other than where where where should be
            retVal = "::FAIL::FAIL::"
            Return retVal

        End If


        For i = 0 To where.Count - 1
           
            Select Case where(i).join
                Case DMT_SQLWhere.jType.First
                    'do nothing, no join type needs to be added
                Case DMT_SQLWhere.jType.A
                    'put an AND in there
                    runSQL = runSQL & " AND"
                Case DMT_SQLWhere.jType.O
                    'put an OR in there
                    runSQL = runSQL & " OR"
                Case Else
                    'fatal error contact support
                    retVal = "::FAIL::FAIL::"
                    Return retVal
            End Select

            runSQL = runSQL & " " & where(i).FieldName & " = " & where(i).atName & ""

        Next i



        Try
            con.ConnectionString = conStr
            con.Open()
            cmd.Connection = con
            cmd.CommandText = runSQL
            For j = 0 To where.Count - 1

                'need to case value into correct type...we know the correct type because of .valueType
                'cmd.Parameters.Add(where(j).atName, where(j).valueType).Value = where(j).FieldValue

                Select Case where(j).valueType
                    Case SqlDbType.BigInt
                    Case SqlDbType.Binary
                    Case SqlDbType.Bit
                    Case SqlDbType.Char
                    Case SqlDbType.Date
                        Dim tDate As Date
                        tDate = CDate(where(j).FieldValue)
                        cmd.Parameters.Add(where(j).atName, where(j).valueType).Value = tDate

                    Case SqlDbType.DateTime
                        Dim tDateTime As DateTime
                        tDateTime = CDate(where(j).FieldValue)
                        cmd.Parameters.Add(where(j).atName, where(j).valueType).Value = tDateTime


                    Case SqlDbType.DateTime2
                    Case SqlDbType.DateTimeOffset
                    Case SqlDbType.Decimal
                    Case SqlDbType.Float
                    Case SqlDbType.Image
                    Case SqlDbType.Int
                        Dim tInt As Int64
                        tInt = CInt(where(j).FieldValue)
                        cmd.Parameters.Add(where(j).atName, where(j).valueType).Value = tInt

                    Case SqlDbType.Money
                    Case SqlDbType.NChar
                    Case SqlDbType.NText
                    Case SqlDbType.NVarChar
                        Dim tString As String
                        tString = CStr(where(j).FieldValue)
                        cmd.Parameters.Add(where(j).atName, where(j).valueType).Value = tString

                    Case SqlDbType.Real
                    Case SqlDbType.SmallDateTime
                    Case SqlDbType.SmallInt
                    Case SqlDbType.SmallMoney
                    Case SqlDbType.Structured
                    Case SqlDbType.Text
                    Case SqlDbType.Time
                    Case SqlDbType.Timestamp
                    Case SqlDbType.TinyInt
                    Case SqlDbType.Udt
                    Case SqlDbType.UniqueIdentifier
                    Case SqlDbType.VarBinary
                    Case SqlDbType.VarChar
                    Case SqlDbType.Variant
                    Case SqlDbType.Xml
                    Case Else
                End Select



NEXT_J:
                Debug.Print(where(j).ToString)
                Debug.Print("" & where(j).atName & "  = " & where(j).FieldValue & "")
            Next j

            retVal = cmd.ExecuteScalar()
        Catch ex As Exception
            Console.WriteLine("ERROR: " & vbCrLf & ex.ToString)
            MsgBox("ERROR: " & vbCrLf & ex.ToString)
            Debug.Print(runSQL)

        Finally
            cmd.Dispose()
            con.Close()
        End Try

        Debug.Print(runSQL)
        Return retVal


    End Function

Public Class DMT_SQLWhere
    Public FieldName As String
    Public FieldValue As Object
    Public valueType As SqlDbType
    Public atName As String
    Public join As jType

    Public Enum jType
        First
        A
        O
    End Enum





    Public Overrides Function ToString() As String
        On Error GoTo Err_ToString
        Dim outCome As String

        outCome = "where clause is: " & FieldName & " = " & FieldValue & vbCrLf
        outCome = outCome & "Data type is " & valueType.ToString & vbCrLf
        outCome = outCome & "Its @value will be: " & atName

Exit_ToString:
        ToString = outCome
        Exit Function

Err_ToString:
        MsgBox(Err.Description)
        Resume Exit_ToString

    End Function
End Class

Open in new window

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pjg418

ASKER

Thanks for the responses. I will check these now and let you all know.

I have to do a far amount of these field look ups and so i thought it would be easier to write a single function for a one table multiple criteria look up. The Access feature DLookUp could handle multiple criteria so i figured i should be able to as well :)

Thanks again

ste5an

I have to do a far amount of these field look ups [..]

Use a a stored procedure, which returns all those values with one call. A "far amount" will result in a unresponsive UI.

pjg418

ASKER

Hey Guys,

The error is in the passing of the Date field, if i remove that condition the method returns a value.

I realize i didn't post it in the code sipping above but i declare a value 'LoadTime ' as a Date at some point it gets set using the now() function.

There should be no type conversion error or problem there. So, i searched around the webs, and even though you think you should be able to say that the sql date object value is = to a vb date you cannot...

However, you can set the sql date object value = to a vb date .toString property. Adding that change to the date processing part of the function DLookUpnWhereSQL allowed it to return the correct value.

Thanks again for you help everyone!

pjg418

ASKER

I have to do a far amount of these field look ups [..]

Use a a stored procedure, which returns all those values with one call. A "far amount" will result in a unresponsive UI.

Spelling error, i meant to say 'fair' and by fair, i mean, at this point, it is used once, and i plan to use it one more time. :/

But it does single table, multiple criteria, single field look ups

Public Class DMT_SQLWhere
    Public FieldName As String
    Public FieldValue As Object
    Public valueType As SqlDbType
    Public atName As String
    Public join As jType

    Public Enum jType
        First
        A
        O
    End Enum





    Public Overrides Function ToString() As String
        On Error GoTo Err_ToString
        Dim outCome As String

        outCome = "where clause is: " & FieldName & " = " & FieldValue.ToString & vbCrLf
        outCome = outCome & "Data type is " & valueType.ToString & vbCrLf
        outCome = outCome & "Its @value will be: " & atName

Exit_ToString:
        ToString = outCome
        Exit Function

Err_ToString:
        MsgBox(Err.Description)
        Resume Exit_ToString

    End Function
End Class

Module CommonFunctions
 Public Function DLookUpnWhereSQL(where() As DMT_SQLWhere, inSQL As String, CallingProd As String) As Object
        Dim conStr As String, runSQL As String, strLen As Int64
        Dim retVal As Object
        Dim con As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        conStr = (SQL STRING CONNECTION STRING TO BE SET HERE, as Ste5an pointed it out it is best to do with method)

        retVal = Nothing

        'make SQL String
        runSQL = inSQL
        strLen = Len(runSQL)
        'check for space after where
Check_Right_One:
        If Right(runSQL, 1).CompareTo(" ") = 0 Then
            'remvoe this space and run again
            runSQL = Left(runSQL, (strLen - 1))
            GoTo Check_Right_One

        ElseIf ((Right(runSQL, 5).CompareTo("WHERE") = 0) Or (Right(runSQL, 5).CompareTo("where") = 0) Or (Right(runSQL, 5).CompareTo("Where") = 0)) Then
            'this is the proper end to the SQL statement we are ready to add our mumbo jumbo
            'i gave you three spellings of where
            'continue

        Else
            'this sadly is a fail condidtion. There is something other than where where where should be
            retVal = "::FAIL::FAIL::"
            Return retVal

        End If


        For i = 0 To where.Count - 1
           
            Select Case where(i).join
                Case DMT_SQLWhere.jType.First
                    'do nothing, no join type needs to be added
                Case DMT_SQLWhere.jType.A
                    'put an AND in there
                    runSQL = runSQL & " AND"
                Case DMT_SQLWhere.jType.O
                    'put an OR in there
                    runSQL = runSQL & " OR"
                Case Else
                    'fatal error contact support
                    retVal = "::FAIL::FAIL::"
                    Return retVal
            End Select

            runSQL = runSQL & " " & where(i).FieldName & " = " & where(i).atName & ""

        Next i



        Try
            con.ConnectionString = conStr
            con.Open()
            cmd.Connection = con
            cmd.CommandText = runSQL
            For j = 0 To where.Count - 1

                'need to case value into correct type...we know the correct type because of .valueType
                'cmd.Parameters.Add(where(j).atName, where(j).valueType).Value = where(j).FieldValue

                Select Case where(j).valueType
                    Case SqlDbType.BigInt
                    Case SqlDbType.Binary
                    Case SqlDbType.Bit
                    Case SqlDbType.Char
                    Case SqlDbType.Date
                        Dim tDate As Date
                        tDate = where(j).FieldValue
                       cmd.Parameters.AddWithValue(where(j).atName, where(j).valueType).Value = tDate.ToString

                    Case SqlDbType.DateTime
                        Dim tDateTime As DateTime
                        tDateTime = where(j).FieldValue
                       cmd.Parameters.AddWithValue(where(j).atName, where(j).valueType).Value = tDateTime.ToString


                    Case SqlDbType.DateTime2
                    Case SqlDbType.DateTimeOffset
                    Case SqlDbType.Decimal
                    Case SqlDbType.Float
                    Case SqlDbType.Image
                    Case SqlDbType.Int
                        Dim tInt As Int64
                        tInt = where(j).FieldValue
                        cmd.Parameters.AddWithValue(where(j).atName, where(j).valueType).Value = tInt

                    Case SqlDbType.Money
                    Case SqlDbType.NChar
                    Case SqlDbType.NText
                    Case SqlDbType.NVarChar
                        Dim tString As String
                        tString = where(j).FieldValue
                        cmd.Parameters.AddWithValue(where(j).atName, where(j).valueType).Value = tString

                    Case SqlDbType.Real
                    Case SqlDbType.SmallDateTime
                    Case SqlDbType.SmallInt
                    Case SqlDbType.SmallMoney
                    Case SqlDbType.Structured
                    Case SqlDbType.Text
                    Case SqlDbType.Time
                    Case SqlDbType.Timestamp
                    Case SqlDbType.TinyInt
                    Case SqlDbType.Udt
                    Case SqlDbType.UniqueIdentifier
                    Case SqlDbType.VarBinary
                    Case SqlDbType.VarChar
                    Case SqlDbType.Variant
                    Case SqlDbType.Xml
                    Case Else
                End Select



NEXT_J:
                Debug.Print(where(j).ToString)
                Debug.Print("" & where(j).atName & "  = " & where(j).FieldValue & "")
            Next j

            retVal = cmd.ExecuteScalar()
        Catch ex As Exception
            Console.WriteLine("ERROR: " & vbCrLf & ex.ToString)
            MsgBox("ERROR: " & vbCrLf & ex.ToString)
            Debug.Print(runSQL)

        Finally
            cmd.Dispose()
            con.Close()
        End Try

        Debug.Print(runSQL)
        Return retVal


    End Function

End Module

public sub name()
 Dim where() As DMT_SQLWhere
Dim wClause As DMT_SQLWhere
Dim inSQL As String
dim loadTime as date

loadTime = now()

 'set first clause
            wClause = New DMT_SQLWhere
            wClause.FieldName = "CREATE_DATE"
            wClause.FieldValue = loadTime
            wClause.valueType = SqlDbType.DateTime
            wClause.atName = "@CreateDate"
            wClause.join = DMT_SQLWhere.jType.First
            where(0) = wClause

            'set second clause
            wClause = New DMT_SQLWhere
            wClause.FieldName = "CREATE_USER"
            wClause.FieldValue = Frm_Home.activeUser.UN_ID
            wClause.valueType = SqlDbType.Int
            wClause.atName = "@CreateUser"
            wClause.join = DMT_SQLWhere.jType.A
            where(1) = wClause

            'set third clause
            wClause = New DMT_SQLWhere
            wClause.FieldName = "HOST_NAME"
            wClause.FieldValue = Sponsor_List.Text
            wClause.valueType = SqlDbType.NVarChar
            wClause.atName = "@HostName"
            wClause.join = DMT_SQLWhere.jType.A
            where(2) = wClause

            'set Query
            inSQL = "SELECT LIST_ID FROM dbo.EVENTS 

            eventID = CInt(DLookUpnWhereSQL(where, inSQL, "Frm_Setup_Events:Create_Event_Click"))
end sub

Open in new window

Vitor Montalvão

VB date uses the format from the client computer's regional settings and SQL Server date uses the format that it's configured in the instance.
That's why I told you to check how SQL Server is receiving the date from VB.

pjg418

ASKER

VB date uses the format from the client computer's regional settings and SQL Server date uses the format that it's configured in the instance.
That's why I told you to check how SQL Server is receiving the date from VB.

Yes! That is why i closed the question and gave you the points because your solution fixed the issue :)

I was confused at first because i could compare on the .toString of the date that printed in the debug, I am fairly new to vb and SQL so this didn't make sense to me, but again you and Guy both pointed to the SQL pass of the date so i started there, and though your original statement should have been enough for me, because of my nubile understanding of SQL i needed to read it like three other places before i got it :/

What I read online that made it click for me was that if use the .Paramerter.add and you set the value of a SQL Date type using a string, it will automatically convert it to the apporpriate type This is because of how exactly dates are stored in SQL Server what i found was that:

SQL Server Books Online wrote:
Stored as two 4-byte integers. The first 4 bytes store the number of days before
or after the base date, January 1, 1900. The base date is the system's reference
date. Values for datetime earlier than January 1, 1753, are not permitted. The
other 4 bytes store the time of day represented as the number of milliseconds
after midnight. Seconds have a valid range of 0–59.

Article for above quote is from a Microsoft Page

Thanks again