SelectCommand="SELECT ID, AuthorFirstName, AuthorLastName, SessionTitle FROM aacr WHERE ?ColumnName LIKE Concat('%', ?SessionTitle, '%')">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="ColumnName" PropertyName="SelectedValue" Type="String" DefaultValue="NULL" />
<asp:ControlParameter ControlID="sessiontitleTextBox" DefaultValue="NULL"
Name="SessionTitle" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The problem is, that when I get the "ColumnName" out of the DropDownList, it comes with apostrophes around, and obviously, when you say WHERE 'AuthorFirstName' = 'John' , for example, mysql doesn't know the 'AuthorFirstName', because it is called AuthorFirstName
without the apostrophes. Could anyone tell me, please, how to get rid of the apostrophes in the WHERE clause?
The SqlDataSource connects to SQL Server only. You will have to use an OleDBDataSoure with the proper provider for MySQL or an ODBCDataSource with the proper driver.
If you are using appropriate DataSource, the apostropy things you can handle in code behind.
So i basically construct all the select statements and populate the GridView from the code behind like this:
MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["atsConnectionString"].ConnectionString);
MySqlCommand cmd = new MySqlCommand();
//this part of command never changes
cmd.CommandText = selectString;
cmd.Connection = con;
con.Open();
MySqlDataAdapter ad = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
//if we get back any rows display both grids
GridView1.Visible = true;
GridView2.Visible = true;
//bind gridView using data table
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//if no rows come back hide features
GridView1.Visible = false;
GridView2.Visible = false;
Button2.Visible = false;
Button3.Visible = false;
}
con.Close();
lennydee
Member
14 Points
3 Posts
How to get rid of apostrophes in mysql SelectCommand?
Feb 07, 2012 08:45 PM|LINK
Hi guys,
I have something like this:
SelectCommand="SELECT ID, AuthorFirstName, AuthorLastName, SessionTitle FROM aacr WHERE ?ColumnName LIKE Concat('%', ?SessionTitle, '%')"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ColumnName" PropertyName="SelectedValue" Type="String" DefaultValue="NULL" /> <asp:ControlParameter ControlID="sessiontitleTextBox" DefaultValue="NULL" Name="SessionTitle" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>The problem is, that when I get the "ColumnName" out of the DropDownList, it comes with apostrophes around, and obviously, when you say WHERE 'AuthorFirstName' = 'John' , for example, mysql doesn't know the 'AuthorFirstName', because it is called AuthorFirstName without the apostrophes. Could anyone tell me, please, how to get rid of the apostrophes in the WHERE clause?
Thank you
rajsedhain
Contributor
4181 Points
1041 Posts
Re: How to get rid of apostrophes in mysql SelectCommand?
Feb 08, 2012 06:58 PM|LINK
The SqlDataSource connects to SQL Server only. You will have to use an OleDBDataSoure with the proper provider for MySQL or an ODBCDataSource with the proper driver.
If you are using appropriate DataSource, the apostropy things you can handle in code behind.
Raj Sedhain
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: How to get rid of apostrophes in mysql SelectCommand?
Feb 08, 2012 07:07 PM|LINK
Step 1 - take away the quotes from the dropdown selections.
Step 2 - User query parameters. Among other things, they will handle apostrophes, which have the same ascii code as a single quote.
lennydee
Member
14 Points
3 Posts
Re: How to get rid of apostrophes in mysql SelectCommand?
Feb 08, 2012 08:44 PM|LINK
Never mind guys, I have totaly changed my code, so it's fine now. Thanks anyway :)
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: How to get rid of apostrophes in mysql SelectCommand?
Feb 09, 2012 01:05 AM|LINK
Congratulations first！
Then would you mind showing us the related codes？
Reguards！
lennydee
Member
14 Points
3 Posts
Re: How to get rid of apostrophes in mysql SelectCommand?
Feb 22, 2012 01:43 PM|LINK
So i basically construct all the select statements and populate the GridView from the code behind like this:
MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["atsConnectionString"].ConnectionString); MySqlCommand cmd = new MySqlCommand(); //this part of command never changes cmd.CommandText = selectString; cmd.Connection = con; con.Open(); MySqlDataAdapter ad = new MySqlDataAdapter(cmd); DataTable dt = new DataTable(); ad.Fill(dt); if (dt.Rows.Count > 0) { //if we get back any rows display both grids GridView1.Visible = true; GridView2.Visible = true; //bind gridView using data table GridView1.DataSource = dt; GridView1.DataBind(); } else { //if no rows come back hide features GridView1.Visible = false; GridView2.Visible = false; Button2.Visible = false; Button3.Visible = false; } con.Close();