S
Sharkknet
Guest
Im new to VB.NET trying to teach my self. Im trying to develop a small software for the company i work for. It takes in information from two tables ParentChild and OpenCO(and will update a result in third table SMH which i havent included yet..!) from a Access database and displays ParentChild table in datagridview. when i click compute it does the few operations (till this i face no issues), but when i try to update ParentChild table back to the datasource it throws an error syntax error in Update statement. Everything else works fine. Please help me out. Thanks in advance.
Heres the code
Imports System.Data.OleDb
Public Class Form1
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Dummy\Documents\dbnew.accdb;persist security info = false"
Dim conn As New OleDbConnection
Dim DataQuery As String = "SELECT * FROM ParentChild"
Dim DataQuery1 As String = "SELECT * FROM OpenCO"
Dim DataQuery2 As String = "SELECT * FROM SMH"
Dim Dset As New DataSet
Dim changes As New DataSet
Dim objDataAdapter As New OleDbDataAdapter(DataQuery, conn)
Dim objDataAdapter1 As New OleDbDataAdapter(DataQuery1, conn)
Dim objDataAdapter2 As New OleDbDataAdapter(DataQuery2, conn)
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conn.ConnectionString = connstring
If conn.State = ConnectionState.Closed Then
conn.Open()
MsgBox("connection established")
Else
MsgBox("error connecting...")
End If
conn.Close()
End Sub
Private Sub LoadBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadBtn.Click
loadtoDGV()
End Sub
Private Sub UpdateBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn.Click
updatetoDB()
End Sub
Private Sub Txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txtsearch.TextChanged
Dim DataQuery As String = "SELECT * FROM ParentChild WHERE ChildPartNo like %" & Txtsearch.Text & "%"
Dim objcmd As New OleDbCommand
Dim objDataAdapter As New OleDbDataAdapter(DataQuery, conn)
Dim Dset As New DataSet
Try
objDataAdapter.FillSchema(Dset, SchemaType.Source, "ParentChild")
Dim dtable As New DataTable
objDataAdapter.Fill(Dset.Tables(0))
dtable = Dset.Tables(0)
DataGridView1.DataSource = dtable
DataGridView1.Refresh()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub loadtoDGV()
Try
objDataAdapter.FillSchema(Dset, SchemaType.Source, "ParentChild")
objDataAdapter.Fill(Dset.Tables(0))
objDataAdapter1.FillSchema(Dset, SchemaType.Source, "OpenCO")
objDataAdapter1.Fill(Dset.Tables(1))
objDataAdapter2.FillSchema(Dset, SchemaType.Source, "SMH")
objDataAdapter2.Fill(Dset.Tables(2))
Dim dtable As New DataTable
dtable = Dset.Tables(0)
DataGridView1.DataSource = dtable
DataGridView1.Refresh()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub updatetoDB()
Dim cmdbuilder As New OleDbCommandBuilder(objDataAdapter)
changes = Dset.GetChanges()
Try
If changes IsNot Nothing Then
Dim dtable As New DataTable
dtable = Dset.Tables(0)
cmdbuilder.GetUpdateCommand()
objDataAdapter.Update(dtable) <---error here
End If
Dset.AcceptChanges()
MsgBox("Table updated")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
Me.Close()
End Sub
Private Sub DataGridView1_UserDeletingRow(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) Handles DataGridView1.UserDeletingRow
MsgBox("One record successfully deleted!..")
End Sub
Private Sub ComputeBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComputeBtn.Click
Dim Dtable1 As New DataTable
Dim Dtable2 As New DataTable
Dim Dtable3 As New DataTable
Dtable1 = Dset.Tables(0)
Dtable2 = Dset.Tables(1)
Dtable3 = Dset.Tables(2)
Dim i, j, k As Integer
For i = 0 To Dtable3.Rows.Count - 1
Dtable3.Rows(i).Item("SMH planned") = 0
Next
For i = 0 To Dtable2.Rows.Count - 1
For j = 0 To Dtable1.Rows.Count - 1
If Dtable2.Rows(i).Item("PartNo") = Dtable1.Rows(j).Item("ParentPartNo") Then
Dtable1.Rows(j).Item("Quantity") = Dtable2.Rows(i).Item("Quantity")
End If
Next
Next
For i = 0 To Dtable1.Rows.Count - 1
For j = 0 To Dtable2.Rows.Count - 1
For k = i To Dtable1.Rows.Count - 1
If Dtable1.Rows(k).Item("ParentPartNo") = Dtable2.Rows(j).Item("PartNo") Then
GoTo end_of_for
End If
Next
Next
end_of_for:
If Dtable2.Rows(j).Item("Jun") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Jun") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Jun"))
Dtable1.Rows(i).Item("Jun(SMH)") = CDbl(Dtable1.Rows(i).Item("Jun") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(0).Item("SMH planned") += Dtable1.Rows(i).Item("Jun(SMH)")
End If
If Dtable2.Rows(j).Item("Jul") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Jul") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Jul"))
Dtable1.Rows(i).Item("Jul(SMH)") = CDbl(Dtable1.Rows(i).Item("Jul") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(1).Item("SMH planned") += Dtable1.Rows(i).Item("Jul(SMH)")
End If
If Dtable2.Rows(j).Item("Aug") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Aug") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Aug"))
Dtable1.Rows(i).Item("Aug(SMH)") = CDbl(Dtable1.Rows(i).Item("Aug") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(2).Item("SMH planned") += Dtable1.Rows(i).Item("Aug(SMH)")
End If
If Dtable2.Rows(j).Item("Sep") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Sep") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Sep"))
Dtable1.Rows(i).Item("Sep(SMH)") = CDbl(Dtable1.Rows(i).Item("Sep") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(3).Item("SMH planned") += Dtable1.Rows(i).Item("Sep(SMH)")
End If
If Dtable2.Rows(j).Item("Oct") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Oct") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Oct"))
Dtable1.Rows(i).Item("Oct(SMH)") = CDbl(Dtable1.Rows(i).Item("Oct") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(4).Item("SMH planned") += Dtable1.Rows(i).Item("Oct(SMH)")
End If
If Dtable2.Rows(j).Item("Nov") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Nov") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Nov"))
Dtable1.Rows(i).Item("Nov(SMH)") = CDbl(Dtable1.Rows(i).Item("Nov") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(5).Item("SMH planned") += Dtable1.Rows(i).Item("Nov(SMH)")
End If
If Dtable2.Rows(j).Item("Dec") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Dec") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Dec"))
Dtable1.Rows(i).Item("Dec(SMH)") = CDbl(Dtable1.Rows(i).Item("Dec") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(6).Item("SMH planned") += Dtable1.Rows(i).Item("Dec(SMH)")
End If
If Dtable2.Rows(j).Item("Jan") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Jan") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Jan"))
Dtable1.Rows(i).Item("Jan(SMH)") = CDbl(Dtable1.Rows(i).Item("Jan") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(7).Item("SMH planned") += Dtable1.Rows(i).Item("Jan(SMH)")
End If
If Dtable2.Rows(j).Item("Feb") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Feb") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Feb"))
Dtable1.Rows(i).Item("Feb(SMH)") = CDbl(Dtable1.Rows(i).Item("Feb") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(8).Item("SMH planned") += Dtable1.Rows(i).Item("Feb(SMH)")
End If
If Dtable2.Rows(j).Item("Mar") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Mar") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Mar"))
Dtable1.Rows(i).Item("Mar(SMH)") = CDbl(Dtable1.Rows(i).Item("Mar") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(9).Item("SMH planned") += Dtable1.Rows(i).Item("Mar(SMH)")
End If
If Dtable2.Rows(j).Item("Apr") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Apr") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Apr"))
Dtable1.Rows(i).Item("Apr(SMH)") = CDbl(Dtable1.Rows(i).Item("Apr") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(10).Item("SMH planned") += Dtable1.Rows(i).Item("Apr(SMH)")
End If
If Dtable2.Rows(j).Item("May") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("May") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("May"))
Dtable1.Rows(i).Item("May(SMH)") = CDbl(Dtable1.Rows(i).Item("May") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(11).Item("SMH planned") += Dtable1.Rows(i).Item("May(SMH)")
End If
Next
End Sub
End Class
Continue reading...
Heres the code
Imports System.Data.OleDb
Public Class Form1
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Dummy\Documents\dbnew.accdb;persist security info = false"
Dim conn As New OleDbConnection
Dim DataQuery As String = "SELECT * FROM ParentChild"
Dim DataQuery1 As String = "SELECT * FROM OpenCO"
Dim DataQuery2 As String = "SELECT * FROM SMH"
Dim Dset As New DataSet
Dim changes As New DataSet
Dim objDataAdapter As New OleDbDataAdapter(DataQuery, conn)
Dim objDataAdapter1 As New OleDbDataAdapter(DataQuery1, conn)
Dim objDataAdapter2 As New OleDbDataAdapter(DataQuery2, conn)
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conn.ConnectionString = connstring
If conn.State = ConnectionState.Closed Then
conn.Open()
MsgBox("connection established")
Else
MsgBox("error connecting...")
End If
conn.Close()
End Sub
Private Sub LoadBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadBtn.Click
loadtoDGV()
End Sub
Private Sub UpdateBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateBtn.Click
updatetoDB()
End Sub
Private Sub Txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txtsearch.TextChanged
Dim DataQuery As String = "SELECT * FROM ParentChild WHERE ChildPartNo like %" & Txtsearch.Text & "%"
Dim objcmd As New OleDbCommand
Dim objDataAdapter As New OleDbDataAdapter(DataQuery, conn)
Dim Dset As New DataSet
Try
objDataAdapter.FillSchema(Dset, SchemaType.Source, "ParentChild")
Dim dtable As New DataTable
objDataAdapter.Fill(Dset.Tables(0))
dtable = Dset.Tables(0)
DataGridView1.DataSource = dtable
DataGridView1.Refresh()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub loadtoDGV()
Try
objDataAdapter.FillSchema(Dset, SchemaType.Source, "ParentChild")
objDataAdapter.Fill(Dset.Tables(0))
objDataAdapter1.FillSchema(Dset, SchemaType.Source, "OpenCO")
objDataAdapter1.Fill(Dset.Tables(1))
objDataAdapter2.FillSchema(Dset, SchemaType.Source, "SMH")
objDataAdapter2.Fill(Dset.Tables(2))
Dim dtable As New DataTable
dtable = Dset.Tables(0)
DataGridView1.DataSource = dtable
DataGridView1.Refresh()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Sub updatetoDB()
Dim cmdbuilder As New OleDbCommandBuilder(objDataAdapter)
changes = Dset.GetChanges()
Try
If changes IsNot Nothing Then
Dim dtable As New DataTable
dtable = Dset.Tables(0)
cmdbuilder.GetUpdateCommand()
objDataAdapter.Update(dtable) <---error here
End If
Dset.AcceptChanges()
MsgBox("Table updated")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
Me.Close()
End Sub
Private Sub DataGridView1_UserDeletingRow(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowCancelEventArgs) Handles DataGridView1.UserDeletingRow
MsgBox("One record successfully deleted!..")
End Sub
Private Sub ComputeBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComputeBtn.Click
Dim Dtable1 As New DataTable
Dim Dtable2 As New DataTable
Dim Dtable3 As New DataTable
Dtable1 = Dset.Tables(0)
Dtable2 = Dset.Tables(1)
Dtable3 = Dset.Tables(2)
Dim i, j, k As Integer
For i = 0 To Dtable3.Rows.Count - 1
Dtable3.Rows(i).Item("SMH planned") = 0
Next
For i = 0 To Dtable2.Rows.Count - 1
For j = 0 To Dtable1.Rows.Count - 1
If Dtable2.Rows(i).Item("PartNo") = Dtable1.Rows(j).Item("ParentPartNo") Then
Dtable1.Rows(j).Item("Quantity") = Dtable2.Rows(i).Item("Quantity")
End If
Next
Next
For i = 0 To Dtable1.Rows.Count - 1
For j = 0 To Dtable2.Rows.Count - 1
For k = i To Dtable1.Rows.Count - 1
If Dtable1.Rows(k).Item("ParentPartNo") = Dtable2.Rows(j).Item("PartNo") Then
GoTo end_of_for
End If
Next
Next
end_of_for:
If Dtable2.Rows(j).Item("Jun") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Jun") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Jun"))
Dtable1.Rows(i).Item("Jun(SMH)") = CDbl(Dtable1.Rows(i).Item("Jun") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(0).Item("SMH planned") += Dtable1.Rows(i).Item("Jun(SMH)")
End If
If Dtable2.Rows(j).Item("Jul") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Jul") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Jul"))
Dtable1.Rows(i).Item("Jul(SMH)") = CDbl(Dtable1.Rows(i).Item("Jul") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(1).Item("SMH planned") += Dtable1.Rows(i).Item("Jul(SMH)")
End If
If Dtable2.Rows(j).Item("Aug") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Aug") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Aug"))
Dtable1.Rows(i).Item("Aug(SMH)") = CDbl(Dtable1.Rows(i).Item("Aug") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(2).Item("SMH planned") += Dtable1.Rows(i).Item("Aug(SMH)")
End If
If Dtable2.Rows(j).Item("Sep") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Sep") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Sep"))
Dtable1.Rows(i).Item("Sep(SMH)") = CDbl(Dtable1.Rows(i).Item("Sep") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(3).Item("SMH planned") += Dtable1.Rows(i).Item("Sep(SMH)")
End If
If Dtable2.Rows(j).Item("Oct") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Oct") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Oct"))
Dtable1.Rows(i).Item("Oct(SMH)") = CDbl(Dtable1.Rows(i).Item("Oct") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(4).Item("SMH planned") += Dtable1.Rows(i).Item("Oct(SMH)")
End If
If Dtable2.Rows(j).Item("Nov") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Nov") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Nov"))
Dtable1.Rows(i).Item("Nov(SMH)") = CDbl(Dtable1.Rows(i).Item("Nov") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(5).Item("SMH planned") += Dtable1.Rows(i).Item("Nov(SMH)")
End If
If Dtable2.Rows(j).Item("Dec") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Dec") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Dec"))
Dtable1.Rows(i).Item("Dec(SMH)") = CDbl(Dtable1.Rows(i).Item("Dec") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(6).Item("SMH planned") += Dtable1.Rows(i).Item("Dec(SMH)")
End If
If Dtable2.Rows(j).Item("Jan") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Jan") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Jan"))
Dtable1.Rows(i).Item("Jan(SMH)") = CDbl(Dtable1.Rows(i).Item("Jan") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(7).Item("SMH planned") += Dtable1.Rows(i).Item("Jan(SMH)")
End If
If Dtable2.Rows(j).Item("Feb") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Feb") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Feb"))
Dtable1.Rows(i).Item("Feb(SMH)") = CDbl(Dtable1.Rows(i).Item("Feb") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(8).Item("SMH planned") += Dtable1.Rows(i).Item("Feb(SMH)")
End If
If Dtable2.Rows(j).Item("Mar") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Mar") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Mar"))
Dtable1.Rows(i).Item("Mar(SMH)") = CDbl(Dtable1.Rows(i).Item("Mar") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(9).Item("SMH planned") += Dtable1.Rows(i).Item("Mar(SMH)")
End If
If Dtable2.Rows(j).Item("Apr") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("Apr") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("Apr"))
Dtable1.Rows(i).Item("Apr(SMH)") = CDbl(Dtable1.Rows(i).Item("Apr") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(10).Item("SMH planned") += Dtable1.Rows(i).Item("Apr(SMH)")
End If
If Dtable2.Rows(j).Item("May") IsNot DBNull.Value Then
Dtable1.Rows(i).Item("May") = CDbl(Dtable1.Rows(i).Item("PerQuantity") * Dtable2.Rows(j).Item("May"))
Dtable1.Rows(i).Item("May(SMH)") = CDbl(Dtable1.Rows(i).Item("May") * Dtable1.Rows(i).Item("SMH"))
Dtable3.Rows(11).Item("SMH planned") += Dtable1.Rows(i).Item("May(SMH)")
End If
Next
End Sub
End Class
Continue reading...