dout in update

Question

0.00/5 (No votes)

See more:

Can anybody clear my mistake?
I used the following code for inserting to one table and to update the field by setting name as primarykey on-another table . On debuging if i select a name to display the content to ultra grid it show the exception like ... 'Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information'. ... "my code is marked below:
private void button2_Click(object sender, EventArgs e)
{

DataTable dts = new DataTable();

int u;
u = dt.DefaultView.Table.Rows.Count;

SqlDataAdapter da1 = new SqlDataAdapter();
//DataTable dts = new DataTable();
connect c = new connect();

SqlConnection s1 = c.con1();

//DataSet ds = new DataSet("electrical");
SqlCommand cm = new SqlCommand("select BILLNO,NAME,CONTRACTOR,SITE, COMPANY,PRODUCT,PRODUCTTYPE,QTY,MRP,RATE,AMT,[DISCOUNT%],TOTAL ,DATE from credit", con);

da1.SelectCommand = cm;

SqlCommandBuilder cmbuilder = new SqlCommandBuilder(da1);//auto generate the UpdateCommand,InsertCommand and DeleteCommand
dts.Clear();
//dt.Clear();
da1.Fill(dts);
if (comboBox5.Text == "")
{
comboBox5.Text = "";
}
if (comboBox4.Text == "")
{
comboBox4.Text = "";
}
if (comboBox6.Text == "")
{
comboBox6.Text = "";
}
DataRow row = null;
DataColumn column1 = new DataColumn("BILLNO", typeof(int));
dt.Columns.Add(column1);
DataColumn column14 = new DataColumn("DATE", typeof(DateTime));
dt.Columns.Add(column14);
DataColumn column2 = new DataColumn("NAME", typeof(string));
dt.Columns.Add(column2);
DataColumn column3 = new DataColumn("CONTRACTOR", typeof(string));
dt.Columns.Add(column3);
DataColumn column4 = new DataColumn("SITE", typeof(string));
dt.Columns.Add(column4);

for (int i = 1; i <= u; i++)
{
row = dt.Rows[i - 1];
row["BILLNO"] = int.Parse(textBox1.Text);
row["DATE"] = dateTimePicker1.Value.ToShortDateString();
row["NAME"] = comboBox5.Text;
row["CONTRACTOR"] = comboBox4.Text;
row["SITE"] = comboBox6.Text;
}

this.ultraGrid1.BindingContext[dts].EndCurrentEdit();

this.ultraGrid1.PerformAction(UltraGridAction.DeleteRows);
dts = dt;
////
//DataSet dst = new DataSet();
//da1.InsertCommand = cmbuilder.GetInsertCommand();
////da1.UpdateCommand = cmbuilder.GetUpdateCommand();
//da1.Update(dst.Tables[0].Select(null, null, DataViewRowState.CurrentRows));

////DataSet ds2 = new DataSet("electricals");

da1.Update(dts);

//da1.Update(dst.Tables["credit"].Select(null, null, DataViewRowState.CurrentRows));//, "credit");

dt.Columns.Remove(column1);
dt.Columns.Remove(column14);
dt.Columns.Remove(column2);
dt.Columns.Remove(column3);
dt.Columns.Remove(column4);
MessageBox.Show("Updated");
// dt.Clear();
// dts.Clear();

this.BeginInvoke(new MethodInvoker(this.SetActiveCell));

try
{

if (comboBox5.Text == "")
{
comboBox5.Text = "";
}
if (comboBox4.Text == "")
{
comboBox4.Text = "";
}
if (comboBox6.Text == "")
{
comboBox6.Text = "";
}
connect c1 = new connect();
c1.idu("insert into creditcustomer values(" + textBox1.Text + ",'" + comboBox5.Text + "','" + dateTimePicker1.Value.ToShortDateString() + "','" + comboBox4.Text + "','" + comboBox6.Text + "'," + textBox7.Text + "," + textBox12.Text + "," + textBox2.Text + "," + textBox12.Text + ")");
MessageBox.Show("inserted");

connect cc4 = new connect();
DataTable dtt4 = cc4.select(" select COMPANY,PRODUCT,RATE,QTY,AMT ,TOTAL from credit where BILLNO=" + textBox1.Text + "");
dgv.DataSource = dtt4;

}
catch (Exception)
{

connect cc = new connect();
cc.idu(" update creditcustomer set BILLNO=" + textBox1.Text + ",DATE='" + dateTimePicker1.Value.ToShortDateString() + "',TOTALAMOUNT=" + textBox7.Text + ",CURRENTBALANCE= " + textBox12.Text + ", PAID = " + ptxt17.Text + ", BALANCE=" + textBox12.Text + " where NAME='" + comboBox5.Text + "'");
MessageBox.Show("ITEMS ADDED");

connect c4 = new connect();
DataTable dt4 = c4.select(" select CONTRACTOR,SITE,CONVERT (VARCHAR(10),DATE,103) AS DATE, TOTALAMOUNT from creditcustomer where BILLNO=" + textBox1.Text + "");
dgv.DataSource = dt4;

}
dgv.DataSource = null;
textBox1.Text = "";
textBox2.Text = "";
textBox4.Text = "";
comboBox4.Text = "";
comboBox5.Text = "";
comboBox6.Text = "";
textBox7.Text = "";
textBox14.Text = "";
textBox12.Text = "";
textBox13.Text = "";
textBox15.Text = "";
textBox16.Text = "";
ptxt17.Text = "";

}

How can I avoid the error? please help me

Thanks in advance

Posted 7-Jan-11 0:26am

sevenbell

Updated 28-Jan-11 19:51pm

v5

Add a Solution

Comments

JF2015 7-Jan-11 6:37am

Edited to fix code formatting. Please use <pre> tags when posting code snippets.

adocas 7-Jan-11 7:01am

I can't write anything if I can't see the table schema.

3 solutions

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)

adocas · Answer 1 · 2011-01-07T00:38:00

If application is raising that error, is because you're attempting to insert a duplicate primary key in the table!

Review your Table schema and make sure that you're not trying to insert duplicate values.

In the foreach loop, I can view an if condition that I think that are not enough for your needs.

Anupama Roy · Answer 2 · 2011-01-09T18:58:00

Solution 2

primary key violation will occur when you are trying to insert a duplicate value or null value into the primary key column.In your case,when you are updating Check the value that is getting inserted in name field.I guess it's inserting null & hence throwing the violation.

Posted 9-Jan-11 18:58pm

Anupama Roy

Vipin Kumar Mallaya G · Answer 3 · 2011-01-09T19:43:00

Dear Friend
I am not repeating what other person had said as what they said is exactly true about primary key

In the above given code snippet,
the if condition may not be giving true condition even if the value value are equal ie there may be spaces for either one so cmaparison is not returning the proper value (true)
eg "Value" is not Equal to "Value "

I Think the if condition should be framed like this

VB

if(dr[1].ToString().trim().Equals(comboBox5.Text.trim()))

I hope this will solve the issue

Regards
Vipin Kumar Mallaya

dout in update

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0