combine PK with Table Records and Insert into other table

[ammodog6942]

I have it where a user inserts a record. Then the page gets redirected and passes the newly created PK to the new page as a query string. What I want to do is take that newly created PK and combine it with a list of default records from a table and insert that string into a different table.

Specifically... it is about permits.. the user fills out a permit application and hits insert , it then goes to another page where I am hoping to take the 10+ default inspections for the permit and insert that into an inspections table with the newly created permit id.

Can anyone provide me with some guidance on where to look for a solution. Thank you


I am using VB

 

[jbenson001]

That soulution should be done in the database. You can use a stored procedure with simple joins to get it done. What database are you using?

 

[ammodog6942]

I am using SQL2005.. I am still learning it as well as asp.net. I have seen people talk about using stored procedures but still haven't grasp the concept of setting one up and calling it from the aspx page.

 

[ammodog6942]

Ok I have created my stored procedure and tested it. It does exactly what I need it to do. So I guess all I need to know now is how on my page where I have the query string value (permitid) pass that value as the parameter and run the stored procedure. Thanks Again.

 

[jmeckley]

a stored procedure (sp, stored proc) is just sql that is defined in the database rather that in you're code. some will argue security or performance reasons to use procs, but they are negligible for a majority of end user interactions. bulk data transformation is another matter and procs can provide value in this area.

my 1st question. how are you saving the orginal record and getting the Id back? please don't say sqldatasource control. if you are then scrap it and learn how to code. for your application to communicate with a database you will use the ado.net framework which is part of the core .net framework.

basically you will need a connection and command(s).
open a connection
define the command(s)
set parameters on the command(s)
execute commands
close/dispose of command(s) and connection

ultimately you are doing this:
1. define a new contract
2. save the contract and get the id (assuming you are using a database id generator like identity)
3. save ~10 other contracts with an aggregate id.

i don't see why you would need to navigate to another page to do this. it can all be done within a single transaction/connection with a series of commands.

whether you go with procs or dynamic sql you want a solution that is easy to maintain and adaptable to change.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

Hope this gives you an idea how to work with SP. MY Database is oracle.I am sure there is not a lot of difference between oracle and sql2005.

Sub BindLetData()
        Dim connectionString As String = ConnectionStrings("costEstimating").ConnectionString
        Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
        oOracleConn.Open()

        Dim drLettingData As OracleDataReader
        Dim cmdLetting As OracleCommand = New OracleCommand()
        With cmdLetting
            .Connection = oOracleConn
            .CommandText = "LettingInformation.LettingDetails"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add(New OracleParameter("p_letting", OracleType.VarChar)).Value = lettId
            .Parameters.Add(New OracleParameter("p_contid", OracleType.VarChar)).Value = contId
            .Parameters.Add(New OracleParameter("p_call", OracleType.VarChar)).Value = calId
            .Parameters.Add(New OracleParameter("b_results", OracleType.Cursor)).Direction = ParameterDirection.Output
        End With
        drLettingData = cmdLetting.ExecuteReader()
        gvlet.DataSource = drLettingData
        gvlet.DataBind()
        drLettingData.Close()
    End Sub

 

[taree]

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'These variables are being used in the main application with stored procedure
        lettId = Request.QueryString("lettingId").ToString
        contId = Request.QueryString("contractId").ToString
        calId = Request.QueryString("callId").ToString
end sub