Need to get the value of a primary key from one table and store it in the foriegn key of another table

Question

User-430157385 posted

I thought that if I linked it in Microsoft Access through relationships, it would link, guess I was wrong. Is there anyway to do this?

I tried using INSERT INTO {tablename] SELECT [     ]  FROM [   ]...... and it worked, however the values were entered in a new row. I'm guessing it's the way I am structuring the insert statements. First I use a regular insert statement to take in data from my form, then I use the above insert staement to take in the data from the other table, but it enters it in the next row.

Please help. Thanks in advance.

Answer 1

User-1199946673 posted

The purpose of an INSERT statement is to add a new row in your table. so I'm not really sure what your question is? But if I look into the title of your question, I think this what you're lookig for:

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

Answer 2

User-430157385 posted

Thanks for your reply. I'm sorry I did not explain it very well. I am going to read that article you linked me. In the meantime, I have come up with a better explanation of what I'm trying to do.

So I have four tables and they are linked by primary and foreign keys. I have a form that is used to enter information and I have set my primary keys column set to autofill in Access database. How can I take those values from the autofill column and enter it in the other table that I want. This is my Insert code.

sqlString = "INSERT INTO [Members] ([MemberName], [Child], [Adult], [PaymentMethod]) VALUES ('" + MemberName + "','" + Child + "','" + Adult + "','" + PaymentMethod + "')"
    cmd = New OleDbCommand(sqlString, myConn)
    cmd.ExecuteNonQuery()

That is what I use to input the info from my form and it works. Now there is another column that nothing is being entered in because it is the foreign key column and my primary key column in the other table is set to autofill with numbers. How can I fill my foreign key column to match my primary key column in the other table? I hope I explained it well this time around.

Answer 3

User-1199946673 posted

How can I take those values from the autofill column and enter it in the other table that I want

Read the article

and I think you should also read this:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

Answer 4

User-430157385 posted

Hi, Hans. I'm sorry, but I've read through the articles and implemented what I learned from them and I keep getting errors. Is the Scope_Identity and Identity supported in a Microsoft Access database? That is what I am using. I have tried a bunch of other articels and I can't even get it to work, because it gets an error. It's either I'm doing it wrong or it's how my current code is structured. Also, when following the examples in the articles like & _ SELECT SCOPE_IDENTITY, I have come to realize that MS ACCESS does not support batch statements. So can I really do this in MS ACCESS database? This is how my current code is. It is set up to take in info entered in a form when a submit button is clicked and it works except for the foriegn key columns which isleft empty. I am also a newbie to this so..

Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
        Dim myConn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim sqlString, MemberName, Child, Adult, PaymentMethod As String
        Dim sqlString2, MovieName, Dat, Time, ReservID As String
        Dim sqlString3, Price As String

        MemberName = TextBox2.Text
        Child = DropDownList4.SelectedValue.ToString
        Adult = DropDownList5.SelectedValue.ToString
        PaymentMethod = DropDownList1.SelectedValue.ToString
        MovieName = TextBox8.Text
        Dat = TextBox5.Text
        Time = TextBox6.Text
        Price = Label4.Text
        ReservID = TextBox7.Text

        myConn = New OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=|DataDirectory|MovieReel.accdb;")

        myConn.Open()

        sqlString = "INSERT INTO [Members] ([MemberName], [Child], [Adult], [PaymentMethod]) VALUES ('" + MemberName + "','" + Child + "','" + Adult + "','" + PaymentMethod + "')"
        cmd = New OleDbCommand(sqlString, myConn)
        cmd.ExecuteNonQuery()
        


        sqlString2 = "INSERT INTO [MovieInfo] ([MovieName], [Date], [Time], [ReservationID]) VALUES ('" + MovieName + "','" + Dat + "','" + Time + "','" + ReservID + "')"
        cmd = New OleDbCommand(sqlString2, myConn)
        cmd.ExecuteNonQuery()



        sqlString3 = "INSERT INTO [Reservation] ([Date], [Time], [Price]) VALUES ('" + Dat + "','" + Time + "','" + Price + "')"
        cmd = New OleDbCommand(sqlString3, myConn)
        cmd.ExecuteNonQuery()


        myConn.Close()
    End Sub

These represent three tables and each have an ID that is set to autofill. I hope you or someone else can help me.

Answer 5

User-1199946673 posted

Please read the article more closely, because it explains how to retrieve the last Identity of both a SQL Server and also a Access database. SCOPE_IDENTITY only works for SQL Server, @@Identity needs to be used in ACCESS. But in your example code, I don't see that you use either of them,, nor do I think you read the second article, explaining why and how to use parameterized queries....

Answer 6

User3866881 posted

either of the,,

Kindly remind you:D

either of the What？Maybe you've pressed the wrong key……