Question 315012 in ASP.NET Question posted in
The official documentation can be found here.

ASP.NET multiline querying multiple names in textbox and adding the results to a single gridview

I have a textbox with multiline feature active. I query the names entered in this textbox from the database and print the result to the Gridview1 object.

However, when I use the code below, it only adds the result of the last name in the textbox to the gridview. I want the results obtained at the same time to be displayed collectively in a single gridview.

For example, I want the surnames of 10 names to be displayed collectively in 1 gridview. Thanks to everyone who helped.

protected void btntoplusorgu_Click(object sender, EventArgs e)
{
    var satırlar = txttoplu.Text.Split(new[] { 'r', 'n' });

    foreach (var veri in satırlar)
    {
        string baglanti = ConfigurationManager
                              .ConnectionStrings["TAKS"].ConnectionString;

        using (SqlConnection con = new SqlConnection(baglanti))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            StringBuilder sbCommand = new
                StringBuilder("Select * from Veriler where 1 = 1");

            if (veri.ToString() != "")
            {
                sbCommand.Append(" AND Tckn=@Tckn");
                SqlParameter paramtckn = new
                SqlParameter("@Tckn", veri.ToString());
                cmd.Parameters.Add(paramtckn);
            }
            else
            {
            }

            cmd.CommandText = sbCommand.ToString();
            cmd.CommandType = CommandType.Text;

            con.Open();

            SqlDataReader rdr = cmd.ExecuteReader();
            GridView1.DataSource = rdr;
            GridView1.DataBind();
        }
    }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Your interest and response are truly excellent. I apologize for returning late. Unfortunately, there is no regular intern in the student dormitory. Unfortunately, I do not understand the source of the term "General" in the codes. Would it be a bad thing if I asked you to edit my codes according to the way you suggested?


  2. 0

    You can add multiple parameters, and still enjoy SQL injection safe code.

    So, say this markup:

    <div style="float: left; border: solid; height: 240px; padding: 10px">

    <div style="float: left">
        <h3>Enter City(s) to search for</h3>
        <asp:TextBox ID="txtCity" runat="server"
            TextMode="MultiLine"
            Style="height: 145px; width: 260px"></asp:TextBox>
    </div>

    <div style="float: left; margin-top: 25%; border: solid;">
        <asp:Button ID="cmdSearch" runat="server" Text="Search"
            CssClass="btn" OnClick="cmdSearch_Click" />

    </div>
</div>

<div style="float: left; margin-left: 40px">

    <asp:GridView ID="GVHotels" runat="server" AutoGenerateColumns="False"
        DataKeyNames="ID" CssClass="table table-hover"
        Width="55%"
        ShowHeaderWhenEmpty="true">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="LastName" />
            <asp:BoundField DataField="City" HeaderText="City" />
            <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
            <asp:BoundField DataField="Description" HeaderText="Description" />
        </Columns>
    </asp:GridView>

</div>

    And our code behind can be this:

    protected void cmdSearch_Click(object sender, EventArgs e)
{
    SqlCommand cmdSQL = new SqlCommand("SELECT * FROM tblhotelsA");

    string sWhere = "";
    string[] sCity = txtCity.Text.Split(new string[] { System.Environment.NewLine }, StringSplitOptions.None);

    int i = 0;
    foreach (string City in sCity)
    {
        i++;
        if (sWhere != "") sWhere += ",";
        sWhere += "@" + i;
        cmdSQL.Parameters.Add("@" + i, SqlDbType.NVarChar).Value = City;
    } 

    if (txtCity.Text != "") 
        cmdSQL.CommandText += $" WHERE City IN ({sWhere})";

    GVHotels.DataSource = General.MyRstP(cmdSQL);
    GVHotels.DataBind(); 
}

    And the result looks like this:
    enter image description here

    So, note how we are free to add parameters to the SQL command object, and there is not really any requirement to have any existing SQL in that command object during the time your code adds parameters.

    And as a FYI, the helper routine MyRstP() is a general (global) routine that I use over and over to save some keyboards every time I have a SQL query to pull data.

    That routine used was thus this:

    public static DataTable MyRstP(SqlCommand cmdSQL)
{
    DataTable rstData = new DataTable();

    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
    {
        using (cmdSQL)
        {
            cmdSQL.Connection = conn;
            conn.Open();
            rstData.Load(cmdSQL.ExecuteReader());
        }
    }
    return rstData;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search