|
Sure it works
u can add values as integer too (for integer filed)
ur example works as this:
INSERT titles (title, cost)<br />
VALUES ('testing', 35)<br />
|
|
|
|
|
i know, because i test before..
but i don't know why i can work....
angela
|
|
|
|
|
Automatic data type conversion.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
oic, no wonder it works lah;)
thanks ya..
angela
|
|
|
|
|
how to change SQL statement below to use CASE instant of "AND" "OR" after condition where :-
ALTER PROCEDURE dbo.authorInfor
@au_lname varchar(40)
AS
select title, price, Name = substring (au_lname, 1,40), type
from authors a, titles t, titleauthor ta
where a.au_id = ta.au_id AND t.title_id = ta.title_id AND(au_lname = @au_lname OR au_lname = '' OR au_lname is Null)
Thanks.
Angela
|
|
|
|
|
If AND OR works use it; its must more efficient than using case.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
really can works and get the result that what i want with my previous SQL statement...
the matter is my boss want me to change the predicate to use "Case" 
anyway thanks for your suggestion;)
angela
|
|
|
|
|
CASE is generally used in instances where you want a different field value returned based on specified conditions. Your select statement is only concerned with returning all fields based on certain conditions. In other words, CASE wouldn't be used in the WHERE clause of an sql statement such as yours.
You could clean this up a little though, like this:
select title, price, Name = substring (au_lname, 1,40), type<br />
from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id<br />
where au_lname = @au_lname OR isnull(au_lname,'') = ''
Or your where clause could be like this:
where isnull(au_lname,'') IN (@au_lname,'')
Other than that, nothing comes to my mind.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
no wonder i can't get what i want if i change the predicate to use "CASE" in my SQL statement
can see that your SQL statement is more simplify and better than mine 
but 1 of my condition is when user do not insert any value for author's name, all author's name record will display in my result..
if using your SQL statement above, if i insert null for my author's name, then the result is empty..
|
|
|
|
|
I get a SQL statement that can use CASE under my condition
Declare @au_lname char(30)<br />
<br />
Select @au_lname = ''<br />
<br />
select title, price, <br />
Name = substring (au_lname, 1,40), type<br />
from titleauthor ta JOIN authors a on ta.au_id = a.au_id<br />
JOIN titles t on ta.title_id = t.title_id<br />
<br />
Where 1 = <br />
Case<br />
When RTRIM(@au_lname) = '' Then 1<br />
When RTRIM(@au_lname) <> '' And <br />
RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname)) Then 1<br />
Else 0<br />
End
it's cool
angela
|
|
|
|
|
I see. Thank you for posting your solution.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Dear Sir
I created New Package by wizard in SQL server 2000. I want to write store procedure to execute it but i don't know how to write it.
could you tell me how to write it?
|
|
|
|
|
SaravuthYos wrote: SQL server 2000. I want to write store procedure
See the documentation: CREATE PROCEDURE[^] and EXECUTE[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Stored Procedure
===================
create procedure dbo.myProcAS
selet * from customers
go
=====================
running this
exec myProc
Parameter Stored Procedure
=============================
create procedure dbo.MyProc
@CustNumber int
AS
SELECT * from customer
WHERE CustomerID = @CustNumber
go
=============================
running this
exec myProc 1001
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
very Simple
--Query without parameter
create proc procedure_name
as
Select * from table_name
--conditional query
create proc procedure_name
as
begin
Select * from table_name
End
Rakesh Jha
|
|
|
|
|
I would like to write a simple inventory application in C# to keep track of my dvds, music and books. While I'll use this application, I am more interested in it for the opportunity to familiarize myself with accessing a database in C#. However, my knowledge about databases, particularly accessing them in C#, is limited.
I am looking for suggestions on a good way to set up this application and databases. Tutorials, code snippets or full blown applications that access a database in a way similar to what I would be doing are all helpful.
Thanks for any help / suggestions you can give.
|
|
|
|
|
Andrew Stampor wrote: I am looking for suggestions on a good way to set up this application and databases.
I typed "ADO.NET Tutorial" into a search engine and got this: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx[^] There are more tutorials out there if you care to look. There are also excellent books on the subject.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Thanks for your help!
I've been looking through the example at the site you suggested and I am having a little trouble getting it to run. I've installed "Microsoft SQL Sever 2005" and am trying to run the code from lesson 2 (http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx). When it gets to the "conn.Open()" call, I get an exception, which says:
"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)"
I have attempted to resolve this but am not sure what needs to be done. I cannot find a setting that specifically allows or disallows remote connections.
|
|
|
|
|
It means you are trying to access the SQL Server over a network connection. By default, for security, the SQL Server 2005 only permits local connections (Previously TCP/IP was also turned on).
If your SQL Server is installed on the same machine as your application is running then change the Data Source value in the connection string to (local), with the brackets, instead of the machine name, an IP address or localhost.
If your SQL Server is running on a different machine to your application then allow it to accept incoming network connections (Open the SQL Server Configuration Manager). Find "Client Protocols", and in the right side Enable "TCP/IP". If you take this option you may wish to check the properties of the TCP/IP connection and ensure the port is locked down on your internet connection's firewall.
Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog
-- modified at 17:16 Sunday 11th June, 2006
|
|
|
|
|
Hi all,
Here's where I am stuck:
I am planning to have a sitemapath or a treeview of available reports for users in SQL reporting services 2005. I am pretty sure it is not an easy job. I am thinking of using frames here. Let's say the left frame shows the treeview and the right frame contains the reports page. Assume, there are three folders (Folder A, B, & C) containing few reports each. I want these folders and the corresponding reports(preferable) to be present in the left frame as treeview. And when I click on the folder from the left frame, it should bring up the corresponding Report Manager page showing the folder contents in the right frame.
any help or ideas would be greatly appreciated!
|
|
|
|
|
I've got a dynamic SQL query that is generated inside a SP, and is run using the
EXEC (@SQL)
or
EXEC sp_executesql @strSQL
This has worked fine until now, where we are now using a database for unicode charachters.
All fixed code stored procedures return data correctly in the unicode format, however, all queries run using EXEC / sp_executesql, return '?' instead of unicode charachters.
This is a cause of some serious issues, and i'm really hoping someone knows if there is a fix for it!
Cheers
Tris
|
|
|
|
|
I am working with a datagrid in c#/asp.net. Within the datagrid I have to display fields from 10 different tables. I am wondering if my thinking of the syntax is correct.
First, the user enters the part number (Parts.PartNumber)which is actually a varchar. Then it finds the Part.ID. This Part.ID is what most things are going to inner join. Right now I have it just displaying the cost:
Select cost from costs c INNER JOIN Parts p ON c.PartID = p.Id where p.PartNumber = @PartID
But I want it to also display SubParts.Quantity where SubParts.PartID = Parts.ID. So would I set it up like this?
Select cost from costs c, quantity from SubParts sp (INNER JOIN Parts p ON c.PartID = p.ID AND INNER JOIN sp.PartID = p.ID) where p.PartNumber = @PartID
I've got a bunch of these so if someone can point me in the right direction much thanks!
|
|
|
|
|
Personally, I don't use the "JOIN" keywords very often. You can do a typical join like this:
SELECT cost FROM costs as c, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID
Then your second statement would look like this:
SELECT cost, quantity FROM costs as c, SubPars as sp, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID AND sp.PartID=p.ID
Basically you just need to make sure that whatever you would put in the "ON" portion, you include in the "WHERE" portion.
If you want to keep the "JOIN" keywords, I believe it would have to look like this:
SELECT cost, quantity FROM costs as c INNER JOIN parts as p ON c.PartID=p.ID INNER JOIN SubParts as sp ON sp.PartID=p.ID WHERE p.PartNumber=@PartID
|
|
|
|
|
Okay, elaborating on the second statement...I have two tables that I have to pull the field 'description' (I had nothing to do with the table structures !). So would you say
SELECT cost, quantity, description, description from costs as c, SubParts as sp, WeightType as wt, CostType as ct, parts as p WHERE c.PartID = p.ID AND p.PartNumber = @PartID AND sp.PartID=p.ID...(and the other joins for the descriptions).
Since description is in there twice, is that a legal statement since the number of select fields matches the number of tables listed? Just curious.
|
|
|
|
|
I think you will have to use the table aliases and then give aliases to the field names. So if description is from the part table and the sub part table it would look like this:
SELECT cost, quantity, p.description as PartDesc, sp.description as SubPartDesc, ... (etc.)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
