Database update help needed

[Guest]

I am a newbie to the ADO .NET stuff. I am using Visual Studio 2003/ VB to
develop an app. Been doing development for a long time, but having a tough
time getting my hands around the new way of doing things. Hoping you can
help.

First, is there a site or book you can point me to that will describe this
stuff in detail? I can't seem to get the stuff I am reading/trying to work.

Second, can someone give me a basic idea on the "preferred" use of data
adapters and datasets? Separate DS for each DA, or one DS for all DAs?

Lastly, I have an issue of data not being saved. I have a DB connection to
an Access DB, a DA with the necessary SQL statements for update and insert,
and a group of text controls bound to the data. I open the form and the data
is there. I make changes and then issue:

MyDataAdapter.Update(MyDataSet)

I get no errors, but nothing changes in the DB.

Part of the issues for me are understanding the opening/closing of the
connection and how the data in the DS gets back to the actual DB.

Sorry for the long question/post and many questions. I appreciate any help!!

 

[geeksgk]

Here is a example code..Hope this helps

public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string
myConnection,string mySelectQuery,string myTableName)
{
OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery,
myConn);
OleDbCommandBuilder custCB = new
OleDbCommandBuilder(myDataAdapter);

myConn.Open();

DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS);

//code to modify data in dataset here

//Without the OleDbCommandBuilder this line would fail
myDataAdapter.Update(custDS);

myConn.Close();

return custDS;
}

Answer for second question, one data adapter for each table in the
database and one dataset for everthing.

Third one, post your skeleton code so that some one can take a look at
it and help

 

[Guest]

Thanks. This makes sense. What you have is setting up the connections and
such manually in code. I am using the visual components and binding the data
to text controls. How would you do it with that?

I have a form for editting a person's demographic info. The user selects
the person from the main form and then selects edit. The code opens the new
form and sets the PersonId property on the new form before showing it.

The new form contains a dbconnection, DA and DS. The SET code does the
following:

daPerson.SelectCommand.Parameters(0).Value = Value
dsPersonUpdate1.Clear()
daPerson.Fill(dsPersonUpdate1, "Person")

The controls are bound to the DS, so everything shows up like it should. I
make the changes I want and then try to save it. I have tried several
different things with no luck.

daPerson.Update(dsPersonUpdate1) --> doesn't error out, but no go
daPerson.Update(DataRowState.Modified) --> no go, but no error.

I am sure I am missing something simple here. But after you stare at
something for a long time........

Basically, how do you handle add/edit/delete of records with bound controls?

 

[J L]

I am a newbie too and I will share with you what I have found:

ADO.Net is all-things-to-all-people and that is what makes it so hard
to get your hands around. My goal is to digest all of this and reduce
it to the bare necessities I require. Basically I am a DAO person, so
I am looking for ways to implement the "tried and true" tools I used
in DAO. But I also see lots of neat new ideas. I really like the
disconnected dataset with relations for generating my data driven
reports and screens. Things that took several iterations in DAO to
achieve.

Anyway, I hope this helps and that it is proper to post the list of
sites that I have saved. Just want to give back some of what I have
gained from all the expert help here.

BTW...you are at the best resource for learning. Just browse the
topics and read the responsed. The MVP's are outstanding!

Book: Sams "Teach Yourself ADO.Net In 21 Days" by Dan Fox... pretty
good. Also for VB.Net in general: "The Book Of VB.Net" by Matthew
MacDonald and "VB.Net Language In A Nutshell" from O'Reilly by Roman,
Petrusha and Lomax.

Sites (mostly harvested from lurking this discussion group. Notes are
mine and date is when I visited the site)
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx
Tutorial 2/2/05
http://www.devarticles.com/c/b/ADO.NET/
ADO.NET Help, ADO.NET Tutorials, ADO.NET Programming,
ADO.NET Code, and more! 2/2/05
http://www.thecodeproject.com/cs/database/DatabaseAcessWithAdoNet1.asp
Article on using ADO.Net and some data-bound stuff. 2/2/05
http://msdn.microsoft.com/library/d...cpguide/html/cpconAccessingDataWithADONET.asp
MSDN articles on using ADO.Net…good stuff 2/2/05
http://www.15seconds.com/issue/040914.htm
Transactions in ADO.Net 2/2/05
http://visualbasic.about.com/od/learnvbnetandadonet/l/aa041203a.htm
Tutorial on ADO.Net 2/2/05
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
Source code to generate classes from SQL database 2/4/05
http://msdn.microsoft.com/msdnmag/issues/02/02/data/default.aspx
Article showing how to create business classess! 2/12/05
http://www.knowdotnet.com/articles/databasetransactions.html
Transactions in ADO.net 2/12/05
http://support.microsoft.com/default.aspx?scid=kb;en-us;309488
MS Article on reading DB Schema from OleDb provider 2/15/05
http://www.ondotnet.com/pub/a/dotnet/2003/05/26/datacolumn_expressions.html
Adding calculated columns to a DataTable…great idea!! 2/4/05
http://www.developer.com/db/article.php/3069061
Article on Strongly Typed DataSets 2/4/05
http://www.samspublishing.com/articles/article.asp?p=30633&seqNum=1
Article on Strongly Typed DataSets with multi-tables and
relations 2/4/05
http://www.knowdotnet.com/articles/datarelation.html
Good article on the DataRelation object. 2/8/05
http://www.dotnet247.com/247referen...ial/4882DAB9-1EF6-41E4-9B27-F44DEB48FF32.dcik
Article on building a Universal Data Access Layer using
Interfaces…interesting idea 2/9/05
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/wriportap2.asp
Writing a Portable Data Access Layer 2/9/05
http://www.codeproject.com/vb/net/data_access_layer.asp
Good article on DAL 2/9/05
http://ipattern.com/simpleblog/PermLink.aspx?entryid=24
Article on Data Access Block (DAAB) and Interfaces 2/10/05
http://msdn.microsoft.com/library/d...lectinginterfacesclassesforimplementation.asp
MSDN on Data Provider classes and interfaces 2/10/05
http://www.dotnet247.com/247referen...ull=/library/en-us/dnvs05/html/vsgenerics.asp
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
2/10/05
http://msdn.microsoft.com/msdnmag/issues/02/02/data/default.aspx
Article showing how to create business classess! 2/12/05
http://www.knowdotnet.com/articles/cdataaccess.html#vbcode

 

[slonocode]

Thanks. This makes sense. What you have is setting up the connections and
such manually in code. I am using the visual components and binding the data
to text controls. How would you do it with that?

I have a form for editting a person's demographic info. The user selects
the person from the main form and then selects edit. The code opens the new
form and sets the PersonId property on the new form before showing it.

The new form contains a dbconnection, DA and DS. The SET code does the
following:

daPerson.SelectCommand.Parameters(0).Value = Value
dsPersonUpdate1.Clear()
daPerson.Fill(dsPersonUpdate1, "Person")

The controls are bound to the DS, so everything shows up like it should. I
make the changes I want and then try to save it. I have tried several
different things with no luck.

daPerson.Update(dsPersonUpdate1) --> doesn't error out, but no go
daPerson.Update(DataRowState.Modified) --> no go, but no error.

I am sure I am missing something simple here. But after you stare at
something for a long time........

Basically, how do you handle add/edit/delete of records with bound controls?

This may be a case where you haven't ended the current edit in the
dataset before updating.

You can check the dataset for changes before you update to be sure.
Just check whether DS.HasChanges is true or false.

You can end the current edit for the record you are working on with the
bindingcontext object.

Me.BindingContext(DS, "TableName").EndCurrentEdit()


If your form had navigation between records the endcurrentedit would be
called each time you moved to a new record. It sounds like you are only
working with one record at a time so you will have to supply it in code
before you update.

HTH
Slonocode

 

[slonocode]

Thanks. This makes sense. What you have is setting up the connections and
such manually in code. I am using the visual components and binding the data
to text controls. How would you do it with that?

I have a form for editting a person's demographic info. The user selects
the person from the main form and then selects edit. The code opens the new
form and sets the PersonId property on the new form before showing it.

The new form contains a dbconnection, DA and DS. The SET code does the
following:

daPerson.SelectCommand.Parameters(0).Value = Value
dsPersonUpdate1.Clear()
daPerson.Fill(dsPersonUpdate1, "Person")

The controls are bound to the DS, so everything shows up like it should. I
make the changes I want and then try to save it. I have tried several
different things with no luck.

daPerson.Update(dsPersonUpdate1) --> doesn't error out, but no go
daPerson.Update(DataRowState.Modified) --> no go, but no error.

I am sure I am missing something simple here. But after you stare at
something for a long time........

Basically, how do you handle add/edit/delete of records with bound controls?

This may be a case where you haven't ended the current edit in the
dataset before updating.

You can check the dataset for changes before you update to be sure.
Just check whether DS.HasChanges is true or false.

You can end the current edit for the record you are working on with the
bindingcontext object.

Me.BindingContext(DS, "TableName").EndCurrentEdit()


If your form had navigation between records the endcurrentedit would be
called each time you moved to a new record. It sounds like you are only
working with one record at a time so you will have to supply it in code
before you update.

HTH
Slonocode

 

[Guest]

Me.BindingContext(DS, "TableName").EndCurrentEdit()

That was it!!! Not sure HOW I missed that. I knew it was something "simple"
I was overlooking, but after staring at things and trying many different
things, I got foggy-brained on the whole thing.

THANKS!!!