1

Please help!

I have a program that uses a stored procedure to retrieve data from the database and dumps the data in a table for display, this is how my code looks:

string connectionString = ConfigurationManager.ConnectionStrings["azcom"].ConnectionString;

  using (SqlConnection conn = new SqlConnection(connectionString))
    {
      conn.Open();
      cmd = new SqlCommand("search_person", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("@searchString", SqlDbType.VarChar).Value = searchString;


        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                DataTable table = new DataTable();
                while (reader.Read())
                {
                    table.Columns.Add("IDNumber", typeof(string));
                    table.Columns.Add("Name", typeof(string));
                    table.Columns.Add("Surname", typeof(string));
                    table.Columns.Add("Company Name", typeof(string));

                    table.Rows.Add("@idnumber", "@name", "@surname", "@companyN");
                }
                table.Load(reader);
                lblDisplay.Text = table.ToString(); }

The problem is my SqlDataReader returns a null which causes my program to give me this error:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Anyone who has an idea what might be the cause please help me.

Improve this question
3
  • 3
    If you want to fill a datatable anyway i would prefer SqlDataAdapter.Fill(table). Btw, what should table.ToString() display? A DataTable is a complex object with no custom ToString. Commented May 17, 2013 at 15:13
  • WEll, you need to set up your DataTable and its columns once before enumerating the reader - and then you need to actually go get the values from the reader by using string name = reader.GetString(1) and so on ... Commented May 17, 2013 at 15:13
  • 1
    Why do you add the DataColumns in the loop? You should do that once before you execute the reader. Commented May 17, 2013 at 15:16

4 Answers 4

Reset to default
4

You are adding the same four columns to the DataTable each time you load a row.

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

3 Comments

This is true, but it's not the cause of his (current) error. The code never makes it that far.
@JoelCoehoorn You are correct (as per your answer below). Adding the same columns to the DataTable would produce the error A column named 'IDNumber' already belongs to this DataTable.. I agree it is likely to be an error in the sproc - +1 for your answer.
Actually I have SEEN c# SQL statements where the code AFTER a cmd.ExecuteNonQuery will cause an error to be thrown before it is ran?!? I think at the Execute statement there must be a checker ran across the code block and errors can be thrown at this point. This is not necessarily the OP's problem but Diamond is correct and this is an issue they should fix
3

If you want to fill a datatable anyway i would prefer SqlDataAdapter.Fill(table). Why do you add the DataColumns in the loop? That will add columns multiple times. You don't need to add the columns manually at all. Both approaches will automatically create the columns from the schema.

You should also load the table from the reader only if there are records. And you should also use the using statement for the SqlDataRader:

DataTable table = new DataTable();
using(var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
    table.Load(reader);
}
lblDisplay.Text = table.Rows.Count.ToString();

The SqlDataAdapter approach:

DataTable table = new DataTable();
// you don't need to add the columns
using(var da = new SqlDataAdapter(cmd))
{
    // you don't need to open/close the connection with the adapter
    da.Fill(table);
}

Btw, what should table.ToString() display? A DataTable is a complex object with no custom ToString.

Improve this answer

2 Comments

I want to display the table data, how do i display it on the web form?
You can use the DataTable as DataSource for a GridView or other databound webcontrol(like Repeater,ListView or DataList) or -if it's just a single column- for a ListBox or DropDownList(set DatTextField and DataValueField). There are many tutorials in the web.
1

You've got it backwards. You seem to think that you get that error message because your SqlDataReader is null. It's the other way around. Your SqlDataReader is still null because the sql you're running produced that error. This caused an exception before anything could be assigned to your reader variable.

You need to debug the search_person stored procedure.

Improve this answer

Comments

0

As we can see, you are adding columns inside the while() loop; Same column name is getting added inside loop.
Another mistake is that, you are not adding rows in the DataTable in correct ways:

Just update your code like this and replace your column accordingly: 

 DataTable table = new DataTable();
 table.Columns.Add("IDNumber", typeof(string));
 table.Columns.Add("Name", typeof(string))
 table.Columns.Add("Surname", typeof(string));
 table.Columns.Add("Company Name", typeof(string));
    while (reader.Read())
      {   
         DataRow dr=table.NewRow();                 
         dr["IDNumber"]=reader["IDNumber"];
         dr["Name"]=reader["Name"];
         dr["Surname"]=reader["Surname"];
         dr["Company Name"]=reader["Company_Name"];
         table.Rows.Add(dr);
      }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.