SQL Errors Fire when Non-Related Button is Clicked

Question

I am currently having trouble with my system with errors saying 'Column_Name is Ambiguous' , 'Connection State = Open' whenever I pressed a button that is totally not related to my SQL Queries. It's the only button that shows errors like that but what bothers me is that this button is non-related not like other buttons that will fire up SQL Queries when clicked. Also I use prefixes for my queries so 'Ambiguous' error shouldn't be there. Also I kept my Connection State Closed all the time too so 'Connection State = Open' error is somewhat confusing

P.S : I have queries on a Timer but I don't think that's the reason for the error since this button is merely for editing the date that has been put on the DataGridView Only and not on the Database Itself. This one and only button shows the errors.

Here is my code to the button :

Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click

    Dim rateprice, hourspaid, totalprice, count As Integer

    BilliardHall.load_timerangeedit() 'SQL Query For Checking Available Dates Only and Fills up the Variable 'Count' if there are Results'

    count = Label122.Text

    x = MetroMessageBox.Show(Me, "Are you sure?", "Edit Hours Paid", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

    If x = vbYes Then

        If TextBox4.Text = Nothing Then
            MetroMessageBox.Show(Me, "Please Fill the Hours Paid Text Field", "System Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        ElseIf TextBox4.Text = "0" Then
            MetroMessageBox.Show(Me, "Hours Paid must be Greater than 0", "System Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            rateprice = Label108.Text
            hourspaid = TextBox4.Text
            totalprice = rateprice * hourspaid

            If count > 0 Then
                MetroMessageBox.Show(Me, "There are currently Reserved Subscriptions on the Given Schedule", "System Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                count = 0
            ElseIf DateTimePicker3.Value <= Now.AddMinutes(-1) Then
                MetroMessageBox.Show(Me, "Starting Date and Time cannot be lower than the Current Date and Time", "System Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Else
                SubscriptionListGrid.Item(4, SubscriptionListGrid.CurrentRow.Index).Value = TextBox4.Text
                SubscriptionListGrid.Item(5, SubscriptionListGrid.CurrentRow.Index).Value = totalprice
                SubscriptionListGrid.Item(6, SubscriptionListGrid.CurrentRow.Index).Value = Format(DateTimePicker3.Value, "MMMM dd, yyyy  hh:mm tt")
                SubscriptionListGrid.Item(7, SubscriptionListGrid.CurrentRow.Index).Value = Format(DateTimePicker3.Value.AddHours(hourspaid), "MMMM dd, yyyy  hh:mm tt")

                MetroMessageBox.Show(Me, "Hours Paid has been Updated", "System Information", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If


        End If


    Else

    End If

Here is the code for the 'BilliardHall.load_timerangeedit()' Function :

Public Function load_timerangeedit()

    MysqlConn.ConnectionString = ServerString
    Dim READER As MySqlDataReader

    Try
        MysqlConn.Open()
        Dim query As String

        query = "SELECT DISTINCT(ratedemand.ratedemand_id),playground.playground_id,playground.playground_name FROM bnb.ratedemand,bnb.playground WHERE ratedemand.playground_id = playground.playground_id AND ((ratedemand.ratedemand_datestart BETWEEN '" & Format(Admin_Menu.DateTimePicker3.Value, "yyyy-MM-dd HH:mm:ss tt") & "' AND '" & Format(Convert.ToDateTime(Admin_Menu.Label121.Text), "yyyy-MM-dd HH:mm:ss tt") & "' OR ratedemand.ratedemand_dateend BETWEEN '" & Format(Admin_Menu.DateTimePicker3.Value, "yyyy-MM-dd HH:mm:ss tt") & "' AND '" & Format(Convert.ToDateTime(Admin_Menu.Label121.Text), "yyyy-MM-dd HH:mm:ss tt") & "') OR (ratedemand.ratedemand_datestart < '" & Format(Admin_Menu.DateTimePicker3.Value, "yyyy-MM-dd HH:mm:ss tt") & "' AND ratedemand.ratedemand_dateend > '" & Format(Convert.ToDateTime(Admin_Menu.Label121.Text), "yyyy-MM-dd HH:mm:ss tt") & "')) AND ratedemand.playground_id = (SELECT playground.playground_id FROM bnb.playground WHERE playground.playground_name ='" & Admin_Menu.Label107.Text & "') AND ratedemand.rate_category = 'Billiard' AND (ratedemand.ratedemand_status = 'On Going' or ratedemand.ratedemand_status = 'Reserved')"
        COMMAND = New MySqlCommand(query, MysqlConn)
        READER = COMMAND.ExecuteReader

        Dim count As Integer
        count = 0

        While READER.Read
            count = count + 1
        End While

        MysqlConn.Close()

        Admin_Menu.Label122.Text = count

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        MysqlConn.Dispose()

    End Try


    Return True
End Function

Here is my code for the Function in a Timer # 1:

Public Function load_billiardqueuetable(dgrid As DataGridView, billiardqueuecomboplayarea As ComboBox, billiardqueuecombostatus As ComboBox, billiardqueuedtp1 As DateTimePicker, billiardqueuedtp2 As DateTimePicker, rdbilliardqueuetoday As RadioButton, rdbilliardqueuefuture As RadioButton)
    MysqlConn.ConnectionString = ServerString
    Dim dbDataSet As New DataTable
    Dim SDA As New MySqlDataAdapter

    Dim bSource As New BindingSource

    Try
        MysqlConn.Open()

        Dim query As String

        If Not billiardqueuecomboplayarea.Text = "All Play Areas" And Not billiardqueuecombostatus.Text = "All" And rdbilliardqueuetoday.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd HH:mm:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd HH:mm:59") & "'  ) AND ratedemand.playground_id = (SELECT playground.playground_id FROM bnb.playground WHERE playground.playground_name = '" & billiardqueuecomboplayarea.Text & "') AND ratedemand.ratedemand_status ='" & billiardqueuecombostatus.Text & "' AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf Not billiardqueuecomboplayarea.Text = "All Play Areas" And Not billiardqueuecombostatus.Text = "All" And rdbilliardqueuefuture.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd 00:00:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd 23:59:59") & "' ) AND ratedemand.playground_id = (SELECT playground.playground_id FROM bnb.playground WHERE playground.playground_name = '" & billiardqueuecomboplayarea.Text & "') AND ratedemand.ratedemand_status ='" & billiardqueuecombostatus.Text & "' AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf Not billiardqueuecomboplayarea.Text = "All Play Areas" And billiardqueuecombostatus.Text = "All" And rdbilliardqueuetoday.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd HH:mm:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd HH:mm:59") & "' ) AND ratedemand.playground_id = (SELECT playground.playground_id FROM bnb.playground WHERE playground.playground_name = '" & billiardqueuecomboplayarea.Text & "') AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf Not billiardqueuecomboplayarea.Text = "All Play Areas" And billiardqueuecombostatus.Text = "All" And rdbilliardqueuefuture.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd 00:00:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd 23:59:59") & "' ) AND ratedemand.playground_id = (SELECT playground.playground_id FROM bnb.playground WHERE playground.playground_name = '" & billiardqueuecomboplayarea.Text & "') AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf billiardqueuecomboplayarea.Text = "All Play Areas" And Not billiardqueuecombostatus.Text = "All" And rdbilliardqueuetoday.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd HH:mm:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd HH:mm:59") & "' ) AND ratedemand.ratedemand_status ='" & billiardqueuecombostatus.Text & "' AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf billiardqueuecomboplayarea.Text = "All Play Areas" And Not billiardqueuecombostatus.Text = "All" And rdbilliardqueuefuture.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd 00:00:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd 23:59:59") & "' ) AND ratedemand.ratedemand_status ='" & billiardqueuecombostatus.Text & "' AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf billiardqueuecomboplayarea.Text = "All Play Areas" And billiardqueuecombostatus.Text = "All" And rdbilliardqueuetoday.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd HH:mm:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd HH:mm:59") & "' ) AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        ElseIf billiardqueuecomboplayarea.Text = "All Play Areas" And billiardqueuecombostatus.Text = "All" And rdbilliardqueuefuture.Checked = True Then
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd 00:00:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd 23:59:59") & "' ) AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        Else
            query = "SELECT ratedemand.sales_code as 'Confirmation Code', playground.playground_name as 'Play Area', rate.rate_name as 'Rate', ratedemand.sales_customername as 'Customer Name', ratedemand.ratedemand_hour as 'Hours Paid', DATE_FORMAT(ratedemand.ratedemand_datestart, '%M %d, %Y %h:%i %p') as 'Start', DATE_FORMAT(ratedemand.ratedemand_dateend, '%M %d, %Y %h:%i %p') as 'End', CONCAT(LPAD(TIMESTAMPDIFF(HOUR,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend), 2, 0), ':', LPAD(MOD(TIMESTAMPDIFF(MINUTE,ratedemand.ratedemand_datestart,ratedemand.ratedemand_dateend),60), 2, 0) ) as 'Time', ratedemand.ratedemand_status as 'Status' FROM bnb.ratedemand,bnb.playground,bnb.rate WHERE ratedemand.playground_id = playground.playground_id AND ratedemand.rate_id = rate.rate_id AND (ratedemand.ratedemand_date BETWEEN '" & Format(billiardqueuedtp1.Value, "yyyy-MM-dd HH:mm:00") & "' AND '" & Format(billiardqueuedtp2.Value, "yyyy-MM-dd HH:mm:59") & "' ) AND ratedemand.rate_category='Billiard' ORDER BY ratedemand.ratedemand_id DESC"
        End If

        COMMAND = New MySqlCommand(query, MysqlConn)
        SDA.SelectCommand = COMMAND
        SDA.Fill(dbDataSet)
        bSource.DataSource = dbDataSet
        dgrid.DataSource = bSource
        SDA.Update(dbDataSet)

        MysqlConn.Close()




    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        MysqlConn.Dispose()



    End Try

    Return True
End Function

Here is my code for the Function in a Timer # 2:

Public Function load_cancelled()

    MysqlConn.ConnectionString = ServerString

    Dim READER As MySqlDataReader

    Try
        MysqlConn.Open()
        Dim query As String


        query = "SELECT * FROM bnb.ratedemand WHERE ratedemand_status = 'Reserved' AND NOW() >= ADDDATE(ratedemand_datestart, INTERVAL 30 MINUTE)"
        COMMAND = New MySqlCommand(query, MysqlConn)
        READER = COMMAND.ExecuteReader

        Dim count As Integer
        count = 0

        If READER.HasRows Then
            While READER.Read
                count = count + 1
            End While
        End If
        MysqlConn.Close()

        If count > 0 Then
            MysqlConn.Open()

            query = "UPDATE bnb.ratedemand SET ratedemand_status = 'Cancelled' WHERE ratedemand_status = 'Reserved' AND NOW() >= ADDDATE(ratedemand_datestart, INTERVAL 30 MINUTE)"
            COMMAND = New MySqlCommand(query, MysqlConn)
            READER = COMMAND.ExecuteReader


            MysqlConn.Close()
        Else

        End If


    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        MysqlConn.Dispose()


    End Try

    Return True
End Function

Add `Option Strict On` to the top of your code file...I suspect some of those ints ought be decimal too. Since that button apparently calls methods which at least seem to be SQL related (like `load_timerangeedit`) it really may not be totally unrelated. Thats one of the problems with reusing things like connections — Ňɏssa Pøngjǣrdenlarp, Nov 22 '17 at 23:49
***ought be decimal*** ie should be. Integer is not a synonym for number. It seems very likely that some values for rateprice and hourspaid would result in a fractional amount like 7.25 An integer cannot hold that value. Your code wont even compile using Option Strict. Since there is no connection shown in that code, this is likely the same thing as the "timeticker" problem: the timer goes off when the code is executuing `load_timerangeedit` and since you are apparently trying to use one connection you get the error — Ňɏssa Pøngjǣrdenlarp, Nov 23 '17 at 00:00
@Plutonix no my hours are whole numbers and my prices too... They cant go with values with decimals ever cause the prices are like 100,250 — , Nov 23 '17 at 00:08
Show the code for BilliardHall.load_timerangeedit(). And is this method being called in multiple parts of your code? — F0r3v3r-A-N00b, Nov 23 '17 at 00:10
@F0r3v3r-A-N00b I edited it. I pasted the Functions that I am being redirected in when the error pops up — , Nov 23 '17 at 00:20
You are being redirected there because thats where the error is, not the code you first posted. Its happening because the "TimeTicker" expired and is trying to run some DB code. So, you can have 2 pieces of code trying to use the same connection at the same time == error. Create, use and dispose of the DBCOnnection in the smallest scope possible — Ňɏssa Pøngjǣrdenlarp, Nov 23 '17 at 00:34
@Plutonix how can I correct this? The code on my TimeTicker is necessary... Both codes have separate timers to them one has 100 milliseconds and one has 25 seconds — , Nov 23 '17 at 00:40
If the problem is that you have 2 methods trying to use the same connection at the same time, maybe the solution is to *not use the same connection*? As I said, `Create, use and dispose of the DBConnection in the smallest scope possible` — Ňɏssa Pøngjǣrdenlarp, Nov 23 '17 at 00:54
@Plutonix like you mean a different connection variable? Also that’s unavoidable for me I think cause they need to run those queries in seconds... Also what do you mean ‘in the smallest scope as possible’? — , Nov 23 '17 at 00:57
Creating a new connection each time will not upset your delicate timetable. **[Scope in Visual Basic](http://stackoverflow.com/a/33249045/1070452)** There are few of the DB Provider objects that are mean to be reused or declared as global vars — Ňɏssa Pøngjǣrdenlarp, Nov 23 '17 at 01:08
What’s the difference of using 2 different variables of a connection to a single database? I don’t quite understand that these errors were cause by me using single variable for my connection? — , Nov 23 '17 at 01:37
@Plutonix does that mean if I create a 'mysqlconn2' the errors will be gone? — , Nov 23 '17 at 01:49
Just a side note; it appears that you are hauling down data just to get a record count. I think it might be quicker to Select Count and let the server do the counting. Then counter = command.ExecuteScalar — Mary, Nov 23 '17 at 05:45
In Public Function load_billiardqueuetable why are you passing controls? Why not just pass the values you need. I don't know much about design patterns but a start would be to separate the data access from the user interface. — Mary, Nov 23 '17 at 06:36
@LuciferRodstark she means in billardqueuetable you are passing multiple radio buttons and and combo boxes, a datatable, a datetimepicker — Isaac Morris, Nov 23 '17 at 07:11
@IsaacMorris yeah the radiobuttons had codes too because it has an sort by all function where nobody has an status of 'all' so I had to pass the data to radiobuttons and comboboxes — , Nov 23 '17 at 07:12

Isaac Morris · Accepted Answer · 2017-11-23T03:38:16.513

0

I would try something similar to the following in side of each if, else if, or else. I would if you can change the time on the 100ms timer(this will fire 10 times a second) so that both timers will not both fire at 25 seconds

Try     
    MysqlConn.Open()
        query = "Enter query text here"
  COMMAND = New MySqlCommand(query, MysqlConn)
        SDA.SelectCommand = COMMAND
        SDA.Fill(dbDataSet)
        bSource.DataSource = dbDataSet
        dgrid.DataSource = bSource
        SDA.Update(dbDataSet)
    MysqlConn.Close()
Catch ex As Exception
    MsgBox(ex.Message)
Finally
    MysqlConn.Dispose()
End Try

edited Nov 23 '17 at 03:38

answered Nov 23 '17 at 03:33

Isaac Morris

336
2
11

But would it work if I just create a new variable for connection string to the queries in my timer – Nov 23 '17 at 03:35
@LuciferRodstark i am sorry but i can not answer that in that all that i have worked on have not need 2 simulaneous sql connections active. If you can skip one run you could combine your timers into one interval set at 100ms and have a global variable that is incremented every 100ms and reset every 2500 when your 25 second sql commands need to be ran. if you also need to run your 100ms commands you could add them after your 25 second commands – Isaac Morris Nov 23 '17 at 05:50
1

Just create new local connection, command, data adapter, dataset and reader variables inside your method. – F0r3v3r-A-N00b Nov 23 '17 at 06:19
@F0r3v3r-A-N00b if I create ‘READER2’,’mysqlconn2’ in my timer method would that really fix the errors? I was wondering cause different variables of the same value are just the same right? – Nov 23 '17 at 06:31
@IsaacMorris I need both timers to run, one must run every 100ms and one every 25s how can I control when they stop executing so that they will not go across each other in executing a query? – Nov 23 '17 at 06:37
@LuciferRodstark Imagine this: First load_timerangeedit() is called. It then goes into the While reader.read loop. Then Timer1 fires and invokes load_billiardqueuetable(). So let's say for example, load_billiardqueuetable() finishes first. It then proceeds to close the connection, the same connection being used by load_timerangeedit(), which is still inside the while loop. So, once the connection was closed, the next call to reader.read will raise an exception. This can be easily avoided by using completely different local variables from inside the different methods. – F0r3v3r-A-N00b Nov 23 '17 at 06:48
@F0r3v3r-A-N00b I would like to try that later, thank you bro hope this works – Nov 23 '17 at 06:52
@LuciferRodstark do you need the code exactly as it is written? you could shorten the amount of code possibly getting a speed increase in execution. If you can i would avoid sending controls to a function unless you are 100% sure it can not be done without sending controls – Isaac Morris Nov 23 '17 at 09:39
@IsaacMorris the thing is my whole coding is like that and this project is deadline next week and I have few modules to work to... I would if I could... So I think I would just make different variables for the connection if that’ll work – Nov 23 '17 at 09:43
@F0r3v3r-A-N00b thanks bro!! Btw could I ask you if you know how to loop in itextsharp? – Nov 23 '17 at 20:23
@LuciferRodstark Sorry I haven't tried ITextSharp yet. – F0r3v3r-A-N00b Nov 24 '17 at 01:27

SQL Errors Fire when Non-Related Button is Clicked

1 Answers1