Searching name in textbox not work "incorrect syntax"

Question

1.00/5 (1 vote)

See more:

, +

Hi I am trying to search name using text box and it is not searching

What I have tried:

C#

protected void txt_SearchName_TextChanged1(object sender, EventArgs e)
       {
           cn.Open();
           SqlCommand cmd = new SqlCommand("select * from gvdetails17 where EmpName LIKE % '" + txt_SearchName.Text + "'%", cn);
           DataTable dt = new DataTable();
           SqlDataAdapter da = new SqlDataAdapter(cmd);
           da.Fill(dt);
           gvDetails.DataSource = dt;
           cn.Close();
       }

ASP.NET

<pre> <asp:TextBox ID ="txt_SearchName" runat="server" style="margin-bottom: -71px;
 margin-left:308px" Width="120px" Font-Size="12px" OnTextChanged= "txt_SearchName_TextChanged1" AutoPostBack="false"></asp:TextBox>

Posted 26-Feb-17 23:49pm

Member 12605293

Updated 27-Feb-17 2:38am

Add a Solution

3 solutions

Solution 1

The percentage wildcard characters must be inside the quotes:

C#

SqlCommand("select * from gvdetails17 where EmpName LIKE '% " + txt_SearchName.Text + "%'", cn);

Posted 27-Feb-17 0:01am

Jochen Arndt

Solution 2

Never do that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Additionally, the quote is in the wrong place.
The chances are that using a parameterised query will remove your problem - and don't forget the rest of your code: one missed command and anyone can delete your database.

C#

using (SqlCommand cmd = new SqlCommand("SELECT * FROM gvdetails17 WHERE EmpName LIKE '%' + @Txt + '%'", cn))
   {
   cmd.Parameters.AddWithValue("@Txt", txt_SearchName.Text);
   ...

Posted 27-Feb-17 0:03am

OriginalGriff

Comments

Ehsan Sajjad 27-Feb-17 6:07am

do we still need contatenation when using parameterzied query?

OriginalGriff 27-Feb-17 6:14am

That isn't using concatenation - it's telling SQL to use the parameters value instead of concatenating it.
When you do this:
string s = "SELECT * FROM MyTable WHERE column = '" + myTextBox.Text + "'";
You pass through the textbox value as part of the command, so if it contains value SQL, the the server will object it.
So If I type

x';DROP TABLE gvdetails17;--

in the textbox, SQL sees it as three commands:

SELECT * FROM MyTable WHERE column = 'x'
DROP TABLE gvdetails17
--'

Where the last is an SQL comment.
It does the select, deletes your table, and ignores the rest of the command.

Never, ever, do that!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-02-27T02:38:00

Solution 3

try

protected void txt_SearchName_TextChanged1(object sender, EventArgs e)
     {
             string name = txt_SearchName.Text.Trim();
             if(name != "") {
             SqlCommand cmd = new SqlCommand("select * from gvdetails17 where EmpName Like '%'+ @name + '%' ", cn);
             cmd.Parameters.Add("@name", name);
             DataTable dt = new DataTable();
             SqlDataAdapter da = new SqlDataAdapter(cmd);
             da.Fill(dt);
             gvDetails.DataSource = dt;
             gvDetails.DataBind();

         }
     }

Posted 27-Feb-17 2:38am

Karthik_Mahalingam

v2

Comments

Member 12605293 27-Feb-17 8:44am

Can I wrap that into my method BindData()

Karthik_Mahalingam 27-Feb-17 8:50am

yes with some correction..

Member 12605293 27-Feb-17 8:58am

private void BindGrid()
{
DataTable dt = new DataTable();

string query = " select * from gvdetails17 WHERE 1=1 And EmpName Like '%'+ @name + '%' ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;

if (ddlAddSalary1.SelectedValue != "" && ddlAddSalary1.SelectedValue != "--Select--")
{

string[] sal = ddlAddSalary1.SelectedValue.Split('-');
string from = sal[0];
string to = sal[1];

query += " and Sal between @fromsal and @tosal ";
cmd.Parameters.AddWithValue("@fromsal", from);
cmd.Parameters.AddWithValue("@tosal", to);

}
if (ddlAge.SelectedValue != "" && ddlAge.SelectedValue != "--Select--")
{

string[] age = ddlAge.SelectedValue.Split('-');
string from1 = age[0];
string to1 = age[1];

query += " and Age between @fromage and @toage ";
cmd.Parameters.AddWithValue("@fromage", from1);
cmd.Parameters.AddWithValue("@toage", to1);

}

if (ddlDepartment.SelectedValue != "" && ddlDepartment.SelectedValue != "--Select--")
{
query += " and Dep = @Dep ";
cmd.Parameters.AddWithValue("@Dep", ddlDepartment.SelectedValue);
}

string name = txt_SearchName.Text.Trim();
if (name != "")
{
query += " and name = @name ";
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
gvDetails.DataSource = dt;
gvDetails.DataBind();
}}

protected void txt_SearchName_TextChanged1(object sender, EventArgs e)
{

BindGrid();

Member 12605293 27-Feb-17 8:59am

I asks me to declar scalar variable and the texbox

Karthik_Mahalingam 27-Feb-17 9:02am

Ok declare it

Member 12605293 27-Feb-17 9:15am

It is not going into last 2 if conditions

Karthik_Mahalingam 27-Feb-17 9:31am

you will have to modify it

Member 12605293 27-Feb-17 9:35am

how Karthik I made the change in the query and made the textbox textchanged inside the method

Karthik_Mahalingam 27-Feb-17 9:38am

chk above code

Karthik_Mahalingam 27-Feb-17 9:37am


 string name = txt_SearchName.Text.Trim();
            if (name != "")
            {
                query += " and EmpName Like '%'+ @name + '%'";
                cmd.Parameters.AddWithValue("@name", name);
            }
           
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            gvDetails.DataSource = dt;
            gvDetails.DataBind();

Member 12605293 28-Feb-17 0:14am

Hi Karthik ,Thanks It works good :)

Karthik_Mahalingam 28-Feb-17 0:19am

cool

Member 12605293 28-Feb-17 0:27am

Happy Morning :)

Karthik_Mahalingam 28-Feb-17 0:30am

good mng

Member 12605293 1-Mar-17 6:11am

Hi Karthik
Is there any tutorial for Dotnet charting using C# not in asp.

Karthik_Mahalingam 1-Mar-17 6:13am

i guess you will have to read the documentation..
i am using jqplot.

Member 12605293 1-Mar-17 6:16am

Can you suggest me any documentation links ?

Karthik_Mahalingam 1-Mar-17 6:18am

http://www.jqplot.com/docs/files/usage-txt.html

Member 12605293 1-Mar-17 6:19am

Thank you.Is there any links for c# documentation

Member 12605293 1-Mar-17 6:20am

Need demo projects Karthik

Karthik_Mahalingam 1-Mar-17 6:22am

http://www.jqplot.com/examples/

Member 12605293 1-Mar-17 6:25am

Thank you :)

Karthik_Mahalingam 1-Mar-17 6:25am

:) welcome

Member 12605293 1-Mar-17 6:29am

I am trying in c# code but not getting any demo karthik

Karthik_Mahalingam 1-Mar-17 6:30am

check in youtube sir.
i havent worked in asp charts.

Member 12605293 1-Mar-17 6:34am

Yeah ok

Karthik_Mahalingam 1-Mar-17 6:38am

hmm

Member 12605293 1-Mar-17 6:42am

If You get any links related to c# code, share with me.Thank You

Karthik_Mahalingam 1-Mar-17 6:46am

sure sir.

Member 12605293 6-Mar-17 6:39am

Hi Karthik

Karthik_Mahalingam 6-Mar-17 7:53am

hi

Member 12605293 7-Mar-17 2:04am

How to use jquery in my plot?

Karthik_Mahalingam 7-Mar-17 2:05am

you dont have to use, its already build in jquery.

Member 12605293 7-Mar-17 2:11am

Hi I have one doubt,

This is my cs code
Chart.Series.ConnectionString = ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString;
Chart.YAxis.Label.Text = "Total No of Students from 2014-2017";
Chart.Title = "Progress Report";
Chart.XAxis.Label.Text = "Years";
Chart.Width = 750;
Chart.Height = 550;
Chart.Type = ChartType.Pies;
Chart.Debug = true;
Chart.DateGrouping = TimeInterval.Years;
Chart.DrillDownChain = "Years,Quarters,Months,Full";
Chart.DefaultSeries.DefaultElement.ToolTip = "%yvalue";
Chart.Series.Name = "Bonus points";
Chart.DefaultSeries.DefaultElement.URL = "jqplot(this)";
Chart.Series.StartDate = new System.DateTime(2014, 1, 1, 0, 0, 0);
Chart.Series.EndDate = new System.DateTime(2017, 12, 31, 23, 59, 59);
Chart.Series.SqlStatement = @"SELECT Duration,Total FROM Report WHERE Duration >= #STARTDATE# ";
Chart.DefaultElement.ShowValue = true;
Chart.SeriesCollection.Add();

Color[] myPalette = new Color[] { Color.Red, Color.Green, Color.Orange,Color.Black };
Chart.DefaultSeries.Palette = myPalette;
}

protected void Ddldrill_SelectedIndexChanged(object sender, EventArgs e)
{
if (Ddldrill.SelectedValue == "Radar")
{
Chart.Type = ChartType.Radar;
}
if (Ddldrill.SelectedValue == "TreeMap")
{
Chart.Type = ChartType.TreeMap;
}
if (Ddldrill.SelectedValue == "ComboHorizontal")
{
Chart.Type = ChartType.ComboHorizontal;
}
if (Ddldrill.SelectedValue == "Gauges")
{
Chart.Type = ChartType.Gauges;
}
}
dotnetCHARTING.SeriesCollection Calculate()
{
dotnetCHARTING.SeriesCollection SC = new dotnetCHARTING.SeriesCollection();
Random myR = new Random(1);
for (int a = 0; a < 4; a++)
{
dotnetCHARTING.Series s = new dotnetCHARTING.Series();
s.Name = "Series " + a.ToString();
for (int b = 0; b < 1; b++)
{
Element e = new Element();
e.Name = "Element " + b.ToString();
e.YValue = myR.Next(20);
s.Elements.Add(e);
}

SC.Add(s);
}
SC[1].Element.Color = Color.FromArgb(255, 255, 0);
SC[1].Elements[1].Name = "Maths";
SC[1].LegendEntry.Value = "5";
SC[1].LegendEntry.Name = "Between 15-30";
SC[1].Element.SmartLabel.Text = "Maths";

SC[2].Element.Color = Color.FromArgb(255, 99, 49);
SC[2].Elements[2].Name = "Science";
SC[2].LegendEntry.Value = "24";
SC[2].LegendEntry.Name = "Between 30-40";
SC[2].Element.SmartLabel.Text = "Maths";

SC[3].Element.Color = Color.FromArgb(0, 156, 255);
SC[3].Elements[3].Name = "Computer";
SC[3].LegendEntry.Value = "38";
SC[3].LegendEntry.Name = "Between 40-50";
SC[3].Element.SmartLabel.Text = "Maths";
return SC;

}

This is my aspx

$.jqplot('Chart', [[[1, 2], [3, 5.12], [5, 13.1], [7, 33.6], [9, 85.9], [11, 219.9]]],
{
title: 'Exponential Line',
axes: { yaxis: { renderer: $.jqplot.LogAxisRenderer } },
series: [{ color: '#5FAB78' }]
});

<ASP:Label id="Label1" runat="server" Width="100px" Text="Chart Type">

<ASP:DropDownList id="Ddld

Karthik_Mahalingam 7-Mar-17 2:12am

post a question,, big to answer

Member 12605293 7-Mar-17 2:25am

ok

Member 12605293 8-Mar-17 1:48am

Hi Karthik
I Have an issue using Telerik COntrols in my application,I have already posted ,Pls help me ..All the control is not visible in toolbox and not able to use.

Karthik_Mahalingam 8-Mar-17 1:50am

sir i am not much aware of telerik

Member 12605293 8-Mar-17 2:01am

Ok Karthik

Karthik_Mahalingam 8-Mar-17 2:03am

post in telerik forums

Member 12605293 10-Mar-17 1:01am

ok Karthik

Karthik_Mahalingam 10-Mar-17 1:03am

:)

Member 12605293 10-Mar-17 4:58am

Hi Karthik
I need help

Karthik_Mahalingam 10-Mar-17 5:10am

what help sir
post a question

Member 12605293 10-Mar-17 8:10am

Hi Karthik
THanks

Member 12605293 14-Mar-17 3:29am

Hi Karthik Hru

Karthik_Mahalingam 14-Mar-17 4:18am

am fine sir