CODEWITHSUNDEEP
c#asp.netwebformsado.net
Mobilemike
Mobilemike

Reputation: 37

Adding rows to a DataTable from the result of a Query

I am trying to do a simple process but can't seem to make it work properly. Please take into account that I am new at this and learning without any assistance except for mostly SO posts.

I want to query a SQL Server table and then append the retrieved record to a DataTable. Here is what I have so far:

ASPX:

<form id="form1" runat="server">
    <div>
        <asp:TextBox ID="txtScan" runat="server"></asp:TextBox><br />

        <asp:Button ID="btnSearch" runat="server" Text="SEARCH" OnClick="btnSearch_Click" />
    </div>

CS:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        AddItem();
    }

    protected void AddItem()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["2008DB"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            cmd.Connection = conn;
            cmd.CommandText = "select * from items where ID = @lookup";
            cmd.Parameters.Add("@lookup", SqlDbType.NVarChar).Value = btnSearch.Text;
            da.Fill(dtScans); //I shouldn't be filling this I don't think?

        }
    }
}

I have confused myself as to the following:

1) Where to put DataTable dtScans = new DataTable();

2) How to properly read the records from the database and then append them to dtScans. I have used SqlDataAdapter.Fill() method before. Is this appropriate here?

3) Once this is figuered out I will make the DataTable the DataSource for a GridView

I've learned a lot from you guys but I am having problems on this one stitching all of it together. Thanks for any assistance.

Upvotes: 0

Views: 254

Answers (1)

squillman
squillman

Reputation: 13641

First - it would be worth your time to familiarize yourself with the ADO.NET framework. There is good documentation in MSDN. This is a pretty basic ADO task, but it's not the only way to go. You can start here for information about datatables: http://msdn.microsoft.com/en-us/library/t31h6yhs%28v=vs.110%29.aspx

That said, you're close! You need to create a new DataTable instance and then you can fill it, like you said. It can go right in your AddItem() method if you're just going to use it once. Once it's filled you can bind it to your GridView.

protected void AddItem()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["2008DB"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter(cmd);

        cmd.Connection = conn;
        cmd.CommandText = "select * from items where ID = @lookup";
        cmd.Parameters.Add("@lookup", SqlDbType.NVarChar).Value = txtScan.Text; //You'll also want to fix this line to use the TextBox instead of the Button

        DataTable dtScans = new DataTable(); //Create the new DataTable so you have something to fill
        da.Fill(dtScans);
        GridView1.DataSource = dtScans; //Set the DataTable to be the sourse for a GridView
    }
}

Upvotes: 1

Related Questions