Trying to pass a string variable into a sqldatasource

Question

User606090160 posted

i have a large table that i am querying where only certain fields need to included into in the query statement.  I was somewhat successful in achieving this using operators and strings in the query statement in code behind, however, i have a class that we are using that requires a sqldatasource.  I want to pass the logic from code behind into a sqldatasource but i am struggling.  I hope someone can please help!  i attempted this using hidden fields to pass the string from code behind into the sqldatasource but to no avail. 

protected void ddlSelectPeriod_SelectedIndexChanged(object sender, EventArgs e)
        {
            MembershipUser myObject = Membership.GetUser();
            string UserId = myObject.ProviderUserKey.ToString();
            hfUserId.Value = UserId;
            
                string course = "";
                if (ddlSelectPeriod.SelectedValue.Contains("1"))
                    course = "1";
                else if (ddlSelectPeriod.SelectedValue.Contains("2"))
                    course = "2";
                else if (ddlSelectPeriod.SelectedValue.Contains("3"))
                    course = "3";
                else if (ddlSelectPeriod.SelectedValue.Contains("4"))
                    course = "4";
                else if (ddlSelectPeriod.SelectedValue.Contains("5"))
                    course = "5";
                else if (ddlSelectPeriod.SelectedValue.Contains("6"))
                    course = "6";
                if (!string.IsNullOrEmpty(course))

                    hfCourse.Value = course;
                gridClasses.DataSourceID = "SqlDataSource1";
          
        }

<asp:HiddenField ID="hfUserId" runat="server" />
                                <asp:HiddenField ID="hfCourse" runat="server" />
                                <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                                    ConnectionString="<%$ ConnectionStrings:wilsonhsconnectionstring %>" 
                                    
                                    ProviderName="<%$ ConnectionStrings:wilsonhsconnectionstring.ProviderName %>"  
                                    SelectCommand="SELECT Students.FirstName, Students.LastName, Classes.P" + course + "CourseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P" + course + "teacher WHERE UserDetail.UserId = @UserId", course="hfCourse" >
                                    
                                    <SelectParameters>
                                        <asp:ControlParameter Name="UserId" ControlID="hfUserId" PropertyName="Value"/>
                                    </SelectParameters>
                                </asp:SqlDataSource>

Answer 1

User-843484705 posted

Use String value like

http://forums.asp.net/t/1249232.aspx

http://forums.asp.net/t/949925.aspx

http://www.tek-tips.com/viewthread.cfm?qid=1641909&page=4

http://forums.asp.net/p/1670907/4374934.aspx/1?How+to+pass+parameters+into+SqlDataSource+on+RowUpdating+

http://forums.asp.net/t/1670912.aspx/1?How+to+pass+parameters+into+SqlDataSource+on+RowUpdating+

http://forums.asp.net/t/1702019.aspx/1?SqlDataSource+Variable+Parameter+Interaction+in+C+

http://forums.asp.net/t/1640235.aspx

Answer 2

User606090160 posted

thank you for responding the examples provided were primarily for passing string parameters where fortunately i am not having an issue with.  i have a field in my query statement that has a dynamic variable based on the selection from the drop down.  please see code example.  thanks

Answer 3

User3866881 posted

Hello kristofour：）

If I'm not misunderstanding your meaning, You want to dynamically change the DataSourceId when Dropdownlist's SelectedChanged???

 

Answer 4

User606090160 posted

Actually i want to able to dynamically change a field in my select query.  e.g., a drop down list will change the field dynamically so end user selects period 1 query inlcudes fields: studentID, period1ID, period1Teacher...etc, a period 2 select will be: studentID, period2ID, period2Teacher, etc...the idea is to make numeric value a variable, i was able to do it in code behind, but it doesnt work right with the class that was created it works best with a sqdatasource hence the reason why i want to attempt to do it with using one sqldatasource.  Any ideas greatly appreciated..thanks again

-kris

Answer 5

User3866881 posted

a drop down list will change the field dynamically so end user selects period 1 query inlcudes fields: studentID, period1ID, period1Teacher...etc, a period 2 select will be: studentID, period2ID, period2Teacher, etc...the idea is to make numeric value a variable,

Hello：）

You can try the following things：

1）Do plz make sure that your SqlDataSource's Select Statement is "Select studentID, period1ID, period1Teacher From xxx" in Default in your aspx codes.

2）Plz make your Dropdownlist DataTextField bound to Fields like Choice 1,2,3……. And your DataValueField is 1,2,3……

3）And then make your Dropdownlist's AutoPostBack=true.

4）In the end, do this plz——

SqlDataSource.SelectCommand="select StudentId,"+"ColumnName+Dropdownlist1.SelectedValue+……;

Answer 6

User606090160 posted

Hi thanks so much for helping me this is becoming a great lesson!  So it looks like your example will work for one field, except there are two fields that need changing.  

I was working on the below and the query appears to be dynamically changing properly except now for some reason it does not render any results i.e., empty gridview!

Thanks Again!

protected void ddlSelectPeriod_SelectedIndexChanged(object sender, EventArgs e)
        {
            MembershipUser myObject = Membership.GetUser();
            string UserId = myObject.ProviderUserKey.ToString();
           
            
                string course = "";
                if (ddlSelectPeriod.SelectedValue.Contains("1"))
                    course = "1";
                else if (ddlSelectPeriod.SelectedValue.Contains("2"))
                    course = "2";
                else if (ddlSelectPeriod.SelectedValue.Contains("3"))
                    course = "3";
                else if (ddlSelectPeriod.SelectedValue.Contains("4"))
                    course = "4";
                else if (ddlSelectPeriod.SelectedValue.Contains("5"))
                    course = "5";
                else if (ddlSelectPeriod.SelectedValue.Contains("6"))
                    course = "6";
                if (!string.IsNullOrEmpty(course))
   
                
            SqlDataSource1.SelectParameters.Add("@UserId", UserId);
            SqlDataSource1.SelectCommand= String.Format("SELECT Students.FirstName, Students.LastName, Classes.P{0}courseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P{0}TEACHER WHERE UserDetail.UserId = @UserId", course);
}

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                                    ConnectionString="<%$ ConnectionStrings:wilsonhsconnectionstring %>" 
                                    ProviderName="<%$ ConnectionStrings:wilsonhsconnectionstring.ProviderName %>" 
                                    SelectCommand="SELECT Students.FirstName, Students.LastName, Classes.p1CourseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P1TEACHER WHERE (UserDetail.UserId = @UserId)">
                                        <SelectParameters>
                                            <asp:QueryStringParameter Name="UserId" QueryStringField="UserId" />
                                        </SelectParameters>
                                </asp:SqlDataSource>
                                    </li>
                        </ul>
                        <asp:GridView ID="gridClasses" runat="server" AutoGenerateColumns="False" 
                                    DataKeyNames="StudentID,UserDetail_ID" DataSourceID="SqlDataSource1" 
                                    EmptyDataText="No data">
                                <Columns>
                                    <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
                                        SortExpression="FirstName" />
                                    <asp:BoundField DataField="LastName" HeaderText="LastName" 
                                        SortExpression="LastName" />
                                    <asp:BoundField DataField="P1CourseID" HeaderText="P1CourseID" 
                                        SortExpression="P1CourseID" />
                                    <asp:BoundField DataField="StudentID" HeaderText="StudentID" ReadOnly="True" 
                                        SortExpression="StudentID" />
                                </Columns>
                                </asp:GridView>

Answer 7

User3866881 posted

Hello kristofour：）

Hello again：）

Plz make sure that your Dropdownlist's AutoPostBack = True, and then do this (Attention to my bold statement)：）

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                                    ConnectionString="<%$ ConnectionStrings:wilsonhsconnectionstring %>" 
                                    ProviderName="<%$ ConnectionStrings:wilsonhsconnectionstring.ProviderName %>" 
                                    SelectCommand="SELECT Students.FirstName, Students.LastName, Classes.p1CourseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P1TEACHER WHERE (UserDetail.UserId = @UserId)">
                                        <SelectParameters>
                                            <asp:Parameter Name="UserId" Type="Int32" DefaultValue = "0" />
                                        </SelectParameters>
                                </asp:SqlDataSource>

protected void Page_Load(……)

{

      if(!IsPostBack)
      {

                 SqlDatasource1.SelectParameters["UserId"].DefaultValue = Request.QueryString("UserId");
                 SqlDatasource1.DataBind();

      }
}

protected void ddlSelectPeriod_SelectedIndexChanged(object sender, EventArgs e)
        {
            MembershipUser myObject = Membership.GetUser();
            string UserId = myObject.ProviderUserKey.ToString();
           
            
                string course = "";
                if (ddlSelectPeriod.SelectedValue.Contains("1"))
                    course = "1";
                else if (ddlSelectPeriod.SelectedValue.Contains("2"))
                    course = "2";
                else if (ddlSelectPeriod.SelectedValue.Contains("3"))
                    course = "3";
                else if (ddlSelectPeriod.SelectedValue.Contains("4"))
                    course = "4";
                else if (ddlSelectPeriod.SelectedValue.Contains("5"))
                    course = "5";
                else if (ddlSelectPeriod.SelectedValue.Contains("6"))
                    course = "6";
                if (!string.IsNullOrEmpty(course))
                {
                 
            SqlDataSource1.SelectCommand= String.Format("SELECT Students.FirstName, Students.LastName, Classes.P{0}courseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P{0}TEACHER WHERE UserDetail.UserId = @UserId", course);
     SqlDataSource1.SelectParameters.Clear();
     SqlDataSource1.SelectParameters.Add("@UserId", UserId);
               }
}

Answer 8

User606090160 posted

Hello Decker and thanks again, almost there...except i'm getting an exception of...Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerServerErrorException: Must declare the scalar variable "@UserId"....a breakpoint indicates that the userid value is indeed there??

Answer 9

User3866881 posted

Hello again：）

Plz debug your app and drop a break point onto my codes inside the DropdownList_SelectIndexChanged——

   if (!string.IsNullOrEmpty(course))
                {
                
            SqlDataSource1.SelectCommand

To see what's the SelectCommand.

Answer 10

User606090160 posted

Hello!  well the variable is changing accordingly...i selected Period 3 from the dropdown this is what i got from the breakpoint at "SelectCommand"

SELECT Students.FirstName, Students.LastName, Classes.P3courseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P3TEACHER WHERE UserDetail.UserId = @UserId 

Answer 11

User3866881 posted

i'm getting an exception of...Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerServerErrorException: Must declare the scalar variable "@UserId"....a breakpoint indicates that the userid value is indeed there??

Hello again：）

Being busy these days.... sorry for late....

Where did you find the err message?? at which event?

Answer 12

User606090160 posted

The error is occuring immediately after SelectedIndexChange event... I have included a stack trace of the error..Thanks Again!

Must declare the scalar variable "@UserId". 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserId".

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[SqlException (0x80131904): Must declare the scalar variable "@UserId".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1951274
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4848827
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2394
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1297
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +19
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +17
   System.Web.UI.Control.PreRenderRecursiveInternal() +80
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

 

Answer 13

User3866881 posted

Hello again：）

I think it's time for us to change to another way——

1）Plz first remove SqlDataSource And then write a private function in the cs file——

private void BindResult(string id,string userid)
{
   string s = string.Format("SELECT Students.FirstName, Students.LastName, Classes.P{0}courseID, Classes.StudentID FROM Classes LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P{0}TEACHER WHERE UserDetail.UserId = @UserId",id);

   using (SqlDataAdapter adapter = new SqlDataAdapter(s,new SqlConnection("Your conn str"))
   {
         DataTable dt = new DataTable();
         adapter.SelectCommand.Parameters["@userId"].Value=userid;
         adapter.Fill(dt);
         GridView1.DataSource = dt;
         GridView1.DataBind();
   }
}

2）Then call it in the Page_Load as:

if(!IsPostBack)

{

      BindResult(1,Request.QueryString("userId"));

}

3）In the SelectIndexChanged event:

protected void ddlSelectPeriod_SelectedIndexChanged(object sender, EventArgs e)
        {
            MembershipUser myObject = Membership.GetUser();
            string UserId = myObject.ProviderUserKey.ToString();
            hfUserId.Value = UserId;
            
                string course = "";
                if (ddlSelectPeriod.SelectedValue.Contains("1"))
                    course = "1";
                else if (ddlSelectPeriod.SelectedValue.Contains("2"))
                    course = "2";
                else if (ddlSelectPeriod.SelectedValue.Contains("3"))
                    course = "3";
                else if (ddlSelectPeriod.SelectedValue.Contains("4"))
                    course = "4";
                else if (ddlSelectPeriod.SelectedValue.Contains("5"))
                    course = "5";
                else if (ddlSelectPeriod.SelectedValue.Contains("6"))
                    course = "6";
                if (!string.IsNullOrEmpty(course))

                    hfCourse.Value = course;
       
                    BindResult(hfCourse,UserId);
       }

Answer 14

User606090160 posted

Oh ok, i had it working prior using a private function too its just that i have class that does a gridview grouping event someone else created that requires a sqldatasource, problem was it didnt work other than from a sqldatasource!!!!  hence the struggle! OK, well then i appreciate your expertise on this matter. 

Answer 15

User3866881 posted

Welcome!

If you are kind enough, share us your codes and your happiness...

Hehe ^_^

Answer 16

User606090160 posted

well its not from a sqldatasource but it works

protected void ddlSelectPeriod_SelectedIndexChanged(object sender, EventArgs e)
        {
            MembershipUser myObject = Membership.GetUser();
            string UserId = myObject.ProviderUserKey.ToString();

            string course = "";
            if (ddlSelectPeriod.SelectedValue.Contains("1"))
                course = "1";
            else if (ddlSelectPeriod.SelectedValue.Contains("2"))
                course = "2";
            else if (ddlSelectPeriod.SelectedValue.Contains("3"))
                course = "3";
            else if (ddlSelectPeriod.SelectedValue.Contains("4"))
                course = "4";
            else if (ddlSelectPeriod.SelectedValue.Contains("5"))
                course = "5";
            else if (ddlSelectPeriod.SelectedValue.Contains("6"))
                course = "6";
            if (!string.IsNullOrEmpty(course))
                BindGridViewData(course, UserId);

        }

        private void BindGridViewData(string course, string UserId)
        {
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["wilsonhsConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection; cmd.CommandText = String.Format("SELECT Classes.StudentID, (Students.FirstName)  + ' ' +  (Students.LastName) AS Name, Classes.p{0}CourseID, StudentScore.score, Assignments.AssignmentName FROM Classes LEFT OUTER JOIN Assignments ON Classes.p{0}CourseID = Assignments.courseID LEFT OUTER JOIN StudentScore ON Assignments.AssignmentID = StudentScore.AssignmentID LEFT OUTER JOIN Students ON Classes.StudentID = Students.StudentID LEFT OUTER JOIN UserDetail ON UserDetail.sisID = Classes.P{0}TEACHER WHERE (UserDetail.UserId = @UserId)", course);
            cmd.Parameters.Add(new SqlParameter("@UserId", UserId));

            connection.Open();
            cmd.ExecuteNonQuery();
            SqlDataReader reader = cmd.ExecuteReader();

            gridClasses.DataSource = reader;
            gridClasses.DataBind();

            connection.Close();
        }