DataAdapter,SQL Sp's and update

  • Thread starter Thread starter Dave
  • Start date Start date
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
 
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...
 
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.
 
Back
Top