Hi! I'm totally discouraged... the application I'm designing was working
fine yesterday, and this morning, nothing works... first I get this weird
error saying I need a valiod Insert Command, but I do have ONE!!!!!!
error:
An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: Update requires a valid InsertCommand when passed
DataRow collection with new rows.
here is the code...
Public Sub refreshQuotation(ByVal strAction As String)
' initialize and instantiate the query const
Const STR_SQL_QUOTATION_UPDATE As String = _
"UPDATE tbl_quotations SET " & _
"id_client=@id_client, " & _
"client_type=@client_type, " & _
"date_added=@date_added, " & _
"no_quotation=@no_quotation, " & _
"subtotal=@subtotal, " & _
"tax_1=@tax_1, " & _
"tax_2=@tax_2, " & _
"discount=@discount, " & _
"freight=@freight, " & _
"total=@total, " & _
"profit=@profit, " & _
"memo=@memo " & _
"WHERE id_quotation=@id_quotation"
Const STR_SQL_QUOTATION_INSERT As String = _
"INSERT INTO tbl_quotations (" & _
"id_client, " & _
"client_type, " & _
"date_added, " & _
"no_quotation, " & _
"subtotal, " & _
"tax_1, " & _
"tax_2, " & _
"discount, " & _
"freight, " & _
"total, " & _
"profit, " & _
"memo) " & _
"VALUES (" & _
"@id_client, " & _
"@client_type, " & _
"@date_added, " & _
"@no_quotation, " & _
"@subtotal, " & _
"@tax_1, " & _
"@tax_2, " & _
"@discount, " & _
"@freight, " & _
"@total, " & _
"@profit, " & _
"@memo)"
Const STR_SQL_QUOTATION_SELECT As String = _
"SELECT * FROM tbl_quotations ORDER BY id_quotation"
' instantiate the connection
cnnQuotation = New SqlConnection(STR_SQL_CONNECTION_STRING)
' instantiate the command
cmmQuotationInsert = New SqlCommand(STR_SQL_QUOTATION_INSERT, cnnQuotation)
cmmQuotationUpdate = New SqlCommand(STR_SQL_QUOTATION_UPDATE, cnnQuotation)
' instantiate the data adapter
dadQuotation = New SqlDataAdapter(STR_SQL_QUOTATION_SELECT, cnnQuotation)
' set the data adapter command properties
dadQuotation.UpdateCommand = cmmQuotationUpdate
'dadQuotation.InsertCommand = cmmQuotationInsert
' add the update command parameter
cmmQuotationUpdate.Parameters.Add("@id_client", SqlDbType.Int, 4, _
"id_client")
cmmQuotationUpdate.Parameters.Add("@client_type", SqlDbType.VarChar, 50, _
"client_type")
cmmQuotationUpdate.Parameters.Add("@date_added", SqlDbType.DateTime, 8, _
"date_added")
cmmQuotationUpdate.Parameters.Add("@no_quotation", SqlDbType.VarChar, 15, _
"no_quotation")
cmmQuotationUpdate.Parameters.Add("@subtotal", SqlDbType.Decimal, 9, _
"subtotal")
cmmQuotationUpdate.Parameters.Add("@tax_1", SqlDbType.Decimal, 9, _
"tax_1")
cmmQuotationUpdate.Parameters.Add("@tax_2", SqlDbType.Decimal, 9, _
"tax_2")
cmmQuotationUpdate.Parameters.Add("@discount", SqlDbType.Decimal, 9, _
"discount")
cmmQuotationUpdate.Parameters.Add("@freight", SqlDbType.Decimal, 9, _
"freight")
cmmQuotationUpdate.Parameters.Add("@total", SqlDbType.Decimal, 9, _
"total")
cmmQuotationUpdate.Parameters.Add("@profit", SqlDbType.Decimal, 9, _
"profit")
cmmQuotationUpdate.Parameters.Add("@memo", SqlDbType.VarChar, 255, _
"memo")
prmQuotationUpdate =
dadQuotation.UpdateCommand.Parameters.Add("@id_quotation", _
SqlDbType.Int, Nothing, "id_quotation")
prmQuotationUpdate.Direction = ParameterDirection.Input
prmQuotationUpdate.SourceVersion = DataRowVersion.Original
' add the insert command parameter
cmmQuotationInsert.Parameters.Add("@id_client", SqlDbType.Int, 4, _
"id_client")
cmmQuotationInsert.Parameters.Add("@client_type", SqlDbType.VarChar, 50, _
"client_type")
cmmQuotationInsert.Parameters.Add("@date_added", SqlDbType.DateTime, 8, _
"date_added")
cmmQuotationInsert.Parameters.Add("@no_quotation", SqlDbType.VarChar, 15, _
"no_quotation")
cmmQuotationInsert.Parameters.Add("@subtotal", SqlDbType.Decimal, 9, _
"subtotal")
cmmQuotationInsert.Parameters.Add("@tax_1", SqlDbType.Decimal, 9, _
"tax_1")
cmmQuotationInsert.Parameters.Add("@tax_2", SqlDbType.Decimal, 9, _
"tax_2")
cmmQuotationInsert.Parameters.Add("@discount", SqlDbType.Decimal, 9, _
"discount")
cmmQuotationInsert.Parameters.Add("@freight", SqlDbType.Decimal, 9, _
"freight")
cmmQuotationInsert.Parameters.Add("@total", SqlDbType.Decimal, 9, _
"total")
cmmQuotationInsert.Parameters.Add("@profit", SqlDbType.Decimal, 9, _
"profit")
cmmQuotationInsert.Parameters.Add("@memo", SqlDbType.VarChar, 255, _
"memo")
' instantiate the dataset
dstQuotation = New DataSet("quotation")
' populate the dataset
dadQuotation.Fill(dstQuotation, "quotation")
If strAction = "add" Then
' create a new row
drwQuotation = dstQuotation.Tables("quotation").NewRow
drwQuotation("id_client") = CInt(childQuotation.cboClient.SelectedValue)
drwQuotation("client_type") = strNewQuotationType
drwQuotation("date_added") = childQuotation.dateQuotation.Value
drwQuotation("no_quotation") = childQuotation.txtNoQuotation.Text
drwQuotation("subtotal") = CType(getQuotationSubtotal(), Decimal)
drwQuotation("tax_1") = childQuotation.numTax1.Value / 100
If childQuotation.numTax2.Enabled = True Then
drwQuotation("tax_2") = childQuotation.numTax2.Value / 100
Else
drwQuotation("tax_2") = 0
End If
drwQuotation("discount") = childQuotation.numDiscount.Value / 100
drwQuotation("freight") = CType(childQuotation.txtFreight.Text, Decimal)
drwQuotation("total") = drwQuotation("subtotal") - _
(drwQuotation("subtotal") * drwQuotation("discount")) + _
(drwQuotation("freight")) + _
(drwQuotation("subtotal") * drwQuotation("tax_1")) + _
(drwQuotation("subtotal") * drwQuotation("tax_2"))
drwQuotation("profit") = 0
drwQuotation("memo") = childQuotation.rtxtMemo.Text
' insert row into dataset
dstQuotation.Tables("quotation").Rows.Add(drwQuotation)
' check if row was added
If dstQuotation.HasChanges Then
dstChanges = dstQuotation.GetChanges()
dadQuotation.Update(dstChanges, "quotation") <---------------- HERE ITS
CAUSING THE ERROR!!!!
End If
dstQuotation.Clear()
' refill the dataset
dadQuotation.Fill(dstQuotation, "quotation")
' retreive the last incremented id
intLastRow = dstQuotation.Tables("quotation").Rows.Count - 1
intNextQuotationID =
dstQuotation.Tables("quotation").Rows(intLastRow)("id_quotation")
ElseIf strAction = "refresh" Then
Similar Threads
