Adding data to MS Access using ASP.net

Question

My school project is to make a website where people can sell/buy used school items like calculators etc. I am using ASP.net & vb.net in Microsoft Visual Studio.

My problem is that I don't really get how to add the data filled in on the website, inserted into the MS Access database file. The problem is that the information is spread over 2 tables.

I've tried to use 2 different SQL statements, 1 for each table, but this doesn't work out either. I've changed the format of the database a bit and I think that now the problem is to input the relation between the 2 tables but I have no idea how to.

I have this code running right now

    Protected Sub bntVerzenden_Click(sender As Object, e As EventArgs) Handles bntVerzenden.Click



    Dim VerkopenConn As New OleDb.OleDbConnection
    Dim VerkopenCommArtikel, VerkopenCommGebruiker As New OleDb.OleDbCommand

    VerkopenConn.ConnectionString = My.Settings.SvShopConnection
    VerkopenCommArtikel.Connection = VerkopenConn
    VerkopenCommGebruiker.Connection = VerkopenConn

    'Gebruiker gegevens aan de database toevoegen
    VerkopenCommGebruiker.CommandText = "INSERT INTO tblGebruiker (GebruikersNaam, GebruikersVoornaam, GebruikersEmail, GebruikersGSM) VALUES (@GebruikersNaam, @GebruikersVoornaam, @GebruikersEmail, @GebruikersGSM)"

    VerkopenCommGebruiker.Parameters.AddWithValue("@GebruikersNaam", txtNaam.Text)
    VerkopenCommGebruiker.Parameters.AddWithValue("@GebruikersVoornaam", txtVNaam.Text)
    VerkopenCommGebruiker.Parameters.AddWithValue("@GebruikersEmail", txtEmail.Text)
    VerkopenCommGebruiker.Parameters.AddWithValue("@GebruikersGSM", txtGsm.Text)

    'Artikel gegevens aan de database toevoegen
    VerkopenCommArtikel.CommandText = "INSERT INTO tblArtikel (ArtikelBeschrijving, ArtikelPrijs, RubriekNaam, ArtikelAankoopdatum, ArtikelTekoopgezet, ArtikelBeschikbaar) VALUES ( @ArtikelBeschrijving, @ArtikelPrijs, @RubriekNaam, @ArtikelAankoopdatum, @ArtikelTekoopgezet, @ArtikelBeschikbaar)"

    VerkopenCommArtikel.Parameters.AddWithValue("@ArtikelBeschrijving", txtArtikelBeschrijving.Text)
    VerkopenCommArtikel.Parameters.AddWithValue("@ArtikelPrijs", txtArtikelPrijs.Text)
    VerkopenCommArtikel.Parameters.AddWithValue("@RubriekNaam", lstRubriek.SelectedItem.ToString)
    VerkopenCommArtikel.Parameters.AddWithValue("@ArtikelAankoopdatum", CalAankoop.SelectedDate)
    VerkopenCommArtikel.Parameters.AddWithValue("@ArtikelTekoopgezet", calVerkoop.SelectedDate)
    VerkopenCommArtikel.Parameters.AddWithValue("@ArtikelBeschikbaar", True)

    If VerkopenConn.State = ConnectionState.Closed Then VerkopenConn.Open()
    VerkopenCommGebruiker.ExecuteNonQuery()
    VerkopenCommArtikel.ExecuteNonQuery()






End Sub

End Class

With this error at the second ExecuteNonQuery:

An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code

Additional information: You cannot add or change a record because a related record is required in table 'tblGebruiker'.

The first query works and that data has been added to the database.

Database relations down below.

Relations

Does it give any errors when it runs? Also, could you post some details about your access database? — Amber
– Amber, Commented May 5, 2016 at 14:06
You're missing RubriekID in your columns section of your second query. — Steven B.
– Steven B., Commented May 5, 2016 at 14:11
@ThimoDemey Yeah, I saw that afterwards again. Just as a test, try removing the relationship between tblArtikel and tblGebruiker. Then run the query. — Steven B.
– Steven B., Commented May 5, 2016 at 15:35

Steven B. · Accepted Answer · 2016-05-05 15:05:17Z

1

Your first exception was because you were missing a column in your query. This second exception is PROBABLY caused by you having a one-to-one relationship defined between tblArtikel and tblRubriek. You need to change the relationship to one-to-many to allow duplcates, in this case. tblRubriek would be the "one" side and tblArtikel would be the "many" side. Because you have one primary key in tblRubriek that associates with "many" rows in tblArtikel.

edited May 5, 2016 at 15:05

answered May 5, 2016 at 14:30

Steven B.

9,4323 gold badges28 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thimi11 Over a year ago

So in 'tblArtikel' do I have to use 'RubriekNaam' instead of 'RubriekID' and make the relation with 'RubriekNaam' in 'tblRubriek' , right?

Amber Over a year ago

RubriekID is not a primary key in tblArtikel, so it shouldn't be a problem to insert it, but you do need to mention that it need to be inserted "INSERT INTO tblArtikel (RubriekID, ArtikelBeschrijving, ArtikelPrijs, ArtikelAankoopdatum, ArtikelTekoopgezet) VALUES (@RubriekID, @ArtikelBeschrijving, @ArtikelPrijs, @ArtikelAankoopdatum, @ArtikelTekoopgezet)"

Steven B. Over a year ago

@ThimoDemey Sorry. As I mentioned above, the query that Amber posted is what you should have. But the relationship you defined may be the issue.

Thimi11 Over a year ago

I've changed the query but still have the same error.

Collectives™ on Stack Overflow

Adding data to MS Access using ASP.net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related