Hi,
When update the record from DataGrid and gave the update command:
Error:
Update requires a valid UpdateCommand when passes DataRaw collection with modified
Syntax:
For UpdateCommand
----------------------------------------------------------------------
Dim message, title, defaultValue As String
message = "Please Enter an invoive number." ' Set prompt.
title = "Invoice Number" ' Set title.
defaultValue = "1" ' Set default value.
' Display message, title, and default value.
myValue = InputBox(message, title, defaultValue)
Dim cb As System.Data.OleDb.OleDbCommandBuilder
' General init
Dim strAppPath = System.IO.Directory.GetCurrentDirectory()
' Connection string (for simplicity and readability)
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAppPath & "\invoicedata.mdb"
'MsgBox(strConnectionString)
' OLEDB Init
Dim objConn As New System.Data.OleDb.OleDbConnection(strConnectionString)
Try
' Data Adapter (globally defined) - SelectCommand specified and ready for use
daIm = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM invoice_main where InvID=" & myValue, objConn)
'da = New System.Data.OleDb.OleDbDataAdapter("SELECT invoice_id as 'IID', invoice_s_no as 'S NO', invoice_qty as Qty, invoice_vat_rate as 'Rate VAT', invoice_zero_vat as 'Rate Zero VAT', invoice_description as Description, invoice_amount as 'Sub Total', invoice_vat_amount as VAT FROM invoice_details where invoice_id=" & myValue, objConn)
da = New System.Data.OleDb.OleDbDataAdapter("SELECT InvID, SNO, Qty, VAT, Zero_VAT, Description, AMOUNT, VAT_AMOUNT FROM invoice_details where InvID=" & myValue, objConn)
' Optional command builder which automates the process of building each command
' by hand (below). This only works if the SelectCommand issued to the data
' adapter contains a primary key. USE EITHER ONE, BUT NOT BOTH
' Use cb.GetUpdateCommand.CommandText to view auto-generated SQL statements
'cb = New System.Data.OleDb.OleDbCommandBuilder( da )
'Dim mystr as String = cb.GetUpdateCommand.CommandText
'Dim val as Integer = cb.GetUpdateCommand.Parameters.Count
' InsertCommand available through the DataAdapter
da.InsertCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO invoice_details (InvID, SNO, Qty, Description, Zero_VAT, VAT, AMOUNT, VAT_AMOUNT) Values(?,?,?,?,?,?,?,?)", objConn)
da.InsertCommand.CommandType = CommandType.Text
da.InsertCommand.Parameters.Add("@InvID", System.Data.OleDb.OleDbType.Char, 50, "InvID")
da.InsertCommand.Parameters.Add("@SNO", System.Data.OleDb.OleDbType.Integer, 4, "SNO")
da.InsertCommand.Parameters.Add("@Qty", System.Data.OleDb.OleDbType.Integer, 4, "Qty")
da.InsertCommand.Parameters.Add("@Zero_VAT", System.Data.OleDb.OleDbType.Integer, 4, "Zero_VAT")
da.InsertCommand.Parameters.Add("@VAT", System.Data.OleDb.OleDbType.Integer, 4, "VAT")
da.InsertCommand.Parameters.Add("@Description", System.Data.OleDb.OleDbType.Char, 50, "Description")
da.InsertCommand.Parameters.Add("@AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "AMOUNT")
da.InsertCommand.Parameters.Add("@VAT_AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "VAT_AMOUNT")
' UpdateCommand available through the DataAdapter (note the parameter order!)
da.UpdateCommand = New System.Data.OleDb.OleDbCommand("UPDATE invoice_details SET Qty = ?, Description = ?, Zero_VAT = ?, VAT = ?, VAT_AMOUNT = ?, AMOUNT = ? WHERE ID = ? and SNO = ?", objConn)
da.UpdateCommand.CommandType = CommandType.Text
'da.UpdateCommand.Parameters.Add("@invoice_id", System.Data.OleDb.OleDbType.Char, 50, "invoice_id")
da.UpdateCommand.Parameters.Add("@Qty", System.Data.OleDb.OleDbType.Integer, 4, "Qty")
da.UpdateCommand.Parameters.Add("@Description", System.Data.OleDb.OleDbType.Char, 150, "Description")
da.UpdateCommand.Parameters.Add("@Zero_VAT", System.Data.OleDb.OleDbType.Integer, 4, "Zero_VAT")
da.UpdateCommand.Parameters.Add("@VAT", System.Data.OleDb.OleDbType.Integer, 4, "VAT")
da.UpdateCommand.Parameters.Add("@VAT_AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "VAT_AMOUNT")
da.UpdateCommand.Parameters.Add("@AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "AMOUNT")
da.UpdateCommand.Parameters.Add("@InvID", System.Data.OleDb.OleDbType.Integer, 4, "InvID")
da.UpdateCommand.Parameters.Add("@SNO", System.Data.OleDb.OleDbType.Integer, 4, "SNO")
' DeleteCommand available through the DataAdapter (note parameter order and number of parameters!)
da.DeleteCommand = New System.Data.OleDb.OleDbCommand("DELETE FROM invoice_details WHERE InvID = ? and SNO = ?", objConn)
da.DeleteCommand.CommandType = CommandType.Text
da.DeleteCommand.Parameters.Add("@InvID", System.Data.OleDb.OleDbType.Integer, 4, "InvID")
da.DeleteCommand.Parameters.Add("@SNO", System.Data.OleDb.OleDbType.Integer, 4, "SNO")
' Connect to provider
objConn.Open()
' Create and fill dataset
dsIm = New DataSet("MS_Access_DataSet1")
daIm.Fill(dsIm, "invoice_main")
CustomerName.DataBindings.Add("Text", dsIm, "invoice_main.CUSTOMER_NAME")
CustomerAddress.DataBindings.Add("Text", dsIm, "invoice_main.ADDRESS")
InvoiceNo.DataBindings.Add("Text", dsIm, "invoice_main.InvID")
InvoiceDate.DataBindings.Add("Text", dsIm, "invoice_main.DATE")
InvoiceAmount.DataBindings.Add("Text", dsIm, "invoice_main.AMOUNT")
VatAmount.DataBindings.Add("Text", dsIm, "invoice_main.VAT_AMOUNT")
InvoiceVatAmount.DataBindings.Add("Text", dsIm, "invoice_main.VAT_AMOUNT")
InvoiceTotalAmount.DataBindings.Add("Text", dsIm, "invoice_main.TOTAL_AMOUNT")
' Create and fill dataset
ds = New DataSet("invoice_details")
'da.Fill(ds, "MS_Access_DataSet")
da.Fill(ds, "invoice_details")
' Specify the dataset that you want your DataGrid control to use.
'dg.GridDataSet = ds
' Specify the source table that you want your DataGrid control to use.
'dg.DataSourceTable = "invoice_details"
' Bind DataGrid control to this dataset
dg.SetDataBinding(ds, "invoice_details")
'MsgBox("invoice_amount")
Dim Summary As New ArrayList()
Summary.Add("5,sum(AMOUNT)")
Summary.Add("6,sum(VAT_AMOUNT)")
' Map the array list to the SummaryColumns property of your DataGrid control.
SummaryCols = Summary
' Set the foreground color and the background color for the footer row.
'dg.FooterColor = Brushes.Brown
'dg.FooterFontColor = Brushes.White
' Bind the DataGrid control to the related data.
'dg.BindDataGrid()
MyDataTable = ds.Tables(0)
MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"))
MyDataTable.Columns("ID").DefaultValue = False
MyDataTable.Columns("ID").ColumnMapping = MappingType.Hidden
'ColCount = MyDataTable.Columns.Count
'Catch DatabaseException As SqlException
'MessageBox.Show("Database exception: " & DatabaseException.Message)
Catch OtherException As Exception
MessageBox.Show(OtherException.Message)
Finally
da.Dispose()
objConn.Dispose()
'objConn.Close()
End Try
' Cleanup
----------------------------------------------------------------------
Update Button Code:
----------------------------------------------------------------------
Private Sub btnDataGridUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridUpdate.Click
Try
da.Update(ds, "invoice_details")
Catch x As Exception
MsgBox(x.Message)
' Error during Update, add code to locate error, reconcile
' and try to update again.
End Try
dg.ReadOnly = True
End Sub
----------------------------------------------------------------------
Please tell me how check my update query syntax,
When update the record from DataGrid and gave the update command:
Error:
Update requires a valid UpdateCommand when passes DataRaw collection with modified
Syntax:
For UpdateCommand
----------------------------------------------------------------------
Dim message, title, defaultValue As String
message = "Please Enter an invoive number." ' Set prompt.
title = "Invoice Number" ' Set title.
defaultValue = "1" ' Set default value.
' Display message, title, and default value.
myValue = InputBox(message, title, defaultValue)
Dim cb As System.Data.OleDb.OleDbCommandBuilder
' General init
Dim strAppPath = System.IO.Directory.GetCurrentDirectory()
' Connection string (for simplicity and readability)
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAppPath & "\invoicedata.mdb"
'MsgBox(strConnectionString)
' OLEDB Init
Dim objConn As New System.Data.OleDb.OleDbConnection(strConnectionString)
Try
' Data Adapter (globally defined) - SelectCommand specified and ready for use
daIm = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM invoice_main where InvID=" & myValue, objConn)
'da = New System.Data.OleDb.OleDbDataAdapter("SELECT invoice_id as 'IID', invoice_s_no as 'S NO', invoice_qty as Qty, invoice_vat_rate as 'Rate VAT', invoice_zero_vat as 'Rate Zero VAT', invoice_description as Description, invoice_amount as 'Sub Total', invoice_vat_amount as VAT FROM invoice_details where invoice_id=" & myValue, objConn)
da = New System.Data.OleDb.OleDbDataAdapter("SELECT InvID, SNO, Qty, VAT, Zero_VAT, Description, AMOUNT, VAT_AMOUNT FROM invoice_details where InvID=" & myValue, objConn)
' Optional command builder which automates the process of building each command
' by hand (below). This only works if the SelectCommand issued to the data
' adapter contains a primary key. USE EITHER ONE, BUT NOT BOTH
' Use cb.GetUpdateCommand.CommandText to view auto-generated SQL statements
'cb = New System.Data.OleDb.OleDbCommandBuilder( da )
'Dim mystr as String = cb.GetUpdateCommand.CommandText
'Dim val as Integer = cb.GetUpdateCommand.Parameters.Count
' InsertCommand available through the DataAdapter
da.InsertCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO invoice_details (InvID, SNO, Qty, Description, Zero_VAT, VAT, AMOUNT, VAT_AMOUNT) Values(?,?,?,?,?,?,?,?)", objConn)
da.InsertCommand.CommandType = CommandType.Text
da.InsertCommand.Parameters.Add("@InvID", System.Data.OleDb.OleDbType.Char, 50, "InvID")
da.InsertCommand.Parameters.Add("@SNO", System.Data.OleDb.OleDbType.Integer, 4, "SNO")
da.InsertCommand.Parameters.Add("@Qty", System.Data.OleDb.OleDbType.Integer, 4, "Qty")
da.InsertCommand.Parameters.Add("@Zero_VAT", System.Data.OleDb.OleDbType.Integer, 4, "Zero_VAT")
da.InsertCommand.Parameters.Add("@VAT", System.Data.OleDb.OleDbType.Integer, 4, "VAT")
da.InsertCommand.Parameters.Add("@Description", System.Data.OleDb.OleDbType.Char, 50, "Description")
da.InsertCommand.Parameters.Add("@AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "AMOUNT")
da.InsertCommand.Parameters.Add("@VAT_AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "VAT_AMOUNT")
' UpdateCommand available through the DataAdapter (note the parameter order!)
da.UpdateCommand = New System.Data.OleDb.OleDbCommand("UPDATE invoice_details SET Qty = ?, Description = ?, Zero_VAT = ?, VAT = ?, VAT_AMOUNT = ?, AMOUNT = ? WHERE ID = ? and SNO = ?", objConn)
da.UpdateCommand.CommandType = CommandType.Text
'da.UpdateCommand.Parameters.Add("@invoice_id", System.Data.OleDb.OleDbType.Char, 50, "invoice_id")
da.UpdateCommand.Parameters.Add("@Qty", System.Data.OleDb.OleDbType.Integer, 4, "Qty")
da.UpdateCommand.Parameters.Add("@Description", System.Data.OleDb.OleDbType.Char, 150, "Description")
da.UpdateCommand.Parameters.Add("@Zero_VAT", System.Data.OleDb.OleDbType.Integer, 4, "Zero_VAT")
da.UpdateCommand.Parameters.Add("@VAT", System.Data.OleDb.OleDbType.Integer, 4, "VAT")
da.UpdateCommand.Parameters.Add("@VAT_AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "VAT_AMOUNT")
da.UpdateCommand.Parameters.Add("@AMOUNT", System.Data.OleDb.OleDbType.Integer, 4, "AMOUNT")
da.UpdateCommand.Parameters.Add("@InvID", System.Data.OleDb.OleDbType.Integer, 4, "InvID")
da.UpdateCommand.Parameters.Add("@SNO", System.Data.OleDb.OleDbType.Integer, 4, "SNO")
' DeleteCommand available through the DataAdapter (note parameter order and number of parameters!)
da.DeleteCommand = New System.Data.OleDb.OleDbCommand("DELETE FROM invoice_details WHERE InvID = ? and SNO = ?", objConn)
da.DeleteCommand.CommandType = CommandType.Text
da.DeleteCommand.Parameters.Add("@InvID", System.Data.OleDb.OleDbType.Integer, 4, "InvID")
da.DeleteCommand.Parameters.Add("@SNO", System.Data.OleDb.OleDbType.Integer, 4, "SNO")
' Connect to provider
objConn.Open()
' Create and fill dataset
dsIm = New DataSet("MS_Access_DataSet1")
daIm.Fill(dsIm, "invoice_main")
CustomerName.DataBindings.Add("Text", dsIm, "invoice_main.CUSTOMER_NAME")
CustomerAddress.DataBindings.Add("Text", dsIm, "invoice_main.ADDRESS")
InvoiceNo.DataBindings.Add("Text", dsIm, "invoice_main.InvID")
InvoiceDate.DataBindings.Add("Text", dsIm, "invoice_main.DATE")
InvoiceAmount.DataBindings.Add("Text", dsIm, "invoice_main.AMOUNT")
VatAmount.DataBindings.Add("Text", dsIm, "invoice_main.VAT_AMOUNT")
InvoiceVatAmount.DataBindings.Add("Text", dsIm, "invoice_main.VAT_AMOUNT")
InvoiceTotalAmount.DataBindings.Add("Text", dsIm, "invoice_main.TOTAL_AMOUNT")
' Create and fill dataset
ds = New DataSet("invoice_details")
'da.Fill(ds, "MS_Access_DataSet")
da.Fill(ds, "invoice_details")
' Specify the dataset that you want your DataGrid control to use.
'dg.GridDataSet = ds
' Specify the source table that you want your DataGrid control to use.
'dg.DataSourceTable = "invoice_details"
' Bind DataGrid control to this dataset
dg.SetDataBinding(ds, "invoice_details")
'MsgBox("invoice_amount")
Dim Summary As New ArrayList()
Summary.Add("5,sum(AMOUNT)")
Summary.Add("6,sum(VAT_AMOUNT)")
' Map the array list to the SummaryColumns property of your DataGrid control.
SummaryCols = Summary
' Set the foreground color and the background color for the footer row.
'dg.FooterColor = Brushes.Brown
'dg.FooterFontColor = Brushes.White
' Bind the DataGrid control to the related data.
'dg.BindDataGrid()
MyDataTable = ds.Tables(0)
MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"))
MyDataTable.Columns("ID").DefaultValue = False
MyDataTable.Columns("ID").ColumnMapping = MappingType.Hidden
'ColCount = MyDataTable.Columns.Count
'Catch DatabaseException As SqlException
'MessageBox.Show("Database exception: " & DatabaseException.Message)
Catch OtherException As Exception
MessageBox.Show(OtherException.Message)
Finally
da.Dispose()
objConn.Dispose()
'objConn.Close()
End Try
' Cleanup
----------------------------------------------------------------------
Update Button Code:
----------------------------------------------------------------------
Private Sub btnDataGridUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataGridUpdate.Click
Try
da.Update(ds, "invoice_details")
Catch x As Exception
MsgBox(x.Message)
' Error during Update, add code to locate error, reconcile
' and try to update again.
End Try
dg.ReadOnly = True
End Sub
----------------------------------------------------------------------
Please tell me how check my update query syntax,