none
Compare Textbox with SQL

    Question

  • Hello.

    I have a Textbox. There the user inserts a text that it has to be compared with a DataBase.

    If it's true it has to create a new GridView and show all the results of the consultation.

    My code is now like that:

    protected void BBuscarfondo_Click(object sender, EventArgs e)
        {
             SqlConnection con = new SqlConnection("Data Source=IMCJB150\\SQLEXPRESS; Initial Catalog=GestorInventario; Integrated Security=True");
            if (Page.IsValid)
            {
                Paginas.SetActiveView(GridResultadoBuscar);
    
                string cadena = "SELECT * FROM Ordenadores o, Monitores m, Impresoras i, Escaneres e, Telefonos t, Proyectores p, Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'";
                SqlCommand miComandoB = new SqlCommand(cadena, con);
                con.Open();
    
                SqlDataReader Lector = miComandoB.ExecuteReader();
                if (Lector.Read() == true)
                {
                        
                }
                
                else
                {
                    Paginas.SetActiveView(Buscarotraopcion);
                }
                con.Close();
            }
        }

    I want to look for NumSerie in all the tables of my DB and if in my DB is some product with that SerialNumber, it has to appear on my GridView.

    My GV has this columns: ID, Type, Name, Model, Serial Number, Room and Inventary ID:

    <asp:View runat="server" ID="MuestraListado">

    <h3> Resultado de la búsqueda con los parámetros introducidos: </h3> <asp:GridView runat="server" ID="GVTabla" AutoGenerateColumns="False" DataSourceID="SQLDS" OnRowDataBound="GVTabla_RowDataBound"> <Columns> <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" /> <asp:BoundField DataField="Tipo" HeaderText="Tipo" /> <asp:BoundField DataField="Nombre" HeaderText="Nombre" /> <asp:BoundField DataField="Modelo" HeaderText="Modelo" /> <asp:BoundField DataField="NumSerie" HeaderText="Número de serie" /> <asp:BoundField DataField="Sala" HeaderText="Sala" /> <asp:BoundField DataField="IdInventario" HeaderText="Id Inventario" /> </Columns> </asp:GridView> </asp:View>

    I don't know how I have to create the gridview... thanks.


    Wednesday, August 21, 2013 7:48 AM

Answers

  • Please post the question in ASP.NET forums. You will get better help than here...

    http://forums.asp.net/

    http://forums.asp.net/18.aspx/1?Web+Forms


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 21, 2013 8:11 AM
  • Hi María,

    First, I want to inform that this forum is to discuss SQL Server questions. Regarding to how to create GridView, we need to post this question to ASP.NET forum. Additionally, I list a link about how to add a GridView to ASP.NET page at runtime, you can refer to the codes there. Now, let’s focus on the T-SQL commands:

    SELECT * FROM Ordenadores o, Monitores m, Impresoras i, Escaneres e, Telefonos t, Proyectores p, Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"

    Since we need to query data from multiple tables, we need to separate the commands and execute them one by one, for example:

    SELECT * FROM Ordenadores o WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"
    …
    SELECT * FROM Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"

    If all these table have the same table structure, we can use “union” to combine the result:

    SELECT NumSerie, Col1, Col2 FROM Ordenadores o WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"
    Union all
    …
    Union all
    SELECT NumSerie, Col1, Col2 FROM Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"

    For more detail information, you can refer to the following link:

    UNION (Transact-SQL)
    http://technet.microsoft.com/en-us/library/ms180026.aspx

    ASP.NET forum
    http://forums.asp.net/

    How to: Create an ASPxGridView at Runtime
    http://documentation.devexpress.com/#AspNet/CustomDocument4262



    Allen Li
    TechNet Community Support

    Friday, August 23, 2013 6:48 AM
    Moderator
  • I don't know how I have to create the gridview... thanks.

    As Latheesh and Allen suggested, you will get better help on an ASPNET forum for your gridview question.  I want to add that it is very important that you use a parameterized query instead of building the SQL statement by concatenating values, especially from user-supplied values.  Parameters are a best practice for security reasons and provide other benefits:

    • allows quotes in data without programmatic escaping
    • avoids the need to format date strings according to DATEFORMAT session settings
    • handles decimal separators automatically
    • improves performance due to plan reuse
    • provides cleaner code

    See http://www.dbdelta.com/help-stop-sql-injection-madness/ for an example and considerations.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 23, 2013 9:28 AM

All replies

  • Please post the question in ASP.NET forums. You will get better help than here...

    http://forums.asp.net/

    http://forums.asp.net/18.aspx/1?Web+Forms


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 21, 2013 8:11 AM
  • Hi María,

    First, I want to inform that this forum is to discuss SQL Server questions. Regarding to how to create GridView, we need to post this question to ASP.NET forum. Additionally, I list a link about how to add a GridView to ASP.NET page at runtime, you can refer to the codes there. Now, let’s focus on the T-SQL commands:

    SELECT * FROM Ordenadores o, Monitores m, Impresoras i, Escaneres e, Telefonos t, Proyectores p, Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"

    Since we need to query data from multiple tables, we need to separate the commands and execute them one by one, for example:

    SELECT * FROM Ordenadores o WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"
    …
    SELECT * FROM Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"

    If all these table have the same table structure, we can use “union” to combine the result:

    SELECT NumSerie, Col1, Col2 FROM Ordenadores o WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"
    Union all
    …
    Union all
    SELECT NumSerie, Col1, Col2 FROM Miscelanea mis WHERE NumSerie = '" + TBNumSerieBuscar.Text + "'"

    For more detail information, you can refer to the following link:

    UNION (Transact-SQL)
    http://technet.microsoft.com/en-us/library/ms180026.aspx

    ASP.NET forum
    http://forums.asp.net/

    How to: Create an ASPxGridView at Runtime
    http://documentation.devexpress.com/#AspNet/CustomDocument4262



    Allen Li
    TechNet Community Support

    Friday, August 23, 2013 6:48 AM
    Moderator
  • I don't know how I have to create the gridview... thanks.

    As Latheesh and Allen suggested, you will get better help on an ASPNET forum for your gridview question.  I want to add that it is very important that you use a parameterized query instead of building the SQL statement by concatenating values, especially from user-supplied values.  Parameters are a best practice for security reasons and provide other benefits:

    • allows quotes in data without programmatic escaping
    • avoids the need to format date strings according to DATEFORMAT session settings
    • handles decimal separators automatically
    • improves performance due to plan reuse
    • provides cleaner code

    See http://www.dbdelta.com/help-stop-sql-injection-madness/ for an example and considerations.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 23, 2013 9:28 AM