Can the SQL Gods Help Me? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can the SQL Gods Help Me?

Author

Topic

PCAKen
Starting Member

2 Posts

Posted - 2005-02-24 : 16:09:19

First time poster here, I think this is the right forum for the following:

Looking for assistance with how to properly update or insert records given the following scenario:

I have an ASP page that displays a table of 20 items.
Each row in the table is as follows:
Item ID, Name, Description, Drop Down Box 1, Drop Down Box 2

I have a checkbox next to each Item so that upon submission of the form I can either have one or more items on the list submitted. The items submitted are the ID and the values from the 2 drop down boxes.

If one item is checked, then the submission looks as follows (assume last item on list is checked and no others)

20,0,0,0....out to the value in spot 20,0,0,0...out to the value in spot 20.

If I have multiple items checked, assume #1 and #20, then the form results are the following:
1,20,X,0,0,0,0...out to value in spot 20,Y,0,0,0,0 out to the value in spot 20.

Obviously if I try to do a SQL INSERT into a table in my database it is going to fail because the value of the ID field is one or more ID's and the value of each drop down box is always a set of 20 values for each drop down box. (default value is 0 for the drop down box when no choice is made 20 you still get 20 values).

I tried giving the value to be inserted a placeholder, for example dropdown1(ID) so that if say item 17 was chosen it would do dropdown1(17), but that didn't work, and of course if I have multiple ID's checked off the list then the situation would just get more confusing.

My thought was that I could parse the strings coming back for the drop down value somehow and then select the value that corresponds to the ID, but I'm not sure how to handle the case of multiple ID's as well.

I was hoping there might be some sort of SQL command that would handle this type of case/scenario or some already proven method of doing this.

Last but not least, I was going to populate the database with 20 default values and that way the SQL command will be an UPDATE as opposed to an INSERT if that changes the picture any.

Thanks in advance for all responses!
----------------------------------
I received a response that this was somewhat confusing, so here is some additional elaboration:

I wish to display a table that looks like the following:

checkbox - Item Name - Item Description - dropdown box - drop down box
checkbox - Item Name - Item Description - dropdown box - drop down box
checkbox - Item Name - Item Description - dropdown box - drop down box
....
on down to having a total of 20 items displayed.

Each of the 20 items has a unique ID#
Each of the 20 rows has 2 drop down boxes where values can be chosen. The values are integers, the first drop down box has values 1-3 (default or no selection is 0), the second drop down box has values 1-10 (default again is 0).

So if I went through the list and did the following:

checked #1 - Item Name - Item Description - 3 - 7
checked #2 - Item Name - Item Description - 1 - 10
#3 unchecked
checked #4 - Item Name - Item Description - 2 - 4
...
on down to item #20

When I submit the form I am capturing only the ItemID (1-20) and the dropdown box 1 and drop down box 2 values (1-3) and (1-10).

What I get returned given the scenario above would be
1,2,4 followed by 3,1,0,2,0...0 followed by 7,10,0,4,0....0
The "....'s' represent that the string has 0's in between I didn't want to type out all of the zeros to the 20th position.

You can see this in action at http://www.smiley.net/mwpx/layout_mgr.asp

Try selecting some values from the drop down box then clicking on the link to Choose Layout.

Hopefully that helps put it into perspective what I am trying accomplish.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-24 : 16:33:15

Use a split function to split the incoming CSVs


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 

	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

Then the query is pretty simple to put things back together:


Declare @itemList varchar(100),
	@ColNumList varchar(100),
	@Priority varchar(100)

Select @itemList = '1,2,12,13,16'
Select @ColNumList = '1,2,0,0,0,0,0,0,0,0,0,3,3,0,0,2'
Select @Priority = '10,1,0,0,0,0,0,0,0,0,0,10,9,0,0,9'


Select * 
From (Select item = convert(int,Data) From dbo.split(@itemList,',')) A
Inner Join (Select item=id, colNum=convert(int,Data) From dbo.split(@colNumList,',')) B
On A.item = B.item
Inner Join (Select item=id, Priority=convert(int,Data) From dbo.split(@Priority,',')) C
On A.item = C.item

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

PCAKen
Starting Member

2 Posts

Posted - 2005-02-24 : 16:47:17

Egads some of that went right over my head! Any chance if give you my contact info that we can chat about this 1:1 or could I send you some code to look at and assist me with?

Here is my MSN ID if you want to try and respond to me that way:
angi555[remove][@]hotmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources