How to correctly propogate data back to the database

[Phil]

I have the following code but do not know the best way to return the updated
DataTable back to the database. I believe I can use the Update method of the
Data Adapter, BUT if true, I also believe I have to 'long-hand' write code
for each individual column data that's being added......this seems a bit
daft considering that the data is already in the disconnected data table.
Have I lost the plot?? Based on the code below, what is the correct
approach?

Note: sqlcnn is defined at module level.

Public Sub AddRequest(ByVal Eng As String, ByVal Bran As String, ByVal Req
As String) Implements IHelpSC.AddRequest

Dim dtNew As New DataTable("dtNew")
Dim drNew As DataRow
sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
sqlda.Fill(dtNew)

'Add and populate the new datarow with
'data passed into this subroutine

drNew = dtNew.NewRow
drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
drNew("Engineer") = Eng
drNew("Branch") = Bran
drNew("Request") = Req
dtNew.Rows.Add(drNew)

End Sub

Hope one of you wizards can help.

Rgds.....and Merry Christmas.

Phil

[Erland Sommarskog]

Phil (Ph**@nospam.com) writes:

I have the following code but do not know the best way to return the
updated DataTable back to the database. I believe I can use the Update
method of the Data Adapter, BUT if true, I also believe I have to
'long-hand' write code for each individual column data that's being
added......this seems a bit daft considering that the data is already in
the disconnected data table.
Yes and no.

First, if memory serves, you don't have to write any extra code, if
you use the default .Update method on the data adapter, but it will
include all columns. But you are better of asking about that in group
like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
Or, instead of asking, just conduct an experiment.

Then comes the next question, whether you actually want it. For a
application of any size, it is usually best to perform all access to
SQL Server through stored procedure. In this way, users does not have
to have direct access to the tables, but only access to the stored
procedures. This makes a big difference for the security of the database.
In many shops, the DBA will not permit anything but stored procedures
anyway.

And if you use stored procedures, it follows by necessity that if you
add another column to a query, that you will have to add it to the
SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
well. And, yes, that means some extra overhead when you add a new
column, but it is not really a big deal.

Finally some notes about your code:
sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
This may be just an example, but permit me to point out that "SELECT *"
should never occur in production code. It may look convenient, but it
isn't. It gives you an extra overhead of retrieving columns, you don't
nead. And it makes it very difficult to find out if a column is actually
used or not, in case you are looking into to drop a column.
drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString

I don't really know what happens in the end, but you should probably
pass dates as dates. If you format dates and passes them as strings to
SQL Server, they may not be understood by SQL Server, if there are
some unexpected dateformat and langauge settings.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Phil]

Hi Erland,

It sounds like you know what you are talking about! :-)......and you raise
some excellent points. I am extremely new to db programming and just finding
my feet. The comments you make about using 'SELECT *' and stored procedures
I have read in my 'programming databases using .NET ' book. THANKS for the
confirmation. I will eventually change to using stored procedures but I am
trying to pick up the basics regarding 'connected classes' and 'disconnected
classes', Data Adapters, Data Tables, Datasets etc etc etc.

I will take a look at the suggested newsgroups, thanks for your response.

All the best,

Phil

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Phil (Ph**@nospam.com) writes:

I have the following code but do not know the best way to return the
updated DataTable back to the database. I believe I can use the Update
method of the Data Adapter, BUT if true, I also believe I have to
'long-hand' write code for each individual column data that's being
added......this seems a bit daft considering that the data is already in
the disconnected data table.

Yes and no.

First, if memory serves, you don't have to write any extra code, if
you use the default .Update method on the data adapter, but it will
include all columns. But you are better of asking about that in group
like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
Or, instead of asking, just conduct an experiment.

Then comes the next question, whether you actually want it. For a
application of any size, it is usually best to perform all access to
SQL Server through stored procedure. In this way, users does not have
to have direct access to the tables, but only access to the stored
procedures. This makes a big difference for the security of the database.
In many shops, the DBA will not permit anything but stored procedures
anyway.

And if you use stored procedures, it follows by necessity that if you
add another column to a query, that you will have to add it to the
SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
well. And, yes, that means some extra overhead when you add a new
column, but it is not really a big deal.

Finally some notes about your code:

sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)

This may be just an example, but permit me to point out that "SELECT *"
should never occur in production code. It may look convenient, but it
isn't. It gives you an extra overhead of retrieving columns, you don't
nead. And it makes it very difficult to find out if a column is actually
used or not, in case you are looking into to drop a column.

drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString

I don't really know what happens in the end, but you should probably
pass dates as dates. If you format dates and passes them as strings to
SQL Server, they may not be understood by SQL Server, if there are
some unexpected dateformat and langauge settings.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp