|
hi,
i am using MSSQL2000.
i wrote a query with sum() of aggregate function.
like
Select sum(sal)from table1 where empid=1 and date>'1/23/2006'
in this if thers is no record it rteurn 'NULL'
is it possible to return it as 0
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
i got its answer
SELECT isnull(sum(Sal),0) FROM table1 WHERE...
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
of cause,
the code like this:
Select case when sum(sal) is null then 0 esle sum(sal) end
from table1 where empid=1 and date>'1/23/2006'
|
|
|
|
|
thank u
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Hai everybody,
Can any one give me a hint how to throw an exception explicitly in SQL stored Procedure inside the try catch block.
and how can i quit form the procedure in mid way
Thanks in advance.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
The command is called raiserror. If you do a seach on it you will find plently of examples. If you want to quit a stored procedure and you don't want to use raiserror with a high enough severity then you the Return key word to exit the procedure.
Hope that helps.
Ben
|
|
|
|
|
Thank you Ben.
It is working fine.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
I make a DataBase and create the table and procedure all in the the default schema and primery file group
Can any one tell me how to replace the tables and procedures in a new file group and schema
merwa
|
|
|
|
|
I am developing a solution in C# on top of ADO.NET. I am using MS Access as my data store. My requirement is that i have to insert a Unicode string containing Arabic characters into the database. So following is the INSERT command
INSERT INTO ClauseTranslations (Translation) VALUES('اردو کی ایک مثال')
So thats it. Now this query executes very well in the MS Access environment but simply fails when executed from the C# application using the ADO.NET classes. The error message is
"Syntax Error in INSERT Statement"
Can anyone guide me whats wrong and where is it wrong?
Regards,
Mohsin
Polite Programmer
More Object Oriented then C#
|
|
|
|
|
It is possible that your field name (Translations) is a keyword for the Jet Engine SQL implementation. Try enclosing it in square brackets [Translation] to avoid interpretation as a keyword.
If that fails, implement the insert using a parameterized insert stored procedure (querydef in Access Lingo) and use an SQLCommand to execute it from C#. This almost always works, as SQL syntax is interpreted at the database, and data values are cleanly encapsulated, avoiding any parsing problems.
Using stored procedures and commands also makes your application far less vulnerable to sql injection attacks.
|
|
|
|
|
Hey guys,
I am trying to write a C# program to import data from an Excel spreadsheet to a Sql table using a stored procedure.
So, I've created the connection objects:
OleDbConnection XLcon = new OleDbConnection(
@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\Desktop\OA.xls;"
+ @"Extended Properties = Excel 8.0;");
SqlConnection SQLcon = new SqlConnection(
@"Data Source = (local); Integrated Security = SSPI; Initial Catalog = WebData");
And the Command Objects:
SqlCommand SQLcmd3 = new SqlCommand();
SQLcmd3.CommandText = "ImportOA";
SQLcmd3.CommandType = CommandType.StoredProcedure;
SQLcmd3.Connection = SQLcon;
OleDbCommand XLcmd = new OleDbCommand("SELECT [Date], [Order Application], "
+ "[Demand Orders], [Demand Units], [Demand Net], [Demand Gross], "
+ "[Demand Freight], [Demand Tax], [Demand Amount], [Demand Product Promo], "
+ "[Demand Ship Promo], [Fulfillment Units], [Fulfillment Net], "
+ "[Fulfillment Freight], [Fulfillment Tax], [Fulfillment Amount], "
+ "[Fulfillment Product Promo], [Fulfillment Ship Promo], [Return Orders], "
+ "[Return Units], [Return Net], [Month], [Quarter], [Year], [Week #], "
+ "[Return Amount] FROM [Excel$]", XLcon);
Set up the parameters:
SQLcmd3.Parameters.Add("@Date", SqlDbType.SmallDateTime);
SQLcmd3.Parameters.Add("@OrderApplications", SqlDbType.VarChar,10);
.
.
.
SQLcmd3.Parameters.Add("@ReturnAmount", SqlDbType.Money);
And then tried to import the data:
XLcon.Open();
OleDbDataReader XLread = XLcmd.ExecuteReader();
while (XLread.Read())
{
SQLcon.Open();
SQLcmd3.Parameters["@Date"].Value = XLread[0];
SQLcmd3.Parameters["@OrderApplications"].Value = XLread[1];
.
.
.
SQLcmd3.Parameters["@ReturnAmount"].Value = XLread[25];
SQLcon.Close();
}
XLcon.Close();
XLread.Close();
The code runs fine but it does not import anything into the table. The spreadsheet has data in it and I have verified that the Store procedure works so why can't I get the values in the spreadsheet into the table?
Thanks,
Bryan
|
|
|
|
|
I'm not sure what you are trying to do here, but it doesn't look correct.
you have created a command query containing a select statement that specifies a list of fields to be selected from the worksheet named Excel$. You read the results of that query into a command object that is linked to a stored proc in SQL server somewhere. You open a connection, set command parameters to row values read from excel and then close the connection (do you execute the command first?).
First, I would open and close the connection outside of the reader while loop, and just execute SQLcmd3 at the bottom of the loop. Close and dispose of the connection (and command) after the while loop.
Set a breakpoint at one of the lines that sets a parameter value and verify that you are really returning data from the sheet.
Good luck
|
|
|
|
|
Hi, I have to show a filesystem on a page. The information is stored in a table where I have ID(PK), Name, Parent, Order
001, Node1, NULL, 1
002, Node2, NULL, 2
003, Node3, 001, 1
004, Node4, 001, 2
005, Node5, 002, 2
I need to display the results as it should be seen:
+Node1
Node3
Node4
+Node2
Node5
The query I have is like this:
SELECT A.*
FROM TableFS A
LEFT OUTER JOIN TableFS B
ON A.ParentID = B.ItemID
ORDER BY A.ParentID, B.Order
The result is similar to what I should get, but similar is not enough... at least it's what they tell me... 
If anyone can help... 
Thanks...!
|
|
|
|
|
SELECT A.*
FROM TableFS A
INNER JOIN TableFS B ON A.ItemID = B.ParentID
ORDER BY A.ParentID, B.Order
Regards
KP
|
|
|
|
|
Sorry, but this is not even close. Items without parents have value in NULL, so your querry dosen't take those nodes in charge, and gives me duplicated values anyway.
Thanks for your time 
|
|
|
|
|
I have a database column that is variable character 1500, If I try to read a record with a value whose length is over 200 characters I get an empty string and the debugger returns the following error:
This expression causes side effects and will not be evaluated
|
|
|
|
|
Hi,
I've been using SQL Server 2005 for almost 2 years now. I want to know if there is anything to look at for the new SQL Server?
Regards
ma se
|
|
|
|
|
|
I haven't heard of SQL Server 2008
|
|
|
|
|
zhousz wrote: I haven't heard of SQL Server 2008
Codenamed Katmai - The CTP was released earlier this month.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I have a central database in a server computer(In sql express 2005).
Two or more computers can connect to the central database.When a terminal computer is doing some operation on some data, how can i ensure that the other terminal
in the same network doesnot change the data at the very same moment?
X
|
|
|
|
|
Please don't crosspost. It's not very polite.
|
|
|
|
|
SQL Server automatically ensures that data modification statements (INSERT, UPDATE, DELETE) are atomic - a statement that affects multiple rows will either modify all of them, or none (if some error occurs, the partial changes will be rolled back).
If you need to execute multiple statements in the same logical operation, and have the results consistent, you can use a transaction. If submitting all the statements in a single batch, or in a stored procedure, you can use the BEGIN TRANSACTION/COMMIT TRANSACTION/ROLLBACK TRANSACTION statements. If you can't execute it all in one batch, or need to call multiple stored procedures, use a client-side transaction (e.g. SqlConnection.CreateTransaction in .NET languages)
There are a number of transaction isolation levels which control which updates from other users can appear between two SELECT statements in the same transaction. Basically, READ COMMITTED (the default) allows changes to be made between two SELECTs - the second statement may see results that another, completed, transaction has made. REPEATABLE READ prevents updates and deletes until your transaction is committed. SERIALIZABLE also prevents inserts that would change your resultset.
Transactions using the built-in transaction features should be kept short. Holding a transaction open for too long prevents other users getting work done. If you need your application to allow users to read a record, make changes to it over a period of time, then eventually commit the change, you need to either come up with a manual record-locking scheme, or somehow check to see if changes have been made since the data was read and either automatically merge the changes made by each user or simply alert the user that the database has been changed.
One of the simplest ways to detect that the data has changed since being read is to use a rowversion column, which changes every time the row is updated.
|
|
|
|
|
Hi
I want to insert records as CustID & Custname in Customer table.
custid Interger (autoincrement)
Custname varchar2
& want to display last added record .
Thnx
|
|
|
|
|
Care Career wrote: I want to insert records as CustID & Custname in Customer table
Insert into Customer (Custname) values ('Value')
Care Career wrote: & want to display last added record
select top 1 * from Customer order by CustID desc
I Love SQL
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
