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