|
If the 'c' column is only a calculation of a+b, why not just use ([a] + [b]) as a formula?
Anyway, you could also use INSTEAD OF triggers:
CREATE TRIGGER InsteadUpdateTrigger on tblTable
INSTEAD OF UPDATE
AS
BEGIN
UPDATE tblTable
SET a=i.a,b=i.b,c=i.a+i.b
FROM INSERTED i
WHERE tblTable.IDField = i.IDField
END
CREATE TRIGGER InsteadInsertTrigger on tblTable
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO tblTable
SELECT a,b,a+b
FROM INSERTED
END
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Is there a reason for not using a VIEW on this table? The table would have columns a and b. The view would have columns a, b, and c where c is defined as (a+b).
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Or, just give the c field a formula of ([a] + [b]):
CREATE TABLE [tblTable] (
[IDField] [int] IDENTITY (1, 1) NOT NULL ,
[a] [int] NULL ,
[b] [int] NULL ,
<code>[c] AS ([a] + [b]) </code>,
CONSTRAINT [PK_tblIDField] PRIMARY KEY CLUSTERED
(
[IDField]
) ON [PRIMARY]
) ON [PRIMARY]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Here i am developing a application which require to fetch Time from database . But i am getting 00:00:00 AM as a time everytime. We are using MySql database.
Code fragment is....
========================================================
_variant_t vtValue;
vtValue = RsSession->Fields->GetItem("StartTime")->GetValue();
COleDateTime d( vtValue );
sprintf( name, "%02d:%02d:%02d",d.GetHour(),d.GetMinute(),d.GetSecond() );
========================================================
in "name" i am getting "00:00:00" ..everytime.. All the other values i am retrieving from ResultSetPtr are OK.
Can any one tell me what is to be done ther ....
Thanks in advance
Prafulla Vedante
e-mail : prafulla_v@asianitg.com
|
|
|
|
|
What is the data type of the field "StartTime"?
|
|
|
|
|
Data type of the field "StartTime" is "time"
Prafulla Vedante
|
|
|
|
|
I have been programming an application with VC++ 2005 and SQL Server 2005. I have converted an old 16-bit database for 32-bit managed code and SQL server and the application seems to be good. Now I want to deploy the application to another server for testing.
I have installed XP SP2, Windows Installer 3.1, Net framework 2.0 and SQL Server 2005 express to the test server. I have transferred the application with WI 3.1 and the program works well in the test server till the first SQL command. I have made a back up of the database and restored in the test server. In the test server I can log in the database with Server Management studio and I can read the data there correctly. I have enabled both named pipes and TCP/IP for the database in the test server. With Surface Area Configuration I have enabled Local and Remote Connections Using both TCP/IP and named pipes. I only need Windows authentication at this time.
After all this when I come to the first SQL command in the application on the test server I receive the error message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server).
My connection string to the database is:
'connection->ConnectionString = "Persist Security Info=False; Integrated Security =SSPI;"
"Data Source=TESTSERVER; Initial Catalog=TESTDATABASE;";'
When I use "Data Source=DevelopmentServer", the application works well on the development server.
Can't understand what is still wrong. Can anyone possibly have an answer?
|
|
|
|
|
Finally found the solution as there was an error in the command string. In SQLExpress server the data source must be presented in format SERVER\SQLExpress. When this is part of the above C++ command string it must have the format
"Data Source=TESTSERVER\\SQLExpress; Initial Catalog=TESTDATABASE;";
although the backslash is within quotation marks.
|
|
|
|
|
If suppose we have to add a row in a table, using insert and there is a primary key(PKID) which is auto incremented.
After executing the SP how will we know that what being the value of that key, i.e. to retrieve it from the SP itself,
i had heard that there is a particular command for that
Can someone help me plz................
|
|
|
|
|
If you want to get the PKID(auto incremented) then you can use @@IDENTITY for that. This will give you the PKID of just inserted recored.
Best Regards,
Apurva Kaushal
|
|
|
|
|
I didn't get your reply and what I soppose that you fine mine question unclear
So let me clarify it
I am asking that Suppose there is an SP that store some information Inserted by user and it generate automatically an ID (identity element) for that particular information.
Now after calling this SP in some of the function I want the PKID that is assigned to recent information, How can I retrive this PKID ??
|
|
|
|
|
Do a select statement using this:
SELECT IDENT_CURRENT('table_name')
This will return the last inserted PK in the table
or
SELECT @@IDENTITY
Try sql help on these two functions and see how they work exacly
|
|
|
|
|
Oh fine and Thanku vary much Mr. Devil and Mr. Apurva
|
|
|
|
|
Hello every one!
Well I have a question here about the self joined table. Well I know how it works theoretically but I’m working on it for the first time in SQL Server 2000. I’m trying to build a data entry form for it and now I’m a bit confused that how do we manage the Foreign Key of that table. I don’t know if I’m putting this question in the rt manner. How do we make the forms over a self joined table. Plz answer that having the auto-increment PK in mind. I’m working in .net 2.0 C# Windows Forms.
Thanks in advance…
Rocky
|
|
|
|
|
I'm not an expert in this but since you've had no other replies, here is my opinion...
Just create your form as if the underlying table didn't have a self join.
It's purpose is to relate two fields in the same table and is really only to make data retrieval (queries, SP's etc) easier and more efficient.
Steve
|
|
|
|
|
hello every one,
well I'm working on my first professional DB project, and this database has got inventory managmnt, billing, ledgers, and employee mgt modules. So for now I've made the main page very simple. Its got a tab control (I'm working in .net 2.0 C#) with two tabs 1 for forms and the other for reports with each tab having the links to the forms. But it doesnt look very elegant.
right now I'm just short of ideas of how to make the mian page and what sort of format I should give to the forms. can u plz suggest a few good looking and easily navigatable designs. or is there any website that gives u a few samples of good looking UIs
thanks in advance...
Rocky
|
|
|
|
|
Hi
Is it good practice to return multiple result set from single stored procedure when all result set will be used to populate a single page.
e.g.
CREATE PROCEDURE WHYNOT
AS
BEGIN
SELECT col1,col2 from table1
SELECT col3,col4 from table2
SELECT col5,col6 from table3
PS: 1) These are simple statements, but i will have joins on each query....
Please advice and guide.
Amit
|
|
|
|
|
It isn't necessarily a bad idea however depending on the amount of data returned it could be. Making fewer trips to the database can be a benefit but if the results returned are very large it may be better to break up the requests.
only two letters away from being an asset
|
|
|
|
|
Hi
I have three tables like parent and child and I don’t know in child table how many records can be. E.g.
[TB_ProductMaster]
[ProductId] [varchar] (4) NOT NULL ,
[ProductTitle] [varchar] NOT NULL ,
Many other fields
Data Looks like
A1 TV
A2 VCR
A3 HT
[TB_InstructionMaster]
[InstructionId] [int] IDENTITY (1, 1) NOT NULL ,
[InstructionDesc] [varchar] (50) NOT NULL
Data Looks like
1 “Always Top”
2 “Always Down”
3 “Always Left”
4 “Always Right”
[TB_ProductInstruction]
[Id] [int] NOT NULL ,
[ProductId] [varchar] (4) NOT NULL And FOREIGN KEY FOR PRODUCTID
[InstructionId] [int] NOT NULL
Data Looks like
1 A1 1
2 A1 2
3 A1 3
4 A1 4
5 A2 1
6 A2 2
(Please note here i don;t know the child count. e.g product can have one Instruction or can have n numbers of Instructions.)
Now i need output like for supplied product parameter as. This paramter will be input to SP.
Product Id InstructionDesc1 InstructionDesc2 and so on
A1 Always Top Always Down
A2 Always Top Always Down
A3 Null NULL
A4 NULL NULL
Here so on means , n number of columns can be....
I know by using LEFT OUTER join i can do the needful but only if I know the count in my child table. But in my case we don't know Moreover because of our company database standards we can not use CURSORS also....
We need to do this by using query or in worest case dynamic SQL...
PLEASE HELP ME……….PLEASE HELP ME……….PLEASE HELP ME……….
Amit
|
|
|
|
|
Too bad you can't use a cursor:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cInstruction VARCHAR(50), @nInstruction int
SET @sql = ''
DECLARE curInstructions CURSOR FOR SELECT DISTINCT InstructionDesc,InstructionID FROM TB_InstructionMaster
OPEN curInstructions
FETCH NEXT FROM curInstructions INTO @cInstruction,@nInstruction
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT ''' + @cInstruction + ''' as fld
FROM TB_ProductInstruction P2
WHERE P2.ProductID = ProdInst.ProductID and InstructionID = ''' +
cast(@nInstruction as varchar) + ''') as [' + @cInstruction + ']'
FETCH NEXT FROM curInstructions INTO @cInstruction,@nInstruction
END
close curInstructions
DEALLOCATE curInstructions
SET @sql = 'SELECT distinct ProdMast.ProductID ' + @sql + '
FROM TB_ProductInstruction ProdInst
right join tb_productmaster ProdMast on ProdInst.ProductID = ProdMast.ProductID'
exec(@sql)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi all,
Here's the problem...
I've got two tables, trains and trainarrivals. Each train has a link to two arrivals records. The first link is to the previous arrival record and the d=second is to the next arrivals record.
I'm trying to join the tables using,
select * from train LEFT OUTER JOIN trainarrivals<br />
ON train.PreviousArrival = trainarrivals.UniqueID<br />
OR train.NextArrival = trainarrivals.UniqueID<br />
ORDER BY train.UniqueID
so that when i get the trains with their arrivals links I will get one row for each train. Due to the fact that the next and previous arrivals are separate records in the trainarrivals table I get two records for each train.
My question is, is there any way to do this so I get only one record with both the next and previous arrival data together.
Thanks for the help in advance.
Rich
|
|
|
|
|
select * from train
LEFT OUTER JOIN trainarrivals ta1 ON train.PreviousArrival = ta1.UniqueID
LEFT OUTER JOIN trainarrivals ta2 ON train.NextArrival = ta2.UniqueID
ORDER BY train.UniqueID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks Eric!
It worked a treat. I really didn't know it would be that simple.
Thanks again!
|
|
|
|
|
what do we mene by daynamic sql in sql server 2000
and how can iuse them
|
|
|
|
|
Dynamic SQL are those SQL which gets the value at runtime. I am sharing one simple example and i hope this will help you.
Declare
@table Varchar(25),
@Sql Varchar(25)
BEGIN
SELECT @table='TB_Emp'
Select @Sql= 'SELECT Count(*) FROM' + @table
exec (@sql)
END
For more details refer google or SQL Online Help.
Amit
|
|
|
|