|
I have a stored proc which populates a drop-down box in an ASP.NET datagrid. The user enters a part number and the datagrid populates based on that part number. The datagrid is already in edit mode using template columns.
What I would like to do on the SQL side if possible is the following:
a) If the part number does not exist, populate the drop-down box with all part types, and order by description. (Okay, this is no problem.)
b) If the part number DOES exist, populate the drop-down box with all part types, but list the saved description first, then list the rest by description.
This is what I have thus far:
CREATE PROCEDURE [dbo].[GetPartDescriptions]<br />
<br />
@PartNumber varchar (60)<br />
<br />
AS<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
SELECT ID as ptID, Description from PartTypes pt ORDER by Description<br />
END<br />
<br />
GO
|
|
|
|
|
Sorry for not answering your question, but I have to ask you this question: Why would you have parts with the same part number? I would think that would be unique for each part type. No?
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
Jerry Hammond wrote: Why would you have parts with the same part number
Parts is the table leckey is using and partnumber is the field.
Yes, the partnumber field ought to be unique for each part type.
|
|
|
|
|
Maybe I'm the confused one. Part b of your requirements seem to imply multiple parts (or part types) with the same part number.
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
Sorry for wording my question poorly. The part number is indeed unique. What I am looking for is if the user enters a part number that already exists in the database, the dropdown list populates with the part type first, then the remaining part type choices alphabetically. If the part does not exist, just order alphabetically. I had a friend help me last week and got this:
CREATE PROCEDURE [dbo].[GetPartDescriptions2]<br />
<br />
@PartType varchar (60)<br />
<br />
AS<br />
<br />
SELECT PartTypes.ID, PartTypes.description,<br />
CASE when PartTypes.ID = @PartType then 1<br />
else PartTypes.ID<br />
end as order_by<br />
from PartTypes INNER JOIN parts p on p.PartTypeID = PartTypes.ID<br />
<br />
ORDER BY order_by<br />
<br />
But I was getting confused and the bolded parts are not right. I am passing in the part number so it should be something like this:<br />
<br />
<code>CREATE PROCEDURE [dbo].[GetPartDescriptions3] <br />
@PartNumber varchar (60)<br />
<br />
AS<br />
<br />
SELECT PartTypes.ID, PartTypes.description, Parts.PartNumber, Parts.PartTypeID<br />
CASE when PartTypes.ID = Parts.PartTypeID then 1<br />
else PartTypes.ID<br />
end as order_by<br />
from PartTypes INNER JOIN parts p on p.PartTypeID = PartTypes.ID<br />
<br />
ORDER BY order_by<br />
GO
But enterprise manager doesn't like that. Asked my friend and she sent me this:
You could get the Part Type by
Declare @PartTypeId int
select @PartTypeId = PartTypeId
from PartTypes where Partnumber = @PartNumber
I'm now having trouble figuring out where to stick this new select statement in the entire code.
GO
|
|
|
|
|
leckey wrote: ORDER BY order_by
Aren't you missing either ASC or DESC to tell it how you want to order the fetched recordset?
What do you mean by order_by?
Usually it is a field or column name that comes after the ORDER BY.
|
|
|
|
|
hey, guys. i'm trying to build a generic usp, which i dont think is possible, so i may end up just making several different usp's that take the same parameters...what i'd love to do is
select @returnColumn
from @table
where @searchColumn=@searchValue
i know the above code wont work, but does anyone know some nifty trick to make something like that work?
Thanks
John
|
|
|
|
|
Something like this will work:
CREATE PROCEDURE GetColumn
@returnColumn varchar(100),@table varchar(100),@searchColumn varchar(100), @searchValue varchar(100)
AS
exec('select ' + @returnColumn + ' from ' + @table + ' where ' + @searchColumn + ' = ' + @searchValue)
But be sure to check out this article: SqlInjectionAttacks[^]
--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 i never would have thought of that...i've been thinking about Sql Injection Attacks since i was told that i'd have to add this kind of functionality to the app...luckily this is only going to be run by a handfull of people in our office, and can only be run in our office...so all i should need to do is make sure that the input is alphanumeric characters only, no puncuntation(sp?) or spiecal characters i.e. *,%, etc.. and i should be ok...mabye.
thanks again for your help
john
|
|
|
|
|
i have sql server 2000 DATABASE i want to add columns to store sounds and another to store video files what type of column for each one (sound files&videos files)
ma_refay
|
|
|
|
|
|
field type should be "image", unless you know they will all be under 8000 bytes long, then you could use varbinary. image fields can be up to 2GB in size.
|
|
|
|
|
Hi,
When I do a normal select in a stored procedure, must I include the BEGIN and END, or is it not necessary?
Regards,
ma se
|
|
|
|
|
BEGIN and END surround a block of statements, allowing them to be treated as a single statement in some circumstances. They're really T-SQL keywords rather than part of SQL. The T-SQL control constructs (IF, WHILE) only control a single statement or a block of statements. I would recommend when using IF and WHILE statements in T-SQL, that you always use BEGIN and END to save any confusion during maintenance. This is the same recommendation as in C, C++, Java, C# where you should always use { } to delimit controlled statements.
Example:
IF EXISTS (
SELECT * FROM authors WHERE au_lname = 'Green' )
SELECT * FROM titleauthor
SELECT * FROM titlesDespite the poor formatting, the second SELECT will always be performed, not just when there's an author with the last name of Green. If you wanted to ensure that both statements would only execute when the IF condition was true, you should do::
IF EXISTS (
SELECT * FROM authors WHERE au_lname = 'Green' )
BEGIN
SELECT * FROM titleauthor
SELECT * FROM titles
END
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
You can also use ELSE as well
<br />
create proc myProc @Account int AS<br />
<br />
-- Select ALL RECORDS<br />
if @Account = 0 <br />
BEGIN<br />
SELECT * FROM Accounts<br />
END<br />
ELSE<br />
-- SELECT SINGLE ACCOUNT<br />
BEGIN<br />
SELECT * FROM Account<br />
WHERE ID = @Account<br />
END<br />
<br />
Hope tihs helps
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Here I use Access to build up a database with a time/date field.
How can I query the database according to the time/date field, that is
SELECT * FROM [DATABASE] WHERE TIMEDATE < 20:20? Can time/date field be operated with > = and < ?Thanks
|
|
|
|
|
Select from [database.table] where [table.field] < '20;20 00:00'
Got the idea 
Glen Harvy
|
|
|
|
|
Sorry, not very clear.Factually I have two question to ask:
1. how to input the date-time format: "1987-12-29 12:23:45" into Access DB with InputMask?
2. if I want to query the record that has the date-time before "1987-12-29 12:23:45" and after "1986-10-15 23:25:26", how do I write the sql string?Thanks
|
|
|
|
|
zouchao1112 wrote: 1. how to input the date-time format: "1987-12-29 12:23:45" into Access DB with InputMask?
UPDATE [tablename] SET [datetimeField] = "1987-12-29 12:23:45" WHERE [record.ID] = [record.needing.update.ID]
zouchao1112 wrote: if I want to query the record that has the date-time before "1987-12-29 12:23:45" and after "1986-10-15 23:25:26", how do I write the sql string?
SELECT [record.id] FROM [tablename] WHERE [record.datetimeField] < "1987-12-29 12:23:45" AND [record.datetimeField] > "1986-10-15 23:25:26".
Glen Harvy
-- modified at 17:54 Friday 7th July, 2006
|
|
|
|
|
So you mean the format "1987-12-29 12:23:45" can be recognized as DATE automatically?Thanks very much
|
|
|
|
|
Hello,
I'm using SQL Server 2005. I have a column named "DATA" with ntext datatype that stores xml data which contains these tags:
<message>Text Free Meal
<multipleaction>
<caction>
<message>Thank you for participating. Just present this [MCCode] to claim your free meal.
How do I use sql select statement to access <message> and return the string
"Thank you for participating..." as a result?
Thank you. :->
|
|
|
|
|
|
I'm having a couple of problems with SQL Queries. I am running a Query
SELECT * from [DATABASE] WHERE DES='My' And i get this error: The data types text and varchar are incompatible in the equal to operator.
What data type Should i set my column to so i can make it searchable?
Don't be overcome by evil, but overcome evil with good
-- modified at 20:23 Thursday 6th July, 2006
|
|
|
|
|
Nevermind i got it. i changed it to varchar(max) and it worked fine.
Don't be overcome by evil, but overcome evil with good
|
|
|
|
|
Hi,
I have 2 tables tbl_Member and tbl_Community, they are related by primary/foreign keys.
I access them using SPs. So I created TableAdapters for SPs which return rows from these tables (only). It generated typed MemberRow and ComunityRow for me.
Now I have created SP which joins these 2 tables and returns me all columns from tbl_Member and one from tbl_Community.
I cannot add this SP as a query to MemberTableAdapter which I use to get records from tbl_Membe because this new field (from tbl_Community) does not fit into the existing XML schema.
How can I use this SP ? I would probably need more queries which return columns from joined tables.
How do you "typify" such queries ? It seems like the same problem would have arised if I created custom business objects and not used typed DataSets, I could have Member and Community objects but not something in between.
Thanks a lot,
Slava
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
