How to insert relational data in Access database using ASP.NET

Question

I have two relational tables: the Profiles table which contains 3 kinds of user roles (Manager, Developer, Common User) and the Users table which contains information about the user and their roles ID (Profile_ID field) in the Access 2010 database.

I created a Webform in ASP.NET which should simply register users, asking for their names, selecting their roles in a dropdown list and inserting it all in the Access database. As in the following code:

Dim cs As String = ConfigurationManager.ConnectionStrings("Access 2010").ConnectionString
    Dim cn As New OleDbConnection(cs)
    Dim cmd As New OleDbCommand
    With cmd
        .CommandText = "INSERT INTO Users (nome, Profile_ID) VALUES ('" & Me.txtNome.Text & "', " & Me.ddRoles.SelectedIndex & ")"
        .Connection = cn
        .Connection.Open()
        .ExecuteNonQuery()
        .Connection.Close()
        .Dispose()
    End With
    cn.Dispose()

It happens that I can't insert in the database because they have a relationship between each other, it gives me an error. Actually, I just need to insert data in the Users table 'cause the roles are fixed. How can I do it?

EDIT

The error I get is You can not add or change records, it is necessary that they have a related record in table 'Profiles'.

You can not add or change records, it is necessary that they have a related record in table 'Profiles'. — Rodrigo Souza
– Rodrigo Souza, Commented Oct 12, 2011 at 21:54

agradl · Accepted Answer · 2011-10-12 22:04:10Z

1

Change your command text to take the SelectedValue of the dropdown instead of the selected index.

.CommandText = "INSERT INTO Users (nome, Profile_ID) VALUES ('" & Me.txtNome.Text & "', " & Me.ddRoles.SelectedValue & ")"

If that doesn't work you need to make sure that the drop down is being bound properly to the Role name/id.

answered Oct 12, 2011 at 22:04

agradl

3,5262 gold badges21 silver badges16 bronze badges

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

3 Comments

Rodrigo Souza Over a year ago

It worked now.. but it's creating only records with one profile (the Common User profile which has the ID number 5, to be exact). Although the HTML seems right: Common User assigned to value 5, Developer assigned as value 6 and Manager assigned as value 7

Rodrigo Souza Over a year ago

here is my code which populates the dropdownlist and tries to insert into the database: pastebin.com/a2XbqaAY

agradl Over a year ago

are you binding the drop down on page load every time? If so you'll lose the selected value each time you re-databind it.

ramprasad yadav · Accepted Answer · 2013-01-31 07:30:13Z

0

insert into tablename(papercode,paper type,semster,dato)values('" & textbox1.text & "', '" & dropdownlist1.text &"')

answered Jan 31, 2013 at 7:30

ramprasad yadav

1

Collectives™ on Stack Overflow

How to insert relational data in Access database using ASP.NET

EDIT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related