IINSERT INTO using ASP and MS Access

[BrianLJMU]

I was wondering if somebody could help me with a small problem i have, i am connecting an ASP website to an Access Database. (The coding is in C#)

I am very inexperienced in ASP and i can't find a solution in the various threads.
I have a registration page that requires data to be stored in two seperate tables (CustomerDetails and CustomerEmail)

The error message i get on my page is: You cannot add or change a record because a related record is required in table 'CustomerDetails'.

I need to update the customer details table, then the customer email table.

Appreciate any help

Regards Brian

Here is my code:

Code: Select all

Using  System;
using System.Web;
using System.Data;
using System.Data.OleDb;
public partial class _Register : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
// Click the button to register with website
protected void Button1_Click(object sender, EventArgs e)
{
//Establish the connection with the database
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("~") + @"\App_Data\websitedesign.mdb";
//New connection
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = myConnection;
//Insert data into the customer details table
myCommand.CommandText = "INSERT INTO CustomerDetails([FirstName], [Surname], [HouseNumber], [RoadName], [Town], [City], [Postcode]) VALUES (@FirstName, @Surname, @HouseNumber, @RoadName, @Town, @City, @Postcode)";
myCommand.Parameters.Add("@FirstName", OleDbType.Char).Value = txtFirstName.Text;
myCommand.Parameters.Add("@Surname", OleDbType.Char).Value = txtSurname.Text;
myCommand.Parameters.Add("@HouseNumber", OleDbType.Char).Value = txtHouseNumber.Text;
myCommand.Parameters.Add("@RoadName", OleDbType.Char).Value = txtRoadName.Text;
myCommand.Parameters.Add("@Town", OleDbType.Char).Value = txtTown.Text;
myCommand.Parameters.Add("@City", OleDbType.Char).Value = txtCity.Text;
myCommand.Parameters.Add("@Postcode", OleDbType.Char).Value = txtPostcode.Text;
//Insert data into the customer email table
myCommand.CommandText = "INSERT INTO CustomerEmail([EmailAddress], [Password], [SecretQuestion], [SecretAnswer]) VALUES (@EmailAddress, @Password, @SecretQuestion, @SecretAnswer)";
myCommand.Parameters.Add("@EmailAddress", OleDbType.Char).Value = txtEmail.Text;
myCommand.Parameters.Add("@Password", OleDbType.Char).Value = txtPassword.Text;
myCommand.Parameters.Add("@SecretQuestion", OleDbType.Char).Value = txtQuestion.Text;
myCommand.Parameters.Add("@SecretAnswer", OleDbType.Char).Value = txtAnswer.Text;
myConnection.Open();

try
{
//Execuste the query, display message if successful
myCommand.ExecuteNonQuery();
Label1.Text = "Record Inserted";
}
catch (Exception ex)
{
//Displays an error message if unsuccessful
Label1.Text = ex.Message;
}
finally
{
myConnection.Close();
}
}
}

[Monie]

You seems executing two different sql with one execute command!
I don't know C# so I dont really understand what the code is doing

You should execute the 2 SQL that you have with 2 execute command, I guess
Cheers...

[BrianLJMU]

Thank you for replying.

i think i know whats wrong know

Thanks

[Monie]

Just make sure you post in the solution here so that people with the same problem can learn from your mistake
Cheers...

[BrianLJMU]

Yes OK,

But thats if i manage to get the solution

regards,
Brian

[Monie]

Originally Posted by BrianLJMU

i think i know whats wrong know

Don't you? So it it related to my previous post up there ^?

[BrianLJMU]

I thought i knew how to fix it, but it didn't work.

i think your method is correct. i will look into it further.

Thanks

[Monie]

Let me know if that success!
Cheers...

[BrianLJMU]

It wasn't a success. It's back to the drawing board.

As i am inexperienced at coding, i am unsure how to execute the two pieces of SQL together.

I have a book from library, so will post any results i do get.

Thanks for your help.

[Monie]

I am not perfectly sure about the sql execution that was causing the problem!
Try google your error message. Here is what I found!

[BrianLJMU]

Thank you so much.

I altered the code, then checked to see what was inserted to the database.

The foriegn key field is not updating (this is linked to an autonumber field)

So i will use what you found and edit the relationship.

I also found code that updates the foriegn key in the SQL but i can't find it again now. Which is quite annoying.

Your help is much apprieciated.

Regards
Brian

[BrianLJMU]

Thanks for your help Monie,

i slightly tweaked the code, but the fundamental error was in the foriegn key field. the default was set to 0 - i have removed this for now as i can insert data into a table. I can fix that another time.

My final code for inserting into two tables (but unable to update foriegn key - If anyone can help, it will be much apprieciated)

using System;
using System.Web;
using System.Data;
using System.Data.OleDb;

public partial class _Register : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
// Once user has clicked the submit button the data is sent to the Database
protected void Button1_Click(object sender, EventArgs e)
{
//Establish the connection with the database
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("~") + @"\App_Data\websitedesign.mdb";
myConnection.Open();

{//New connection to insert data into the first table
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = myConnection;

//SQL to Insert data into the customer details table
myCommand.CommandText = "INSERT INTO CustomerDetails([FirstName], [Surname], [HouseNumber], [RoadName], [Town], [City], [Postcode]) VALUES (@FirstName, @Surname, @HouseNumber, @RoadName, @Town, @City, @Postcode)";
myCommand.Parameters.Add("@FirstName", OleDbType.Char).Value = txtFirstName.Text;
myCommand.Parameters.Add("@Surname", OleDbType.Char).Value = txtSurname.Text;
myCommand.Parameters.Add("@HouseNumber", OleDbType.Char).Value = txtHouseNumber.Text;
myCommand.Parameters.Add("@RoadName", OleDbType.Char).Value = txtRoadName.Text;
myCommand.Parameters.Add("@Town", OleDbType.Char).Value = txtTown.Text;
myCommand.Parameters.Add("@City", OleDbType.Char).Value = txtCity.Text;
myCommand.Parameters.Add("@Postcode", OleDbType.Char).Value = txtPostcode.Text;
//Execute the query
myCommand.ExecuteNonQuery();
//Close the above connection with the database
myConnection.Close();
}

{//New connection to insert data into second table
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = myConnection;
//Open new connection
myConnection.Open();
//SQL to Insert data into the customer details table
myCommand.CommandText = "INSERT INTO CustomerEmail([EmailAddress], [Password], [SecretQuestion], [SecretAnswer]) VALUES (@EmailAddress, @Password, @SecretQuestion, @SecretAnswer)";
myCommand.Parameters.Add("@EmailAddress", OleDbType.Char).Value = txtEmail.Text;
myCommand.Parameters.Add("@Password", OleDbType.Char).Value = txtPassword.Text;
myCommand.Parameters.Add("@SecretQuestion", OleDbType.Char).Value = txtQuestion.Text;
myCommand.Parameters.Add("@SecretAnswer", OleDbType.Char).Value = txtAnswer.Text;
myCommand.ExecuteNonQuery();
//If successful then a message is displayed on the website
Label1.Text = "Record Inserted";
//Close the above connection
myConnection.Close();
}
}
}

[Monie]

Glad to help you but I am not sure I can help you on the Foreign Key thing
I like how you re-design your code to execute the 2 SQL Command