DataAdapter,SQL Sp's and update

D

Dave

Hello All,
I hope that someone might be kind enough to take a look at
the following code, I am a novice when it comes to VB .net, and am trying to
grasp ADO .net and SQL server. I'm using VB .Net 2003 and SQL2005 Express
edition, the Code Sample is mostly from David Sceppa's Microsoft ADO .net
book. I have created the stored procedures that the code calls. The problem
is that I end up with an error if I change the data in the data table and
click the update button, "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."

I have been struggling with this for the past few days, any help would be
greatly appreciated.

Thanks Dave.

-----Start Code ------

Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As SqlConnection
Dim da As SqlDataAdapter
Dim tbl As DataTable = GenTable()

Private Sub SubmitChangesViaStoredProcedures()
da.UpdateCommand = CreateUpdateViaSPCommand()
da.InsertCommand = CreateInsertViaSPCommand()
da.DeleteCommand = CreateDeleteViaSPCommand()

End Sub

Private Function CreateUpdateViaSPCommand() As SqlCommand
Dim cmd As New SqlCommand("spUpdateDetail", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
pc.Add("OrderID_New", SqlDbType.Int, 0, "OrderID")
pc.Add("ProductID_New", SqlDbType.Int, 0, "ProductID")
pc.Add("Quantity_New", SqlDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")

Dim param As SqlParameter
param = pc.Add("OrderID_Orig", SqlDbType.Int, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID_Orig", SqlDbType.Int, 0, _
"ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity_Orig", SqlDbType.SmallInt, 0, _
"Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice_Orig", SqlDbType.Money, 0, _
"UnitPrice")
param.SourceVersion = DataRowVersion.Original

Return cmd
End Function

Private Function CreateInsertViaSPCommand() As SqlCommand
Dim cmd As New SqlCommand("spInsertDetail", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
pc.Add("OrderID", SqlDbType.Int, 0, "OrderID")
pc.Add("ProductID", SqlDbType.Int, 0, "ProductID")
pc.Add("Quantity", SqlDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice", SqlDbType.Money, 0, "UnitPrice")

Return cmd
End Function

Private Function CreateDeleteViaSPCommand() As SqlCommand
Dim cmd As New SqlCommand("spDeleteDetail", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
Dim param As SqlParameter
param = pc.Add("OrderID", SqlDbType.Int, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID", SqlDbType.Int, 0, "ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity", SqlDbType.SmallInt, 0, "Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice", SqlDbType.Money, 0, "UnitPrice")
param.SourceVersion = DataRowVersion.Original

Return cmd
End Function

Public Function GenTable() As DataTable
Dim tbl As New DataTable("Order Details")
Dim col As DataColumn
With tbl.Columns
col = .Add("OrderID", GetType(Integer))
col.AllowDBNull = False
col = .Add("ProductID", GetType(Integer))
col.AllowDBNull = False
col = .Add("Quantity", GetType(Int16))
col.AllowDBNull = False
col = .Add("UnitPrice", GetType(Decimal))
col.AllowDBNull = False
End With
tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), _
tbl.Columns("ProductID")}
Return tbl
End Function

Sub LoadData()
Dim strConn, strSQL As String
strConn = "integrated security=SSPI;data source=XXXXAPTIVA;" & _
"persist security info=False;initial catalog=Northwind"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
cn = New SqlConnection(strConn)
da = New SqlDataAdapter(strSQL, cn)

cn.Open()

da.Fill(tbl)
Dim MyDv As New DataView(tbl)
DataGrid1.DataSource = MyDv
cn.Close()
End Sub

Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e _
As System.EventArgs) Handles btnLoadData.Click
LoadData()
End Sub

Private Sub btnUpDate_Click(ByVal sender As System.Object, ByVal e _
As System.EventArgs) Handles btnUpDate.Click

SubmitChangesViaStoredProcedures()
Try

da.Update(tbl)

Catch ex As System.InvalidOperationException

MessageBox.Show(ex.Message)

End Try

End Sub
End Class
 
W

W.G. Ryan - MVP

Dave - I'm guessing you're using the Visual Tools to generate this code? If
so, just rerun the wizard to regen the commands. Check the
CreateUpdateViaSPCommand for instance and check that all the paramaters are
there. Also, ensure that your table has a PK on it, otherwise you'll have
issues generating the new update logic.
Dave said:
Hello All,
I hope that someone might be kind enough to take a look at
the following code, I am a novice when it comes to VB .net, and am trying
to grasp ADO .net and SQL server. I'm using VB .Net 2003 and SQL2005
Express edition, the Code Sample is mostly from David Sceppa's Microsoft
ADO .net book. I have created the stored procedures that the code calls.
The problem is that I end up with an error if I change the data in the
data table and click the update button, "Update requires a valid
UpdateCommand when passed DataRow collection with modified rows."

I have been struggling with this for the past few days, any help would be
greatly appreciated.

Thanks Dave.

-----Start Code ------

Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As SqlConnection
Dim da As SqlDataAdapter
Dim tbl As DataTable = GenTable()

Private Sub SubmitChangesViaStoredProcedures()
da.UpdateCommand = CreateUpdateViaSPCommand()
da.InsertCommand = CreateInsertViaSPCommand()
da.DeleteCommand = CreateDeleteViaSPCommand()

End Sub

Private Function CreateUpdateViaSPCommand() As SqlCommand
Dim cmd As New SqlCommand("spUpdateDetail", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
pc.Add("OrderID_New", SqlDbType.Int, 0, "OrderID")
pc.Add("ProductID_New", SqlDbType.Int, 0, "ProductID")
pc.Add("Quantity_New", SqlDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")

Dim param As SqlParameter
param = pc.Add("OrderID_Orig", SqlDbType.Int, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID_Orig", SqlDbType.Int, 0, _
"ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity_Orig", SqlDbType.SmallInt, 0, _
"Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice_Orig", SqlDbType.Money, 0, _
"UnitPrice")
param.SourceVersion = DataRowVersion.Original

Return cmd
End Function

Private Function CreateInsertViaSPCommand() As SqlCommand
Dim cmd As New SqlCommand("spInsertDetail", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
pc.Add("OrderID", SqlDbType.Int, 0, "OrderID")
pc.Add("ProductID", SqlDbType.Int, 0, "ProductID")
pc.Add("Quantity", SqlDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice", SqlDbType.Money, 0, "UnitPrice")

Return cmd
End Function

Private Function CreateDeleteViaSPCommand() As SqlCommand
Dim cmd As New SqlCommand("spDeleteDetail", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pc As SqlParameterCollection = cmd.Parameters
Dim param As SqlParameter
param = pc.Add("OrderID", SqlDbType.Int, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID", SqlDbType.Int, 0, "ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity", SqlDbType.SmallInt, 0, "Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice", SqlDbType.Money, 0, "UnitPrice")
param.SourceVersion = DataRowVersion.Original

Return cmd
End Function

Public Function GenTable() As DataTable
Dim tbl As New DataTable("Order Details")
Dim col As DataColumn
With tbl.Columns
col = .Add("OrderID", GetType(Integer))
col.AllowDBNull = False
col = .Add("ProductID", GetType(Integer))
col.AllowDBNull = False
col = .Add("Quantity", GetType(Int16))
col.AllowDBNull = False
col = .Add("UnitPrice", GetType(Decimal))
col.AllowDBNull = False
End With
tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), _
tbl.Columns("ProductID")}
Return tbl
End Function

Sub LoadData()
Dim strConn, strSQL As String
strConn = "integrated security=SSPI;data source=XXXXAPTIVA;" & _
"persist security info=False;initial catalog=Northwind"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
cn = New SqlConnection(strConn)
da = New SqlDataAdapter(strSQL, cn)

cn.Open()

da.Fill(tbl)
Dim MyDv As New DataView(tbl)
DataGrid1.DataSource = MyDv
cn.Close()
End Sub

Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e _
As System.EventArgs) Handles btnLoadData.Click
LoadData()
End Sub

Private Sub btnUpDate_Click(ByVal sender As System.Object, ByVal e _
As System.EventArgs) Handles btnUpDate.Click

SubmitChangesViaStoredProcedures()
Try

da.Update(tbl)

Catch ex As System.InvalidOperationException

MessageBox.Show(ex.Message)

End Try

End Sub
End Class
Click to expand...
 
D

Dave

Hello Mr. Ryan,

Thanks for your promt reply, The code I've used isn't produced form the
dataadaptor wizard but from David Sceppa's Microsoft ADO .net
book (mostly), specifically Chapter 10. I've checked for an instance of
CreateUpdateViaSPCommand and it is there along with all parameters, the
table has a primary key. Anyhow I'll try adding a dataadaptor to the form
and getting the wizard to generate the code and see how I go.

Thanks again,
Dave.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

After updating, value in db dissapears (intermittent) [using SQL sever] 2
Concurrency issue on Update 4
Error - Deleted row information cannot be accessed through row 3
Can execute a query with multiple parameters 2
UPDATE command is driving me crazy 1
dataadapter insert command MSAccess 97 stored procedure - data truncated 2
Problems inserting data into Access database 2
Row Update Not Updating Data source 3

Top