vendredi 23 octobre 2015

How to write manual SQL command in VB.Net to Update from DataGridView without using CommandBuilder

I am creating a windows application using VB.Net & SQLite Database. In one of my win form i used to retrieve data from two tables (ImpShpHeader, ImpShpDetail) by separate SQL command under a single event. ImpShpHeader Data retrieving in textboxes while data from ImpShpDetail retrieving in DataGridView and updating both tables with button click event with two separate SQL commands one command is to update from textboxes to ImpShpHeader but update from DataGridView to ImpshpDetail i am using CommandBuilder. Update from textboxes to ImpShpHeader is Successful but update by CommandBuilder gives following error: Dynamic SQL generation not supported for multiple base tables. So here i need manual SQL command to update any changes in DataGridView. Here is My Code:

'TO RETRIEVE DATA FROM TABLES
Private Sub txtContainerNo_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtContainerNo.KeyDown
    If e.KeyCode = Keys.Enter Then
        Try
            con.Open()

            cmdString = "SELECT * FROM ImpShpHeader WHERE container = '" & txtContainerNo.Text & "'"
            ImpShpHeaderDA = New SQLiteDataAdapter(cmdString, con)
            ImpShpHeaderDA.Fill(ImpShpHeaderDT)

            txtContainer.Text = ImpShpHeaderDT.Rows(0).Item("container").ToString
            txtContainer.Enabled = False
            txtShipper.Text = ImpShpHeaderDT.Rows(0).Item("shipper").ToString
            txtPackages.Text = ImpShpHeaderDT.Rows(0).Item("packages").ToString
            txtETA.Text = ImpShpHeaderDT.Rows(0).Item("eta").ToString

            cmdString = "SELECT item, description, shipqty FROM ImpShpDetail WHERE container = '" & txtContainer.Text & "'"
            ImpShpDetailDA = New SQLiteDataAdapter(cmdString, con)
            ImpShpDetailDA.Fill(ImpShpDetailDT)
            dgvImpShpDetail.DataSource = ImpShpDetailDT

            'STYLE AND APPEARENCE OF DATA GRID VIEW
            dgvImpShpDetail.Columns(0).HeaderText = "Item No."
            dgvImpShpDetail.Columns(1).HeaderText = "Description"
            dgvImpShpDetail.Columns(2).HeaderText = "Ship Qty"

            dgvImpShpDetail.RowHeadersVisible = False

            dgvImpShpDetail.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.DisplayedCells

            dgvImpShpDetail.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            dgvImpShpDetail.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            dgvImpShpDetail.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

        Catch ex As Exception
            MsgBox("Ann error occured!" & vbCrLf & vbCrLf & ex.Message)
        End Try
        con.Close()
    End If
End Sub

'TO UPDATE MULTIPLE TABLES
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    dlgResult = MessageBox.Show("Do you want to save the changes?", "Confirmation", MessageBoxButtons.YesNo)
    If dlgResult = DialogResult.Yes Then
        Try
            con.Open()

            cmdString = "UPDATE ImpShpHeader SET container = @container, shipper = @shipper, packages = @packages, eta = @eta WHERE container = '" & txtContainer.Text & "'"

            cmd = New SQLiteCommand(cmdString, con)

            cmd.Parameters.Add("@container", SqlDbType.VarChar).Value = txtContainer.Text
            cmd.Parameters.Add("@shipper", SqlDbType.VarChar).Value = txtShipper.Text
            cmd.Parameters.Add("@packages", SqlDbType.VarChar).Value = txtPackages.Text
            cmd.Parameters.Add("@eta", SqlDbType.VarChar).Value = txtETA.Text

            cmd.ExecuteNonQuery()

            cmdbl = New SQLiteCommandBuilder(ImpShpDetailDA)
            ImpShpDetailDA.Update(ImpShpDetailDT)

            MsgBox("Updated successfully!")

        Catch ex As Exception
            MsgBox("An error occured!" & vbCrLf & vbCrLf & ex.Message)
        End Try
        con.Close()
    End If
End Sub

Aucun commentaire:

Enregistrer un commentaire