locked
Trying to pass a string variable into a sqldatasource RRS feed

  • 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>
    Friday, August 5, 2011 1:55 PM

Answers

  • 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();
            }

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 22, 2011 12:18 PM

All replies

  • 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

    Friday, August 5, 2011 9:06 PM
  • User3866881 posted

    Hello kristofour:)

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

     

    Monday, August 8, 2011 10:36 PM
  • 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

    Saturday, August 13, 2011 8:08 PM
  • 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+……;

    Saturday, August 13, 2011 9:50 PM
  • 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>
    Sunday, August 14, 2011 12:50 AM
  • 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);
                   }
    }

    Sunday, August 14, 2011 5:05 AM
  • 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??

    Sunday, August 14, 2011 12:33 PM
  • 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.

    Sunday, August 14, 2011 10:00 PM
  • 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 

    Sunday, August 14, 2011 11:37 PM
  • 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?

    Wednesday, August 17, 2011 3:21 AM
  • 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
    
     
    
    Wednesday, August 17, 2011 12:32 PM
  • 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);
           }

    Wednesday, August 17, 2011 9:20 PM
  • 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. 

    Wednesday, August 17, 2011 10:55 PM
  • User3866881 posted

    Welcome!

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

    Hehe ^_^

    Wednesday, August 17, 2011 10:59 PM
  • 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();
            }

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 22, 2011 12:18 PM