Table doesn't have a primary key.

Question

0.00/5 (No votes)

See more:

Generally i need to delete row from datatable in dataset.my table have primary key.
i had verified many times but while excuting below code get the error like

Table doesn't have a primary key.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.MissingPrimaryKeyException: Table doesn't have a primary key.

Source Error:

Line 78: {
Line 79: DataTable dt = ds.Tables[0];
Line 80: DataRow dr = dt.Rows.Find(0);
Line 81: dr.Delete();
Line 82: dt.AcceptChanges();

DataRow dr = dt.Rows.Find(0);-----> this statment get the error.

insert,update worked correctly but delete not work

Mycode:

C#

protected void Button4_Click(object sender, EventArgs e)
 {

  SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial 
  Catalog=E_learn;Integrated Security=True");
  string query="select * from Student where sid="+TextBox1.Text;
  SqlDataAdapter da = new SqlDataAdapter(query, con);
  SqlCommandBuilder br = new SqlCommandBuilder(da);
  DataSet ds = new DataSet();
  da.Fill(ds, "Student");
  if (ds.Tables[0].Rows.Count > 0)
  {
  DataTable dt = ds.Tables[0];
  DataRow dr = dt.Rows.Find(0);
  dr.Delete();
  dt.AcceptChanges();
  da.Update(ds, "Student");
  Response.Write("deleted successfully");
}

 }

please help me.
thank u.

What I have tried:

Table doesn't have a primary key.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.MissingPrimaryKeyException: Table doesn't have a primary key.

Source Error:

Line 78: {
Line 79: DataTable dt = ds.Tables[0];
Line 80: DataRow dr = dt.Rows.Find(0);
Line 81: dr.Delete();
Line 82: dt.AcceptChanges();

DataRow dr = dt.Rows.Find(0);-----> this statment get the error.

Posted 30-Mar-18 8:45am

Krishna Veni

Updated 30-Mar-18 9:09am

v2

Add a Solution

Comments

Patrice T 30-Mar-18 17:31pm

sid is numeric or alpha ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-03-30T09:10:00

Solution 1

You're just filling the data table from the database but you're not defining any primary key column for the data table. Before you can use Find method to locate a ro based on a primary key you need to tell what the key column is.

To define he key column you can use for example DataTable.PrimaryKey Property (System.Data)[^]

Another thing is the query. You're concatenating text directly from the text box to the query. This leaves you wide open to SQL injections. You should use SqlParameter[^] with values coming from UI.

Have a look at Properly executing database operations[^]

Posted 30-Mar-18 9:10am

Wendelius

Updated 30-Mar-18 9:13am

v2

Comments

Krishna Veni 30-Mar-18 15:36pm

SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial Catalog=E_learn;Integrated Security=True");
string query="select * from Student where sid="+TextBox1.Text;
SqlDataAdapter da = new SqlDataAdapter(query, con);
SqlCommandBuilder br = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "Student");
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
DataColumn[] columns;
columns = dt.PrimaryKey;
DataRow dr = dt.Rows.Find(0);
dr.Delete();
dt.AcceptChanges();
da.Update(ds, "Student");
Response.Write("deleted successfully");

}

i had set the primary key in code but get the same error Table doesn't have a primary key.
error occured in this line----> DataRow dr = dt.Rows.Find(0);

please help me.
thank u

Wendelius 30-Mar-18 15:46pm

What you have done in the code above is that you have read the empty column array from the data table.You haven't set any keys.

I take it that the SID column is the primary key in Student table. If this is the case, try defining the key as follows:

DataColumn[] keyColumns = new DataColumn[1];
keyColumns[0] = dt.Columns["sid"];
dt.PrimaryKey = keyColumns;

Krishna Veni 31-Mar-18 1:51am

My code worked properly but row can't be delete from database table.in datatable row deleted.after call the .da.update(ds,"student") need to effected database table but row can't be deleted in database table
my code:
SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial Catalog=E_learn;Integrated Security=True");
string query="select * from Student where sid="+TextBox1.Text;
SqlDataAdapter da = new SqlDataAdapter(query, con);
SqlCommandBuilder br = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "Student");
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
DataColumn[] keyColumns = new DataColumn[1];
keyColumns[0] = dt.Columns["sid"];
dt.PrimaryKey = keyColumns;
DataRow dr = dt.Rows[0];
dt.Rows.Remove(dr);
da.Update(ds, "Student");
Response.Write("deleted successfully");
}

I think u have doubt about delete row. how should know row deleted from datatable in dataset. i had verfied through breaking points keys like F10,F11.at the time in dataset visualizer shows before call da.update(ds,"Student") i have row. after call da.update(ds,"Student").dataset visualizer shows row deleted but database does not effected.row can't be deleted from database . what is the problem
i don't know. please help me.
Thank u.

Wendelius 31-Mar-18 2:07am

If you want to reflect the changes from the data table to the database, you need to set the commands that handle the SQL statements, DeleteCommand in this case.

Have a look at the example in SqlDataAdapter.DeleteCommand Property (System.Data.SqlClient)[^]

Krishna Veni 31-Mar-18 4:55am

can u possible using da.update(ds,"Student");

Wendelius 31-Mar-18 5:41am

If I understand you correctly, yes you can use data adapter's update method once you have set the appropriate commands.