VB .NET talking to databases...
-
Hi,
I'm doing a VB project that has bound controls using ADO.NET. I'm trying to figure out how to clear a DataSet object table.
Basically, my app is currently able to read and update a database table just fine. Now I go to add the "New" fuctionality where I return the app to its initial state, ie: tables in a dataset object are not present. I want to do it this way because when the user hits "Save" the first time, data is written to the database, read back into tables, and those tables are bound to the appropriate controls on a form. The "new" commmand is supposed to undo all that but I'm having trouble figuring out how to actually de-allocate these tables. Microsoft's docs aren't too clear on how to do that.
Any help is appreciated.
Thanks,
-John C.
-
Well, the DataSet .Clear() method removes all rows from all tables. There's also a .Reset() method to reset it to its original state. You might play around with those for starters to see if they get you closer.
-
Yea, I tried those....
Basically, I tried the following dataset methods...
dataset.table("tablename").clear()
dataset.table("tablename").reset()
dataset.table.remove("tablename")
I'm still stumped... The really odd thing is that when I look at the dataset in the debugger I can see that the table is "Nothing" when I go to re-create it but I keep getting an error when I try to Fill() it again. I used to get that error by accidentally Fill()ing it twice so thats why I'm going down this path. I wish MS would give a little more details on why errors happen.
-JC.
-
Hmmm... Are you just trying to get rid of one table or the whole DataSet? What does it do if you Dispose of the DataSet and recreate a new one? i.e.,
MyDataSet.Dispose()
MyDataSet = new DataSet()
It's ugly, but hey... Not 100% sure about exactly what you're trying to do... Got more info?
-
OK, let me describe what I'm trying to do a little better...
This is a VB.Net 2003 app that talks to MS SQL server using ADO.NET and bound controls.
Its an expense report program, with two main tables. Expense Reports and Expenses. There's many expenses for each expense report. The main page lets the user enter and edit expense reports one at a time and shows one expense within the report but lets the user scroll through the set of expenses using arrow buttons to show one at a time.
When the apps starts, it starts from a module that creates the forms as objects and also creates and configures all the objects that talk to the database in Sub Main(). It gets some minor tables from the DB to populate some list controls then calls application.run() to start the whole thing off.
When the user hits File, Save the contents of all the controls is put into SQLCommand object that insert data to the DB, then the data is read back into the DataSet object, and controls are bound to View objects that show the appropriate tables. Next time the user hits save, an update of existing objects and DB tables is performed. That works great.
Now the snag. User hits File, New so they can enter a new record and clear the one thats on screen. I can clear all the controls no problem but somehow I have to get the DataSet so it will accept a new set of data when user hits Save again. I've tried several things including:
objDataSet.Tables("ExpenseReport").Clear()
objDataSet.Tables("ExpenseReport").Reset()
objDataSet.Tables.Remove("ExpenseReport")
The last one is the part that really stumps me. The .Remove() method is supposed to remove the desired table from the DataSet so that when you go objDataAdapter.Fill("ExpenseReport") again in the save call it just fills in the DataSet with a new table called ExpenseReport. I can see that the ExpenseReport table is Nothing in the debugger just before .Fill() is called but I always get an error.
I get:
System.Data.SqlClient.SqlException error, and the extra info is: System error.
Any ideas?
-JC.
-
Do me a favor, put it in a try catch block and copy the exception. It'll look something like this:
Try
objDataSet.Tables.Remove("ExpenseReport")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Then copy from the Output window, so we can try to get a more specific error message. You might even put a breakpoint on the Console.WriteLine statement and copy the contents of ex from the Watch window.
I've done similar things to what you describe, but I encapsulated the DataSet in a class and exposed it as a property. It sounds like you're nearing the finish line on this project though, and probably don't want to refactor the whole thing at this point. So use the Try Catch block to see if you can get a more specific exception.
-
Ah, OK.
I wrapped the offending line with Try-catch and here's what I caught:
Try
objDataAdapter.Fill(objDataSet, "ExpenseReport")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
result was:
The variable name '@ReportNumber' has already been declared. Variable names must be unique within a query batch or stored procedure.
Then I remembered I'd seen this in the SQL query analyzer when I made a boo-boo there and I was able to repeat the message by executing a wad of code in the analyzer with two Declare statements for @ReportNumber. Thus: There's an implied variable declaration going on at the server end that I need to figure out and probably nothing wrong with code on the client end.
I'm gonna try clearing or resetting the DataAdapter and see how I do. Thanks for your help.
-JC.
-
Hmmm. Are you adding Parameters to the DataAdapter's SelectCommand Parameters collection? Sounds like you're re-adding them twice. If that's the case, clear out the parameters collection before adding them or look at the parameters collection to see if those parameters already exist first.
-
Would you post the source for the entire function or sub where you are getting the error so we can see exactly what you are doing with you SQL Command, SQL Data Adapter and your DataSet/Datatables?
-
Duh, I just found it.
Seems I thought I was creating all my SQL command param objects up in sub Main but turns out I wasnt so when I went to do another insert, that same param got added again. Bugger was right under my nose all the time.
Sorry to waste everyone's time.
-JC.
-
No waste of time
We all hit little snags and brain freezes every now and then. Best of luck!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply