Compare form field value to an SQL entry [SQL2012]

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

General SQL Server Forums

New to SQL Server Programming

Compare form field value to an SQL entry [SQL2012]

Author

Topic

jgardounis
Starting Member

2 Posts

Posted - 2012-09-18 : 10:06:51

I am having a problem how to compare form field values to a value in an SQL entry, through a stored procedure.

For example, this is the stored procedure:


ALTER PROCEDURE [dbo].[vts_spVoterExportCSVData]
				@SurveyID int,
				@StartDate datetime,
				@EndDate datetime
AS

SELECT  SUBSTRING(Q.QuestionText,1,20) as QuestionText,Q.QuestionId,
 AnswerID,SelectionModeId,AnswerTypeId, 
SUBSTRING(Q.QuestionText,1,20)+'...'+' | '+ AnswerText   as ColumnHeader ,
AnswerText,
Q.DisplayOrder QuestionDisplayOrder,
Q.QuestionId,
Q.Alias QuestionAlias,
Q.QuestionIdText QuestionIdText,
A.DisplayOrder AnswerDisplayOrder,
A.AnswerId ,
A.AnswerAlias,Q.ParentQuestionid,
	case when q.parentQuestionId is null then null
	else (select count(*)+1 from vts_tbquestion q1 
	         where q1.parentquestionid=q.parentquestionid
	         and   q1.questionid<q.questionid
	         ) 
	end as roworder,
	case when q.parentQuestionId is null then null
	else (select QuestionText from vts_tbquestion q1 
	         where q1.questionid=q.parentquestionid
	         ) 
	end as ParentQuestiontext,
	case when q.parentQuestionId is null then null
	else (select QuestionIdText from vts_tbquestion q1 
	         where q1.questionid=q.parentquestionid
	         ) 
	end as ParentQuestionIdtext,
	case when q.parentQuestionId is null then null
	else (select ALIAS from vts_tbquestion q1 
	         where q1.questionid=q.parentquestionid
	         ) 
	end as ParentQuestionAliastext,
A.AnswerIDText AnswerIdText
 FROM vts_tbQuestion Q
INNER JOIN vts_tbAnswer A
	ON A.QuestionID = Q.QuestionID
WHERE 
	SurveyID = @SurveyID  
ORDER BY Q.DisplayOrder, Q.QuestionID, A.DisplayOrder

SELECT
	V.VoterID,
	V.VoteDate,
	V.StartDate,
	V.IPSource,
	V.ContextUserName as username,
	(SELECT sum(ScorePoint) FROM vts_tbVoter 
		INNER JOIN vts_tbVoterAnswers
			ON vts_tbVoterAnswers.VoterID = vts_tbVoter.VoterID
		INNER JOIN vts_tbAnswer
			ON vts_tbAnswer.AnswerID = vts_tbVoterAnswers.AnswerID
		WHERE vts_tbVoter.VoterID = V.VoterID) AS Score
	FROM vts_tbVoter V
	WHERE 
		V.SurveyID = @SurveyID AND
		V.Validated <> 0 AND
		DATEDIFF (d,@startDate,V.VoteDate) >= 0 AND DATEDIFF (d,@endDate,V.VoteDate) <= 0 AND
	        V.VoterID IN (SELECT VoterID FROM vts_tbVoterAnswers WHERE AnswerID='31')
	ORDER BY V.VoterID DESC
 
SELECT
	V.VoterID,
	VA.AnswerID,
	SectionNumber,
	VA.AnswerText,
	AnswerTypeId,
	SelectionModeId,
	Q.QuestionId,
	A.AnswerText AnswerAnswerText,
	A.DisplayOrder AnswerDisplayOrder,
A.AnswerAlias,
A.AnswerIDText AnswerIdAlias
FROM vts_tbVoterAnswers VA
INNER JOIN vts_tbVoter V
	ON V.VoterID = VA.VoterID
INNER JOIN vts_tbAnswer A
    ON VA.AnswerId=A.AnswerId
INNER JOIN vts_tbQuestion Q
     ON A.QuestionId=Q.QuestionId
WHERE 
	V.SurveyID = @SurveyID AND
	V.Validated <> 0 AND
	DATEDIFF (d,@startDate,V.VoteDate) >= 0 AND DATEDIFF (d,@endDate,V.VoteDate) <= 0 AND
	V.VoterID IN (SELECT VoterID FROM vts_tbVoterAnswers WHERE AnswerID='31')
	
ORDER BY V.VoterID DESC

What this does is collect the proper data from four tables:
- vts_tbQuestion: Q
- vts_tbAnswer: A
- vts_tbVoterAnswers: VA
- vts_tbVoter: V

And pushes them for extraction in CSV file.

the following line:


V.VoterID IN (SELECT VoterID FROM vts_tbVoterAnswers WHERE AnswerID='31')

..actually looks for the VoterID entry in the vts_tbVoterAnswers table where the entry of the AnswerID='31'.

What I want to do is have the value of the AnswerID be taken from an asp form field with the ID: ddlSeed. I considered adding an SQL variable: @Seed which should get the value and simply change the above code into:

V.VoterID IN (SELECT VoterID FROM vts_tbVoterAnswers WHERE AnswerID=@Seed)

But the problem is I dont know how to supply @Seed with a value from the specific form field.

Anyone has any idea how to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 10:46:18

add a parameter as you did and link it to asp like below

http://support.microsoft.com/kb/164485

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jgardounis
Starting Member

2 Posts

Posted - 2012-09-19 : 03:13:11

Thank you for the reply. I am not sure how to implement this in an ASP form. Let me show you the code:


<%@ Page Language="c#" MasterPageFile="MsterPageTabs.master" AutoEventWireup="false"
    Inherits="Votations.NSurvey.WebAdmin.ExportData" CodeBehind="ExportData.aspx.cs" %>

<%@ Register TagPrefix="uc1" TagName="HeaderControl" Src="UserControls/HeaderControl.ascx" %>
<%@ Register TagPrefix="uc1" TagName="FooterControl" Src="UserControls/FooterControl.ascx" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
<script type="text/javascript">
        $(function () {

            $("#<%=StartDateTextBox.ClientID%>").datepicker();
            $("#<%=EndDateTextBox.ClientID%>").datepicker();
        });
    </script>
<table summary="maintable" class="TableLayoutContainer">
<tr>
<td class="contentCell" valign="top">
<asp:Label ID="MessageLabel" runat="server" CssClass="ErrorMessage" Visible="False"></asp:Label>
  <table summary="exporttable" class="innerText">
  <tr>
  <td>
        <table summary="exportlabeltable" width="100%">
        <tr>
        <td width="99%">
           <font class="titleFont">
           <asp:Literal ID="SurveyExportTitle" runat="server" EnableViewState="False">Survey CSV export</asp:Literal></font>
        </td>
        </tr>
        </table>
  </td>
  </tr>
  <tr>
  <td> <br />
        <table summary="exporttypetable" class="innerText">
        <tr>
        <td>
          <strong>
          <asp:Literal ID="ExportTypeLabel" runat="server" EnableViewState="False" Text="Export type" /></strong>
        </td>
        <td>
          <asp:DropDownList ID="ExportDropDownList" runat="server" AutoPostBack="True">
          </asp:DropDownList>
        </td>
        </tr>
        <tr>
        <td colspan="2">
            <asp:Label ID="info1Label" runat="server">: Note: XML format must be chosen to import the data to another Survey installation.</asp:Label>
        </td>
        </tr>
            <asp:PlaceHolder ID="plhCSVStyles" runat="server">
        <tr>
        <td colspan="2"><br />
            <font class="titleFont">
            <asp:Label ID="layoutLabel" runat="server">Export Layout</asp:Label></font>
        </td>
        </tr>
        <tr>
        <td colspan="2"><br />
             <asp:RadioButton ID="rdStyle2" AutoPostBack="true" GroupName="StylesGroup" runat="server"
                                                Text="Export each response as a Row with one Column per possible answer to each Question" />
        </td>
        </tr>
        <tr>
        <td colspan="2">
             <asp:RadioButton ID="rdStyle1" AutoPostBack="true" GroupName="StylesGroup" Checked="true"
                                                runat="server" Text="Export each response as a Row with one Column per Question" />
        </td>
        </tr>
             <asp:PlaceHolder ID="plhDdls" runat="server" Visible="true">
        <tr>
        <td><strong>
             <asp:Label ID="HeaderFieldLabel" runat="server">Header field:</asp:Label></strong>
        </td>
        <td>
             <asp:DropDownList ID="ddlHeader" runat="server">
             <asp:ListItem Value="Question">Question</asp:ListItem>
             <asp:ListItem Value="QuestionDisplayOrderNumber">Question Display Order Number</asp:ListItem>
             <asp:ListItem Value="QuestionID">Question ID</asp:ListItem>
             <asp:ListItem Value="QuestionAlias">Question Alias</asp:ListItem>
             <asp:ListItem Value="QuestionIDAlias">Question ID Alias</asp:ListItem>
             </asp:DropDownList>
        </td>
        </tr>
        <tr>
        <td><strong>
             <asp:Label ID="answerFieldLabel" runat="server">Answer field:</asp:Label></strong>
        </td>
        <td>
             <asp:DropDownList ID="ddlAnswer" runat="server">
             <asp:ListItem Value="Answer">Answer</asp:ListItem>
             <asp:ListItem Value="AnswerDisplayOrderNumber">Answer Display Order Number</asp:ListItem>
             <asp:ListItem Value="AnswerID">Answer ID</asp:ListItem>
             <asp:ListItem Value="AnswerAlias">Answer Alias</asp:ListItem>
             <asp:ListItem Value="AnswerIdAlias">Answer Id Alias</asp:ListItem>
             </asp:DropDownList>
        </td>
        </tr>
             </asp:PlaceHolder>
             </asp:PlaceHolder>
             <asp:PlaceHolder ID="CSVOptionPlaceHolder" runat="server">
        <tr>
        <td  class="innerText">
             <font class="subtitleFont" >
             <asp:Literal ID="formatlabel" runat="server" EnableViewState="False" Text="Format"></asp:Literal>
             </font>
        </td>
        <td>
        </td>
        </tr>
        <tr>
        <td width="155">
              <strong>
              <asp:Literal ID="FieldDelimiterLabel" runat="server" EnableViewState="False" Text="Field delimiter"></asp:Literal></strong>
        </td>
        <td>
              <asp:TextBox ID="FieldDelimiterTextBox" runat="server" Columns="1" MaxLength="1">,</asp:TextBox>
        </td>
        </tr>
        <tr>
        <td width="155">
               <strong>
               <asp:Literal ID="TextDelimiterLabel" runat="server" EnableViewState="False">Text delimiter:</asp:Literal></strong>
        </td>
        <td>
               <asp:TextBox ID="TextDelimiterTextBox" runat="server" Columns="1" MaxLength="1">"</asp:TextBox>
        </td>
        </tr>
        <tr>
        <td width="155">
               <strong>
               <asp:Literal ID="MultiSeperatorLabel" runat="server" EnableViewState="False">Multiple Choice Separator</asp:Literal></strong>
        </td>
        <td>
               <asp:TextBox ID="MultiSeparatorTextBox" Text="#" runat="server" Columns="1" MaxLength="1"></asp:TextBox>
        </td>
        </tr>
        <tr>
        <td width="155">
               <strong>
               <asp:Literal ID="ReplaceCarriageLabel" runat="server" EnableViewState="False">Carriage return:</asp:Literal></strong>
        </td>
        <td>
               <asp:DropDownList ID="CarriageReturnDropDownList" runat="server" AutoPostBack="True">
               </asp:DropDownList>
                <asp:TextBox ID="CRCharTextbox" runat="server" Visible="False" Columns="1"
                                                MaxLength="255"></asp:TextBox>
        </td>
        </tr>
               </asp:PlaceHolder>
        <tr>
        <td class="innerText"><br />
               <font class="titleFont">
               <asp:Label runat="server" ID="dataSelectionLabel" Text="Data Selection"></asp:Label>
               </font>
        </td>
        </tr>
        <tr>
        <td width="155"><br />
               <asp:RadioButton Checked="true" ID="rdAllDates" runat="server" GroupName="DateOption"
                                            Text="all data" />
        </td>
        <td><br />
               <asp:RadioButton ID="rdSelectedDates" runat="server" GroupName="DateOption" Text="only data in the selected date range" />
        </td>
        </tr>
        <tr>
        <td width="155">
               <strong>
               <asp:Literal ID="ExportFromDateLabel" runat="server" EnableViewState="False" Text="Export from date :"></asp:Literal></strong>
        </td>
        <td>
               <asp:TextBox ID="StartDateTextBox" runat="server" Columns="8"></asp:TextBox>
        </td>
        </tr>
        <tr>
        <td width="155">
               <strong>
               <asp:Literal ID="ExportToDateLabel" runat="server" EnableViewState="False" Text="To date :"></asp:Literal></strong>
        </td>
        <td>
        <asp:TextBox ID="EndDateTextBox" runat="server" Columns="8"></asp:TextBox>
        </td>
        </tr>                                
        <tr>
        <td width="155"><br />
               <strong>
               <asp:Literal ID="GenderLabel" runat="server" EnableViewState="False">F??? : </asp:Literal></strong>
        </td>
        <td>
               <asp:DropDownList ID="ddlGender" runat="server">
               <asp:ListItem Value=""> ???? </asp:ListItem>
               <asp:ListItem Value="57"> ??d?e? </asp:ListItem>
               <asp:ListItem Value="58"> G??a??e? </asp:ListItem>
               <asp:ListItem Value="59"> ???? </asp:ListItem>
               </asp:DropDownList>
        </td>
        </tr>
        <tr>
        <td width="155"><br />
               <strong>
               <asp:Literal ID="SeedLabel" runat="server" EnableViewState="False">Seed : </asp:Literal></strong>
        </td>
        <td>
               <asp:DropDownList ID="ddlSeed" runat="server">
               <asp:ListItem Value=""> All </asp:ListItem>
               <asp:ListItem Value="32"> Yes </asp:ListItem>
               <asp:ListItem Value="31"> No </asp:ListItem>
               </asp:DropDownList>
        </td>
        </tr>
        </table>
               <br />
        </td>
        </tr>
        </table>
               <asp:Button ID="ExportDataButton" runat="server" Text="Export CSV"></asp:Button>
               <asp:Button ID="VoterExportXMLButton" runat="server" Text="Export XML" Visible="False">
               </asp:Button>
        </td>
        </tr>
    </table>
</asp:Content>

I imagine the code I will have to use from the link you shared will look like this:


        <%  cmd.CommandText = "vts_spVoterExportCSVData"
            cmd.CommandType = adCmdStoredProc
 	    cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _adParamReturnValue)
   	    cmd.Parameters.Append cmd.CreateParameter("@Seed", adInteger, _adParamInput)
	    cmd("@Seed") = 31
	    cmd.Execute
	%>

But Im not sure how to apply it in the form.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 11:45:53

i'm not an expert on asp and this is not asp forum either.
You may be better off posting it in some asp forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources