2 Replies - 1981 Views - Last Post: 06 August 2013 - 12:34 PM Rate Topic: ***** 1 Votes

#1 nvielbig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 03-April 12

Oracle, C# parameterized queries via user Textbox input - Gridview

Posted 06 August 2013 - 08:31 AM

Hello Dreamincode Forum,

This issue is one that I have seen much talk about throughout the web as it's a fairly common task to want to do. However, I am very new with ASP.NET & C#, and my experience with SQL is generally limited as well. That said, I'll try to explain my process thus far, attempts and give you a clear understanding of what I would like to eventually achieve here.

I have a form that has two input fields in it: Firstname -- Lastname. (I will leave out the aspx markup, it's very simple. IDs are FirstNameTextbox and LastNameTextbox).

 C# Code-Behind
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                connection.Open();
                string sqlquery = @"SELECT * FROM NICK_TEST WHERE FIRSTNAME" = "%" FirstNameTextbox.Text "%"; //wrong
                try
                {
                    using (OracleCommand command = new OracleCommand(sqlquery, connection))
                    {
                        DataTable SearchResultTable = new DataTable();
                        command.CommandType = CommandType.Text;
                        command.BindByName = true;
                        command.Parameters.Add("FIRSTNAME", FirstNameTextbox.Text);
                        command.Parameters.Add("LASTNAME", LastNameTextbox.Text);
                        SearchResultGridview.Visible = true;
                        SearchResultGridview.DataSource = SearchResultTable;
                        SearchResultGridview.DataBind();
                    }
                }
                catch (Exception)
                {
                    Response.Redirect("SearchForEmployee.aspx", true);
                    ErrorMessage.Visible = true;
                }
                finally {
                    connection.Close();
                    connection.Dispose();
                }
            }


GridView control in .aspx page,
<asp:GridView runat="server" ID="SearchResultGridview" CssClass="Grid gridview" DataKeyNames="EMPID"
                        DataSourceID="SqlDataSource2" ShowHeaderWhenEmpty="false" AutoGenerateColumns="false" 
                        BackColor="white" AlternatingRowStyle-BackColor="#d9d9d9" AutoGenerateSelectButton="true">
                        <Columns>
                            <asp:TemplateField>
                                <ItemTemplate>
                                    <asp:LinkButton ID="lbDelete" runat="server" CommandName="Delete"
                                        OnClientClick="return confirm('Are you sure you want to delete this record?');"
                                        Text="Delete">
                                    </asp:LinkButton>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="FIRSTNAME"
                                HeaderText="First"



I have a page where all of my table results are displayed, but now I would like to allow filtering on my GridView on this .aspx page.
Sorry if it seems that I haven't attempted too much, really trying but it's tough to do things like this as a beginner. Also, this is my first time visiting this forum. I have been here before in reference to other threads and the help always seemed to be very good, and respectful. Hopefully I have done a good job being clear in my first post. Definitely let me know if you want other stuff from me. Any direction on what I should consider next would be great.

Thanks,
Nick

Forgot to mention that my GridView contains more, I just didn't want to link unnecessary amounts of code lines.

Is This A Good Question/Topic? 0
  • +

Replies To: Oracle, C# parameterized queries via user Textbox input - Gridview

#2 nvielbig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 03-April 12

Re: Oracle, C# parameterized queries via user Textbox input - Gridview

Posted 06 August 2013 - 10:36 AM

Hi all,

I've yet to figure out a solution to this. I am getting a blank Gridview when I click my button that this code is linked up with.

New code (not working)... :
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                string sqlquery = @"SELECT * FROM NICK_TEST " +
                    "WHERE LASTNAME=:LASTNAME";
                OracleCommand command = new OracleCommand(sqlquery, connection);
                connection.Open();

                command.BindByName = true;
                command.Parameters.Add("LASTNAME", LastNameTextbox.Text);
                SearchGridview.DataBind();


            }

Was This Post Helpful? 0
  • +
  • -

#3 nvielbig  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 03-April 12

Re: Oracle, C# parameterized queries via user Textbox input - Gridview

Posted 06 August 2013 - 12:34 PM

Hello everyone,

I wanted to come back and explain how I was able to solve this in case someone ever stumbles upon this wondering the same thing.

My entire C# code for the button click to 'Filter' data is -
using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                string sqlquery = @"SELECT * " + 
                                "FROM NICK_TEST " +
                                "WHERE LASTNAME=:LASTNAME";
                using (OracleDataAdapter oda = new OracleDataAdapter(sqlquery, connection))
                {
                    connection.Open();

                    oda.SelectCommand.BindByName = true;
                    oda.SelectCommand.Parameters.Add(":LASTNAME", LastNameTextbox.Text);
                    DataTable dt = new DataTable();
                    oda.Fill(dt);
                    SearchGridview.DataSource = dt;
                    SearchGridview.DataBind();

                }
            }


This is functioning just as I wanted it to :)
*Also*, one major mistake I was making was I had my
GridView's DataSourceID set. The fact that I had it set here, and I was also referencing it in the C# code was creating troubles, so be sure to consider that as well.


Thanks all.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1