Answered by:
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>
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
-
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/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+
Friday, August 5, 2011 2:10 PM -
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.SelectCommandTo 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