Solved

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

Posted on 2014-09-11
9
305 Views
Last Modified: 2014-09-12
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.
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_28407003.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

0
Comment
Question by:pjg418
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
Comment Utility
Maybe is how VB is passing the dates to the SQL Server. You should check if the date are in the correct format when VB tries to pass it to SQL Server.
0
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 100 total points
Comment Utility
DLookup is a simplifcation, thus when you think, that you really need it, then use a simple implementation:

Imports System.Data.SqlClient

Public Class DLookup

    Public Shared Function Execute(AColumnExpression As String, ATableExpression As String, AWhereCondition As String) As Object

        Const CONNECTION_STRING As String = "Data Source=XXXXX;Initial Catalog=EAFiles;Persist Security Info=True;User ID=Login;Password=XXXXXXX"
        Const COMMAND_TEXT As String = "SELECT {0} FROM {1} WHERE {2};"

        Dim result As Object = Nothing

        Using connection As New SqlConnection(CONNECTION_STRING)
            connection.Open()
            Dim commandText As String = String.Format(COMMAND_TEXT, AColumnExpression, ATableExpression, AWhereCondition)
            Using command As New SqlCommand(commandText, connection)
                result = command.ExecuteScalar()
            End Using
        End Using

        Return result

    End Function

End Class

Open in new window


btw, the connection (connection string) should be injected or retrieved by an appropriate factory method or abstract factory.

The better approach would be using a clean object model. Thus there is normally no need for such intermediate queries
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
tDate = CDate(where(j).FieldValue)

if the FieldValue, which is typed object, but set as DateTime initially, don't use CDate() there, but just use the FieldValue (which you should do on all parameters, btw)

any than that I cannot see anything wrong
0
 
LVL 1

Author Comment

by:pjg418
Comment Utility
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
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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.
0
 
LVL 1

Author Comment

by:pjg418
Comment Utility
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!
0
 
LVL 1

Author Comment

by:pjg418
Comment Utility
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

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 1

Author Comment

by:pjg418
Comment Utility
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now