|
it succeeded
but the insert value affected all rows i want it to affect 1 row only (new row)
hers is trigger syntax
create trigger InsertInvoice on dbo.Invoice_Summary
for insert
As
begin
insert into Invoice_details.Invoice_num select Invoice_num from Inserted
end
go
example :
insert into invoice_summary (P_name) values ('N1')
results:
the row insert to invoice_summary with Invoice_num 80
problem :
all invoice numbers in the invoice_details(invoice_num) is 80
should 1 row only added with that value
regards
|
|
|
|
|
Never overlook the obvious! Good spot.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks .. but still have problem
|
|
|
|
|
|
It depends on many things like nyumber of records, what you are going to do with them etc etc. The best way to find out is to try it - its a quick job to knock up 2 versions of the proc and test it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Trying to install TFS, and after solving a plethora of annoying issues, I've reached an issue that google provides ineffective answers for.
TFS needs to be able to access the WSS, however, WSS needs to be able to configure the DB, providing it with all the neccessary parameters, it (or SQL Server) decides that the service does not have sufficient priviliges, indeed the quoted message reads:
"CREATE DATABASE permission denied in database 'master'. (Error code: 262)"
Does anyone have a clue how to work this out?
Solved, but still TFS won't install 
"Every time Lotus Notes starts up, somewhere a puppy, a kitten, a lamb, and a baby seal are killed. Lotus Notes is a conspiracy by the forces of Satan to drive us over the brink into madness. The CRC-32 for each file in the installation includes the numbers 666." Gary Wheeler
"You're an idiot." John Simmons, THE Outlaw programmer
"I realised that all of my best anecdotes started with "So there we were, pissed". Pete O'Hanlon
modified on Sunday, June 1, 2008 6:22 PM
|
|
|
|
|
|
Got the email, thanks. I've been buried under with work and studying, my apologies.
My gratitude as well for the email 
Actually, its on 2003 SP1, a VM even.
Its a serious pain. Now, I'm having problems integrating the Sql Server Reporting services with WSS. Its a bugger.
"Every time Lotus Notes starts up, somewhere a puppy, a kitten, a lamb, and a baby seal are killed. Lotus Notes is a conspiracy by the forces of Satan to drive us over the brink into madness. The CRC-32 for each file in the installation includes the numbers 666." Gary Wheeler
"You're an idiot." John Simmons, THE Outlaw programmer
"I realised that all of my best anecdotes started with "So there we were, pissed". Pete O'Hanlon
|
|
|
|
|
I'm not much of one with SQL, and I'm not sure if this is possible, but it seemed to me that it would be better to do it this way if it is than to split it into several queries.
I'm making a page usage site for my ASP.NET page, and I want to store statistics for what browser is being used, which OS, etc. So I have a table with 2 fields: BrowserName and Sessions. I want to make a query that if the BrowserName exists, then it increments Sessions, otherwise it adds the BrowserName and sets Sessions to 1. Switching between Update/Insert in particular has left me clueless.
Thanks in advance!
|
|
|
|
|
Hello, this is a sample stored procedure that does what you need, I assume that you use MS SQL
This is just one way of doing it.
create proc InsertOrUpdateCount(@Browsername varchar)
as
update tablename
Set Sessions=session+1
where BrowserName = @Browsername
if @@rowcount=0 --no updates so the record does not exist
insert into tablename (BrowserName ,Sessions) values (@Browsername,1)
|
|
|
|
|
when i write code <sp_dropmergearticle @publication = 'RepTest'
, @article = 't1'
, @ignore_distributor = 1
, @force_invalidate_snapshot = 0>
i received error
error : <Cannot drop article 't1' from publication 'RepTest' because its snapshot has been run and this publication could have active subscriptions.
>
please help
|
|
|
|
|
Have you tried to remove the snapshot?
|
|
|
|
|
How to remove the snapshot?
|
|
|
|
|
Dear All,
I am implementing a shopping where the prices of the products are structured according to the quantity taken
for examle:
1 = 1 Euro
3 = 0.80Euro
5 = 0.70 Euro
etc etc
The database is structured as follows:
quantity1 pricequantity1 quantity2 pricequantity2 quantity3 pricequantity3 etc
A quantity and pricequantity can be set to 0 if no further quantity groups exist.
I need a query where I pass a quantity and it gets the subtotal.
Can someone help please?
Thanks
Chris
|
|
|
|
|
I guess you can put a case statement in your select.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hello
Last week i have upgrade my OS xp home edition to xp professional becuase i want to start learning ASP.NET and ADO.NET but before i have backup my all projects which i have created with vb.net 2003, vb.net 2005 and few of them was sql server 2005
so after upgrading OS i have saved all those projects to it's default loaction and i have tried to run few of them in vb.net 2003 and vb.net 2005 so it was ok but when i checked projects with sql server then the problem starts and i am still fighting with it , when ever i try to run application it shows me error 26 and even if i try to configure the data adapter then it's same error so now i dont know what to do with this
error is "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by th fact the undre the default settings SQL Server does not allow remote connections.(Provider: Sql Network Interfaces, error : 26 - Error Locating Server / Instance Specified) " 
and i have also try to create new project from start but when i try to add sql data adapter it shows me same error
please help me with this
i have also check this web site
http://support.microsoft.com/kb/914277[^]
any help with this
|
|
|
|
|
Hi
I have created a Trigger Statement and assigned to CommandText property of
sqlCommand.While Try to Execute using
command.ExecuteNonQuery() it throws an exception like
{"Incorrect syntax near the keyword 'TRIGGER'."}
Created Trigger Statement:
CREATE TRIGGER SampleTrigger
ON dbo.sample
AFTER INSERT
AS begin
SET NOCOUNT ON
DECLARE @Name AS nvarchar(50)
SELECT @Name= i.Name from inserted i
DECLARE @Address AS nvarchar(50)
SELECT @Address= i.Address from inserted i
insert into triggertest(statusID,FieldName,FieldValue) values( 25,'Name',@Name)
insert into triggertest(statusID,FieldName,FieldValue) values( 25,'Address',@Address)
end
Can anyone help me?
Thanks in advance
Sri
|
|
|
|
|
Since you don't show the way you build up the string, my guess is that the string sent to the SqlCommand is not exactly the one shown.
For example:
string sql = "CREATE TRIGGER SampleTrigger" +
"ON dbo.sample";
will actually result in a string that reads:
CREATE TRIGGER SampleTriggerON dbo.sample
|
|
|
|
|
i want to write a query to adding the numeric values in one row
how to do that ?
regards
|
|
|
|
|
So whats the prob??
select column1+column2+column3+.... from table.
When you fail to plan, you are planning to fail.
|
|
|
|
|
Hi all,
i m new in database,i m using a SDI type appliaction in VC++ 2005, and MS-Access Datase through ODBC.
i m add class using MFC ODBC Consumer,and add desired database and its table for code.
By default it is derived from CRecordset. here GetDefaultConnect() for default connection and GetDefaultSQL() is present.
i want to know that in GetDefaultConnect() the connection string or path of database present, if i run my application on other computer then it gives an error, can i change the path in GetDefaultConnect(),
i dont knoe how can use it.
please tell me what string i pass in GetDefaultConnect() for execute my application properly for each computer.
Thanks in advance.
I m waiting for ur Valuable suggetions.
IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH
|
|
|
|
|
|
Hi All,
I have the following defined in a class that I'm using to access and export the contents of an SQL Compact database. After having read around some I was under the impression that the following code would work, but its failing to actually LOAD any data from the database. Any help would be great.
to create my typed dataset:
SSdbDataSet ds = new SSdbDataSet();
The method to load the entire database from the .sdf file is:
public void ExportDatabaseXML()
{
ds.Clear();
ds.localSamples.Load(GetLocalSamplesReader());
ds.WriteXml("SStest.xml", XmlWriteMode.WriteSchema);
}
And there is this 'utility' method to create the reader:
private IDataReader GetLocalSamplesReader()
{
return ds.localSamples.CreateDataReader();
}
I'm a bit stuck now as I was under the impression that the Load() method pulled all the data from the underlying datatable.
Any pointers would be great.
Jammer
Going where everyone here has gone before! 
My Blog
|
|
|
|
|
Hey Guys,
Lets say we have a table T1 that have a numeric field F:
Table T1 (int ID, int F)
I need to update this table and set F to the MAX +1 to insure uniqueness:
UPDATE T1
SET F = (SELECT ISNULL(MAX(F),0) + 1)
WHERE ID IN(1,2)
The thing is I end up with both IDs 1 and 2 having the same value for F. I remember doing but what do I have to change so that Max is calucated for row in T1 so that if my initial table is like:
ID F
1 1
2 2
Running the update query will get the following result
ID F
1 3
2 4
Thanks.
|
|
|
|
|
UPDATE T1<br />
SET F = (SELECT top 1 ISNULL(MAX(t2.F),0) + 2 from T1 as t2 where t2.id=T1.ID)<br />
WHERE ID IN(1,2)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
