Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic Variable Creation 1

Status
Not open for further replies.
sandylou

sandylou

Programmer
Joined
Jan 18, 2002
Messages
147
Location
US
I have a web page which has 20 checkboxes with numbers 1-20. I am trying to create a stored procedure which takes these parameters passed from the page (ck1, ck2,ck3, etc.) and stores into a table.

Code: 
 CREATE TABLE #temptest (
	[intStep] [int] NOT NULL ,
	[vchDisplayStep] [varchar] (19) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
	
) ON [PRIMARY]
GO




DECLARE @intFlag INT,
	@intStep varchar(20),
	@vchDisplay varchar(25),
	@tnyActive1 tinyint,
	@tnyActive2 tinyint,
	@vchActiveStepDisplay1 varchar(19),
	@vchActiveStepDisplay2 varchar(19),
	@sql varchar(500)

select @tnyActive1 = 10,
         @tnyActive2 = 20,
          @vchActiveStepDisplay1 = 'A',
         @vchActiveStepDisplay2 ='B' 
	
SET @intFlag = 1
WHILE (@intFlag <=2)
BEGIN

print @intFlag

print @intStep
Print @vchDisplay

select @sql = 'insert into #temptest (intStep,vchDisplayStep)
		values(@tnyActive'+ Cast(@intFlag as varchar(5))+', @vchActiveStepDisplay'+ Cast(@intFlag as varchar(5))+ ')'
--print @sql
Exec( @sql)

SET @intFlag = @intFlag + 1
--IF @tnyActive+@intFlag = 0
--BREAK;
END
GO

This doesn't work. I just am not thinking today. I want to create a dynamic variable and assign the value passed to that dynamic variable.

Any ideas
 
Sort by date Sort by votes
I believe I undertand the problem now - I don't think there is an easy solution.

In Visual Foxpro this is easily solved with macro or evaluate()

In C# you would need to use reflection for this, I believe.

How to do this is T-SQL - I can not figure out from the top of my head.
 
Upvote 0 Downvote
maybe i will just have to loop through checkboxes in .net page and execute stored procedure from there or 20 if statements in the stored procedure!! :)
 
Upvote 0 Downvote
i have already passed the checkboxes as variables ck1-ck2 only if i have a value for the checkbox, now i need to save it..i guess i can just do the 20 inserts :(
 
Upvote 0 Downvote
You haven't said anything about the structure of your data in your table, how the web page is converting the form post into SQL statements, and so on.

I don't think anyone can give you any really good advice until you provide more information.

I am 100% confident that doing 20 inserts is not necessary. If you want a better way, you can probably get it, if you help us help you.
 
Upvote 0 Downvote
1. Will you always pass all 20 checkboxes?
2. Will they always be passed in the same order?
3. Will they always be passed as a single 20 value CSV?

If the answers to the above are all "Yes", I can help. Please respond quickly and you could have the answer tonight.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
 
Upvote 0 Downvote
i am sorry, i thought that the same table showed basically what i am looking for. on the web page basically it is a table with 20 possible values to check (1-20) which a text display of those values. So if i want 1 = A i check one and type in A. The table is basically a binderNo, step, and displaystep. For each of the items they select on the web page, i would like to insert those items. I gave each checkbox and checkboxtest a name. So checkbox1.checked i pass the parameter @checkbox1 = 1 and if there checkboxtest has a value i pass that value as @checkboxtext1.

I can pass in all of the checkbox values, but to insert into 1 table looping dynamically through these values i am not sure how to do. i can do 20 statements, but then on update, i will need to do 20 checks if that exists and delete from the table.


Code: 
<table cellspacing=10>
					<tr align="center"><td colspan=4>Active Steps</td></tr>
					<tr>
						<td>
							<asp:CheckBox ID="ckbActiveStep1" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="1" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr1" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep6" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="6" Runat=server/>
							&nbsp;
							<asp:TextBox ID="txtActiveStepDescr6" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep11" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="11" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr11" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep16" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="16" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr16" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
					</tr>
					<tr>
						<td>
							<asp:CheckBox ID="ckbActiveStep2" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="2" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr2" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep7" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="7" Runat=server/>
							&nbsp;
							<asp:TextBox ID="txtActiveStepDescr7" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep12" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="12" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr12" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep17" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="17" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr17" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
					</tr>
					<tr>
						<td>
							<asp:CheckBox ID="ckbActiveStep3" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="3" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr3" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep8" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="8" Runat=server/>
							&nbsp;
							<asp:TextBox ID="txtActiveStepDescr8" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep13" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="13" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr13" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep18" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="18" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr18" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
					</tr>
					<tr>
						<td>
							<asp:CheckBox ID="ckbActiveStep4" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="4" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr4" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep9" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="9" Runat=server/>
							&nbsp;
							<asp:TextBox ID="txtActiveStepDescr9" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep14" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="14" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr14" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
							</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep19" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="19" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr19" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
					</tr>
					<tr>
						<td>
							<asp:CheckBox ID="ckbActiveStep5" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="5" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr5" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep10" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="10" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr10" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep15" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="15" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr15" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
						<td>
							<asp:CheckBox ID="ckbActiveStep20" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" Text="20" Runat=server/>
							<asp:TextBox ID="txtActiveStepDescr20" Font-Names="Tahoma, Arial, Helvetica, sans-serif" Font-Size="10pt" MaxLength=19 Width=85 Runat=server/>
						</td>
					</tr>
					</table>
create procedure test
@Binder varchar(5),
@tnyActiveStep1 TINYINT = 0,
@tnyActiveStep2 TINYINT = 0,
@tnyActiveStep3 TINYINT = 0,
@tnyActiveStep4 TINYINT = 0,
@tnyActiveStep5 TINYINT = 0,
@tnyActiveStep6 TINYINT = 0,
@tnyActiveStep7 TINYINT = 0,
@tnyActiveStep8 TINYINT = 0,
@tnyActiveStep9 TINYINT = 0,
@tnyActiveStep10 TINYINT = 0,
@tnyActiveStep11 TINYINT = 0,
@tnyActiveStep12 TINYINT = 0,
@tnyActiveStep13 TINYINT = 0,
@tnyActiveStep14 TINYINT = 0,
@tnyActiveStep15 TINYINT = 0,
@tnyActiveStep16 TINYINT = 0,
@tnyActiveStep17 TINYINT = 0,
@tnyActiveStep18 TINYINT = 0,
@tnyActiveStep19 TINYINT = 0,
@tnyActiveStep20 TINYINT = 0,
@vchActiveStepDisplay1 VARCHAR(19) = '',
@vchActiveStepDisplay2 VARCHAR(19) = '',
@vchActiveStepDisplay3 VARCHAR(19) = '',
@vchActiveStepDisplay4 VARCHAR(19) = '',
@vchActiveStepDisplay5 VARCHAR(19) = '',
@vchActiveStepDisplay6 VARCHAR(19) = '',
@vchActiveStepDisplay7 VARCHAR(19) = '',
@vchActiveStepDisplay8 VARCHAR(19) = '',
@vchActiveStepDisplay9 VARCHAR(19) = '',
@vchActiveStepDisplay10 VARCHAR(19) = '',
@vchActiveStepDisplay11 VARCHAR(19) = '',
@vchActiveStepDisplay12 VARCHAR(19) = '',
@vchActiveStepDisplay13 VARCHAR(19) = '',
@vchActiveStepDisplay14 VARCHAR(19) = '',
@vchActiveStepDisplay15 VARCHAR(19) = '',
@vchActiveStepDisplay16 VARCHAR(19) = '',
@vchActiveStepDisplay17 VARCHAR(19) = '',
@vchActiveStepDisplay18 VARCHAR(19) = '',
@vchActiveStepDisplay19 VARCHAR(19) = '',
@vchActiveStepDisplay20 VARCHAR(19) = '',
@i Int,
@vchActiveStepDisplay VARCHAR(19),
@tnyActiveStep TINYINT = 0


set @i = 1
While (@i<=20)
BEGIN
--Set ,@vchActiveStepDisplay,@tnyActiveStep by assigning the value of ,@vchActiveStepDisplay(i),,@tnyActiveStep(i)

if @tnyActiveStep(i) <> 0
BEGIN
insert into testtable (binder,step,stepdescr)
values(@binder,@vchActiveStepDisplay,@tnyActiveStep)
END

set @intFlag = @intFlag +1

Code:
 
Upvote 0 Downvote
Passing them all as one single CSV should be very simple then. We really don't need 20 parameters. The only minor problem is that we need to loop through them in our page to pass comma-delimited string 1,0,1,0,0,1,1,0 etc.
 
Upvote 0 Downvote
Ok... that gave me enough to go on. Here's some test code that you can work with and maybe even create a stored proc from. The details are in the comments in the code.

Like Markros said, the only thing left to do is to concatenate your "collections" of check and text boxes.

Code: 
--===== This is just so you can do reruns for testing purposes.
     -- It may be removed when you convert to a stored proc
     -- or you can leave it in.
     IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL
        DROP TABLE #TestTable

--===== These would be parameters in a stored proc
DECLARE @pBinder     VARCHAR(5) 
DECLARE @pCheckBoxes VARCHAR(100)
DECLARE @pDisplays   VARCHAR(400)

--===== These are just for testing.  Remove these when you convert to stored proc.
     -- The later two variables represent the concatenation of "collections" you
     -- need to create in and pass from the GUI code.
 SELECT @pBinder     = 'Test1',
        @pCheckBoxes = '10110100001010000110',
        @pDisplays   = 'aaaaa,bbb,ccccc,d,eee,f,ggggg,h,iiiiiiii,jjjj,kk,l,m,n,oooo,ppp,qqqq,rrrrr,s,tt'

--===== This little slice of computational heaven splits the parameters, matches them up, creates
     -- a temp table called #TestTable, and populates it with only active data from the parameters.
     -- The splits are done using a "Tally" table split.  See the following URL for how that works.
     -- [URL unfurl="true"]http://www.sqlservercentral.com/articles/T-SQL/62867/[/URL]
 SELECT CAST(split.Step AS TINYINT) AS Step,
        MAX(split.DisplayValue) AS DisplayValue
   INTO #TestTable
   FROM (--==== This SELECT splits the text box active parameter
         SELECT t.Number AS Step,
                SUBSTRING(@pCheckBoxes,t.Number,1) AS IsActive,
                CAST(NULL AS VARCHAR(19)) AS DisplayValue
           FROM Master.dbo.spt_Values t --"t" for "Tally" table
          WHERE t.Type = 'P'
            AND t.Number BETWEEN 1 AND LEN(@pCheckBoxes)
          UNION ALL
         --==== This SELECT splits the text box display value parameter
         SELECT ROW_NUMBER() OVER (ORDER BY t.Number) AS Step,
                NULL AS IsActive,
                SUBSTRING(@pDisplays, t.Number, CHARINDEX(',',@pDisplays+',',t.Number)-t.Number) AS DisplayValue
           FROM Master.dbo.spt_Values t --"t" for "Tally" table
          WHERE t.Type = 'P'
            AND Number BETWEEN 1 AND LEN(@pDisplays) + 1
            AND SUBSTRING(','+@pDisplays,t.Number,1) = ','
        ) split
  GROUP BY Step
 HAVING MAX(split.IsActive) = 1

--===== This is just to show what's in the test table.
     -- It may be removed when you convert to a stored proc.
 SELECT * FROM #TestTable


--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
 
Upvote 0 Downvote
the only thing left to do is to concatenate your "collections" of check and text boxes
Click to expand...

With ASP, this is usually a lot easier than most people think. If you have multiple controls named the same, you can easily get a comma separated list of values automatically from ASP.

Note: I have no experience with ASP.net. The following code is ASP Classic, but it probably works the same way.

Code: 
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
    </head>
    
    <body>
    <%=Request.Form("AnyName") %><br />
    <%=Request.Form("TextName") %>
    <form method="post" action="test.asp">
		<input type="checkbox" name="AnyName" value="1" />
		<input type="text" name="TextName" /><br />
		
		<input type="checkbox" name="AnyName" value="2" />
		<input type="text" name="TextName" /><br />

		<input type="checkbox" name="AnyName" value="3" />
		<input type="text" name="TextName" /><br />

		<input type="checkbox" name="AnyName" value="4" />
		<input type="text" name="TextName" /><br />

		<input type="checkbox" name="AnyName" value="5" />
		<input type="text" name="TextName" /><br />

		<input type="checkbox" name="AnyName" value="6" />
		<input type="text" name="TextName" /><br />

		<input type="checkbox" name="AnyName" value="7" />
		<input type="text" name="TextName" /><br />

		<input type="submit" name="Submit" value="Submit" />
	</form>
	</body>
</html>

When you run this code, you will see a series of checkboxes and text boxes. Notice the check boxes have a value attribute. When you post the form data, any check boxes with a check in them will get added to the comma delimited string. So... if check box 1 and check box 4 are selected, then Request.Form("AnyName") will hold the value "1,4".

With the text boxes, you will also get a comma delimited list.

This method may not be appropriate for your situation, but I do encourage you to spend a couple minutes with it to evaluate is usefulness.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Good call, George.

And another tip is that instead of checking for Request.QueryString("a") <> "", using Request.QueryString("a").Count <> 0 will let you detect parameters that ARE present but have an empty value. I THINK my syntax is right, but if not, it's something close.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Auguy
  • Locked
  • Question Question
Passing Table Variable to Update Table
Replies
2
Views
408
Auguy
Auguy
jimoo
  • Locked
  • Question Question
How to set variable to field in current row
Replies
2
Views
2K
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question Question
How to make pivot for features dynamically based on feature name and feature value ?
Replies
1
Views
463
TamarGranor
TamarGranor
trisoldee
  • Locked
  • Question Question
Query multiple DB's on same server for Dynamics GP
Replies
1
Views
342
trisoldee
trisoldee
penguinspeaks
  • Locked
  • Question Question
Undeclared variable error
Replies
2
Views
284
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top