asked on

Dynamically setting the SqlDataSource.SelectCommand in C#

Hello,

I have been stuck on this for nearly 2 weeks now and can't see the problem. All Internet searches have shown what I have is correct, but it doesn't work for me.

Basically, I'm setting the Select Command for my SqlDataSource in my C# code behind.

When I have the query directly in the datasource, i.e.

<asp:SqlDataSource ID="SqlDataSourceAdmin" runat="server" ConnectionString="<%$ ConnectionStrings:SIMSDBConnection %>" ProviderName="<%$ConnectionStrings:SIMSDBConnection.ProviderName %>"

SelectCommand="SELECT * FROM [Staff_List] WHERE([FIRST NAME] LIKE @Search) ORDER BY [FIRST NAME]" OnSelecting="SqlDataSourceAdmin_Selecting"

This works fine - The @Search parameter is set correctly, etc. otherwise it wouldn't work.

As soon as I move this into the C# coding, it returns a blank gridview. I took out the query from the datasource and left the OnSelecting=SqlDataSourceAdmin_Selecting", which calls the C# function attached.

What am I missing?

Thanks =)

protected void SqlDataSourceAdmin_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
	string letter = Convert.ToString(e.Command.Parameters["@Search"].Value);
	
	if (letter == "ShowAll")
	{
		letter = "%";
	}
	else
	{
		letter = "" + letter + "%";
	}
 
	e.Command.Parameters["@Search"].Value = letter;
	
	SqlDataSourceAdmin.SelectCommand="SELECT * FROM [Staff_List] WHERE([FIRST NAME] LIKE @Search) ORDER BY [FIRST NAME]";
 
}

Open in new window

Juan_Barrera

Hi there,

The thing is that you don't need a parameter if you set it like that, try this to see if it works:

e.Command.CommandText = "SELECT * FROM [Staff_List] WHERE([FIRST NAME] LIKE '" + letter + "') ORDER BY [FIRST NAME]";
 
//anyway, this should work as well:
 
e.Command.CommandText = "SELECT * FROM [Staff_List] WHERE([FIRST NAME] LIKE @Search) ORDER BY [FIRST NAME]";

Open in new window

Anurag Thakur

I will be doing it this way and not be using any parameter

      string sql = "SELECT * FROM [Staff_List] WHERE([FIRST NAME] LIKE '"
      if (letter == "ShowAll")
        {
                sql += letter + "%' ORDER BY [FIRST NAME]";
        }
        else
        {
                sql += "%" + letter "%' ORDER BY [FIRST NAME]";
        }        
        SqlDataSourceAdmin.SelectCommand= sql;

Open in new window

SOLUTION

Gorkem Yuksel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sal-ee

ASKER

Thanks for the replies, guys.

However, none of them work and I had already tried without the parameters as Juan and ragi0017 suggested.

I also set a query in the PageLoad event, still nothing. Even with the query in the SqlDataSource itself, setting it dynamically doesn't "overwrite" it.

I don't know what else it could be...

sal-ee

ASKER

I've attached the relevant code snippets from my page so you can see what I've done.

Thanks.

******** PAGELOAD FUNCTION: ********
 
<script Language="C#" runat="server">
void Page_Load()
{
   // Some code for something else
 
// I set the query here
SqlDataSourceAdmin.SelectCommand="SELECT [ID], [FIRST NAME] AS FIRST_NAME, [SURNAME], [EXTENSION], [DEPARTMENT], [DIRECT LINE] AS DIRECT_LINE, [MOBILE NUMBER] AS MOBILE_NUMBER, [EMAIL], [OFFICE], [JOB TITLE] AS JOB_TITLE, [LOGIN], [ACTIVE] FROM [Staff_List] WHERE ([ACTIVE] = 'True') ORDER BY [FIRST NAME]";
 
}
 
</script>
 
/*******************************************************************************************/
**** DataSource Selecting Function ****
 
protected void SqlDataSourceAdmin_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
	string letter = Convert.ToString(e.Command.Parameters["@Search"].Value);
	
	if (letter == "ShowAll")
	{
		letter = "%";
	}
	else
	{
		letter = "" + letter + "%";
	}
 
	e.Command.Parameters["@Search"].Value = letter;
	
	SqlDataSourceAdmin.SelectCommand="SELECT [ID], [FIRST NAME] AS FIRST_NAME, [SURNAME], [EXTENSION], [DEPARTMENT], [DIRECT LINE] AS DIRECT_LINE, [MOBILE NUMBER] AS MOBILE_NUMBER, [EMAIL], [OFFICE], [JOB TITLE] AS JOB_TITLE, [LOGIN], [ACTIVE] FROM [Staff_List] WHERE([FIRST NAME] LIKE @Search) AND ([ACTIVE] = 'True') ORDER BY [FIRST NAME]";
 
}
 
/*******************************************************************************************/
**** ASP DataSource ****
 
<form id="form1" runat="server">
 
<asp:SqlDataSource ID="SqlDataSourceAdmin" runat="server" ConnectionString="<%$ ConnectionStrings:SIMSDBConnection %>"
ProviderName="<%$ ConnectionStrings:SIMSDBConnection.ProviderName %>" 			
			
OnSelecting="SqlDataSourceAdmin_Selecting"
			
InsertCommand="INSERT INTO [Staff_List] ([FIRST NAME], [SURNAME], [EXTENSION], [DEPARTMENT], [DIRECT LINE], [MOBILE NUMBER], [EMAIL], [OFFICE], [JOB TITLE], [LOGIN]) VALUES (@FirstName, @Surname, @Extension, @Department, @DirectLine, @Mobile, @Email, @Office, @JobTitle, @Login)" 
			
UpdateCommand="UPDATE [Staff_List] SET [FIRST NAME] = @FIRST_NAME, [SURNAME] = @SURNAME, [EXTENSION] = @EXTENSION, [DEPARTMENT] = @DEPARTMENT, [DIRECT LINE] = @DIRECT_LINE, [MOBILE NUMBER] = @MOBILE_NUMBER, [EMAIL] = @EMAIL, [OFFICE] = @OFFICE, [JOB TITLE] = @JOB_TITLE, [LOGIN] = @LOGIN WHERE [ID] = @ID" 
						
DeleteCommand="UPDATE [Staff_List] SET [ACTIVE] = 'False' WHERE [ID] = @ID">
			
  <SelectParameters>
     <asp:QueryStringParameter Name="Search" QueryStringField="Search" Type="String" />
  </SelectParameters>
			  
  <UpdateParameters>
     <asp:parameter Type="Int32" Name="ID" />
     <asp:parameter Type="String" Name="FIRST_NAME" />
     <asp:parameter Type="String" Name="SURNAME" />
     <asp:parameter Type="Double" Name="EXTENSION" />
     <asp:parameter Type="String" Name="DEPARTMENT" />
     <asp:parameter Type="String" Name="DIRECT_LINE" />
     <asp:parameter Type="String" Name="MOBILE_NUMBER" />
     <asp:parameter Type="String" Name="OFFICE" />
     <asp:parameter Type="String" Name="EMAIL" />
     <asp:parameter Type="String" Name="JOB_TITLE" />
     <asp:parameter Type="String" Name="LOGIN" />
     <asp:parameter Type="String" Name="ACTIVE" />
  </UpdateParameters>
			  
  <InsertParameters>
     <asp:parameter Type="String" Name="FirstName" />
     <asp:parameter Type="String" Name="Surname" />
     <asp:parameter Type="Double" Name="Extension" />
     <asp:parameter Type="String" Name="Department" />
     <asp:parameter Type="String" Name="DirectLine" />
     <asp:parameter Type="String" Name="Mobile" />
     <asp:parameter Type="String" Name="Email" />
     <asp:parameter Type="String" Name="Office" />
     <asp:parameter Type="String" Name="JobTitle" />
     <asp:parameter Type="String" Name="Login" />
  </InsertParameters>
			  
  <DeleteParameters>
     <asp:parameter Type="String" Name="ID" />
  </DeleteParameters>
			  
</asp:sqldatasource>

Open in new window

ASKER CERTIFIED SOLUTION

Juan_Barrera

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sal-ee

ASKER

Juan,

It's funny you suggested that because I already tried doing it that way this morning, and found that it works but not with the parameter @Search. It works with: LIKE " + letter + "...

Thing is, I am trying to find why it doesn't work using the DataSource ID, i.e. SqlDataSourceAdmin.SelectCommand, because I need to set another query in a different function for a button, and I don't think I can use the e argument with a button event?

I need to know what's missing in order to be able to dynamically set the queries in other functions...

Juan_Barrera

If the query is going to cause the SqlDataSource to execute it's "Select" machinery, then you'll be able to use the "Selecting" event with the "e" parameter. If not, then there is no really a reason for changing something that is not going to be used :)
Or is there something I'm missing from your scenario?

sal-ee

ASKER

Sorry if I'm being dim, but I'm not quite sure what you mean.

I don't think any part won't be used here. All I know is, I need to set the Select queries dynamically because they are search facilities on the page for the user. One of which, is a button, and I need to recreate a different query for this particular search.

I believe SqlDataSourceSelectingEventArgs e can not be set in a button function, has to be:

void search(object sender, EventArgs e)

Hope that makes sense!

SOLUTION

Juan_Barrera

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sal-ee

ASKER

Ahhh OK, I understand now. I have tried putting my queries in conditional statements within the "Selecting" event, but I struggled to get it working.

I'm a beginner in C# and ASP.Net so please bear with me. I will look at doing it the way you suggested, and get back to you tomorrow.

Thank you very much for your time today :)

Gorkem Yuksel

Once you have created/set the SelectCommand, you must call it by using the Select() method. That will trigger the DataSource to do an actual query.

Or, you can always call the DataBind() method of the control that it is bound to. That will also force the datasource to pull information.

Cheers,

G.

Juan_Barrera

Yeah, take you time! Let me know how it goes..

sal-ee

ASKER

OK guys, I have done it this way. It's working but I feel it isn't quite "neat"?

I've added a new parameter called "Name" as well as "Search", but can't use them in the queries! It returns nothing, but I have to leave the e.Command.Parameters bit outside of the conditional statements. From what I can see, it works this way because it virtually takes the query from the function and puts it in the SQL DS, therefore needing the parameters?

Don't know if that makes sense.

The button calls the searchName function.

void searchName(object sender, EventArgs e)
{
	string URL = "staff_admin.aspx?Name=" + txtSearch.Text;
	string strSearch = txtSearch.Text;	
	
	// the textbox contains a search criteria
	if (strSearch != "")
	{
		Response.Redirect(URL);
	}
}
 
/*******************************************************************************************/
 
protected void SqlDataSourceAdmin_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
	string letter = Convert.ToString(e.Command.Parameters["@Search"].Value);
	string strSearch = Convert.ToString(e.Command.Parameters["@Name"].Value);	
		
	e.Command.CommandText = "SELECT [ID], [FIRST NAME] AS FIRST_NAME, [SURNAME], [EXTENSION], [DEPARTMENT], [DIRECT LINE] AS DIRECT_LINE, [MOBILE NUMBER] AS MOBILE_NUMBER, [EMAIL], [OFFICE], [JOB TITLE] AS JOB_TITLE, [LOGIN], [ACTIVE] FROM [Staff_List] WHERE ([ACTIVE] = 'True') ORDER BY [FIRST NAME]";
	
	if (letter != "")
	{
		letter = "'" + letter + "%'";
		e.Command.CommandText = "SELECT [ID], [FIRST NAME] AS FIRST_NAME, [SURNAME], [EXTENSION], [DEPARTMENT], [DIRECT LINE] AS DIRECT_LINE, [MOBILE NUMBER] AS MOBILE_NUMBER, [EMAIL], [OFFICE], [JOB TITLE] AS JOB_TITLE, [LOGIN], [ACTIVE] FROM [Staff_List] WHERE([FIRST NAME] LIKE "+letter+") AND ([ACTIVE] = 'True') ORDER BY [FIRST NAME]";
	}
 
	if (strSearch != "")
	{
		strSearch = "'%" + strSearch + "%'";
		e.Command.CommandText = "SELECT [ID], [FIRST NAME] AS FIRST_NAME, [SURNAME], [EXTENSION], [DEPARTMENT], [DIRECT LINE] AS DIRECT_LINE, [MOBILE NUMBER] AS MOBILE_NUMBER, [EMAIL], [OFFICE], [JOB TITLE] AS JOB_TITLE, [LOGIN], [ACTIVE] FROM [Staff_List] WHERE([FIRST NAME] LIKE "+strSearch+") OR ([SURNAME] LIKE "+strSearch+") AND ([ACTIVE] = 'True') ORDER BY [FIRST NAME]";
	}
	
	e.Command.Parameters["@Search"].Value = letter;
	e.Command.Parameters["@Name"].Value = strSearch;
}

Open in new window

Juan_Barrera

I'm not quite sure why you need to set both the parameters AND the select statement. You should be able to use only one of these.

sal-ee

ASKER

I don't know either, because if I take the parameters bit out, it doesn't work... but yet I can't use the parameter in the query!

Juan_Barrera

Why it doesn't work? It gives you an error or the query doesn't execute properly?

sal-ee

ASKER

Probably because the query doesn't execute properly, as I get a blank gridview. Actually, I have a message appear when there are no rows in the gridview, and this message is displayed when I have the parameters in the queries, therefore no records have been retrieved...

Juan_Barrera

Mmm...don't know really...

sal-ee

ASKER

No worries, it works anyway, eventhough I'm annoyed that it wouldn't work the more elegant way as it should do!

I will look into it again when I have time.

Thanks for your help everyone.

sal-ee

ASKER

Thanks for your help all :)