insert statement in database

Question

0.00/5 (No votes)

See more:

try to submit documents through dropdownlist when admin select approve/reject in gridview once then click on submit button then action performed i code in submit button

C#

protected void Button1_Click(object sender, EventArgs e)
       {

           string connStr = ConfigurationManager.ConnectionStrings["mydms"].ConnectionString;
           SqlConnection mySQLconnection = new SqlConnection(connStr);
           if (mySQLconnection.State == ConnectionState.Closed)
           {
               mySQLconnection.Open();
           }
              // DropDownList drdList;
          // SqlCommand mySqlCommand;


           foreach (GridViewRow row in GrdFileApprove.Rows)
           {

               if (row.RowType == DataControlRowType.DataRow)
               {
                   DropDownList DropDownListcontrol = row.FindControl("DropDownList4") as DropDownList;

                   SqlCommand cmd = new SqlCommand("approved", mySQLconnection);

                   docc.approve(Convert.ToInt32(Session["UserID"]), Convert.ToInt32(Session["DocID"]), Convert.ToInt32(Session["ApproveID"]), Convert.ToString(Session["Login2"]));


                   cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = Convert.ToInt32(GrdFileApprove.DataKeys[row.RowIndex]["UserID"]);

                   cmd.Parameters.Add("@DocID", SqlDbType.Int).Value = Convert.ToInt32(GrdFileApprove.DataKeys[row.RowIndex]["DocID"]);

                   cmd.Parameters.Add("@ApproveID", SqlDbType.Int).Value = DropDownListcontrol.SelectedValue;



                   cmd.ExecuteNonQuery();
               }
               else
               {
                   apfi.Text = "Error";
               }

           }


           if (mySQLconnection.State == ConnectionState.Open)
           {
               mySQLconnection.Close();
           }
       }

image
http://i.stack.imgur.com/BGVdM.png[^]

and there is also approval table when admin click on submit button respective columns filled
http://i.stack.imgur.com/mfuK8.png[^]

here is sp

SQL

ALTER procedure [dbo].[approved]
@UserID int,
@DocID int,
@ApproveID int,
@AppoveBy varchar(50)
as
insert Approval (UserID,DocID,ApproveID,AppoveBy)
values(@UserID,@DocID,@ApproveID,@AppoveBy)

here is function

C#

public void approve(int userid, int docid, int approveid, string ApproveBy)
        {
            db.ExecuteNonQuery("approved",new object[]{userid,docid,approveid,ApproveBy});
        }

but it show me error
SQL

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Approval_DocumentInfo". The conflict occurred in database "DMSFYPP", table "dbo.DocumentInfo", column 'DocID'. The statement has been terminated.

any solution please

Posted 7-Oct-13 8:50am

Diya Ayesa

Add a Solution

Comments

Azee 7-Oct-13 14:58pm

Debug and check the value of DocID that you are trying to insert in Approval table, it should be present in Primary key values of DocumentInfo table.

Diya Ayesa 7-Oct-13 15:05pm

yes it is present in documentinfo table.. and there are also several documents in dcumentinfo table

Azee 7-Oct-13 15:08pm

Take a closer look, that is the only reason this Exception could occur, where exactly the Exception is occuring, here:
docc.approve(Convert.ToInt32(Session["UserID"]), Convert.ToInt32(Session["DocID"]), Convert.ToInt32(Session["ApproveID"]), Convert.ToString(Session["Login2"]));
or here:
cmd.ExecuteNonQuery(); ?

Diya Ayesa 7-Oct-13 15:12pm

in above code..forget about cmd.ExeucuteNonquery();
an when i set a breakpoint it show me user '10' and docid '0' approveid '0' approveby 'sundus'

Azee 7-Oct-13 15:15pm

That is the problem. docid '0', I am sure there is no docid in DocumentInfo table with value 0. Why are you getting values from Session for docc.approve? when did you store them there?

ZurdoDev 7-Oct-13 15:26pm

I'd suggest posting as solution.

Diya Ayesa 7-Oct-13 15:30pm

there is no soultion yettt..

ZurdoDev 7-Oct-13 15:32pm

Yes, the error is correct. You are trying to pass in a value that does not exist in the foreign table. You just need to fix that.

Diya Ayesa 7-Oct-13 15:34pm

in documentinfo table there are several docid ...so how i solve it...

Diya Ayesa 7-Oct-13 15:35pm

i try so may times to fix but i can't thats y ask the question

ZurdoDev 7-Oct-13 15:38pm

What do you mean? You need to pick the matching one.

Diya Ayesa 7-Oct-13 15:06pm

would u please tell me i use correct sp or not?

Azee 7-Oct-13 15:11pm

By the way, your code doesn't make much sense, you are trying to call the same SP twice with different values using docc.approve and rest of the code.

Diya Ayesa 7-Oct-13 15:22pm

yes i comment on it..would u plz tell me any other solution

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)

**Azee** · Answer 1 · 2013-10-07T09:37:00

Hey there,

As stated in your comments above you are getting the docid as '0', which is the reason why insertion in table Approval is causing the Exception.

You are using the values from the Session in this statement, which does not seem right as its in a loop and you'll be inserting same values for all rows:

C#

docc.approve(Convert.ToInt32(Session["UserID"]), Convert.ToInt32(Session["DocID"]), Convert.ToInt32(Session["ApproveID"]), Convert.ToString(Session["Login2"]));

Firstly, I would suggest you to use the GridView DataKeys for UserID and DOCID and DropDownList value from approval ID.

Secondly you could just remove this method call:

C#

docc.approve(Convert.ToInt32(Session["UserID"]), Convert.ToInt32(Session["DocID"]), Convert.ToInt32(Session["ApproveID"]), Convert.ToString(Session["Login2"]));

and add another parameter in the below code for AppoveBy :

C#

cmd.Parameters.Add("@ApproveBy", SqlDbType.VarChar, 50).Value = Convert.ToString(Session["Login2"]);

Let me know if it helps

Azee...