Update requires a valid UpdateCommand when passes DataRaw collection with modified

mohdelhi

Member
Joined
Mar 2, 2005
Messages
7
Programming Experience
Beginner
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,
 
It doesn't seem like the parameter syntax is correct as you have no @ symbols in the SQL statement.
You can use the Data Adapter Configuration Wizard to generate the command and parameters for you by adding a dataAdapter to the designer, right clicking it and selecting 'Configure Data Adapter...'
 
Thank,

It is working now,

Error is due to :

da.Dispose()

objConn.Dispose()

When comments these two line it works.
 
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 = db.DataSet11.Tables(0)
'MyDataTable = ds.Tables(0)

MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"))
MyDataTable.Columns("ID").DefaultValue = True
MyDataTable.Columns("ID").ColumnMapping = MappingType.Hidden
----------------------------------------------------------

Dim data1(2) As Double
Dim im As Integer
im = 0
' Calculate the value for each cell in the footer.
Dim MyArray(2) As String
Dim MyString As String
Try
For Each MyString In SummaryCols
MyArray = MyString.Split(","c)
data1(im) = (MyDataTable.Compute(MyArray(1), "ID is null"))
im = im + 1
Next
InvoiceAmount.Text = (data1(0)).ToString()
VatAmount.Text = (data1(1)).ToString()
InvoiceVatAmount.Text = (data1(1)).ToString()
Dim totalv As Double
Dim totals As String
totalv = data1(0) + data1(1)
InvoiceTotalAmount.Text = totalv.ToString()
---------------------------------------------

This code generate sub-total when i click twice in check box of datagrid twice. How to make it autometically
 
Back
Top