473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

vb6's recordset replacement in vb.net?

In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.

I use disconnected recordset, and call updatebatch to send updates to
database.

In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.

If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.

If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.

Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.

Is there any other way? Or should I just bite the bullet and build my
own command generator?

--
Endy
Jun 27 '08 #1
11 5146
endy_tj wrote:
>
Is there any other way? Or should I just bite the bullet and build my
own command generator?
I don't use a data adapter or a table adapter. I have 3 methods, one for insert,
update, and delete, that will take any table, and create and run the appropriate
commands. It takes a bit of work to set it up; once you have it, though, that
task is done for good.
Jun 27 '08 #2
The way VS.Net works (VB.Net/C# for this explanation) is that is sort of
creates an in memory database with the objects selected from the server
DB. If you have related tables on the server DB and want to view data
from these tables you would use a select statement as follows in a
dataAdapter:

'--these vars are global
dim da As SqlDataAdapter, ds As Dataset, conn as SqlConnection
---------------------------------------------
Private Sub Form1_Load(...) Handles....
da = New SqlDataAdapter
ds = New Dataset
da.SelectCommand = new SqlCommand
da.SelectComman.Connection = conn
da.InsertCommand = New sqlCommand
da.InsertCommand.Connection = conn
da.InsertCommand.Parameters.Add("@p1",...)
da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@p1",...)
da.SelectCommand.CommandText = "Select * From tbl1"
da.Fill(ds, "tblA")
datagridview1.DataSource = ds.Tables("tblA")
...
End Sub

Private Sub datagridview1CellValue_Changed(...) Handles...
ds.Tables("tblA").Rows(e.RowIndex)(e.ColumnIndex) =
datagridview1.Rows(e.RowIndex).Cells(e.columnIndex ).Value

da.Update(ds,"tblA")
End Sub
-----------------------------------------------------

you have to write a specific select statement for the server table that
you want to update or add rows to and the insert/Update statements have
to meet the constraint conditions place on the tables on the server.
Then - to view joined tables just write a simple

"Select t1.* from tbl1 t1 Join tbl2 t2 on t1.ID = t2.ID"

da.Fill(ds, "tblB")

Once you get the hang of ADO.Net you will see that it blows the doors
off classic ADO.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #3
I did not know that you could update joined datatables with the recordset, I
thought that it was only doing tables.

However if you want to update a datatable using a SQLDataAdapter then the
only thing you need is to do before that update somewhere

dim cmb = new SqlCommandBuilder(TheDataAdapter)

Cor

"endy_tj" <en*****@lycos.comschreef in bericht
news:62**********************************@i36g2000 prf.googlegroups.com...
In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.

I use disconnected recordset, and call updatebatch to send updates to
database.

In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.

If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.

If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.

Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.

Is there any other way? Or should I just bite the bullet and build my
own command generator?

--
Endy
Jun 27 '08 #4
On Jun 16, 10:00*pm, "Steve Gerrard" <mynameh...@comcast.netwrote:
endy_tj wrote:
Is there any other way? Or should I just bite the bullet and build my
own command generator?

I don't use a data adapter or a table adapter. I have 3 methods, one for insert,
update, and delete, that will take any table, and create and run the appropriate
commands. It takes a bit of work to set it up; once you have it, though, that
task is done for good.
OK, no other way around it :P

These methods of yours, you feed them only connection and datatable or
some more parameters? Does the datatable contain enough metadata to
construct the insert, update, delete command? Would be very glad if
you can give me some hints.
Jun 27 '08 #5
On Jun 16, 11:25*pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
I did not know that you could update joined datatables with the recordset,I
thought that it was only doing tables.
You can, if you put the table name in the "unique table" property of
the recordset, and your select query contains the primary key of the
table.
However if you want to update a datatable using a SQLDataAdapter then the
only thing you need is to do before that update somewhere

dim cmb = new SqlCommandBuilder(TheDataAdapter)
OK need to try the command builder. Many articles say it is slow and
can only handle simple select statements. If it can't handle simple
subquery like this:

select O.id, O.orderdate,
(select name from customers where id = O.customer_id) as customer_name
from orders O

then it will be as limited as strongly typed datatables, and provides
little help for me.
"endy_tj" <endy...@lycos.comschreef in berichtnews:62**********************************@i 36g2000prf.googlegroups.com...
In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.
I use disconnected recordset, and call updatebatch to send updates to
database.
In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.
If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.
If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.
Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.
Is there any other way? Or should I just bite the bullet and build my
own command generator?
--
Endy
Jun 27 '08 #6
It has the same limites as a strongly typed dataset.

You cannot update joined tables using any adapter, a resultset can exist
from any combinations then of database table even without needed
identifiers.

Cor

"endy_tj" <en*****@lycos.comschreef in bericht
news:a0**********************************@d19g2000 prm.googlegroups.com...
On Jun 16, 11:25 pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
I did not know that you could update joined datatables with the recordset,
I
thought that it was only doing tables.
You can, if you put the table name in the "unique table" property of
the recordset, and your select query contains the primary key of the
table.
However if you want to update a datatable using a SQLDataAdapter then the
only thing you need is to do before that update somewhere

dim cmb = new SqlCommandBuilder(TheDataAdapter)
OK need to try the command builder. Many articles say it is slow and
can only handle simple select statements. If it can't handle simple
subquery like this:

select O.id, O.orderdate,
(select name from customers where id = O.customer_id) as customer_name
from orders O

then it will be as limited as strongly typed datatables, and provides
little help for me.
"endy_tj" <endy...@lycos.comschreef in
berichtnews:62**********************************@i 36g2000prf.googlegroups.com...
In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.
I use disconnected recordset, and call updatebatch to send updates to
database.
In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.
If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.
If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.
Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.
Is there any other way? Or should I just bite the bullet and build my
own command generator?
--
Endy
Jun 27 '08 #7
ADO.Net is quite a bit more sophisticated than classic ADO (does a ton
more stuff). Instead of writing a book on ADO.Net in this post I would
just recommend getting a book on ADO.Net.

Note: Even though you could use the CommandBuilder object to build your
commands - these end up being fairly generic and difficult to
manipulate. With all due respect to people who use the commandbuilder -
most people (myself included) will steer away from it. It is better to
write your own commands.

The big deal with ADO.Net is that the dataAdapter will automatically
create a dataTable for you when you run this statement

da.Fill(ds, "tblwhatever")

da.SelectCommand.CommanText = "Select t1.fld1, t1.fld2, t2.fldx,
t2.fldy, t2.fldz from tbl1 t1 Join tbl2 t2 On t1.ID = t2.ID and t1.Name
= t2.Name Where Not Exists (Select something from tbl3 t3 Where t3.ID =
t1.ID)"

da.Fill(ds, "tblSteve")

so "tblSteve" represents the joins above in memory, and you display the
data in a datagridview (the equivalent of a subform in VB6 or Access -
but 1000+ times more powerful and versatile). Note: If you edit
"tblSteve" you would have to write the appropriate Update statements
behind the datagridview (which has hundreds of events - pick the proper
event) and run da.Update(ds, "tblSteve").

I could go on, but it would be easier to get a book on ADO.Net.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #8
On Jun 17, 12:41*am, Rich P <rpng...@aol.comwrote:
ADO.Net is quite a bit more sophisticated than classic ADO (does a ton
more stuff). *Instead of writing a book on ADO.Net in this post I would
just recommend getting a book on ADO.Net. *
Any recommendations for a book that covers the things I intend to do?
Jun 27 '08 #9
Rich
Note: Even though you could use the CommandBuilder object to build your
commands - these end up being fairly generic and difficult to
manipulate. With all due respect to people who use the commandbuilder -
most people (myself included) will steer away from it. It is better to
write your own commands.
In my idea has even William Vaughn changed his opinion of this.

Although I realize me that I seldom use it anymore, is it a fine tool as it
is about one table which has less then 100 columns and is selected by
"Select * from Table".

(But in those situations is Linq to SQL much easier)

Cor
Jun 27 '08 #10
endy_tj wrote:
>
Thanks to Steve, Rich, and Cor, I have created a function to save
datatable:
(snip)
>
With this class, I just have to pass the transaction object, the
datatable, and the name of the table that I want to update, and it
will save the changes. I can do joins, I can add subqueries in the
select clause, and it will ignore the extra columns, as long as I
differentiate the column names, and all the real columns are intact.
Nice! Now a couple of comments. :)

- - - - -
First, I think I would explicitly close the new connection created to build the
adapter.
conn = New
Odbc.OdbcConnection(Trans.Connection.ConnectionStr ing)
You are changing the connection afterwards to use Trans.Connection, so conn
probably closes on its own when it goes out of scope, but adding conn.Close or
conn.Dispose might be a good idea.

- - - -
Second, the requirement "all columns of the target table must be present" might
be a nuisance in the future. I have a table that stores uploaded BLOBs, for
instance, with a separate title. It is useful to be able to list the titles for
a user, and allow changing the title, without retrieving all the BLOBs. The
BLOBs themselves are only retrieved one at a time, on demand.

You could get around this by first retrieving all the column names from the
table, by adding a few lines:
lsSQL = "select * from " & CleanTableName & " where 1 = 2"
adp = New Odbc.OdbcDataAdapter(lsSQL, conn)
Dim dt As New DataTable
adp.Fill(dt)

Using dt.Columns, you could now loop through the column names, to see which ones
are included in the passed in Table. Any that are go into a string of the form
"col1, col2" etc. Then continue as before:

lsSQL = "select " & strColumns & " from " & CleanTableName & "
where 1 = 2"
adp = New Odbc.OdbcDataAdapter(lsSQL, conn)
' and build the commands on that...

- - - - -
The nice thing about have a class like this is that you can continue to develop
it at your leisure, and you have a centralized way of handling updates
throughout the programs that use it.
Jun 29 '08 #11
On Jun 30, 12:39*am, "Steve Gerrard" <mynameh...@comcast.netwrote:
endy_tj wrote:
Thanks to Steve, Rich, and Cor, I have created a function to save
datatable:

(snip)
With this class, I just have to pass the transaction object, the
datatable, and the name of the table that I want to update, and it
will save the changes. I can do joins, I can add subqueries in the
select clause, and it will ignore the extra columns, as long as I
differentiate the column names, and all the real columns are intact.

Nice! Now a couple of comments. :)

- - - - -
First, I think I would explicitly close the new connection created to build the
adapter.
Yes, forgot that one, and also I have added these lines after
updating:

adp.DeleteCommand.Connection = nothing
...

so the adapter's commands release the connection, so it can be garbage
collected.
Second, the requirement "all columns of the target table must be present" might
be a nuisance in the future. I have a table that stores uploaded BLOBs, for
instance, with a separate title. It is useful to be able to list the titles for
a user, and allow changing the title, without retrieving all the BLOBs. The
BLOBs themselves are only retrieved one at a time, on demand.
Yeah, good point. I forgot about blobs. Thanks!
Jun 30 '08 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Brian Parker | last post by:
Hello all, I have been trying to find some information for reading XML files into VB6 but I'm somewhat confused by what is possible without getting onto VB.Net. Basically I have to read some...
0
by: Dot Netizen | last post by:
I am having trouble passing an ADODB.Recordset from a VB6 application to a VB.Net class library using COM Interop. I am running this on XP SP2 with the .Net Framework 1.1 and MDAC 2.8 SP1. I've...
0
by: adam | last post by:
I am getting an error when trying to use an ADODB.Recordset that was passed from a VB6 application to a VB.Net class library using COM Interop. I am running this on WinXP SP2 using the .Net...
4
by: Terry Diederich | last post by:
I have a client with a VB6 application that uses the OLE Control to display PowerPoint slides. The PowerPoint slides are saved in a column in the database (Oracle). The problem is that the OLE...
3
by: Lumpierbritches | last post by:
I have an application my partner wrote that would allow an autoresponse to any Mapi compliant email that apparently in .Net won't, can someone assist me with fixing this? Here is the code: ...
1
by: Tobester | last post by:
Hi I am having problems calling a method in a vb6 object that returns a recordset. Everything was working fine until I added the interop dlls for the vb6 objects into the GAC. Before this, I...
1
by: OutlookCoder | last post by:
Hi All, I have a simple question. Howto consume a .NET webservice form VB6. I have tried very many methods. I currently use MSXML. Here is the piece of code that I use. This works flawlessly....
2
by: slinky | last post by:
I have an unbound form for adding assets, everything works OK till the line: ..AddNew where I get a 424 / Object required Runtime Error.... anyone have any clues?... thanks!!! Option Compare...
0
by: desmondgreen | last post by:
We have a problem with transaction management/concurrency when using ADO to update a database on a SQL 2005 database. We have a test application, to isolate and demonstrate the problem, where a VB6...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.