How to use searching from textbox and result show in gridview

Question

I am trying to fetch the matching database attribute from textbox text and display the related database in GridView. What am I doing wrong here?

 protected void btnsearch_Click(object sender, EventArgs e)
 {
        string q = "Select * from facultyreg where fname ='"+txtsearch.Text.ToString() + "'";
        sda = new SqlDataAdapter(q, con);
        ds = new DataSet();
        sda.Fill(ds, "facultyreg");
        GridView2.DataSource = null;
        //GridView2.DataBind();
        GridView2.DataSource=ds.Tables[0];

       /* cmd = new SqlCommand(q,con);
        if (sdr.HasRows && sdr != null)
        {
            sdr.Read();

        }*/
 }

general suggestion:pls use parameterized query to avoid sql injection .. http://stackoverflow.com/questions/17509169/parameterized-queries-vs-sql-injection — pravprab, Mar 24 '14 at 06:01

score 1 · Answer 1 · answered Mar 24 '14 at 06:04

You are not binding your gridview use somthing like this

protected void btnsearch_Click(object sender, EventArgs e)
{
    string q = "Select * from facultyreg where fname ='"+txtsearch.Text.ToString() + "'";
    sda = new SqlDataAdapter(q, con);
    ds = new DataSet();
    sda.Fill(ds);
    GridView2.DataSource = ds;
    GridView2.DataBind();

   /* cmd = new SqlCommand(q,con);
    if (sdr.HasRows && sdr != null)
    {
        sdr.Read();

    }*/
}

also as pravpab says, don't use parameterized query to avoid sql injection(ie, don.t concatenate textbox in the query directly).

score 1 · Accepted Answer · answered Mar 24 '14 at 06:28

You can easily bind your gridview with SqlInjection and Parameterized Query like this.

protected void btnsearch_Click(object sender, EventArgs e)
 {       
    var searchresult = SqlInjection(txtsearch.Text.ToString());
    var dt = GetData(searchresult);
    if(dt != null)
    {
            GridView2.DataSource= dt;
        GridView2.DataBind();
    }
 }

private DataTable GetData(string searchvalue)
        {
            using (var dataset = new DataSet())
            {
                dataset.Locale = CultureInfo.InvariantCulture;
                using (var connection = new SqlConnection("Your connection string"))
                {
                    using (var sqlCommand = new SqlCommand("write your store procedure name here", connection))
                    {
                        sqlCommand.Parameters.AddWithValue("parameter name from store procedure", searchvalue);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandTimeout = 180;
                        using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                        {
                            dataset.Reset();
                            sqlDataAdapter.Fill(dataset);
                            sqlCommand.Connection.Close();
                        }
                    }
                }

                return dataset.Tables[0];
            }
        }

private static string SqlInjection(string stringValue)
        {
            if (null == stringValue)
            {
                return null;
            }
    enter code here
            return stringValue
                        .RegexReplace("-{2,}", "-")                 // transforms multiple --- in - use to comment in sql scripts                        
                        .RegexReplace(@"(;|\s)(exec|execute|select|insert|update|delete|create|alter|drop|rename|truncate|backup|restore)\s", string.Empty, RegexOptions.IgnoreCase);
        }

score 1 · Answer 3 · answered Mar 24 '14 at 07:28

try this out

Partial Class _Default Inherits System.Web.UI.Page

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

End Sub


Protected Sub BindGrid(searchText As String)
    Dim connection As New OleDbConnection("myconnection")
    Dim cmd As New OleDbCommand
    Dim sql As String = "SELECT * FROM OPENQUERY([xxxx.NET\CSI], 'SELECT * FROM SReader.table1 WHERE CurrentCostCenter IN(''27177'') ')"
    cmd.Parameters.AddWithValue("@CurrentCostCenter", searchText)

    Dim dt As New DataTable()
    Dim ad As New OleDbDataAdapter(cmd)
    ad.Fill(dt)

    If dt.Rows.Count > 0 Then
        'check if the query returns any data
        GridView1.DataSource = dt
        GridView1.DataBind()
        'No records found
    Else
    End If


End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs)
    BindGrid(TextBox1.Text.Trim())
End Sub

http://www.mikesdotnetting.com/Article/116/Parameterized-IN-clauses-with-ADO.NET-and-LINQ

How to use searching from textbox and result show in gridview

3 Answers3