Manually writing updatecommands for the data adapter

E

Eph0nk

Hi,

I have a datagrid in a winform that lets users input "detail lines" of an order form. I manually create both the dataset and the datagrid (ie: to have dropdownboxes etc) - but because of this the sqlCommandBuilder seems to be unable to create the update and insert commands - so I have to make them manually.

This is what I have thusfar, but my problem is that it only updates the first row that got changed with the new value.. So if I update one row, it seems to work, but I'm unable to update multiple lines at once. Any idea's? Or am I taking the complete wrong approach to this? ;)

Dim objRow As DataRow
For Each objRow In ds.Tables("Details").Rows
If objRow.RowState = DataRowState.Modified Then
Dim strUpdate As String
strUpdate = "UPDATE Details SET Aantal = @Aantal where Detailnr = @Detailnr"
da.UpdateCommand = New SqlCommand(strUpdate, sqlConn)
da.UpdateCommand.Parameters.Add(New SqlParameter("@Detailnr", SqlDbType.Int))
da.UpdateCommand.Parameters("@Detailnr").Value = objRow("Detailnr")
da.UpdateCommand.Parameters.Add(New SqlParameter("@Aantal", SqlDbType.Int))
da.UpdateCommand.Parameters("@Aantal").Value = objRow("Aantal")
'Debug.WriteLine(objRow("Detailnr").ToString())
da.Update(ds, "Details")
End If
Next

Thanks in advance for looking into this,
Tim De Vogel
S-Data NV
 
M

Miha Markic [MVP C#]

Hi,

Don't set parameter values (they will be set within Update) and set each parameter's SourceColumn property.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
Hi,

I have a datagrid in a winform that lets users input "detail lines" of an order form. I manually create both the dataset and the datagrid (ie: to have dropdownboxes etc) - but because of this the sqlCommandBuilder seems to be unable to create the update and insert commands - so I have to make them manually.

This is what I have thusfar, but my problem is that it only updates the first row that got changed with the new value.. So if I update one row, it seems to work, but I'm unable to update multiple lines at once. Any idea's? Or am I taking the complete wrong approach to this? ;)

Dim objRow As DataRow
For Each objRow In ds.Tables("Details").Rows
If objRow.RowState = DataRowState.Modified Then
Dim strUpdate As String
strUpdate = "UPDATE Details SET Aantal = @Aantal where Detailnr = @Detailnr"
da.UpdateCommand = New SqlCommand(strUpdate, sqlConn)
da.UpdateCommand.Parameters.Add(New SqlParameter("@Detailnr", SqlDbType.Int))
da.UpdateCommand.Parameters("@Detailnr").Value = objRow("Detailnr")
da.UpdateCommand.Parameters.Add(New SqlParameter("@Aantal", SqlDbType.Int))
da.UpdateCommand.Parameters("@Aantal").Value = objRow("Aantal")
'Debug.WriteLine(objRow("Detailnr").ToString())
da.Update(ds, "Details")
End If
Next

Thanks in advance for looking into this,
Tim De Vogel
S-Data NV
 
E

Eph0nk

Thanks a lot! It seems to work.

---
Tim De Vogel
S-Data NV
"Miha Markic [MVP C#]" <miha at rthand com> schreef in bericht Hi,

Don't set parameter values (they will be set within Update) and set each parameter's SourceColumn property.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
Hi,

I have a datagrid in a winform that lets users input "detail lines" of an order form. I manually create both the dataset and the datagrid (ie: to have dropdownboxes etc) - but because of this the sqlCommandBuilder seems to be unable to create the update and insert commands - so I have to make them manually.

This is what I have thusfar, but my problem is that it only updates the first row that got changed with the new value.. So if I update one row, it seems to work, but I'm unable to update multiple lines at once. Any idea's? Or am I taking the complete wrong approach to this? ;)

Dim objRow As DataRow
For Each objRow In ds.Tables("Details").Rows
If objRow.RowState = DataRowState.Modified Then
Dim strUpdate As String
strUpdate = "UPDATE Details SET Aantal = @Aantal where Detailnr = @Detailnr"
da.UpdateCommand = New SqlCommand(strUpdate, sqlConn)
da.UpdateCommand.Parameters.Add(New SqlParameter("@Detailnr", SqlDbType.Int))
da.UpdateCommand.Parameters("@Detailnr").Value = objRow("Detailnr")
da.UpdateCommand.Parameters.Add(New SqlParameter("@Aantal", SqlDbType.Int))
da.UpdateCommand.Parameters("@Aantal").Value = objRow("Aantal")
'Debug.WriteLine(objRow("Detailnr").ToString())
da.Update(ds, "Details")
End If
Next

Thanks in advance for looking into this,
Tim De Vogel
S-Data NV
 
J

Jim Hughes

You should not have to loop through the rows to get the modified changes.

Dim strUpdate As String = "UPDATE Details SET Aantal = @Aantal where
Detailnr = @Detailnr"
Dim SQLCmd = New SqlCommand(strUpdate, sqlConn)
SQLCmd.Parameters.Add(New SqlParameter("@Detailnr", SqlDbType.Int))
SQLCmd.Parameters.Add(New SqlParameter("@Aantal", SqlDbType.Int))
da.UpdateCommand = SQLCmd
' add additional commands for da.InsertCommand and da.DeleteCommand here
da.Update(ds, "Details") ' This line will automatically loop through
all
'of the modified rows and make the changes to the database and
issue the ds.AcceptChanges.

For best practices, take a look at the Microsoft Data Access Application
Block (DAAB).
Hi,

I have a datagrid in a winform that lets users input "detail lines" of an
order form. I manually create both the dataset and the datagrid (ie: to
have dropdownboxes etc) - but because of this the sqlCommandBuilder seems to
be unable to create the update and insert commands - so I have to make them
manually.

This is what I have thusfar, but my problem is that it only updates the
first row that got changed with the new value.. So if I update one row, it
seems to work, but I'm unable to update multiple lines at once. Any idea's?
Or am I taking the complete wrong approach to this? ;)

Dim objRow As DataRow
For Each objRow In ds.Tables("Details").Rows
If objRow.RowState = DataRowState.Modified Then
Dim strUpdate As String
strUpdate = "UPDATE Details SET Aantal = @Aantal where Detailnr =
@Detailnr"
da.UpdateCommand = New SqlCommand(strUpdate, sqlConn)
da.UpdateCommand.Parameters.Add(New SqlParameter("@Detailnr",
SqlDbType.Int))
da.UpdateCommand.Parameters("@Detailnr").Value = objRow("Detailnr")
da.UpdateCommand.Parameters.Add(New SqlParameter("@Aantal",
SqlDbType.Int))
da.UpdateCommand.Parameters("@Aantal").Value = objRow("Aantal")
'Debug.WriteLine(objRow("Detailnr").ToString())
da.Update(ds, "Details")
End If
Next

Thanks in advance for looking into this,
Tim De Vogel
S-Data NV
 

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

Cannot Update data via DatagridView 1
How to insert a record with a stored procecure 2

Top