none
How Can I get a value from an SQL Database if certain condition are met, Using Visual Basic RRS feed

  • Question

  • Hi Good People

    on my form I have a Masked textbox called ( mtbTimeOfPickup.Text) where a user inserts a time, I also have a Textbox called (txtVehicleRequired.Text) that is Filled when a User selects one of three Radio Button. But On Form Load there Is one radio Button that is checked as Default. 

    I am having some trouble getting a value from a SQL Database, I will try to explain...

    ..Insert a Time into (mtbTimeOfPickup) lets say '08:30' (am) select radio Button to insert type of vehicle Radio Button one selected that Puts value into (txtVehicleRequired) =  "4 SEAT VEHICLE" 

    There are two other textboxes that I type Addresses into (txtPickupAddress And txtDropAddress) And on Leaving (txtDropAddress) I would Like to get a value in money for the Above Address textboxes, from A SQL Database called Fares where (txtPickupAddress And txtDropAddress) equals the Fares Database/Table and insert the value into Another Textbox called (txtFares) 

    But if the time is between 05:00 (am) and Midnight then normal time should be selected in the Database Fares

    else if the time is between Midnight and 02:00 (am) the select timeHalf in the Database Fares

    else if time is between 02:00 (am) to (05:00) then select Double in the Database Fares

    Picture (1)..

    Picture (2).. Marked in red should be the value from the SQL Fares Database

    codes I have tried is below....

     Private Sub txtDropAddress_Leave(sender As Object, e As EventArgs) Handles txtDropAddress.Leave
            Dim mTime As DateTime = DateTime.Parse(mtbTimeOfPickup.Text)
    
    
            If mTime < "00:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("NormalFare4")
    
                Next
    
            ElseIf mTime < "02:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress ")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("TimeHalfFare4")
    
                Next
    
            ElseIf mTime < "05:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("DoubleFare4")
    
                Next
    
            ElseIf mTime < "00:00" AndAlso txtVehicleRequired.Text = "6 SEAT VEHICLE" Then
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("NormalFare6")
    
                Next
    
            ElseIf mTime < "02:00" AndAlso txtVehicleRequired.Text = "6 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("TimeHalfFare6")
    
                Next
    
            ElseIf mTime < "05:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("DoubleFare6")
                Next
    
            ElseIf mTime < "00:00" AndAlso txtVehicleRequired.Text = "8 SEAT VEHICLE" Then
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("NormalFare8")
    
                Next
    
            ElseIf mTime < "02:00" AndAlso txtVehicleRequired.Text = "8 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("TimeHalfFare8")
    
                Next
    
            ElseIf mTime < "05:00" AndAlso txtVehicleRequired.Text = "8 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress=@PickupAddress " & _
                               "AND DropAddress=@DropAddress")
    
                For Each F As DataRow In SQL.SQLDT.Rows
                    txtFare.Text = F("DoubleFare8")
    
                Next
            Else
                If MsgBox("Would You Like to Save this Fare?", MsgBoxStyle.YesNo, "This Fare Cannot Be found?") = MsgBoxResult.Yes Then
    
                End If
            End If

    And this Code.. But It doesn't work Properly ( I cannot get that value into textbox (txtFare)

    Please can Someone Put my right...

    Kind Regards

    Gary


    Gary Simpson


    Monday, November 6, 2017 8:50 PM

Answers

  • I would inspect the columns in the DataGridView e.g. I have a DataGridView loaded from a DataTable which means the DataPropertyName is set to the underlying DataTable.Column for each DataGridViewColumn.

    Here is the DataTable

    DataTable = New DataTable
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "Process", .DataType = GetType(Boolean)})
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "FirstName", .DataType = GetType(String)})
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})

    I set the DataGridView e.g. DataGridView1.DataSource = DataTable

    Once loaded I can inspect them.

    For Each col As DataGridViewColumn In DataGridView1.Columns
        Console.WriteLine($"Name: {col.Name} Data property: {col.DataPropertyName}")
    Next

    In the IDE Output window we get

    Name: Process Data property: Process
    Name: FirstName Data property: FirstName
    Name: LastName Data property: LastName

    Same goes for the Columns in the DataTable from the DataGridView

    Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
    For Each col As DataColumn In dt.Columns
        Console.WriteLine($"Index: {col.Ordinal} Name: {col.ColumnName} Type: {col.DataType}")
    Next

    Output

    Index: 0 Name: Process Type: System.Boolean
    Index: 1 Name: FirstName Type: System.String
    Index: 2 Name: LastName Type: System.String

    If the DataGridView columns were generated for you (via a data wizard) the column name is something different than you expect so the code above may assist.

    You can try it out here

    https://1drv.ms/u/s!AtGAgKKpqdWjiHHqExRmEKkS-Djg


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Hi Karen Payne.

    I have Managed to sort out the Issue, It maybe a Long winded way around. But Here is the code and it does work. I could open any of your Data grid views. from https://1drv.ms/u/s!AtGAgKKpqdWjiHHqExRmEKkS-Djg  But I decided to go straight to the database instead of using the Data grid view way...   Here is my code...

     Private Sub txtDropAddress_Leave(sender As Object, e As EventArgs) Handles txtDropAddress.Leave
    
            Dim t1 As New DateTime
            t1 = TimeValue("23:59")
    
            Dim t2 As New DateTime
            t2 = TimeValue("02:00")
    
            Dim t3 As New DateTime
            t3 = TimeValue("05:00")
    
            Dim MyTime As DateTime
            MyTime = TimeValue (mtbTimeOfPickup .Text )
    
            If mtbTimeOfPickup.Text >= (t3) = True And mtbTimeOfPickup.Text < (t1) = True AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" = True Then
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.RunQuery2("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
    
            ElseIf mtbTimeOfPickup.Text <= (t1) = True And mtbTimeOfPickup.Text <= (t2) = True AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.RunQuery3("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
    
            ElseIf mtbTimeOfPickup.Text >= (t2) = True And mtbTimeOfPickup.Text <= (t3) = True AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.RunQuery4("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
            End If
        End Sub

    Thank you Karen Payne for trying to help ...

    Kind Regards

    Gary


    Gary Simpson

    Wednesday, November 8, 2017 10:15 PM
  • Gary,

    I did not look at this earlier. 

    But I see for sure a well known pitfall, afaik you don't clear the parameters which should be done everytime. 

    (Or SQL should everytime be new instanced). 


    Success
    Cor

    • Marked as answer by Gary Simpson Sunday, April 8, 2018 3:33 PM
    Tuesday, November 14, 2017 9:03 AM

All replies

  • Hi Good People

    Here is another code I have tried...

     Private Sub GetFare4(sender As Object, e As EventArgs) '4 Seat Vehicle
    
            Dim myTime As DateTime = DateTime.Parse(mtbTimeOfPickup.Text)
    
            'If Masked textbox value Greater than Time 05:00am But Less than Midnight 00:00am
            'And also the Value Vehicle Required Textbox (txtVehicleRequired) = "4 SEAT VEHICLE"
    
            If myTime > "05:00" And myTime < "00:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                'Get Value from sql Fares Tables where PickupAddress = txtPickupAddress.Text
                'And the Value of DropAddress = txtDropAddress.Text.
                'Then Insert Value of the Fare to = NormalFare4 into txtFare..
                'PickupAddress And DropAddress is = txtPickupAddress.Text & txtDropAddress.Text
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
                Dim NormFare4 As Decimal
                SQL.ExecQuery("SELECT NormalFare4 " & _
                               "Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & _
                               "And DropAddress='" & txtDropAddress.Text & "' ")
                txtFare.Text = NormFare4
    
                'If Masked textbox value Greater than Time 00:00am But Less than 2am 02:00
                'And also the Value Vehicle Required Textbox (txtVehicleRequired) = "4 SEAT VEHICLE"
    
            ElseIf myTime > "00:00" And myTime < "02:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                'Get Value from sql Fares Tables where PickupAddress = txtPickupAddress.Text
                'And the Value of DropAddress = txtDropAddress.Text.
                'Then Insert Value of the Fare to = NormalFare4 into txtFare..
                'PickupAddress And DropAddress is = txtPickupAddress.Text & txtDropAddress.Text
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
                Dim TimeHalf4 As Decimal
                SQL.ExecQuery("SELECT TimeHalfFare4 " & _
                               "Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & _
                               "And DropAddress='" & txtDropAddress.Text & "' ")
                txtFare.Text = TimeHalf4
    
                'If Masked textbox value Greater than Time 02:00am But Less than  05:00am
                'And also the Value Vehicle Required Textbox (txtVehicleRequired) = "4 SEAT VEHICLE"
    
            ElseIf myTime > "00:00" And myTime < "02:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                'Get Value from sql Fares Tables where PickupAddress = txtPickupAddress.Text
                'And the Value of DropAddress = txtDropAddress.Text.
                'Then Insert Value of the Fare to = NormalFare4 into txtFare..
                'PickupAddress And DropAddress is = txtPickupAddress.Text & txtDropAddress.Text
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
                Dim Double4 As Decimal
                SQL.ExecQuery("SELECT DoubleFare4 " & _
                               "Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & _
                               "And DropAddress='" & txtDropAddress.Text & "' ")
                txtFare.Text = Double4
    
            Else
                'If record does not exist then ask user if they wish to save the fare 
                ' into he Fare Database. 
                MsgBox("Would You Like to save this fare ", MsgBoxStyle.Information, "This Record Does Not Exist ")
    
            End If
    
        End Sub

    Gary


    Gary Simpson

    Monday, November 6, 2017 10:08 PM
  • And I have tried this code....

    Private Sub txtDropAddress_Leave(sender As Object, e As EventArgs) Handles txtDropAddress.Leave
            Dim myTime As DateTime = mtbTimeOfPickup.Text
            myTime = myTime.ToShortTimeString()
    
            If Val(myTime > "05:00") And Val(myTime < "00:00") And txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                Dim myAddress1 = txtPickupAddress.Text
                Dim myAddress2 = txtDropAddress.Text
                Dim myFare = txtFare.Text
                Dim cn As New SqlConnection("Data Source=GARYS\BOOKINGS;Initial Catalog=TaxiBookings;Integrated Security=True")
                Dim cmd As New SqlCommand With {
                    .Connection = cn,
                    .CommandType = CommandType.Text,
                    .CommandText = "SELECT NormalFare4 FROM Fares"}
                cn.Open()
                Dim dr As SqlDataReader = cmd.ExecuteReader
                Do While dr.Read
                    If dr.GetString(0)(1) = myAddress1.ToString And myAddress2.ToString Then
                        txtFare.Text = (dr.GetString(cmd.ToString))
                    End If
                Loop
                cn.Close()
    
            ElseIf Val(myTime > "00:00") And Val(myTime < "02:00") And txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                Dim myAddress1 = txtPickupAddress.Text
                Dim myAddress2 = txtDropAddress.Text
                Dim myFare = txtFare.Text
                Dim cn As New SqlConnection("Data Source=GARYS\BOOKINGS;Initial Catalog=TaxiBookings;Integrated Security=True")
                Dim cmd As New SqlCommand With {
                    .Connection = cn,
                    .CommandType = CommandType.Text,
                    .CommandText = "SELECT TimeHalfFare4 FROM Fares"}
                cn.Open()
                Dim dr As SqlDataReader = cmd.ExecuteReader
                Do While dr.Read
                    If dr.GetString(0)(1) = myAddress1.ToString And myAddress2.ToString Then
                        txtFare.Text = (dr.GetString(cmd.ToString))
                    End If
                Loop
                cn.Close()
    
            ElseIf Val(myTime > "02:00") And Val(myTime < "05:00") And txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
    
                Dim myAddress1 = txtPickupAddress.Text
                Dim myAddress2 = txtDropAddress.Text
                Dim myFare = txtFare.Text
                Dim cn As New SqlConnection("Data Source=GARYS\BOOKINGS;Initial Catalog=TaxiBookings;Integrated Security=True")
                Dim cmd As New SqlCommand With {
                    .Connection = cn,
                    .CommandType = CommandType.Text,
                    .CommandText = "SELECT DoubleFare4 FROM Fares"}
                cn.Open()
                Dim dr As SqlDataReader = cmd.ExecuteReader
                Do While dr.Read
                    If dr.GetString(0)(1) = myAddress1.ToString And myAddress2.ToString Then
                        txtFare.Text = (dr.GetString(cmd.ToString))
                    End If
                Loop
                cn.Close()
    
            End If
        End Sub
    I do not get an Error Nothing seems to happen. Not even frozen form? I think it maybe something to do with my If statement :   If myTime > "05:00" And myTime < "00:00" AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then ... But not to sure.... 
    Gary


    My Fares SQL Database looks Like this... 

    Gary Simpson


    • Edited by Gary Simpson Monday, November 6, 2017 11:50 PM to insert Picture of my fares Database
    Monday, November 6, 2017 11:21 PM
  • If you are working with time(7) field type in SQL-Server all operations should be with a TimeSpan to determine things like if a time is lesser than or greater than a fixed time. This can be done with TimeSpan.Compare were it returns -1 for less than 0 equals and greater than is 1. TimeSpan has many methods and properties to assist you in your current work. In short TimeSpan is better to deal with time than working with string representation of time. I highly suggest this rather than using strings which will lead to issues with getting data back and also storing data.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 6, 2017 11:50 PM
    Moderator
  • If you are working with time(7) field type in SQL-Server all operations should be with a TimeSpan to determine things like if a time is lesser than or greater than a fixed time. This can be done with TimeSpan.Compare were it returns -1 for less than 0 equals and greater than is 1. TimeSpan has many methods and properties to assist you in your current work. In short TimeSpan is better to deal with time than working with string representation of time. I highly suggest this rather than using strings which will lead to issues with getting data back and also storing data.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Hi Karen Payne,

    Thank you for getting back to me. I have sorted out my if statement. But I still have an Issue/Error with getting a value from my Fares Table into txtfare.

    But in my fares SQL database Table I do have a column called NormalFare4

     Private Sub txtDropAddress_Leave(sender As Object, e As EventArgs) Handles txtDropAddress.Leave
    
    
            Dim t1 As New DateTime
            t1 = TimeValue("00:00")
    
            Dim t2 As New DateTime
            t2 = TimeValue("02:00")
    
            Dim t3 As New DateTime
            t3 = TimeValue("05:00")
    
            If mtbTimeOfPickup.Text >= (t3) AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                MsgBox("Condition is True: Time in masked textbox is " & mtbTimeOfPickup.Text)
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.ExecQuery("SELECT NormalFare4 " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
    
                MsgBox("Records Found = " & SQL.RecordCount)
    
                For Each R As DataRow In SQL.SQLDS.Tables(0).Rows
                    txtFare.Text = R("NormalFare4")
                Next
            End If
    
        End Sub

    I have tried using a number of the column (4) instead of column name (NormalFare4) But I still cannot get the result of the fare into the txtFare. 

    Have you any suggestion on what could be wrong...

    Kind Regards

    Gary


    Gary Simpson

    Tuesday, November 7, 2017 10:09 PM
  • I would inspect the columns in the DataGridView e.g. I have a DataGridView loaded from a DataTable which means the DataPropertyName is set to the underlying DataTable.Column for each DataGridViewColumn.

    Here is the DataTable

    DataTable = New DataTable
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "Process", .DataType = GetType(Boolean)})
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "FirstName", .DataType = GetType(String)})
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})

    I set the DataGridView e.g. DataGridView1.DataSource = DataTable

    Once loaded I can inspect them.

    For Each col As DataGridViewColumn In DataGridView1.Columns
        Console.WriteLine($"Name: {col.Name} Data property: {col.DataPropertyName}")
    Next

    In the IDE Output window we get

    Name: Process Data property: Process
    Name: FirstName Data property: FirstName
    Name: LastName Data property: LastName

    Same goes for the Columns in the DataTable from the DataGridView

    Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
    For Each col As DataColumn In dt.Columns
        Console.WriteLine($"Index: {col.Ordinal} Name: {col.ColumnName} Type: {col.DataType}")
    Next

    Output

    Index: 0 Name: Process Type: System.Boolean
    Index: 1 Name: FirstName Type: System.String
    Index: 2 Name: LastName Type: System.String

    If the DataGridView columns were generated for you (via a data wizard) the column name is something different than you expect so the code above may assist.

    You can try it out here

    https://1drv.ms/u/s!AtGAgKKpqdWjiHHqExRmEKkS-Djg


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, November 7, 2017 11:09 PM
    Moderator
  • I would inspect the columns in the DataGridView e.g. I have a DataGridView loaded from a DataTable which means the DataPropertyName is set to the underlying DataTable.Column for each DataGridViewColumn.

    Here is the DataTable

    DataTable = New DataTable
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "Process", .DataType = GetType(Boolean)})
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "FirstName", .DataType = GetType(String)})
    DataTable.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})

    I set the DataGridView e.g. DataGridView1.DataSource = DataTable

    Once loaded I can inspect them.

    For Each col As DataGridViewColumn In DataGridView1.Columns
        Console.WriteLine($"Name: {col.Name} Data property: {col.DataPropertyName}")
    Next

    In the IDE Output window we get

    Name: Process Data property: Process
    Name: FirstName Data property: FirstName
    Name: LastName Data property: LastName

    Same goes for the Columns in the DataTable from the DataGridView

    Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
    For Each col As DataColumn In dt.Columns
        Console.WriteLine($"Index: {col.Ordinal} Name: {col.ColumnName} Type: {col.DataType}")
    Next

    Output

    Index: 0 Name: Process Type: System.Boolean
    Index: 1 Name: FirstName Type: System.String
    Index: 2 Name: LastName Type: System.String

    If the DataGridView columns were generated for you (via a data wizard) the column name is something different than you expect so the code above may assist.

    You can try it out here

    https://1drv.ms/u/s!AtGAgKKpqdWjiHHqExRmEKkS-Djg


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Hi Karen Payne.

    I have Managed to sort out the Issue, It maybe a Long winded way around. But Here is the code and it does work. I could open any of your Data grid views. from https://1drv.ms/u/s!AtGAgKKpqdWjiHHqExRmEKkS-Djg  But I decided to go straight to the database instead of using the Data grid view way...   Here is my code...

     Private Sub txtDropAddress_Leave(sender As Object, e As EventArgs) Handles txtDropAddress.Leave
    
            Dim t1 As New DateTime
            t1 = TimeValue("23:59")
    
            Dim t2 As New DateTime
            t2 = TimeValue("02:00")
    
            Dim t3 As New DateTime
            t3 = TimeValue("05:00")
    
            Dim MyTime As DateTime
            MyTime = TimeValue (mtbTimeOfPickup .Text )
    
            If mtbTimeOfPickup.Text >= (t3) = True And mtbTimeOfPickup.Text < (t1) = True AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" = True Then
    
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.RunQuery2("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
    
            ElseIf mtbTimeOfPickup.Text <= (t1) = True And mtbTimeOfPickup.Text <= (t2) = True AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.RunQuery3("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
    
            ElseIf mtbTimeOfPickup.Text >= (t2) = True And mtbTimeOfPickup.Text <= (t3) = True AndAlso txtVehicleRequired.Text = "4 SEAT VEHICLE" Then
                SQL.AddParam("@PickupAddress", txtPickupAddress.Text)
                SQL.AddParam("@DropAddress", txtDropAddress.Text)
    
                SQL.RunQuery4("SELECT * " & _
                               "FROM Fares " & _
                               "WHERE PickupAddress='" & txtPickupAddress.Text & "' " & _
                               "AND DropAddress='" & txtDropAddress.Text & "' ")
            End If
        End Sub

    Thank you Karen Payne for trying to help ...

    Kind Regards

    Gary


    Gary Simpson

    Wednesday, November 8, 2017 10:15 PM
  • Hi Gary,

    It seems that you have resolved your issue now, please remember to close your thread by marking the helpful post or yourself post as answer, it is beneficial to other community members who face the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 14, 2017 8:47 AM
    Moderator
  • Gary,

    I did not look at this earlier. 

    But I see for sure a well known pitfall, afaik you don't clear the parameters which should be done everytime. 

    (Or SQL should everytime be new instanced). 


    Success
    Cor

    • Marked as answer by Gary Simpson Sunday, April 8, 2018 3:33 PM
    Tuesday, November 14, 2017 9:03 AM