Orders
ProductPrice ProductQuantity TotalCost
-- --
￡2.50 2
￡1.75 3
￡12.99 2
Can anyone please help me?You don't need a trigger, you need a computed column:
CREATE TABLE [dbo].[xxx](
[col_a] [int] NOT NULL default(2),
[col_b] [int] NOT NULL default(2),
[col_axb] as a * b,
) ON [PRIMARY]
GO|||Thank you for the help!
I have a table with the following columns:
ID INTEGEDR,
Name VARCHAR(32),
Surname VARCHAR(32),
GroupID INTEGER,
SubGroupOneID INTEGER,
SubGroupTwoID INTEGER
How can I create a rule/default/check which update SubGroupOneID &
SubGroupTwoID columns when GroupID for example is equal 15 on
MSSQL2000.
It is imposible to make changes on client, so I need to check
inserted/updated value of GroupID column and automaticly update
SubGroupOneID & SubGroupTwoID columns.
Sincerely,
Rustam BogubaevYou haven't explained what value(s) you want the subgroup columns updated
to.
If the two subgroup columns are solely determined by the the Groupid then
the answer is not to put those columns in the table at all because to do so
destroys normalisation in your schema. Put the subgroups in a separate,
related Groups table.
Rules and Check constraints don't actually change data - they just validate
it. Use a trigger update dependent columns when data is inserted or updated.
See CREATE TRIGGER in Books Online for details.
--
David Portas
----
Please reply only to the newsgroup
--|||You can do this in a trigger, assuming the primary key value is never
changed. For example:
CREATE TABLE MyTable
(
ID int
CONSTRAINT PK_MyTable PRIMARY KEY,
Name varchar(32),
Surname varchar(32),
GroupID int,
SubGroupOneID int,
SubGroupTwoID int
)
GO
CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE t
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM MyTable t
JOIN inserted i ON t.ID = i.ID
WHERE i.GroupID = 15
GO
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
'surname', 1)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
'surname', 2)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
'surname', 15)
SELECT * FROM MyTable
UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
SELECT * FROM MyTable
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
news:20046852.0401202332.386aa33c@.posting.google.c om...
> Hi,
> I have a table with the following columns:
> ID INTEGEDR,
> Name VARCHAR(32),
> Surname VARCHAR(32),
> GroupID INTEGER,
> SubGroupOneID INTEGER,
> SubGroupTwoID INTEGER
> How can I create a rule/default/check which update SubGroupOneID &
> SubGroupTwoID columns when GroupID for example is equal 15 on
> MSSQL2000.
> It is imposible to make changes on client, so I need to check
> inserted/updated value of GroupID column and automaticly update
> SubGroupOneID & SubGroupTwoID columns.
> Sincerely,
> Rustam Bogubaev|||Hi dan
This problem was one I am grappling with, and despite its apparent
simplicity, is not touched on in any simple way in SQL books online, or
other 3rd party books, or rarely in Deja archives.
Could I impose on you a little more to elaborate on a couple of points in
this eample please ?
> For example:
> CREATE TABLE MyTable
> (
> ID int
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Name varchar(32),
> Surname varchar(32),
> GroupID int,
> SubGroupOneID int,
> SubGroupTwoID int
> )
> GO
Yup, I can handle that, lets make a table
> CREATE TRIGGER TR_MyTable
> ON MyTable FOR INSERT, UPDATE
> AS
> UPDATE t
> SET
> SubGroupOneID = 1,
> SubGroupTwoID = 1
> FROM MyTable t
> JOIN inserted i ON t.ID = i.ID
> WHERE i.GroupID = 15
> GO
Making a trigger i can handle, but ..
Bits that puzzle me
UPDATE t, can you explain the reason for and the use of 't' ?
Is that a temporary table where data is stored in the process?
the MyTable t bit, assuming t is a table, should that be MyTable, t -
joining two different tables ?
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> 'surname', 1)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> 'surname', 2)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> 'surname', 15)
> SELECT * FROM MyTable
> UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> SELECT * FROM MyTable
> GO
I presume this next bit is an alternative method, where we insert 3 records,
and then do a bulk update to make
the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand the
"GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
online, as it rarely gives me any usefull results from my inquiries :-)
The other bit that puzzles me is why the "SELECT * FROM MyTable" is needed.
Does the UPDATE row not process all records automatically when the GO is
encountered?
Many thanks for any help you can provide.
> "Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
> news:20046852.0401202332.386aa33c@.posting.google.c om...
> > Hi,
> > I have a table with the following columns:
> > ID INTEGEDR,
> > Name VARCHAR(32),
> > Surname VARCHAR(32),
> > GroupID INTEGER,
> > SubGroupOneID INTEGER,
> > SubGroupTwoID INTEGER
> > How can I create a rule/default/check which update SubGroupOneID &
> > SubGroupTwoID columns when GroupID for example is equal 15 on
> > MSSQL2000.
> > It is imposible to make changes on client, so I need to check
> > inserted/updated value of GroupID column and automaticly update
> > SubGroupOneID & SubGroupTwoID columns.
> > Sincerely,
> > Rustam Bogubaev|||> > CREATE TRIGGER TR_MyTable
> > ON MyTable FOR INSERT, UPDATE
> > AS
> > UPDATE t
> > SET
> > SubGroupOneID = 1,
> > SubGroupTwoID = 1
> > FROM MyTable t
> > JOIN inserted i ON t.ID = i.ID
> > WHERE i.GroupID = 15
> > GO
> Making a trigger i can handle, but ..
> Bits that puzzle me
> UPDATE t, can you explain the reason for and the use of 't' ?
> Is that a temporary table where data is stored in the process?
> the MyTable t bit, assuming t is a table, should that be MyTable, t -
> joining two different tables ?
The 't' is simply an alias declared for MyTable so that I didn't need to
specify the full table name when qualifying column names. The following is
functionally identical. Both examples join MyTable with the inserted table
in order to identify newly inserted or updated rows.
CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE MyTable
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM inserted
WHERE MyTable.ID = inserted.ID AND
inserted.GroupID = 15
GO
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> > 'surname', 1)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> > 'surname', 2)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> > 'surname', 15)
> > SELECT * FROM MyTable
> > UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> > SELECT * FROM MyTable
> > GO
> I presume this next bit is an alternative method, where we insert 3
records,
> and then do a bulk update to make
> the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand
the
> "GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
> online, as it rarely gives me any usefull results from my inquiries :-)
'GroupID IN(1, 2)' is equivalent to 'GroupID = 1 OR GroupID = 2'. You can
find details of 'IN' in the SQL 2000 Books Online. I was able to find the
BOL topic by clicking the index tab, typing 'IN' and double-clicking on the
'IN' keyword in the list. I then selected the 'IN' title from the topic
list.
> The other bit that puzzles me is why the "SELECT * FROM MyTable" is
needed.
The SELECT statements before and after the UPDATE are to display the data
before and after the UPDATE. These are only for illustration.
> Does the UPDATE row not process all records automatically when the GO is
> encountered?
GO is a batch separator. Tools like Query Analyzer execute the preceding
batch of SQL statements when a GO is encountered in the script. The insert,
update and select statements are executed sequentially as part of the same
batch.
--
Hope this helps.
Dan Guzman
SQL Server MVPsql
I am trying to create Replication Topology (Merge Replication) like below.
Subscriber1 --> Publisher <-- Subscriber2.
I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.
Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.
Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.
I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.
Can anyone help ?
Thanks in advance
Merge replication tracks changes via a rowguid, not a timestamp. it has no idea what time a change was made. If you want to decide who wins in a conflict scenario based on a column value in your table, then you should create a custom conflict resolver for your article. see sp_addmergearticle in books online for more information.|||Greg,Thanks for the reply. After going through MDSN I found one solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.
Thanks,
Thams
I am trying to create Replication Topology (Merge Replication) like below.
Subscriber1 --> Publisher <-- Subscriber2.
I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.
Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.
Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.
I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.
Can anyone help ?
Thanks in advance
Merge replication tracks changes via a rowguid, not a timestamp. it has no idea what time a change was made. If you want to decide who wins in a conflict scenario based on a column value in your table, then you should create a custom conflict resolver for your article. see sp_addmergearticle in books online for more information.|||Greg,Thanks for the reply. After going through MDSN I found one solution. We can use Microsoft Provided Custom Resolver for this. DateTime Later Wins resolver. I think using this my issue will be resolved. But i didn't try this resolver yet.
Thanks,
Thams
Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete.
I right click on the adapter bar and select Add Query.
I sleect 'Use SQL Statements'
I select 'Update' (or 'Delete')
I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'
I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious.
Diane
Have you made sure that you have set a primary key for your table.|||The table didn't have a primary key. I added one, but I still don't know how I'm supposed to use update and delete in a recordset.
Diane
Hi Diane,
You can remove and re-add the TableAdapter. Set the SELECT command to select from that table which has primary key.
When you walk to the end of the wizard, it will show you that Update and Delete methods are generated. In this case, you will be able to Update and Delete through the TableAdapter.
|||Update and Delete have been generated, I just don't know how to use them.
Diane
|||Hi Diane,
When you have generated the Update and Delete methods, you can call them directly from an instance of TableADapter.
The Update method has several overloads. Some take a DataRow or a DataSet. They can update rows in batch. There is another overload that takes each field value seperately.
The Delete method takes arguments for all the field values, and it will check then delete the row.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e)
{
sqlDataSource1.Update();
sqlDateSource2.Update();
}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang
You need to actually set the update parameters. Take a look at this link.
http://msdn2.microsoft.com/en-us/library/fkzs2t3h(VS.80).aspx
Sub EmployeeDetailsSqlDataSource_OnInserted(senderAsObject, eAs SqlDataSourceStatusEventArgs)
Dim commandAs System.Data.Common.DbCommand = e.Command
EmployeeDetailsSqlDataSource.SelectParameters("EmpID").DefaultValue = _
command.Parameters("@.EmpID").Value.ToString()
EmployeesGridView.DataBind()
EmployeeFormView.DataBind()
EndSub
|||
I set the parameters' DafaultValue in the Clicked Event of the Update button. And it work fine.
But it is too hard to set so much parameters by codes. Are there any ways to set them automatically as the update link button had done in the FormView?
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
Why do you need a trigger for that? Cant your application/stored proc handle the logic? Triggers are generally a maintenance nightmare (IMHO). You have to use them cautiously if you have to.
|||Thanks for the advice. Oliver
|||I agree with ndinakar, stay away from triggers.
Your best bet is to handle the logic in your app or stored proc. For a stored procedure you could handle it as so
Create Procedure dbo.myUpdateProc( @.fieldIdentityint, @.RequestIDbigint =NULL)If @.RequestIDISNOT NULLBegin Update <table>set RequestID = @.RequestID, Booktime =GetDate()where = @.fieldIdentityEnd
sql
I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:
________________________
__________________ | PortfolioTitle
|
| Portfolio |
+------------+
+----------+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+----------+ | tfolio_weight
(decimal(6,5)) |
+-------------+
Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).
My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)
For example, if the portfolio #2 would contain :
[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30
and I must update the PortfolioTitle based on these values :
idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40
then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio
For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.
Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?
And this applies to many situation :(
If you need other examples, I can give you.
thanks a lot!
ibizaerrr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio
Hi ibiza,
Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):
-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids =
(SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids
-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL
This is just the basic outline - you should enclose it in a transaction
and add proper error handling.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.
If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?
Thanks again! :)
ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:
>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.
Hi ibiza,
Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!
If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?
If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.
(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).
--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.
Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, you are both right, I will remove it then.
I thought it was always a good idea to have an identity primary key on
any table.
Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.
If you ask some people, they will tell you that it is never a good idea!
Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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.
PhilPhil (Phil@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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" <esquel@.sommarskog.se> wrote in message
news:Xns95CB77CF3AB42Yazorman@.127.0.0.1...
> Phil (Phil@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
