"Invalid column" error populating GridView from SqlDataReader

Question

I am trying to read data from the database, then populate the datatable before binding to GridView.

When I run the application, it produces these erors:

Exception Details: System.Data.SqlClient.SqlException: Invalid column name  'PrescriptionNumber'.
Invalid column name 'PrescriptionNumber'.
Invalid column name 'DrugCode'.
Invalid column name 'PrescriptionDate'.
Invalid column name 'PaymentStatus'.
Invalid column name 'AmountPaidFrom'.

Source Error: 


Line 34:           cmd.Connection = con;
Line 35:           con.Open();
Line 36:           using (SqlDataReader reader = cmd.ExecuteReader())
Line 37:           {
Line 38:               if (reader.HasRows)

Here is the markup:

<asp:GridView ID="grdpayment" runat="server" Width="876px" 
        AutoGenerateColumns="False" CellPadding="4" GridLines="Horizontal" 
        BackColor="White" BorderColor="#336666" BorderStyle="Double" 
        BorderWidth="3px" style="margin-right: 288px" 
        onselectedindexchanged="grdpayment_SelectedIndexChanged">
    <FooterStyle BackColor="White" ForeColor="#333333" />
    <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="White" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F7F7F7" />
    <SortedAscendingHeaderStyle BackColor="#487575" />
    <SortedDescendingCellStyle BackColor="#E5E5E5" />
    <SortedDescendingHeaderStyle BackColor="#275353" />
</asp:GridView>

Did any want know am am doing wrong? here is my table script:

CREATE TABLE [dbo].[Payment](
[PrescriptionNumber] [varchar](50) NOT NULL,
[TreatmentCode] [varchar](50) NOT NULL,
[DrugCode] [varchar](50) NOT NULL,
[PrescriptionDate] [date] NOT NULL,
[DrugQuantity] [int] NOT NULL,
[PaymentStatus] [varchar](50) NOT NULL,
[AmountPaid] [money] NOT NULL

) ON [PRIMARY]

C# Method that read data from the data:

 public void makepayment(string prescriptionValue)
    {
      DataTable storedata = new DataTable();
      string _connection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
      SqlConnection con = new SqlConnection(_connection);
      string sqlquery =
           "Select PrescriptionNumber,DrugCode,PrescriptionDate,PaymentStatus,AmountPaid"
         + "From Payment where PrescriptionNumber='" + prescriptionValue + "'";
      SqlCommand cmd = new SqlCommand(sqlquery,con);
      SqlDataAdapter adapt = new SqlDataAdapter(cmd);
      adapt.Fill(storedata);
      grdpayment.DataSource = storedata;
      grdpayment.DataBind();
    }

i call the method like this:

protected void btnsearchprescripitionnum_Click(object sender, EventArgs e)
    {
        if (txtprescriptionnum.Text == "")
        {

            checkval.Text = "Enter PriscriptionNumber";
        }
        else
        {
            makepayment(txtprescriptionnum.Text.ToString());
        }


    }

It looks like the query you're passing to your SqlCommand specifies fields not present in the table. — Comfortably Numb
– Comfortably Numb, Commented Dec 26, 2013 at 18:41

Josh Darnell · Accepted Answer · 2013-12-26 18:47:42Z

3

The error means that the columns listed in the error message ('PrescriptionNumber', 'PrescriptionNumber', 'DrugCode', 'PrescriptionDate', 'PaymentStatus', and 'AmountPaidFrom') are not actually present in the table being queried.

You need to verify that you are directing your query at the proper table, that there are no typos in your query, and that the columns actually exist in the table you're trying to query.

answered Dec 26, 2013 at 18:47

Josh Darnell

11.5k9 gold badges40 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kombo Over a year ago

@jadrnel27,the table definately exist with the correct columns

kombo Over a year ago

@jadrnel27, i have edited my question.Add the full detail of the table and methods.

Josh Darnell Over a year ago

@kombo Wow, that's odd! The only thing I can think of at this point is your connection string ("ApplicationServices") is not pointing you to the right database. Do you have more than one instance / version of this database / table? Like, a production, QA, and test. Or an old, incomplete version of the Payment table somewhere?

Josh Darnell Over a year ago

However, @kombo, I don't see the code from your error message (using (SqlDataReader reader = cmd.ExecuteReader()) and all that). Where does that get called?

kombo Over a year ago

i want to believe this is the problem: SqlCommand cmd = new SqlCommand(sqlquery,con);

|

Collectives™ on Stack Overflow

"Invalid column" error populating GridView from SqlDataReader

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related