.NET database handling is fu**edup

[kejpa]

Hi,

It's getting to my nerves... I'm already at 10, 10 times 10 times...

I have an old table from which I want to fill values into a new table and at the same time add two more fields (the new table contains two more fields)

 

Strategy:

Open old table

Open new table

For each row in old_table

insert new column value(0)

insert new column value(1)

insert all old column values

Close old table

close new table

 

Current status:

Open old table - Fix

Open new table - Fix

For each row in old_table - Fix

insert new column value(0) - Fail

insert new column value(1) - Fail

insert all old column values - Fail

Close old table - Fix

Update and close new table - Fail

 

Plz enlight my darkness!

Regards

/Kejpa

[PlausiblyDamp]

Does it give any particular errors when it fails or does the update just not occur? Any chance you could post the relevant code to give us a chance to look for problems.

[kejpa]

Does it give any particular errors when it fails or does the update just not occur?

I'd be one of the luckiest guys at this place if I even got the syntax in order ....

The database handling in .NET stinks

 

My fragmented try...

Dim dtRow as ?!?!??
Dim dtCol as !?!??

For each dtRow in dtTableOld.Rows
   dtTableNew.NewRow()
   dtTableNew.Col(0)=iMyInteger
   dtTableNew.Col(1)=iMyInteger
   for each dtCol in dtTable.Cols
         dtTable.Col(dtCol.Caption).Value=dtCol.Value
   next
next
dtTableNew.Update

 

But as I said this will not even compile, I don't even know which objects to use. The tables are Access tables if it matters.

 

Still quite clueless in .NET, with VB6 I'd be on to the next issue on my everlasting list...

 

Regards

/Kejpa

[PlausiblyDamp]

Curious sense of logic you have there - you do not know what objects to use therefore .Net is at fault. Also could you try to keep the language toned down - especially in the thread's title.

 

I'm guessing that the start should be

Dim dtRow As DataRow
Dim dtCol As DataColumn

but that is based on the variable names and nothing else.

It will also fail to compile because the property is Columns not Cols. I'm also assuming that dtTableOld and dtTableNew are DataTables - curious as to which of them dtTable is supposed to be though.

[kejpa]

Curious sense of logic you have there - you do not know what objects to use therefore .Net is at fault.

I've been doing database programming for 10 years with DAO, ADO, ODBC and others in VB(3-6) and Delphi(4-7) and always found my way by reading the help texts and analyzing examples.

Now suddenly MS is giving me hell. I don't know what objects to use, I don't know where to find obvious properties and so "suddenly I'm not half the man I used to be, there's a shadow hanging over me" to make a sensible quote.

 

Dim dtRow As DataRow
Dim dtCol As DataColumn

but that is based on the variable names and nothing else.

Yes, I used those variable names in order for all of you to know what they are in my head, but as I said, I'm completely lost. From the help texts I know that it's Columns, not cols but the Column object doesn't have a Value property!

 

So today after a good nights sleep I guess my somewhat more polite question is...

How do you read, modify and update a database table?

 

Regards

/Kejpa

[Gladimir]

So today after a good nights sleep I guess my somewhat more polite question is...

How do you read, modify and update a database table?

 

The code is C#, but the objects will be the same for VB. I don't know what the VB equivalent to the using keyword (maybe Inherits) but you'll need these libraries for an Access database. These statements belong at the top of your code file with the other similar statements.

using System.Data;
using System.Data.OleDb;

Now let's define the steps and create a connection object for our MS Access database and create our database connection object.

[color=Green]/* Three steps to a database connection:
* 1. Create a database connection object for our MS Access database. 
* 2. Create a data adapter object for our connection object.
* 3. Create a command object for our data adapter object.*/

/* Step 1: A database connection object is a .NET connection class and a 
* connection string.  See [url=http://www.connectionstrings.com/]Connection Strings[/url] for more.*/[/color]
[color=Blue]string[/color] mdbstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\temp\northwind.mdb;"; 
OleDbConnection mdbConnx = [color=Blue]new[/color] OleDbConnection();

Now, we'll use the connection object in the creation of our data adapter. The data adapter is not always necessary. Depending on what we're trying to do, we could just use a connection object and a command object, but we'll be using the CommandBuilder in this example, which requires an adapter.

[color=Green]/* Step 2: A typical data adapter is a .NET data adapter, an SQL statement,
* and an instance of a .NET database connection object.*/[/color]
[color=Blue]string[/color] strSelect = "SELECT * FROM Customers WHERE (Region Is Null)";
OleDbDataAdapter mdbCustomers = [color=Blue]new[/color] OleDbDataAdapter(strSelect, mdbConnx);

At this point, we're actually ready to read records from the database table and start using them in our application, but we'll go ahead and define a command object while we're here. The OleDbCommandBuilder object automatically creates the SQL statements necessary to update a single table from a DataSet, through a data adapter. As with anything that is done "automatically", the OleDbCommandBuilder is not infallible, but it is pretty reliable and I have often used it.

[color=Green]/* Step 3: create an OleDbCommandBuilder object to automatically generate SQL 
* statements for single-table updates if you set the SelectCommand property 
* of the OleDbDataAdapter.*/[/color]
OleDbCommandBuilder custCommands = [color=Blue]new[/color] OleDbCommandBuilder(mdbCustomers);

Now let's use these objects in our application. First, we will declare and populate a DataSet, a collection of database tables. We will have only one table in our collection.

[color=Green]/* Create a dataset and populate it using the data adapter object. */[/color]
[color=Blue]string[/color] dsName = "dsNorthwind";
[color=Blue]string[/color] tableName = "Customers";
DataSet ds1 = [color=Blue]new[/color] DataSet(dsName);
[color=Blue]try[/color]
{
mdbCustomers.Fill(ds1, tableName);
}
[color=Blue]finally[/color]
{
mdbConnx.Close();
}

Now, throw a DataGrid, a ListBox, and a ComboBox on a windows form and bind them to appropriate elements in our dataset.

[color=Green]/* Bind the data in our dataset to our data aware controls.*/[/color]
DataTable dt = ds1.Tables[tableName];
dataGrid1.DataSource = dt;
comboBox1.DataSource = dt;
comboBox1.DisplayMember = dt.Columns["CompanyName"].ColumnName;
listBox1.DataSource = dt;
listBox1.DisplayMember = dt.Columns["ContactName"].ColumnName;

The great part about all of this is coming. Select the drop-down from your ComboBox, scroll all the way down and select the last company name. You'll see the contact name automatically selected in your ListBox and the DataGrid will automatically move the selection caret to the record matching that company name! Brilliant!

 

When you're ready to update, simply call the Update method of the data adapter object, just as you used the Fill method.

[color=Green]/* This code will not work without an instance of the OleDbCommandBuilder object. */[/color]
[color=Blue]try[/color]
{
mdbCustomers.Update(ds1, tableName);
}
[color=Blue]finally[/color]
{
mdbConnx.Close();
}

 

And you're on your way...

[samsmithnz]

The VB equiv (in this context) is imports.

[MikeyLDS]

I've been doing database programming for 10 years with DAO' date=' ADO, ODBC and others in VB(3-6) and Delphi(4-7) and always found my way by reading the help texts and analyzing examples. [/quote']

 

Have you considered reading a book about vb.net? I would recommend: "Visual Studio .Net: Mastering Visual Basic" by Sybex ... Wrox and Deitel are good authors too.

 

MS are trying to over take the C family of programming. At the end of the day vb.net is so much improved on the previous version you're bound to have to learn new stuff. Good luck in your studies. ;-)

[laredo512]

I'd be one of the luckiest guys at this place if I even got the syntax in order ....

The database handling in .NET stinks

 

My fragmented try...

Dim dtRow as ?!?!??
Dim dtCol as !?!??

For each dtRow in dtTableOld.Rows
dtTableNew.NewRow()
dtTableNew.Col(0)=iMyInteger
dtTableNew.Col(1)=iMyInteger
for each dtCol in dtTable.Cols
dtTable.Col(dtCol.Caption).Value=dtCol.Value
next
next
dtTableNew.Update

 

But as I said this will not even compile, I don't even know which objects to use. The tables are Access tables if it matters.

 

Still quite clueless in .NET, with VB6 I'd be on to the next issue on my everlasting list...

 

Regards

/Kejpa

If I understand correctly you simply want to copy over data from an old dataset to a new dataset and then update...

 

To some experts, it might seem a little out of whack, but it works for me http://www.xtremedotnettalk.com/images/smilies/wink.gif

 

[size=2][color=#0000ff]Dim[/color][/size][size=2] r [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]Integer[/color][/size][size=2] = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].dsOldDataset.OldTable.Count 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] c [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]Integer[/color][/size][size=2] = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].dsOldDataset.OldTable.Columns.Count

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] i [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]Integer
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] j [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]Integer

[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] dat(r, c) [/size][size=2][color=#0000ff]As [/color][/size][size=2][color=#0000ff]Object

[/color][/size][size=2][color=#0000ff]For[/color][/size][size=2] i = 0 [/size][size=2][color=#0000ff]To[/color][/size][size=2] r - 1
 [/size][size=2][color=#0000ff]For[/color][/size][size=2] j = 0 [/size][size=2][color=#0000ff]To[/color][/size][size=2] c - 1
	 dat(i, j) = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].dsOldDataset.OldTable(i)(j)
 [/size][size=2][color=#0000ff]Next
[/color][/size][size=2][color=#0000ff]	 [/color][/size]
[size=2][color=#0000ff]	 me[/color][/size][size=2].dsNewDataset.NEWTable.addrows(dat(i,0), dat(i,1), dat(i,2) ..... newValue1, newValue2)

[/size][size=2][color=#0000ff]Next

[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].dataAdapterNewTable.update([/size][size=2][color=#0000ff]Me[/color][/size][size=2].dsNewDataset.NEWTable)

[/size]

 

Hopefully this helps a bit.

[Nerseus]

I'd start by checking out this link, which you have in the Linx section of your own website.

 

Question to you: Are you just trying to add two columns/values to your DataSet, or are you trying to move data from one DB to another (or one table to another) and add two columns as you go? Generally, if you just want to move data from one table to another or one db to another, you don't want/need a bunch of code. Access provides linked tables - you could link to a table in another DB and write a one-shot query to insert into the other table from an existing table. SQL Server provides Data Transformation Services (DTS) to do even more.

 

Here's the 10,000 foot view of Data Access in .NET:

1. Mostly forget what you learned in VB6.

2. Data Access in .NET falls into 3 categories (more or less):

A. DataReaders - basically a cursor to read from the database

B. A database-specific adapter, command object, and connection object

C. The DataSet, DataView and "child" objects (DataTable, DataRow, DataColumn, etc.)

 

The concept of a DataSet is brand new to .NET - nothing like it before in VB6. Generally you use a DataAdapter (one specific to SQL Server, OleDb or other varieties) to communicate with the DataBase. The DataSet is like an in-memory DataBase that knows NOTHING of the real database. It has it's own .NET data types, it's own version of keys and relationships, and more.

 

Once you have a decent grasp of the DataSet and how to manipulate that, you will see that it doesn't matter if you use Access or SQL Server - the DataSet is agnostic to it all.

 

-nerseus

[kejpa]

I'd start by checking out this link' date=' which you have in the Linx section of your own website.[/quote']

Point taken, guess I wasn't at my best last Tuesday...

I guess I have to repent and do a few Hail Mary's

 

Question to you: Are you just trying to add two columns/values to your DataSet' date=' or are you trying to move data from one DB to another (or one table to another) and add two columns as you go? Generally, if you just want to move data from one table to another or one db to another, you don't want/need a bunch of code. Access provides linked tables - you could link to a table in another DB and write a one-shot query to insert into the other table from an existing table. [/quote']

The database is at our customers, for the new version of our App I will need two more fields and so I'm making a new table in the database copying all the records and add the two new fields. But, here's the catch, the two fields will together form the primary key. As of today there are no primary key in the table (it's NOT my design ;) ) I will compute the two fields from other data in the table.

Linked tables are great, but not in this case.

 

Now' date=' throw a DataGrid, a ListBox, and a ComboBox on a windows form and bind them to appropriate elements in our dataset.[/quote']

Sorry, you were great this far, but I don't have a form, the updates will be executed in code only :(

 

 

Here's the 10,000 foot view of Data Access in .NET:

1. Mostly forget what you learned in VB6.

2. Data Access in .NET falls into 3 categories (more or less):

A. DataReaders - basically a cursor to read from the database

B. A database-specific adapter, command object, and connection object

C. The DataSet, DataView and "child" objects (DataTable, DataRow, DataColumn, etc.)

.NET is pretty much forget all what you've learned in VB6 :(

 

Once you have a decent grasp of the DataSet and how to manipulate that, you will see that it doesn't matter if you use Access or SQL Server - the DataSet is agnostic to it all.

So I've understood, I guess it's great once you get a grip, but where do I find a decent description of how to use it, with code only.

I don't have any user interface with the app connected to the database. It's a C/S model where the server is handling calculations, communications with external devices including the database and the client is merely showing the results of the calculations.

 

repentant

/Kejpa

[Gladimir]

Sorry' date=' you were great this far, but I don't have a form, the updates will be executed in code only :([/quote']

All this is actually easier without the user interface.

 

After following my example, you have a DataSet with at least one DataTable. You can add as many DataTables as you like. Working with the data programmatically is simple.

 

[color=Green]/* add column to customers table */[/color]
DataTable dt = ds1.Tables["Customers"];
dt.Columns.Add("YTDSales", System.Type.GetType("System.Decimal"));

[color=Green]/* Do something to each Row (record) in a DataTable */[/color]
[color=Blue]string[/color] ytdsales = "1,076,903.89";
DataRowCollection drc = dt.Rows;
[color=Blue]foreach[/color] (DataRow row [color=Blue]in[/color] drc)
{
row.BeginEdit();
row["Region"] = "NONE";
[color=Blue]if[/color] (ytdsales.GetType().Equals([color=Blue]typeof[/color] (System.Double)))
                row["YTDSales"] = (decimal) ytdsales; [color=Green]// this line won't compile[/color]
[color=Blue]else if[/color] (ytdsales.GetType().Equals([color=Blue]typeof[/color] (System.String)))
	row["YTDSales"] = Convert.ToDecimal(ytdsales);
row.EndEdit();
}

The previous code won't actually build because my compiler knows that ytdsales is a string and that string can't be converted to type decimal implicitly, but it demonstrates a point.

[bri189a]

On a side note I highly suggest looking into strongly typed datasets at this point. You'll find them a great time saver and make your code so, so, much simpler...I'm talking VB6 simple... strong typed datasets allow you to do things like (C# syntax, but VB basically the same:

 

//Update Row two of the Customers table in the Northwind database

Northwind.Customers[2].FullName = "John Q. Doe";

 

rather than:

myDataSet.Tables["Customers"].Rows[2]["FullName"] = "John Q. Doe";

or something that would be even harder to figure out later:

myDataSet.Tables[3].Rows[2][4] = "John Q. Doe";

 

Which is simpler to read? Also strongly typed DataSets are significantly faster than non-strongly typed. Once you understand strongly typed datasets you might see a much simpler way in your future endevors to do things.

[kejpa]

Thanx!

So it all ends up in me not having a proper OleDbCommandBuilder object to go with my dataset.

 

This might be fun to doodle with after all. A wild idea of mine is to pass a modified table to a general sub which will make the updates

Public Sub Update(ByVal Table As DataTable)
Dim daTemp As OleDbDataAdapter = New OleDbDataAdapter("Select * from " & Table.TableName, olocDBConn)
Dim cmdCB As OleDbCommandBuilder = New OleDbCommandBuilder(daTemp)

daTemp.Update(Table)

End Sub

 

Is this a good or bad idea?

 

Very much politer

/Kejpa

[bri189a]

It depends on your style. I myself avoid CommandBuilder like the plague, I have a code generator that makes all my commands for me that I can tack on additional 'specialized' commands for later if needed. CommandBuilder is good for very basic single table type of things, but it's too close to a wizard for my taste and I tend to make databases that require more than a single table update/insert/delete statements. Some people swear by them. Like I said it all depends on your coding style, if it were me though, I'd stay away.

[kejpa]

It depends on your style. I myself avoid CommandBuilder like the plague' date=' I have a code generator that makes all my commands for me that I can tack on additional 'specialized' commands for later if needed. CommandBuilder is good for very basic single table type of things, but it's too close to a wizard for my taste and I tend to make databases that require more than a single table update/insert/delete statements. Some people swear by them. Like I said it all depends on your coding style, if it were me though, I'd stay away.[/quote']

Hi Bri,

I totally understand you. I'm just the same in most cases, there's few things as wonderful as a complex unionized subselect, well maybe my 3-year old son ;)

But in this complex app of mine I at least have a simple database where I most of the times just update a single table, or to be correct, most of the time just reads the table. Very few update's and almost no inserts.

 

So maybe it's not just the coding style, but the app as well.

 

Regards

/Kejpa

[Nerseus]

Also note that the CommandBuilder will not always build valid SQL, especially if you have any columns that are reserved words or if the table name is a reserved word.

 

-ner