dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question???
Go Back  Xtreme Visual Basic Talk > > > dataset changes - back to sql question???


Reply
 
Thread Tools Display Modes
  #1  
Old 12-07-2005, 10:03 AM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default dataset changes - back to sql question???


Help:

I am trying to populate data back to my database from the dataset. I get the following error on da.update below.

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

da is a public variable in the class. The function is called from a form button outside the class, like this:

runClassVC.vcUpdDataSet_PopulateVantage(ds1, "PART")

I can only imagine the code is looking for columns in the database, referenced from the dataset or something.

Thanks in advance for any light you can shed on this.

Public Function vcUpdDataSet_PopulateVantage(ByVal ds As DataSet, ByVal Table As String)


'Try
da.Update(ds, Table)

'Catch ex As Exception
' MessageBox.Show("No data exists for criteria.")

'End Try

End Function
Reply With Quote
  #2  
Old 12-07-2005, 01:11 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

How are you creating your DataAdapter (da)?

Did you specify anything for the .UpdateCommand, .InsertCommand or .DeleteCommand?
Are you using a CommandBuilder?
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #3  
Old 12-07-2005, 01:41 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default

Public da As New SqlClient.SqlDataAdapter

This is done in a function where I perform my initial server connection
da = New SqlDataAdapter(sqlString, sqlConn.ConnectionString)

This is done when I populate my datagrid
da.Fill(ds, table)

This is done in attempt to put data back into the database
da.Update(ds, Table)

Obviously, I'm missing something here. Thanks for your reply and any continued help you can provide.
Reply With Quote
  #4  
Old 12-07-2005, 02:58 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

Take a look at this page on MSDN. It has a sample of creating an Update Command.

I think that's the part you're missing. If you need to do deletes and inserts, you can also take a look at those commands.

The SQLCommandBuilder (there's a link in the page above) will create them all for you, but I recommend creating your own. There's no need to use resources for things that seldom change. (Just my opinion)
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #5  
Old 12-08-2005, 09:19 AM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Question

With regard to the prior posts, I have an ADO.NET segment from O'Reilly's Programming in Visual Basic.NET.

There is an example called "Allowing an SqlDataAdapter object to infer SQL UPDATE, INSERT, and DELETE statements from a SELECT statement." Somehow, they are just doing a da.Update(ds, Table) as I have done. The only difference I have seen is that they also use code to change their data in the datagrid.

ex: Dim row As DataRow = dt.Select("CategoryName = 'Dairy Products'") (0)
row("Description") = "Milk and stuff"

My preference is not to do this in code, but to change the data table on the form and have the upodates done. Do I somehow need to reference the datatable and create code to update my changes, similar to the above two lines, or is there an easier way?

I thought the whole point of a dataset was that there was no need to do multiple select statements as was done with recordsets in VB6.

Finally, what is the difference between a dataview and a data table?

Thanks again for your continued support.
Reply With Quote
  #6  
Old 12-08-2005, 10:26 AM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

I'm going to say right now, I haven't done a lot with databases in a Windows forms application. I've done it all in Web Apps, so I'm not sure all of the ins and outs of using the Binding Contexts, etc...

If you created the DataAdapter by dragging it onto your form, It may have automatically used the Command Builder to create the rest of the statements. Otherwise you can use the CommandBuilder to do it.

In your sqlString, are you using multiple tables via a Join or something like that? That may also be causing problems. Using a DataSet the intedend method is to create two DataTables, and use a DataRelation to join them. Not all Databases can handle update statements that reference more than one table. (And those that do, have many rules on when it's appropriate.)
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #7  
Old 12-20-2005, 09:19 AM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Is this sql string correct?

Public sqlString1 As String = "select partnum, partdescription, character09, character07 from part WHERE classid = 'CRIB'"

Public sqlString2 As String = "update part set partnum = @partnum, partdescription = @partdescription, character09 = @character09, character07 = @character07 WHERE classid = 'CRIB'"

I am trying to update data. I read data in to my dataset with sqlString1, and am trying to write data back (ALL CHANGES TO THE DATASET) with sqlString2. The problem is, I'm not sure the syntax here is even correct.

Thanks for any answers you can provide on this!

Private Sub btnTmpUpdateVantage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTmpUpdateVantage.Click

' Connect to SQL database
runClassVC.vcServerConn("Pilot80", "Zathras", sqlString2)
runClassVC.vcUpdDataSet_PopulateVantage(ds1, sqlString2, "PART")

' Disconnect SQL database
runClassVC.vcServerClose()

End Sub

Public Function vcUpdDataSet_PopulateVantage(ByVal ds As DataSet, ByVal QueryCriteria As String, ByVal Table As String)

da.Update(ds, Table)

End Function
Reply With Quote
  #8  
Old 12-20-2005, 12:58 PM
FrankxCid's Avatar
FrankxCid FrankxCid is offline
Regular
 
Join Date: Nov 2004
Location: Northwest Pennsylvania
Posts: 88
Default

I assume you are copying changes between two databases where each database has a table with the same format and same name. If the databases are on the same server, you can do this in one sql statement like this
UPDATE db2..part
set partnum = p2.partnum, partdescription = p2.partdescription, etc...
FROM db1..part p1
INNER JOIN db2..part p2
ON p1. classid = p2.classid
WHERE p1.classid = 'crib'

Make this a stored procedure and the code is even easier.
Reply With Quote
  #9  
Old 12-20-2005, 01:30 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default No, one database

I am using this to port data in. The select is to take the data from the database to the dataset to the data table / datagrid.

The update is to put the data back from the tata table / datagrid back to the database.

Our management here is not interested in stored procedures...

Thanks for your reply, but if you can shed some more light on this it would be greatly appreciated.
Reply With Quote
  #10  
Old 12-20-2005, 02:25 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Update Command Question

The problem here is the update example appears to be for individual records on a form. I have an entire dataset that I am changing in a data table. It is my understanding that the data adapter will manage these changes, and then update them by simple doing a

dataadapter.Update(dataset, Table)

Please help. I have been stuck on this now for weeks and there are not many good examples out there!
Reply With Quote
  #11  
Old 12-20-2005, 03:25 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Configuring the Data Adapter?

Here's another thought!

There's something about configuring the data adapter with SELECT, INSERT, UPDATE and DELETE commands. The problem is, in every example I cannot relate the data adapter with how it controls the application. I just don't get it! Many examples are also using stored procedures, I am not.
Reply With Quote
  #12  
Old 12-20-2005, 03:26 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

Quote:
Originally Posted by Sanders720
The problem here is the update example appears to be for individual records on a form.
What gave you that impression? The Update works for an entire dataset. Basically after you're done making changes to the dataset, it will loop through the records, and check the DataRow's state. If it's "Modified" then it will call the UpdateCommand with data from that row.

You have to create the UpdateCommand with Parameters that look at specific columns in your DataTable. After the update either call Accept Changes (or have the Update do it automatically) so the next time you call update, it will only push new changes since the last time you sent data.

I'm merging your other thread in with this one since they are the same issue. It makes it easier for the people helping you.

Yes your SQL looks correct for the Update. How are you putting it into the DataAdapter, and what do your parameters look like. The SQL alone isn't good enough. You need more.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #13  
Old 12-20-2005, 03:26 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Configuring the Data Adapter?

Here's another thought!

There's something about configuring the data adapter with SELECT, INSERT, UPDATE and DELETE commands. The problem is, in every example I cannot relate the data adapter with how it controls the application. I just don't get it! Many examples are also using stored procedures, I am not.
Reply With Quote
  #14  
Old 12-20-2005, 03:29 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default I still get an error

So da.Update(ds, Table) would be correct?

When I do this, I get the following error:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

The other option is this:

Dim sqlConnStr As New SqlClient.SqlCommand
sqlConnStr.Connection = sqlConn
sqlConnStr.CommandText = QueryCriteria


Dim param As SqlClient.SqlParameter
param = da.UpdateCommand.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09")
param = da.UpdateCommand.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07")
da.UpdateCommand = sqlConnStr.Connection.CreateCommand

which produces this error:

An unhandled exception of type 'System.NullReferenceException' occurred in vcLogic.exe

Additional information: Object reference not set to an instance of an object.

Perhaps the second is correct, and I am having problems with nulls, however the issue is on character09 which I am trying to populate. I am also guessing on the character09 field length. Is this an issue? If anything, it is far longer than 40 characters.

Thanks a bunch for your continued help!

Last edited by Sanders720; 12-20-2005 at 03:35 PM.
Reply With Quote
  #15  
Old 12-20-2005, 03:36 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

Quote:
Originally Posted by Sanders720
So da.Update(ds, Table) would be correct?
Yes. That is correct.
Quote:
Originally Posted by Sanders720
Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
That's where this comes in:
Quote:
Originally Posted by wayneph
Yes your SQL looks correct for the Update. How are you putting it into the DataAdapter, and what do your parameters look like. The SQL alone isn't good enough. You need more.
In my link from a few weeks back it had lines that look like this:
Quote:
Originally Posted by MSDN link from Post 4
Code:
parm = cmd.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID") parm.SourceVersion = DataRowVersion.Original
In your SQL Query for each @partnum, @partdesc, etc... you will need something equivalent to tell the update query which column to look at for the data.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #16  
Old 12-20-2005, 04:07 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default

Is this at all valid? The da.Update does not work regardless. character09 and character07 are the field names I am trying to update.

Dim param As SqlClient.SqlParameter
param = da.UpdateCommand.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09")
param = da.UpdateCommand.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07")
da.UpdateCommand = sqlConnStr.Connection.CreateCommand

Do I also need to reference the other two fields that I'm not trying to update?

Public sqlString2 As String = "update part set partnum = @partnum, partdescription = @partdescription, character09 = @character09, character07 = @character07 WHERE classid = 'CRIB'"

I did read the MSDN, the problem is, all these articles assume I know what I am doing. I have no idea how the items in the statement relate to each other or what they are trying to do!

Thanks for the help!
Reply With Quote
  #17  
Old 12-20-2005, 05:24 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

Well, you have two options. 1. Take out the items that you don't want to update. 2. Create parameters for those fields because in the SQL you are saying to want to update it.

What I think you actually want is the partnumber in the where clause. Since you haven't actually told us what your application does, there's no way for us to know.

Here's a Breakdown of your SQL Statement:
Code:
UPDATE part  -- Says what table you want to update
SET
  partnum = @partnum, -- make the partnum field equal to the @partnum Parameter
  partdescription = @partdescription, -- make the partdescription field = to @partdescription parameter
  character09 = @character09, -- same as above
  character07 = @character07 -- same as above
WHERE
  classid = 'CRIB' -- This will update all items with the classid of 'CRIB'
What I THINK you want:
Code:
UPDATE part  -- Says what table you want to update
SET
  character09 = @character09, -- same as above
  character07 = @character07 -- same as above
WHERE
  partnum = @partnum -- This will update all item with the partnum of  @partnum
Then you will need to create 3 parameters. One for @character09, One for @character07 and one for @partnum. Also take a look at the parm.SourceVersion. By setting that, you make sure that you get the new version of teh character07 and character09 fields. And by setting the Source Field, you know that a process hasn't inadvertantly updated your datasource, and you're updating the correct item in your table.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #18  
Old 12-21-2005, 08:20 AM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default More Questions...

Thanks for all your help. I will research the parm.SourceVersion issue. I still have questions, and am wondering if this is getting more complicated than intended.

Should I be using da.Update(ds, Table) or

Dim sqlConnStr As New SqlClient.SqlCommand
sqlConnStr.Connection = sqlConn
sqlConnStr.CommandText = QueryCriteria


Dim param As SqlClient.SqlParameter
param = da.UpdateCommand.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09")
param = da.UpdateCommand.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07")
da.UpdateCommand = sqlConnStr.Connection.CreateCommand

I'm guessing if I need to look at param.SourceVersion, the latter is true. When I changed my query statement to:

"update part set character09 = @character09, character07 = @character07 WHERE partnum = @partnum"

I got the following error:

An unhandled exception of type 'System.NullReferenceException' occurred in vcLogic.exe

Additional information: Object reference not set to an instance of an object.


To review, I am feeding in to a dataset four fields, PartNo, PartDescription, character09 and character07.

I am making the dataset a data table. I am a little unsure what the difference is between a data table and a data view? Both seem to work.

The datagrid uses the data table to populate.

Code:
Public Function vcUpdDataSet_PopulateDataGrid(ByVal Control As DataGrid, ByVal ds As DataSet, ByVal QueryCriteria As String, ByVal Table As String) da.Fill(ds, Table) da.AcceptChangesDuringFill = True ' Create datatable Dim dt As DataTable = ds.Tables(Table) ' Set datasource Control.DataSource = dt

I can now edit my data grid, and then there is a button to update the changes.

This is where I am trying to reopen the database and update the data.

The sql strings are two public variables:

Code:
Public sqlString1 As String = "select partnum, partdescription, character09, character07 from part WHERE classid = 'CRIB'" Public sqlString2 As String = "update part set character09 = @character09, character07 = @character07 WHERE partnum = @partnum" Public Function vcUpdDataSet_PopulateVantage(ByVal ds As DataSet, ByVal QueryCriteria As String, ByVal Table As String) Dim sqlConnStr As New SqlClient.SqlCommand sqlConnStr.Connection = sqlConn sqlConnStr.CommandText = QueryCriteria Dim param As SqlClient.SqlParameter param = da.UpdateCommand.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09") param = da.UpdateCommand.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07") da.UpdateCommand = sqlConnStr.Connection.CreateCommand
When the form is loaded:

Code:
Private Sub frmStockList_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load ' *** SQL QUERY LOGIC FOR GETTING PARTNUM INFORMATION *** Debug.Write("sqlString1: " & sqlString1) ' Connect to SQL database runClassVC.vcServerConn("Pilot80", "Zathras", sqlString1) ' Populate dataset runClassVC.vcUpdDataSet_PopulateDataGrid(dtgStockList, ds1, sqlString1, "PART") ' Format Datagrid Style Format_dtgStockList(dtgStockList) ' Populate comboboxes runClassVC.vcUpdDataReader_PopulateComboBox(cboPartMatl, sqlString1, "character09") runClassVC.vcUpdDataReader_PopulateComboBox(cboPartShape, sqlString1, "character07") ' Disconnect SQL database runClassVC.vcServerClose()
And when the update button is pressed:
Code:
Private Sub btnTmpUpdateVantage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTmpUpdateVantage.Click ' *** SQL QUERY LOGIC FOR UPDATING PARTNUM INFORMATION *** Debug.Write("sqlString2: " & sqlString2) ' Connect to SQL database runClassVC.vcServerConn("Pilot80", "Zathras", sqlString2) runClassVC.vcUpdDataSet_PopulateVantage(ds1, sqlString2, "PART") ' Disconnect SQL database runClassVC.vcServerClose()

Last edited by wayneph; 12-21-2005 at 08:28 AM. Reason: please use [vb][/vb] tags for posting code
Reply With Quote
  #19  
Old 12-21-2005, 12:12 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

is there any chance you can attach your code? just zip up the project directory with out the bin or debug directory because no binaries are allowed.

Then attach it here, so we can take a look at it.

In your initial quest, it's not an either/or. You need both of those lines. The top one does the work, but it's the rest of them that specify what needs to be done.

One other problem that I immediately notice is that "da.UpdateCommand = sqlConnStr.Connection.CreateCommand" should just be "da.UpdateCommand = sqlConnStr"

sqlConnStr is a bit misleading as a instance name for a SQLCommand object. It was very confusing trying to figure out what you're doing.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #20  
Old 12-21-2005, 12:19 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default

Done, and thank you.

I believe sqlConnStr.Connection.CreateCommand in my example = sqlConnStr in your example
Attached Files
File Type: zip vcLogic.zip (30.7 KB, 4 views)
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
 
dataset changes - back to sql question???
dataset changes - back to sql question???
 
-->