how to insert data into multiple tables?

[nebrom]

I am using asp.net,c# and ms sql for my web application.
i have two tables
tableone
idno int identity(1,1),fullname varchar(200),mobileno varchar(200)

tabletwo code int idenity(1,1),totalpayment float, paidamount float,unpaidamount float, idno foreign key
the column idno in tableone is auto increment by one, but the idno in tabletwo should be added from tableone.
now i want to insert data at the same time to both of the tables.
but i get it hard how to insert the foreign key values
please help?

[jmcilhinney]

Use a DataSet with a DataRelation between the two DataTables. You can configure the DataRelation to cascade on updates and that will allow you to propagate the generated IDs from the parent table to the child table.

[jmcilhinney]

E.g.

Dim data As New DataSet
Dim parentTable = data.Tables.Add()
Dim childTable = data.Tables.Add()
Dim parentKey As DataColumn
Dim childKey As DataColumn
Dim foreignKey As DataColumn
 
With parentTable.Columns
    parentKey = .Add("ParentId", GetType(Integer))
    .Add("ParentName", GetType(String))
End With
 
With childTable.Columns
    childKey = .Add("ChildId", GetType(Integer))
    foreignKey = .Add("ParentId", GetType(Integer))
    .Add("ChildName", GetType(String))
End With
 
parentKey.AutoIncrement = True
childKey.AutoIncrement = True
 
parentTable.PrimaryKey = {parentKey}
childTable.PrimaryKey = {childKey}
 
Dim relation As New DataRelation("ParentChild", parentKey, foreignKey, True)
 
relation.ChildKeyConstraint.UpdateRule = Rule.Cascade
 
'Add a parent row and let a temporary parent ID be generated.
Dim parentRow = parentTable.Rows.Add(DBNull.Value, "First Parent")
 
'Add a child row and use the temporary parent ID as the foreign key value.
Dim childRow = childTable.Rows.Add(DBNull.Value, parentRow("ParentId"), "FirstChild")
 
'After insert a parent record, select the last generated ID back into the ParentId column of parentTable.
Dim parentSql = "INSERT INTO Parent (ParentName) VALUES (@ParentName); SELECT ParentId = SCOPE_IDENTITY()"
 
Dim childSql = "INSERT INTO Child (ParentId, ParentName) VALUES (@ParentId, ChildName)"
 
Using connection As New SqlConnection("connection string here"),
      parentCommand As New SqlCommand(parentSql, connection),
      childCommand As New SqlCommand(childSql, connection),
      parentAdapter As New SqlDataAdapter With {.InsertCommand = parentCommand},
      childAdapter As New SqlDataAdapter With {.InsertCommand = childCommand}
    parentCommand.Parameters.Add("@ParentName", SqlDbType.VarChar, 50, "ParentName")
 
    With childCommand.Parameters
        .Add("@ParentId", SqlDbType.Int, 0, "ParentId")
        .Add("@ChildName", SqlDbType.VarChar, 50, "ChildName")
    End With
 
    connection.Open()
 
    'Save the new parent record(s), which will also retrieve the generated ID(s),
    'which will be propagated to the child table by the DataRelation.
    parentAdapter.Update(parentTable)
 
    'Save the new child record(s), which now contain the correct foreign key value(s).
    childAdapter.Update(childTable)
End Using