6 Replies - 237 Views - Last Post: 24 February 2019 - 08:10 PM

#1 wire_jp   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 10-January 19

Updating do not work in different tables in vb.net

Posted 24 February 2019 - 11:26 AM

Hello,

I am using a Winforms application in visual studio which is linked to a MySQL database. I am using a vb.net code. I am able to use the vb.net to make edits to my data in textboxes, comboboxes in visual and save the updates back to the MySQL database.

Private Sub EndEditOnAllBindingSources()
        Dim BindingSourcesQuery = From bindingsources In Me.components.Components
                                  Where (TypeOf bindingsources Is Windows.Forms.BindingSource)
                                  Select bindingsources

        For Each bindingSource As Windows.Forms.BindingSource In BindingSourcesQuery
            bindingSource.EndEdit()
        Next
    End Sub


    Private Sub BtnOrderSave_Click(sender As Object, e As EventArgs) Handles BtnOrderSave.Click

        Me.EndEditOnAllBindingSources()

        SaveOrders()
    End Sub



    Private Sub SaveOrders(Optional messages As Boolean = True)


           con = New MySqlConnection
           con.ConnectionString = conString
           Dim daOrders As New MySqlDataAdapter("select * from Orders", con)
          Dim dsOrders As New DataSet
         Dim dtOrders As New DataTable
        '    Dim cbOrders As New MySqlCommandBuilder(daOrders)
          daOrders.Update(dtOrders)


         If Me.Validate Then
          Me.OrderBindingSource.EndEdit()
           Me.daOrders.Update(Me.dtOrders)
          dsOrders.EnforceConstraints = False
         Else
           System.Windows.Forms.MessageBox.Show(Me, "Validation _
                                                errors occurred.",
                "Save", System.Windows.Forms.MessageBoxButtons.OK,
          System.Windows.Forms.MessageBoxIcon.Warning)
          End If

         Try
           dsOrders.EnforceConstraints = True
           Catch e As System.Data.ConstraintException
           ' Process exception and return.
          Console.WriteLine("Exception of type {0} occurred.",
           e.GetType().ToString())
          End Try

    End Sub




This general vb.net works for some forms but it does not saves updates back to the MySQL database for other forms. I modified this vb.net code in the SaveOrders event and it still not save updates back to the MySQL database:-

    Private Sub SaveOrders(Optional messages As Boolean = True)

        Dim Connect = "UPDATE orders SET  [email protected]_fk, [email protected], [email protected] " & "WHERE orderid= @orderid AND orderdate LIKE  @orderdate"

        Dim cmd As New MySqlCommand(Connect)
        Try
            With cmd
                .Connection = con
                .CommandText = Connect
                .Parameters.AddWithValue("@productid_fk", CboProductID_fk.Text)
                .Parameters.AddWithValue("@order", txtOrder.Text)
                .Parameters.AddWithValue("@ordernotes", txtOrderNotes.Text)
                .Parameters.AddWithValue("@orderid", txtOrderID.Text)
                .Parameters.AddWithValue("@orderdate", Convert.ToDateTime(txtOrderDate.Text))
            End With
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: Updating do not work in different tables in vb.net

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15061
  • View blog
  • Posts: 60,147
  • Joined: 12-June 08

Re: Updating do not work in different tables in vb.net

Posted 24 February 2019 - 12:54 PM

I would start by not using 'like' when comparing datetimes. If you only want days, months, and years then convert both the SQL data and the parameter to the same format.

https://dev.mysql.co...ion_str-to-date
Was This Post Helpful? 0
  • +
  • -

#3 wire_jp   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 10-January 19

Re: Updating do not work in different tables in vb.net

Posted 24 February 2019 - 01:37 PM

Hi modi23_1,

I updated the date string but it is threw an error message: -

you have an error in your sql syntax check the manual that corresponds to your mysql server version for the right syntax  to select str_to_date( OrderDate, '%d/%m/%Y' ) as date from Orders at line 1


my vb.net code is:-

Private Sub SaveOrders(Optional messages As Boolean = True)

    Dim Connect = "UPDATE orders SET  [email protected]_fk, [email protected], [email protected] " & "WHERE orderid= @orderid AND orderdate AND select str_to_date( OrderDate, '%d/%m/%Y' ) as date from Orders"

    Dim cmd As New MySqlCommand(Connect)
    Try
        With cmd
            .Connection = con
            .CommandText = Connect
            .Parameters.AddWithValue("@productid_fk", CboProductID_fk.Text)
            .Parameters.AddWithValue("@order", txtOrder.Text)
            .Parameters.AddWithValue("@ordernotes", txtOrderNotes.Text)
            .Parameters.AddWithValue("@orderid", txtOrderID.Text)
            .Parameters.AddWithValue("@orderdate", Convert.ToDateTime(txtOrderDate.Text))
        End With
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

 

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15061
  • View blog
  • Posts: 60,147
  • Joined: 12-June 08

Re: Updating do not work in different tables in vb.net

Posted 24 February 2019 - 01:51 PM

Why is there a "and select" in your where? Get ride of it.
Was This Post Helpful? 0
  • +
  • -

#5 wire_jp   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 10-January 19

Re: Updating do not work in different tables in vb.net

Posted 24 February 2019 - 02:16 PM

Hi modi123_1,

Thank you for your help so far. I updated my vb.net code but it is now throwing an error at this line:-

 cmd.ExecuteNonQuery()


and the error message is: -

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[ProductID_fk]='Product No. 1', at line 1'


the full vb.net code: -

Imports MySql
Imports MySql.Data.MySqlClient
Imports MySql.Data

Public Class Form1
    Inherits Form

Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True"
    Dim con As MySqlConnection = New MySqlConnection(conString)


Private Sub SaveOrders(Optional messages As Boolean = True)

        con = New MySqlConnection
        con.ConnectionString = conString
        Using con As New MySqlConnection(conString)

            Dim cmd As New MySqlCommand
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE Orders Set [ProductID_fk][email protected]_fk, [OrderStatus] = @orderstatus, [Order] = @order, [Orderowner] = @orderowner, [OrderNotes] = @ordernotes " &
      "WHERE OrderID = @orderid AND OrderDate = str_to_date( OrderDate, '%d/%m/%Y' ) as date from Orders"

            cmd.Parameters.AddWithValue("@productid_fk", Me.CboProductID_fk.Text)
            cmd.Parameters.AddWithValue("@orderstatus", Me.txtOrderStatus.Text)
            cmd.Parameters.AddWithValue("@order", Me.txtOrder.Text)
            cmd.Parameters.AddWithValue("@orderowner", Me.txtOrderOwner.Text)
            cmd.Parameters.AddWithValue("@ordernotes", Me.txtOrderNotes.Text)
            cmd.Parameters.AddWithValue("@orderId", Me.txtOrderID.Text)
            cmd.Parameters.AddWithValue("@orderdate", Me.txtOrderDate.Text)

            cmd.ExecuteNonQuery()
            con.Close()
        End Using
End Sub
End Class



Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15061
  • View blog
  • Posts: 60,147
  • Joined: 12-June 08

Re: Updating do not work in different tables in vb.net

Posted 24 February 2019 - 02:21 PM

For Peter's sake. Debugging by proxy now?

Why is there an 'as date'? Where did you pick that up from? Come on, now. Instead of bumbling around perhaps try using the MYSQL query work bench or the phpMyAdmin.
Was This Post Helpful? 0
  • +
  • -

#7 wire_jp   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 52
  • Joined: 10-January 19

Re: Updating do not work in different tables in vb.net

Posted 24 February 2019 - 08:10 PM

Hi modi123_1,

I am finally to get the vb.net code to update and save changes back to the MySQL database. I used the following vb.net code in the SaveOrders event below: -

  Private Sub SaveOrders(Optional messages As Boolean = True)

        Dim query = "UPDATE Orders Set ProductID_fk = @productid_fk, Order = @order, Orderowner = @orderowner, OrderNotes = @ordernotes " &
            "WHERE OrderID = @orderId"
        con = New MySqlConnection("Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True")
        con.Open()
        cmd = New MySqlCommand(query, con)

        cmd.Parameters.AddWithValue("@productid_fk", Me.CboProductID_fk.SelectedItem(0).ToString)
        cmd.Parameters.AddWithValue("@jobstatus", Me.txtOrderStatus.Text)
        cmd.Parameters.AddWithValue("@order", Me.txtOrder.Text)
        cmd.Parameters.AddWithValue("@orderowner", Me.txtOrderOwner.Text)
        cmd.Parameters.AddWithValue("@ordernotes", Me.txtOrderNotes.Text)
        cmd.Parameters.AddWithValue("@orderId", Me.txtOrderID.Text)

        Try

            Dim dob As DateTime

            dob = Convert.ToDateTime(txtOrderDate.Text)

        Catch ex As Exception



        End Try

        cmd.ExecuteNonQuery()
            con.Close()


    End Sub



Hi modi123_1,

Thanks again for your help.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1