[RESOLVED] [2008] Combo box read write MySQL

[Niak32]

for starters not sure if this is the right place for the post considering that it includes MySQL and VB, its here.

I'm trying to load data from one table into a combo box as the data you can select, this i have been able to do "tbl_Wind" using "WindDirID" and "WindDir"



also, I am trying to select (if it exists) a value from "tbl_date" "WindDirID" to select a value from the existing data in the combo box loaded from "tbl_wind"


and finally, I want to be able to change the value in the "tbl_Date" depending on if a value is selected in the combo box.

when a new record is created in tbl_date it uses default values, so there is always something in the field

below is about how far i got, and its the only part that works (extracted and edited of course to exclude the other queries)

  Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  

        Dim myCommandWindDir As New MySqlCommand
        Dim myAdapterWindDir As New MySqlDataAdapter
        Dim myDataWindDir As New DataTable
        Dim SQLWindDir As String


        SQLWindDir = "(select * " _
          & "FROM" & " " & My.Settings.Surfcom & ".tbl_Wind x " _
          & "order by x.WindDirid)"

		
        conn.ConnectionString = myConnString


        Try
            conn.Open()

            Try



		 myCommandWindDir.Connection = conn
                myCommandWindDir.CommandText = SQLWindDir
                myAdapterWindDir.SelectCommand = myCommandWindDir
                myAdapterWindDir.Fill(myDataWindDir)
                cboWindDir.DataSource = myDataWindDir
                cboWindDir.DisplayMember = "WindDirection"
                cboWindDir.ValueMember = "WindDirID"


            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try


   End Sub

I hope that i am clear on what i am after, reading this, im not so sure

thanks

[Jenner]

Ok, what you're asking for isn't hard. Basically, you want to do the following:

User types in an ID value into a search-box of some sort. If that ID exists in the DataTable you made, jump to that entry in your ComboBox.

Also:

If the text in the ComboBox is changed, update the data in the database, perhaps when a "save" button is pressed?

I just need a little clarification as to exactly how you want the form to work. Is there a "Save" button? Is there a TextBox for searching?

[Niak32]

that sounds pretty close to it,
I didn't want to use a second text or combo box for the search string.
I wanted to get the SQL statement to return an integer and use that integer to select an index from the combo box directly, but couldn't get that to work.

my work around at the moment is to get the integer into another combo box, and use the text from that combo box to update the selected value in the original - my head is spinning a bit with that statement. I think i have got it but will report with the code i am using to see if i can refine it to use only one combo box for all of it

[Niak32]

UPDATE:
got it all working with the 2 combo box's for each field still would like to refine the code a little bit if i can to get it to use only the
intended combo box. if i can't, well i can't but i think its worth a try.

  Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  
      cboWindDir.DropDownStyle = ComboBoxStyle.DropDown
  
   Dim conn As New MySqlConnection

        Dim myCommandWindDir As New MySqlCommand
        Dim myAdapterWindDir As New MySqlDataAdapter
        Dim myDataWindDir As New DataTable
        Dim SQLWindDir As String


        SQLWindDir = "(select * " _
          & "FROM" & " " & My.Settings.Surfcom & ".tbl_Wind x " _
          & "order by x.WindDirid)"

        conn.ConnectionString = myConnString


        Try
            conn.Open()

            Try
     
         myCommandWindDir.Connection = conn
                myCommandWindDir.CommandText = SQLWindDir
                myAdapterWindDir.SelectCommand = myCommandWindDir
                myAdapterWindDir.Fill(myDataWindDir)
                cboWindDir.DataSource = myDataWindDir
                cboWindDir.DisplayMember = "WindDirection"
                cboWindDir.ValueMember = "WindDirID"

            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try

  End Sub



    Private Sub cmdGetQuicklinks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGetQuicklinks.Click

        Dim hiddenDate As String = Format(DateTime.Now, "yyyy-MM-dd")
        lblDatehidden.Text = hiddenDate

   Dim conn As New MySqlConnection


        Dim myCommanddate As New MySqlCommand
        Dim myAdapterdate As New MySqlDataAdapter
        Dim myDatadate As New DataTable
        Dim SQLdate As String


        SQLdate = "(SELECT * FROM tbl_date where ddate =" & "'" & hiddenDate & "' " & "ORDER BY DDate DESC limit 1)"


        Try
            conn.Open()

            Try
           	myCommanddate.Connection = conn
                myCommanddate.CommandText = SQLdate
                myAdapterdate.SelectCommand = myCommanddate
                myAdapterdate.Fill(myDatadate)


                cboWindDirtemp.DataSource = myDatadate
                cboWindDirtemp.ValueMember = "WindDirID"
                Me.cboWindDir.SelectedValue = cboWindDirtemp.Text


         Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
        'MsgBox("that sucked")



    End Sub
 
 Private Sub cmdUpdateQuickLinks_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdateQuickLinks.Click

        Dim hiddenDate As String = Format(DateTime.Now, "yyyy-MM-dd")
        lblDatehidden.Text = hiddenDate

           Me.cboWindDirtemp.Text = Me.cboWindDir.SelectedValue


        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand

        'UPDATE `surfcomfnc`.`tbl_date` SET `Sunrise` = '01:00:00' WHERE `tbl_date`.`DDate` = '2008-09-27' LIMIT 1 ;
        conn.ConnectionString = myConnString

        myCommand.Connection = conn
        myCommand.CommandText = "UPDATE " & " " & My.Settings.Surfcom & ".`tbl_date` SET " _
        & "`WindDirID` = (?WindDir), " _
        & "WHERE `tbl_date`.ddate =" & "'" & hiddenDate & "' " & " LIMIT 1 "

      myCommand.Parameters.AddWithValue("?WindDir", Me.cboWindDirtemp.Text)
  

        Try
            conn.Open()
            myCommand.ExecuteNonQuery()

        Catch myerror As MySqlException
            MsgBox("There was an error updating the database: " & myerror.Message)


        End Try

    End Sub

This is working, but also this code has been HEAVILY edited to only show the one value "WindDirID", there
are about another 13 combo box's values that are populated, retrieved and updated, will try to change the code so
it start automaticly and updates periodicly

Cheers

Guess I should mark this thread as resolved as i kind of changed what i was after half way through.