0

Hello, I'm building an expenses application in asp.net and I'm attempting to insert some data in a SQL database in visul studio but I get the following error (screenshot here http://s13.postimg.org/4kjmn4nev/SQL_error.jpg):

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_expenses_ToexpenseCode". The conflict occurred in database "Applications", table "dbo.expenseCode", column 'Id'.
The statement has been terminated.

I've done a bit of googling and I've found something on stackoverflow saying that the error might happen because the table that contains the primary key needs to have data in before I insert data in the table with the foreign key...and that's exactly my situation, in that the table with the primary key (expenseCode) is empty.
So, as this is the first time I deal with primary and secondary keys, I thought it might be worth asking a few more questions.
First, here are the two tables:
expenseCode (primary key is id):
http://s11.postimg.org/6wdkcdlzn/expense_Code.jpg
and expenses (foreign key):
http://s12.postimg.org/b0mamknd9/expenses.jpg

Now, the first table expenseCode is supposed to contain the id (1,2,3,4) and a string denoting the expense type (food, bills, rent, car); the expenses table, among the other things, contains an int reference to that string stored in the other table (1 for rent, 2 for car, 3 for bills, 4 for food). So, if the solution to my problem is the above, how do I populate the first table? Should I hardcode the values (which to me seems the best solution)?

This is what I'm doing trying to populate expenses:

protected void submitForm(object sender, EventArgs e){
        //billValue.Text = "submitted";
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
        strInsert = "INSERT INTO expenses(Date,ExpenseId,Cost,Comment) VALUES (@date,@expenseid,@cost,@comment)";
        sqlCmd = new SqlCommand(strInsert, hookUp);
        sqlCmd.Parameters.Add("@date", DateTime.Now.ToString("MM/dd/yyyy"));
        sqlCmd.Parameters.Add("@expenseid", expenseID);
        sqlCmd.Parameters.Add("@cost", Convert.ToDecimal(billValue.Text));
        sqlCmd.Parameters.Add("@comment", expenseComment.Value);
        hookUp.Open();
        sqlCmd.ExecuteNonQuery();
        hookUp.Close();




        initForm();//re Initialize form after submission
    }

thanks

4
Contributors
30
Replies
67
Views
2 Years
Discussion Span
Last Post by djjeavons
Featured Replies
  • That's true but then you could look at using the `Request.Form` option that I mentioned. Or alternatively, leave as is but store the selected expenseID in a session variable which you can clear once you have submitted to the database. Read More

0

Hi

You are correct in that the error is caused because you are attempting to insert a record into your expenses table without a related record existing in your expense_code table. Your expense_code table is commonly referred to as a Lookup table as it contains values that will be used multiple times in other tables.

Usually, when creating lookup tables you would have some default values in there, so yes, it would be a good idea to populate this table with the values you mentioned.

HTH

0

hi djjeavons,
thanks I populated the expenseCode table
expensesTable.jpg

but still get an error, the same one in fact. Is there something wrong with the data I used to populate the table with?
So just to make sure I've done things properly, let's summarize everything quickly:
First table called expenses (screenshot in previous post) has a primary key which is the recordedId and a foreign key which is ExpenseId. The second table called expenseCode has a primary key called Id (and that's the one that should be linked to the foreign key of the other table)

Sorry SpottyBlue, not sure what you mean?

Edited by Violet_82

0

Line 7 of your code should refer to expenseID.Text or expenseId.SelectedValue (in the case of a dropdown or radiobutton list.)

Edited by madCoder: Added other control type value properties.

0

Line 7 of your code should refer to expenseID.Text or expenseId.SelectedValue (in the case of a dropdown or radiobutton list.)

No, that is a int variable and it has absolutely nothing to do with the error I'm getting!

Edited by Violet_82

0

Right, maybe it is better to have all the code in here, just in case there is something I'm missing.
So here is the Home.aspx.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;


public partial class Home : System.Web.UI.Page
{
    private int expenseID;//expense string can be rent, car, bills, food as integer so as 1,2,3,4
    private SqlCommand sqlCmd;
    private SqlConnection hookUp;
    private string strInsert;
    private string radioButtonId;
    private SqlDataReader reader;
    //private int storedInt;



    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack) {//returns false the first time the page is displayed true when redisplayed
            initForm();
        }

    }
    private void initForm() {
        amount.Visible = false;//hide amount info on page load
        rent.Checked = false;//unselect all radio buttons
        car.Checked = false;
        bills.Checked = false;
        food.Checked = false;
        radioButtonID.Value = "";//empty hidden field
        //System.Diagnostics.Debug.WriteLine("oh oh oh");

    }
    protected void CheckedChanged(object sender, EventArgs e)
    {
        amount.Visible = true;//showamount info on page load  
        /*find out the id of the selected button*/
        RadioButton buttonId = sender as RadioButton;//cast sender as button
       // radioButtonID.Text = buttonId.ID;//select the ID
       radioButtonId = radioButtonID.Value = buttonId.ID;//select the ID
       switch (radioButtonId) { 
           case "rent":
               expenseID = 1;
               break;
           case "car":
               expenseID = 2;
               break;
           case "bills":
               expenseID = 3;
               break;
           case "food":
               expenseID = 4;
               break;
       }
        //TO REMOVE
      //outputTxt.Text =  DateTime.Now.ToString("dd/MM/yyyy");
     //  outputTxt.Text = Convert.ToString(expenseID);

    }
    protected void submitForm(object sender, EventArgs e){
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
        strInsert = "INSERT INTO expenses(Date,ExpenseId,Cost,Comment) VALUES (@date,@expenseid,@cost,@comment)";
        sqlCmd = new SqlCommand(strInsert, hookUp);
        //  sqlCmd.Parameters.Add("@date", DateTime.Now.ToString("dd/MM/yyyy"));
        sqlCmd.Parameters.Add("@date", DateTime.Now);
        sqlCmd.Parameters.Add("@expenseid", expenseID);
        sqlCmd.Parameters.Add("@cost", Convert.ToDecimal(billValue.Text));
        sqlCmd.Parameters.Add("@comment", expenseComment.Value);
        hookUp.Open();
        sqlCmd.ExecuteNonQuery();
        hookUp.Close();




        initForm();//re Initialize form after submission
    }

    //to check what's in the other sql table
    //protected void getValues(object sender, EventArgs e) {

    //    hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
    //    strInsert = "SELECT Id FROM expenseCode";
    //    sqlCmd = new SqlCommand(strInsert, hookUp);
    //    hookUp.Open();
    //    reader = sqlCmd.ExecuteReader();
    //    while (reader.Read())
    //    {
    //        outputTxt.Text += Convert.ToString(reader["Id"]);
    //    }
    //    reader.Close();
    //    hookUp.Close();
    //}


}

or available here if you prefer http://pastebin.com/CK0TfiD6

And here is the Home.aspx, also available here http://pastebin.com/hRU54JVv

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="Home.aspx.cs" Inherits="Home" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="HeadingPlaceholder" Runat="Server">
    <div class="header">
        <h2>Upload expenses</h2>
    </div>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceholder" Runat="Server">
    <p>Upload your expenses here.</p>
    <div class="expensesForm">
        <div class="control-group">
            <label class="control-label" for="rent">Rent</label>
            <div class="controls">
                <asp:RadioButton ID="rent" runat="server" GroupName="expenses" ClientIDMode="Static" AutoPostBack="true" OnCheckedChanged="CheckedChanged" />            
            </div>
        </div>
        <div class="control-group">
            <label class="control-label" for="car">Car</label>
            <div class="controls">
                <asp:RadioButton ID="car" runat="server" GroupName="Expenses" ClientIDMode="Static" AutoPostBack="true" OnCheckedChanged="CheckedChanged" />
            </div>
        </div>
        <div class="control-group">
            <label class="control-label" for="bills">Bills</label>
            <div class="controls">
                <asp:RadioButton ID="bills" runat="server" GroupName="Expenses" ClientIDMode="Static" AutoPostBack="true" OnCheckedChanged="CheckedChanged" />            
            </div>
        </div>
        <div class="control-group">
            <label class="control-label" for="food" >Food</label>
            <div class="controls">
                <asp:RadioButton ID="food" runat="server" GroupName="Expenses" ClientIDMode="Static" AutoPostBack="true" OnCheckedChanged="CheckedChanged" />            
            </div>
        </div>
         <div class="control-group">
             <asp:Panel ID="amount" runat="server">
                <label class="control-label" for="billValue">Enter amount</label>
                <div class="controls">
                    <asp:TextBox ID="billValue" runat="server" Visible="True" ></asp:TextBox>
                </div>
                <label class="control-label" for="expenseComment">Comment</label>
                <div class="controls">
                    <textarea id="expenseComment" runat="server"></textarea>
                </div>

                <div class="controls">
                  <input id="Submit1" type="submit" value="Submit" onserverclick="submitForm" runat="server"/>
                  <%--<input id="QueryDb" type="submit" value="Query db" onserverclick="getValues" runat="server"/>--%>
                </div>              

            </asp:Panel>
        </div>
        <%--<asp:TextBox ID="radioButtonID" runat="server" ReadOnly="True" ></asp:TextBox>--%>
        <%--<asp:TextBox ID="outputTxt" runat="server" Text="output"></asp:TextBox>--%>
        <asp:HiddenField ID="radioButtonID" runat="server" />
    </div>

</asp:Content>

Sorry, a bit long, I'll try to summarize here what I had in mind. When you click on a radio button, the ID of that button gets checked and depending on its value, the variable expenseID becomes 1,2,3 or 4:

switch (radioButtonId) { 
           case "rent":
               expenseID = 1;
               break;
           case "car":
               expenseID = 2;
               break;
           case "bills":
               expenseID = 3;
               break;
           case "food":
               expenseID = 4;
               break;

When you submit the form, that expenseID variable is passed on onto the sql table sqlCmd.Parameters.Add("@expenseid", expenseID);
It has been brought to my attention though, that this may not work because everytime the page is submitted to the server for processing, another instance of the page object is created and therefore that expenseID variable is reset to 0 causing the error (the way around it could be to do this inside the submitForm method ). Let's look at the SQL tables, I think they are OK the structure can be seen here:
http://s11.postimg.org/6wdkcdlzn/expense_Code.jpg (expenseCode, screenshot of the data is in the previous post)
http://s12.postimg.org/b0mamknd9/expenses.jpg (expenses, currently empty as I'm trying to populate it with my application)
Any help would be appreciated as I'm a bit stumped

0

Hi

It's been a while since I have done ASP.NET WebForms but I think you may have answered your own problem in that the page object is recreated and you are not receiving the correct value for your expense ID via the radio buttons. This would definitely cause the error you are receiving if the expense ID is zero.

Have you tried putting a breakpoint in your code where you are assigning the expense ID (switch statement) and ensuring you get a value. I assume you would be as this is fired when the checked state changes.

Also, I wonder if it is because you are assigning expense ID in the checked changed event but expecting it to still be available in the SubmitForm event when in fact it might not be as these are two completely different events that are fired at different times. So again, as you mentioned you might be better served testing which radio option is selected when you submit the form and not in the CheckedChanged event.

Finally, if neither of these work, have you tried doing a Request.Form("radioButtonID") to see if the value is available there. This could be done in the SubmitForm event.

Just some thoughts, as like I said, it has been a while since I have done WebForms development.

HTH

0

Thanks. I did put a breakpoint there and the values returned are OK. So I'll then get the ID and change the value of expenseID accordingly inside the submitForm function (I guess it's just a matter of moving the switch statement). But there is one thing that bugs me though: if all these problems are caused by the above, shouldn't I get a different type of error rather than

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_expenses_ToexpenseCode". The conflict occurred in database "Applications", table "dbo.expenseCode", column 'Id'.
The statement has been terminated.
0

But there is one thing that bugs me though: if all these problems are caused by the above, shouldn't I get a different type of error rather than

Well yes if the value of expense ID is zero.

I have just recreated your tables using the screen shots you provided and populated the expense code table with the four values that you have and then proceeded to insert a record into the expenses table and did not receive any errors so there is nothing wrong with your database structure.

Let us know if moving the switch statement has helped.

Also, you might want to try putting a breakpoint in the SubmitForm (before making changes) and then test the value of expenseID. I am guessing it will be zero at this point.

Edited by djjeavons

0

Ah, one thing though: if I move the switch statement, it won't work will it, because I'm relying on the sender which in the CheckedChanged method will work but in the submitForm, it won't "contain" (if that's the right way to say) the radio button anymore, so I can't get its ID. Correct? I'll add a breakpoint and see if I can look at the value of expenseID as you said

1

That's true but then you could look at using the Request.Form option that I mentioned. Or alternatively, leave as is but store the selected expenseID in a session variable which you can clear once you have submitted to the database.

0

Right, the expenseID is indeed 0 inside submitForm. OK so the choices I have are 2:
1)Use Request.Form, which I've never used (or heard of), but happy to investigate if you think it's a good idea. If I go with this option I will have to move the switch inside the submitForm() method and hopefully cook something up with the Request.Form which will allow me to get around the problem described above
2)session variable: never heard of that before either, but again, more than happy to investigate: this option will allow me to keep things as they are and get around the above issue with expenseID being 0 and not keeping its value.
As I don't know much about c# to decide, which one do you think is better? I suspect the 2nd one will involve less changes? So is it just a matter of storing the value currently in expenseID in a session variable and use it to push the content onto the database?

0

Hi

To be fair, both options should not require you to change your code (at least I don't believe it would). The Session option is the easiest to implement, so in your CheckedChanged event, once you have the expense ID add the following line of code:

session["expenseID"] = expenseID;

Then, when you are adding the expenseID to your parameter in the submitForm use:

sqlCmd.Parameters.Add("@expenseid", session["expenseID"]);

To remove the session once the data has been added to the database you can do:

session.Remove("expenseID");

HTH

0

Hi, thanks, sorry I've been reading a bit about state management and sessions, interesting stuff, never heard of that before : -), but no surprise here as I'm still at the very beginning!

I seem to understand that, as I've seen from the examples, the session variable is not declared? is that possible? But from what you're saying, your copying an existing string variable (expenseID) in a session variable (which doesn't have any type?)session["expenseID"] = expenseID;.
So if I understand everything correctly my code should look like this:

public partial class Home : System.Web.UI.Page
{
    private int expenseID;
    ...



protected void CheckedChanged(object sender, EventArgs e)
    {
        amount.Visible = true;//showamount info on page load  
        /*find out the id of the selected button*/
        RadioButton buttonId = sender as RadioButton;//cast sender as button
        // radioButtonID.Text = buttonId.ID;//select the ID
        radioButtonId = radioButtonID.Value = buttonId.ID;//select the ID
        switch (radioButtonId)
        {
            case "rent":
                expenseID = 1;
                break;
            case "car":
                expenseID = 2;
                break;
            case "bills":
                expenseID = 3;
                break;
            case "food":
                expenseID = 4;
                break;
        }
        session["expenseID"] = expenseID;
      }



protected void submitForm(object sender, EventArgs e){
    ...
    sqlCmd.Parameters.Add("@expenseid", session["expenseID"]);
    sqlCmd.Parameters.Add("@cost", Convert.ToDecimal(billValue.Text));
    sqlCmd.Parameters.Add("@comment", expenseComment.Value);
    hookUp.Open();
    sqlCmd.ExecuteNonQuery();
    hookUp.Close();
    session.Remove("expenseID");
}

Edited by Violet_82

0

Um, I get an error unfortunately

The name 'session' doesn't exist in the current context

The using statements seem all there, but on the net people with the same error are saying that it may be that somehow my class is not inheriting from Page...but surely if that was the case I would have sen the error before...

0

Hi

If the session ID does not exist you can add it. So you might do the following in your CheckedChanged event:

    if (Session["expenseID"] == null)
        Session.Add("expenseID", expenseID);

HTH

0

um, still no joy, same error, or I should say errors, as there are 3 of them:
the first one in the checkedChanged, towards the end of it session["expenseID"] = expenseID; (note that I have added the code you suggested at the top of the method):

protected void CheckedChanged(object sender, EventArgs e)
    {
        amount.Visible = true;//showamount info on page load  
        //adding the session 
        if (Session["expenseID"] == null)
            Session.Add("expenseID", expenseID);

        /*find out the id of the selected button*/

        RadioButton buttonId = sender as RadioButton;//cast sender as button
        // radioButtonID.Text = buttonId.ID;//select the ID
        radioButtonId = radioButtonID.Value = buttonId.ID;//select the ID
        switch (radioButtonId)
        {
            case "rent":
                expenseID = 1;
                break;
            case "car":
                expenseID = 2;
                break;
            case "bills":
                expenseID = 3;
                break;
            case "food":
                expenseID = 4;
                break;
        }
        session["expenseID"] = expenseID;
    }

The second and third errors are in the submitForm method, when I insert the data in the SQL table sqlCmd.Parameters.Add("@expenseid", session["expenseID"]); and when I remove the session session.Remove("expenseID");:

 protected void submitForm(object sender, EventArgs e){
        hookUp = new SqlConnection("Server=localhost\\SqlExpress;Database=Applications;" + "Integrated Security=True");
        strInsert = "INSERT INTO expenses(Date,ExpenseId,Cost,Comment) VALUES (@date,@expenseid,@cost,@comment)";
        sqlCmd = new SqlCommand(strInsert, hookUp);
        //  sqlCmd.Parameters.Add("@date", DateTime.Now.ToString("dd/MM/yyyy"));
        sqlCmd.Parameters.Add("@date", DateTime.Now);
        sqlCmd.Parameters.Add("@expenseid", session["expenseID"]);
        sqlCmd.Parameters.Add("@cost", Convert.ToDecimal(billValue.Text));
        sqlCmd.Parameters.Add("@comment", expenseComment.Value);
        hookUp.Open();
        sqlCmd.ExecuteNonQuery();
        hookUp.Close();
        session.Remove("expenseID");



        initForm();//re Initialize form after submission
    }

Could I be that I placed them in the wrong place?

0

that seems to be the problem, things work now. Thanks a lot for your help. Can I ask you, are you aware of any good tutorial on sessions and state management I can look at? The topic is interesting and I think it will be useful for me to learn more. I couldn't really find a good tutorial unfortunately, not a good one that I could understand, some were really way too advanced for me.
thanks

0

Hmmm, that would suggest that your class is not inheriting from Page. Although your original code would shows that it is.

Can you zip up your project and attach it and I will take a look.

0

oops, maybe you didn't see the previous post : -), session must have a capital S and not small, that's why I got the errors

0

No problem. Yep I have 2 books on ASP.NET (I started with 3.5 and now have 4.5) but none of them mentioned anything about sessions...that's why I asked about an online tutorial :-).
Anyway, can I ask you a cheeky question still about states? I've read that you could use a bunch of things to achieve what we have done now, even hidden fields. Now, in theory, would I be able to get the same results if I had a hidden field where I stored the ID of the radio button clicked on, then I'd use the switch statement as I did in my code and then copy the expenseID which is the variable that holds the int (1,2,3 or 4 depending on the ID) in another hidden field and go on as normal? Would that work? Funny thing is, I did use a hidden field but only to store the ID of the radio button, not to store expenseID which kept going back to 0 at each new session. Here is some code and pseudo code to illustrate that:

 protected void CheckedChanged(object sender, EventArgs e)
    {
        amount.Visible = true;//showamount info on page load         

        /*find out the id of the selected button*/

        RadioButton buttonId = sender as RadioButton;//cast sender as button        
        radioButtonId = radioButtonID.Value = buttonId.ID;//select the ID and copy it into hidden field radioButtonId
        switch (radioButtonId)
        {
            case "rent":
                expenseID = 1;
                break;
            case "car":
                expenseID = 2;
                break;
            case "bills":
                expenseID = 3;
                break;
            case "food":
                expenseID = 4;
                break;
        }
        //Now I will store the expenseID in another hidden field and use its value in the submitForm() method
        //I presume its value will be preserved and I will be able to use it?
    }
0

Yes, you could use a hidden field to do this. You could then make use of the Request.Form("ID of control") to get the value. For example, given the following markup:

    <input type="hidden" value="Hello World" name="myHiddenField" />
    <input type="submit" id="Button1" value="Click Me"/>

You could do this in your code behind:

    Response.Write(Request.Form["myHiddenField"]);
0

Fab, thanks and I take I can save that in a variable to then reuse if I need to, something like string text = Response.Write(Request.Form["myHiddenField"]);

0

Almost, it would be string text = Request.Form["myHiddenField"];

Also, I would look at the idea of using client side scripting to update the hidden field when one of the radio buttons is selected, this way you could remove the need to do this in the CheckedChanged event and simply read it from the form in the submitForm method. Not that you have to do this, but it would be tidier.

0

OK great, thanks for all your help and patient explanations, much appreciate it. By the way I had a look at the book you suggested, it looks pretty good, will probably get that!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.