Solved

How to return the Primary Key of the record just entered to set up a relationship.

Posted on 2007-11-28
22
209 Views
Last Modified: 2010-04-21
How can I grab the Primary Key of a record just submitted in MultiView1 and use it as part of the data submitted in MultiView2 of a Web Form?

I'm currently generating my own primary key on Page_Load then sticking it in the the next part of the for to use as a Foreign Key in a related table.  

I know this is a bad way to do it.  That's why I'm looking for a better way.

There are actually three related tables:  Employers, Worksites and JobTitles.  Each with a OTM relationship with the next down the line.  e.g. One employer will have multiple worksites and each worksite will have multiple job titles.

What happens is, an employer fills out a job order requesting one or more worksites and one or more job titles for each site, so they are filling out a three part form (I'm using a MultiView).

I'm sure there is a better way.  I can post more code if necessary.

Thanks!
David
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'If Not IsPostBack Then
        If txtPriKeyEmployerID.Text = "" Then
            'Create a new Random class in VB.NET
            Dim RandomClass As New Random()
            'Generate a random "Primary Key" for the Employers table between 1000000 and 9999999
            Dim priKeyEmployers As Integer
            priKeyEmployers = RandomClass.Next(1000000, 9999999)
            txtPriKeyEmployerID.Text = priKeyEmployers          ' Put Employers Primary Key in Employer ID# TextBox on Employer Form
            txtForeignKeyWsEmployerID.Text = priKeyEmployers    ' Put Employers Primary Key in Employer ID# TextBox on Worksite Form
            txtForeignKeyPosEmployerID.Text = priKeyEmployers   ' Put Employers Primary Key in Employer ID# TextBox on Position Form
        End If
    End Sub

Open in new window

0
Comment
Question by:megnin
  • 12
  • 6
  • 3
  • +1
22 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 20368042
In SQL Server, you can SELECT SCOPE_IDENTITY() to get the last inserted PK for the scope (i.e. stored procedure). Returning that and storing it should suffice.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20368235
Providing you are using an IDENTITY column AND it is the Primary Key than you can use the SCOPE_IDENTITY() function as Chaosian suggests.  But those are two big If's you will first have to confirm ...
0
 
LVL 22

Expert Comment

by:dportas
ID: 20368249
Obviously your code will fail as soon as you generate a key that clashes. What are you trying to achieve? Have you considered using the IDENTITY property to generate a surrogate key?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:megnin
ID: 20373866
acperkins,

Yes, I have an identity column.  keyEmployerID, keyWorksiteID and keyJobTitleID are the respective primary key fields of each of the related tables.

Table Worksites also has keyEmployerID as a foreign key and
Table JobTitles also has keyWorksitesID as a foreign key.

The primary keys fields are set as Primary Keys in SQL Server but they are not set to "auto increment", so I think they are not set as "IDENTITY" colums so that I could put my own number in the field.

I think that if when they fill out the first part of the form which is only Employer information that the information creates a new record in the Employers table then returns the primary key value so I can put it into the foreign key field of the record they are about to create for the Worksites table I'll be good.

That's the whole purpose of what I'm trying to do.  I'm trying to create the relationship between the Empoyer information, the Worksite information and the JobTitle information that they are inputting on this one form.

I'm just learning and that was the only way I could figure out how to do it.  If there's an easier or better way, please let me know.  ;-)

Anyway, what would a "SELECT SCOPE_IDENTITY()" Stored Procedure look like?  And could I call it as the last command in the Submit button that creates the record to begin with or should I call it a bit later?

Thanks
0
 
LVL 22

Expert Comment

by:dportas
ID: 20374198
Lookup the CREATE PROCEDURE statement in Books Online to see how to create a procedure. You should generally aim to put all your data access code into stored procs and then call the procs from ASP. If you do that you can retrieve the SCOPE_IDENTITY() value immediately after INSERT so that you can populate the related table with it.

That assumes your columns are in fact IDENTITY, about which you don't seem very sure. You can easily check that using the Object Browser in SQL Server Management Studio.
0
 
LVL 1

Author Comment

by:megnin
ID: 20374624
I checked.  The columns are no IDENTITY, but I know I have to make them IDENTITY in order for it to work.  I could not set them as IDENTITY and be able to insert my own generated number as I was doing.
0
 
LVL 22

Expert Comment

by:dportas
ID: 20374832
megnin: Do you have a solution now? Let us know if your question was answered.
0
 
LVL 1

Author Comment

by:megnin
ID: 20375967
I've been trying to get the SCOPE_IDENTITY to return so I can use it.  Before I attempted to create a stored procedure to do so I was going to try to get it to work in my code page.  I'm haveing trouble figuring out how to hook everything up.  If you could demonstrate how to turn the attached code snippet into a stored procedure I could call, instead of putting it in my code page I think that would get me up and running. :-}
        'Send form fields to Employer Table when "Submit Employer" button is clicked.
        Dim DBConn As New SqlConnection("UID=myUserid;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=THEDATABASE;Data Source=SQLSERVER;Packet Size=4096;")
        Dim DBCmd As New SqlCommand
        Dim DBAdap As New SqlDataAdapter
        Dim DS As New DataSet
        DBConn.Open()
        Try
            'Add Insert Statement 
            'DBCmd = New SqlCommand("INSERT INTO Employers(keyEmployerID,txtEmployerName,ddlOrganizationType,txtFedID,txtContactContract,txtContactContractTitle,txtCompanyStreet,txtCompanyCity,txtCompanyState,txtCompanyZip,txtCompanyContractContactTelephone,txtCompanyContractContactFAX,txtCompanyContractContactCell,txtCompanyContractContactEmail,txtContactProgram,txtContactProgramTitle,txtContactProgramAddress,txtContactProgramCity,txtContactProgramState,txtContactProgramZip,txtContactProgramPhone,txtContactProgramFAX,txtContactProgramCell,txtContactProgramEmail,txtWorksitesRequestedNumber,ddlOrgHasContract,ddlConfirmUnderstand,ApplicationDateEmployer) VALUES (@txtPriKeyEmployerID,@txtEmployerName,@ddlOrganizationType,@txtFedID,@txtContactContract,@txtContactContractTitle,@txtCompanyStreet,@txtCompanyCity,@txtCompanyState,@txtCompanyZip,@txtCompanyContractContactTelephone,@txtCompanyContractContactFAX,@txtCompanyContractContactCell,@txtCompanyContractContactEmail,@txtContactProgram,@txtContactProgramTitle,@txtContactProgramAddress,@txtContactProgramCity,@txtContactProgramState,@txtContactProgramZip,@txtContactProgramPhone,@txtContactProgramFAX,@txtContactProgramCell,@txtContactProgramEmail,@txtWorksitesRequestedNumber,@ddlOrgHasContract,@ddlConfirmUnderstand,getdate())", DBConn)
            DBCmd = New SqlCommand("INSERT INTO Employers(txtEmployerName,ddlOrganizationType,txtFedID,txtContactContract,txtContactContractTitle,txtCompanyStreet,txtCompanyCity,txtCompanyState,txtCompanyZip,txtCompanyContractContactTelephone,txtCompanyContractContactFAX,txtCompanyContractContactCell,txtCompanyContractContactEmail,txtContactProgram,txtContactProgramTitle,txtContactProgramAddress,txtContactProgramCity,txtContactProgramState,txtContactProgramZip,txtContactProgramPhone,txtContactProgramFAX,txtContactProgramCell,txtContactProgramEmail,txtWorksitesRequestedNumber,ddlOrgHasContract,ddlConfirmUnderstand,ApplicationDateEmployer) VALUES (@txtEmployerName,@ddlOrganizationType,@txtFedID,@txtContactContract,@txtContactContractTitle,@txtCompanyStreet,@txtCompanyCity,@txtCompanyState,@txtCompanyZip,@txtCompanyContractContactTelephone,@txtCompanyContractContactFAX,@txtCompanyContractContactCell,@txtCompanyContractContactEmail,@txtContactProgram,@txtContactProgramTitle,@txtContactProgramAddress,@txtContactProgramCity,@txtContactProgramState,@txtContactProgramZip,@txtContactProgramPhone,@txtContactProgramFAX,@txtContactProgramCell,@txtContactProgramEmail,@txtWorksitesRequestedNumber,@ddlOrgHasContract,@ddlConfirmUnderstand,getdate())", DBConn)
            'Add Database Parameters
            'DBCmd.Parameters.Add("@txtPriKeyEmployerID", SqlDbType.Int).Value = txtPriKeyEmployerID.Text  ' *** Removed to generate SCOPE_IDENTITY
            'Primary Key for Employer Table ^^^^^^^^^^
            DBCmd.Parameters.Add("@txtEmployerName", SqlDbType.VarChar).Value = txtEmployerName.Text
            DBCmd.Parameters.Add("@ddlOrganizationType", SqlDbType.VarChar).Value = ddlOrganizationType.Text
            DBCmd.Parameters.Add("@txtFedID", SqlDbType.VarChar).Value = txtFedID.Text
            DBCmd.Parameters.Add("@txtContactContract", SqlDbType.VarChar).Value = txtContactContract.Text
            DBCmd.Parameters.Add("@txtContactContractTitle", SqlDbType.VarChar).Value = txtContactContractTitle.Text
            DBCmd.Parameters.Add("@txtCompanyStreet", SqlDbType.VarChar).Value = txtCompanyStreet.Text
            DBCmd.Parameters.Add("@txtCompanyCity", SqlDbType.VarChar).Value = txtCompanyCity.Text
            DBCmd.Parameters.Add("@txtCompanyState", SqlDbType.VarChar).Value = txtCompanyState.Text
            DBCmd.Parameters.Add("@txtCompanyZip", SqlDbType.VarChar).Value = txtCompanyZip.Text
            DBCmd.Parameters.Add("@txtCompanyContractContactTelephone", SqlDbType.VarChar).Value = txtCompanyContractContactTelephone.Text
            DBCmd.Parameters.Add("@txtCompanyContractContactFAX", SqlDbType.VarChar).Value = txtCompanyContractContactFAX.Text
            DBCmd.Parameters.Add("@txtCompanyContractContactCell", SqlDbType.VarChar).Value = txtCompanyContractContactCell.Text
            DBCmd.Parameters.Add("@txtCompanyContractContactEmail", SqlDbType.VarChar).Value = txtCompanyContractContactEmail.Text
            DBCmd.Parameters.Add("@txtContactProgram", SqlDbType.VarChar).Value = txtContactProgram.Text
            DBCmd.Parameters.Add("@txtContactProgramTitle", SqlDbType.VarChar).Value = txtContactProgramTitle.Text
            DBCmd.Parameters.Add("@txtContactProgramAddress", SqlDbType.VarChar).Value = txtContactProgramAddress.Text
            DBCmd.Parameters.Add("@txtContactProgramCity", SqlDbType.VarChar).Value = txtContactProgramCity.Text
            DBCmd.Parameters.Add("@txtContactProgramState", SqlDbType.VarChar).Value = txtContactProgramState.Text
            DBCmd.Parameters.Add("@txtContactProgramZip", SqlDbType.VarChar).Value = txtContactProgramZip.Text
            DBCmd.Parameters.Add("@txtContactProgramPhone", SqlDbType.VarChar).Value = txtContactProgramPhone.Text
            DBCmd.Parameters.Add("@txtContactProgramFAX", SqlDbType.VarChar).Value = txtContactProgramFAX.Text
            DBCmd.Parameters.Add("@txtContactProgramCell", SqlDbType.VarChar).Value = txtContactProgramCell.Text
            DBCmd.Parameters.Add("@txtContactProgramEmail", SqlDbType.VarChar).Value = txtContactProgramEmail.Text
            DBCmd.Parameters.Add("@txtWorksitesRequestedNumber", SqlDbType.VarChar).Value = txtWorksitesRequestedNumber.Text
            DBCmd.Parameters.Add("@ddlOrgHasContract", SqlDbType.VarChar).Value = ddlOrgHasContract.Text
            DBCmd.Parameters.Add("@ddlConfirmUnderstand", SqlDbType.VarChar).Value = ddlConfirmUnderstand.Text
            'DBCmd.Parameters.Add("@ApplicationDateEmployer", SqlDbType.SmallDateTime).Value = GetDate() 'A Parameter is no used here. The GetDate() function is in the INSERT "VALUE" statement
 
            DBCmd.ExecuteNonQuery()
 
'This is where I started experimenting....
' It just returned a "0" even though the primary key field was what it was suppose to be.
            Dim DBCmd3 As New SqlCommand
            Dim priKeyEmployers As Integer  ' I moved this from the Page_Load to grab the SCOPE_IDENTITY() for this new record.  Copy it to the other Form keys.
            DBCmd3 = New SqlCommand("Select priKeyEmployers = SCOPE_IDENTITY()")
            txtPriKeyEmployerID.Text = priKeyEmployers
            txtForeignKeyWsEmployerID.Text = priKeyEmployers
            txtForeignKeyPosEmployerID.Text = priKeyEmployers
 
' The rest below is original code.
        Catch exp As Exception
            Response.Write(exp)
        End Try
        'Close Database connection 
        'and Dispose Database objects 
        DBCmd.Dispose()
        DBAdap.Dispose()
        DBConn.Close()
        DBConn = Nothing

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 20376126
To the above code (still in my code page) I added:
DBCmd3.ExecuteScalar()
after the Select priKeyEmployers = SCOPE_IDENTITY()

Will that work or should the SELECT statement be:
"Select SCOPE_IDENTITY() as priKeyEmployers"  ?

Thanks.
0
 
LVL 1

Author Comment

by:megnin
ID: 20376184
Well, that got me this:
ExecuteScalar: Connection property has not been initialized.

Do I have to open another connection?  I thought if I executed this SELECT before I closed the first conneciton that it should work (but, I'm new to this).
0
 
LVL 1

Author Comment

by:megnin
ID: 20377309
I tried both:

DBCmd = New SqlCommand("INSERT INTO Employers(txtEmployerName, ApplicationDateEmployer) VALUES (@txtEmployerName, getdate()); SELECT priKeyEmployers = SCOPE_IDENTITY()", DBConn)
and
DBCmd = New SqlCommand("INSERT INTO Employers(txtEmployerName, ApplicationDateEmployer) VALUES (@txtEmployerName, getdate()); SELECT  SCOPE_IDENTITY() as priKeyEmployers", DBConn)

and I did not get an error.  The record was created okay, but it did not set the value of SCOPE_IDENTITY() to the variable, priKeyEmployers.

could someone point me in the right directions?
0
 
LVL 1

Author Comment

by:megnin
ID: 20377574
This:

DBCmd = New SqlCommand("INSERT INTO Employers(txtEmployerName) VALUES (@txtEmployerName); SELECT @EmployerID = SCOPE_IDENTITY()", DBConn)

gets me:

System.Data.SqlClient.SqlException: Must declare the variable '@EmployerID'.

Is anyone still out there?  

I'm just trying things at random at this point.  I've been "Googling" for this all day and finding nothing that shows me exactly how to do this.
0
 
LVL 1

Author Comment

by:megnin
ID: 20377633
>>megnin: Do you have a solution now?

no
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20378269
Try it this way:
'Send form fields to Employer Table when "Submit Employer" button is clicked.
Dim DBConn As New SqlConnection("UID=myUserid;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=THEDATABASE;Data Source=SQLSERVER;Packet Size=4096;")
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
Dim DS As New DataSet
DBConn.Open()
Try
      'Add Insert Statement
      'DBCmd = New SqlCommand("INSERT INTO Employers(keyEmployerID,txtEmployerName,ddlOrganizationType,txtFedID,txtContactContract,txtContactContractTitle,txtCompanyStreet,txtCompanyCity,txtCompanyState,txtCompanyZip,txtCompanyContractContactTelephone,txtCompanyContractContactFAX,txtCompanyContractContactCell,txtCompanyContractContactEmail,txtContactProgram,txtContactProgramTitle,txtContactProgramAddress,txtContactProgramCity,txtContactProgramState,txtContactProgramZip,txtContactProgramPhone,txtContactProgramFAX,txtContactProgramCell,txtContactProgramEmail,txtWorksitesRequestedNumber,ddlOrgHasContract,ddlConfirmUnderstand,ApplicationDateEmployer) VALUES (@txtPriKeyEmployerID,@txtEmployerName,@ddlOrganizationType,@txtFedID,@txtContactContract,@txtContactContractTitle,@txtCompanyStreet,@txtCompanyCity,@txtCompanyState,@txtCompanyZip,@txtCompanyContractContactTelephone,@txtCompanyContractContactFAX,@txtCompanyContractContactCell,@txtCompanyContractContactEmail,@txtContactProgram,@txtContactProgramTitle,@txtContactProgramAddress,@txtContactProgramCity,@txtContactProgramState,@txtContactProgramZip,@txtContactProgramPhone,@txtContactProgramFAX,@txtContactProgramCell,@txtContactProgramEmail,@txtWorksitesRequestedNumber,@ddlOrgHasContract,@ddlConfirmUnderstand,getdate())", DBConn)
      DBCmd = New SqlCommand("SET NOCOUNT ON; INSERT INTO Employers(txtEmployerName,ddlOrganizationType,txtFedID,txtContactContract,txtContactContractTitle,txtCompanyStreet,txtCompanyCity,txtCompanyState,txtCompanyZip,txtCompanyContractContactTelephone,txtCompanyContractContactFAX,txtCompanyContractContactCell,txtCompanyContractContactEmail,txtContactProgram,txtContactProgramTitle,txtContactProgramAddress,txtContactProgramCity,txtContactProgramState,txtContactProgramZip,txtContactProgramPhone,txtContactProgramFAX,txtContactProgramCell,txtContactProgramEmail,txtWorksitesRequestedNumber,ddlOrgHasContract,ddlConfirmUnderstand,ApplicationDateEmployer) VALUES (@txtEmployerName,@ddlOrganizationType,@txtFedID,@txtContactContract,@txtContactContractTitle,@txtCompanyStreet,@txtCompanyCity,@txtCompanyState,@txtCompanyZip,@txtCompanyContractContactTelephone,@txtCompanyContractContactFAX,@txtCompanyContractContactCell,@txtCompanyContractContactEmail,@txtContactProgram,@txtContactProgramTitle,@txtContactProgramAddress,@txtContactProgramCity,@txtContactProgramState,@txtContactProgramZip,@txtContactProgramPhone,@txtContactProgramFAX,@txtContactProgramCell,@txtContactProgramEmail,@txtWorksitesRequestedNumber,@ddlOrgHasContract,@ddlConfirmUnderstand,getdate()); Select SCOPE_IDENTITY()", DBConn)
      'Add Database Parameters
      'DBCmd.Parameters.Add("@txtPriKeyEmployerID", SqlDbType.Int).Value = txtPriKeyEmployerID.Text  ' *** Removed to generate SCOPE_IDENTITY
      'Primary Key for Employer Table ^^^^^^^^^^
      DBCmd.Parameters.Add("@txtEmployerName", SqlDbType.VarChar).Value = txtEmployerName.Text
      DBCmd.Parameters.Add("@ddlOrganizationType", SqlDbType.VarChar).Value = ddlOrganizationType.Text
      DBCmd.Parameters.Add("@txtFedID", SqlDbType.VarChar).Value = txtFedID.Text
      DBCmd.Parameters.Add("@txtContactContract", SqlDbType.VarChar).Value = txtContactContract.Text
      DBCmd.Parameters.Add("@txtContactContractTitle", SqlDbType.VarChar).Value = txtContactContractTitle.Text
      DBCmd.Parameters.Add("@txtCompanyStreet", SqlDbType.VarChar).Value = txtCompanyStreet.Text
      DBCmd.Parameters.Add("@txtCompanyCity", SqlDbType.VarChar).Value = txtCompanyCity.Text
      DBCmd.Parameters.Add("@txtCompanyState", SqlDbType.VarChar).Value = txtCompanyState.Text
      DBCmd.Parameters.Add("@txtCompanyZip", SqlDbType.VarChar).Value = txtCompanyZip.Text
      DBCmd.Parameters.Add("@txtCompanyContractContactTelephone", SqlDbType.VarChar).Value = txtCompanyContractContactTelephone.Text
      DBCmd.Parameters.Add("@txtCompanyContractContactFAX", SqlDbType.VarChar).Value = txtCompanyContractContactFAX.Text
      DBCmd.Parameters.Add("@txtCompanyContractContactCell", SqlDbType.VarChar).Value = txtCompanyContractContactCell.Text
      DBCmd.Parameters.Add("@txtCompanyContractContactEmail", SqlDbType.VarChar).Value = txtCompanyContractContactEmail.Text
      DBCmd.Parameters.Add("@txtContactProgram", SqlDbType.VarChar).Value = txtContactProgram.Text
      DBCmd.Parameters.Add("@txtContactProgramTitle", SqlDbType.VarChar).Value = txtContactProgramTitle.Text
      DBCmd.Parameters.Add("@txtContactProgramAddress", SqlDbType.VarChar).Value = txtContactProgramAddress.Text
      DBCmd.Parameters.Add("@txtContactProgramCity", SqlDbType.VarChar).Value = txtContactProgramCity.Text
      DBCmd.Parameters.Add("@txtContactProgramState", SqlDbType.VarChar).Value = txtContactProgramState.Text
      DBCmd.Parameters.Add("@txtContactProgramZip", SqlDbType.VarChar).Value = txtContactProgramZip.Text
      DBCmd.Parameters.Add("@txtContactProgramPhone", SqlDbType.VarChar).Value = txtContactProgramPhone.Text
      DBCmd.Parameters.Add("@txtContactProgramFAX", SqlDbType.VarChar).Value = txtContactProgramFAX.Text
      DBCmd.Parameters.Add("@txtContactProgramCell", SqlDbType.VarChar).Value = txtContactProgramCell.Text
      DBCmd.Parameters.Add("@txtContactProgramEmail", SqlDbType.VarChar).Value = txtContactProgramEmail.Text
      DBCmd.Parameters.Add("@txtWorksitesRequestedNumber", SqlDbType.VarChar).Value = txtWorksitesRequestedNumber.Text
      DBCmd.Parameters.Add("@ddlOrgHasContract", SqlDbType.VarChar).Value = ddlOrgHasContract.Text
      DBCmd.Parameters.Add("@ddlConfirmUnderstand", SqlDbType.VarChar).Value = ddlConfirmUnderstand.Text
      'DBCmd.Parameters.Add("@ApplicationDateEmployer", SqlDbType.SmallDateTime).Value = GetDate() 'A Parameter is no used here. The GetDate() function is in the INSERT "VALUE" statement
      Dim priKeyEmployers As Integer = DbCmd.ExecuteScalar()
      
      txtPriKeyEmployerID.Text = priKeyEmployers
      txtForeignKeyWsEmployerID.Text = priKeyEmployers
      txtForeignKeyPosEmployerID.Text = priKeyEmployers
      
      ' The rest below is original code.
Catch exp As Exception
      Response.Write(exp)
End Try
'Close Database connection
'and Dispose Database objects
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing
Open in New Window
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 20378280
Let's try that again:

'Send form fields to Employer Table when "Submit Employer" button is clicked.
Dim DBConn As New SqlConnection("UID=myUserid;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=THEDATABASE;Data Source=SQLSERVER;Packet Size=4096;")
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
Dim DS As New DataSet
DBConn.Open()
Try
	'Add Insert Statement 
	'DBCmd = New SqlCommand("INSERT INTO Employers(keyEmployerID,txtEmployerName,ddlOrganizationType,txtFedID,txtContactContract,txtContactContractTitle,txtCompanyStreet,txtCompanyCity,txtCompanyState,txtCompanyZip,txtCompanyContractContactTelephone,txtCompanyContractContactFAX,txtCompanyContractContactCell,txtCompanyContractContactEmail,txtContactProgram,txtContactProgramTitle,txtContactProgramAddress,txtContactProgramCity,txtContactProgramState,txtContactProgramZip,txtContactProgramPhone,txtContactProgramFAX,txtContactProgramCell,txtContactProgramEmail,txtWorksitesRequestedNumber,ddlOrgHasContract,ddlConfirmUnderstand,ApplicationDateEmployer) VALUES (@txtPriKeyEmployerID,@txtEmployerName,@ddlOrganizationType,@txtFedID,@txtContactContract,@txtContactContractTitle,@txtCompanyStreet,@txtCompanyCity,@txtCompanyState,@txtCompanyZip,@txtCompanyContractContactTelephone,@txtCompanyContractContactFAX,@txtCompanyContractContactCell,@txtCompanyContractContactEmail,@txtContactProgram,@txtContactProgramTitle,@txtContactProgramAddress,@txtContactProgramCity,@txtContactProgramState,@txtContactProgramZip,@txtContactProgramPhone,@txtContactProgramFAX,@txtContactProgramCell,@txtContactProgramEmail,@txtWorksitesRequestedNumber,@ddlOrgHasContract,@ddlConfirmUnderstand,getdate())", DBConn)
	DBCmd = New SqlCommand("SET NOCOUNT ON; INSERT INTO Employers(txtEmployerName,ddlOrganizationType,txtFedID,txtContactContract,txtContactContractTitle,txtCompanyStreet,txtCompanyCity,txtCompanyState,txtCompanyZip,txtCompanyContractContactTelephone,txtCompanyContractContactFAX,txtCompanyContractContactCell,txtCompanyContractContactEmail,txtContactProgram,txtContactProgramTitle,txtContactProgramAddress,txtContactProgramCity,txtContactProgramState,txtContactProgramZip,txtContactProgramPhone,txtContactProgramFAX,txtContactProgramCell,txtContactProgramEmail,txtWorksitesRequestedNumber,ddlOrgHasContract,ddlConfirmUnderstand,ApplicationDateEmployer) VALUES (@txtEmployerName,@ddlOrganizationType,@txtFedID,@txtContactContract,@txtContactContractTitle,@txtCompanyStreet,@txtCompanyCity,@txtCompanyState,@txtCompanyZip,@txtCompanyContractContactTelephone,@txtCompanyContractContactFAX,@txtCompanyContractContactCell,@txtCompanyContractContactEmail,@txtContactProgram,@txtContactProgramTitle,@txtContactProgramAddress,@txtContactProgramCity,@txtContactProgramState,@txtContactProgramZip,@txtContactProgramPhone,@txtContactProgramFAX,@txtContactProgramCell,@txtContactProgramEmail,@txtWorksitesRequestedNumber,@ddlOrgHasContract,@ddlConfirmUnderstand,getdate()); Select SCOPE_IDENTITY()", DBConn)
	'Add Database Parameters
	'DBCmd.Parameters.Add("@txtPriKeyEmployerID", SqlDbType.Int).Value = txtPriKeyEmployerID.Text  ' *** Removed to generate SCOPE_IDENTITY
	'Primary Key for Employer Table ^^^^^^^^^^
	DBCmd.Parameters.Add("@txtEmployerName", SqlDbType.VarChar).Value = txtEmployerName.Text
	DBCmd.Parameters.Add("@ddlOrganizationType", SqlDbType.VarChar).Value = ddlOrganizationType.Text
	DBCmd.Parameters.Add("@txtFedID", SqlDbType.VarChar).Value = txtFedID.Text
	DBCmd.Parameters.Add("@txtContactContract", SqlDbType.VarChar).Value = txtContactContract.Text
	DBCmd.Parameters.Add("@txtContactContractTitle", SqlDbType.VarChar).Value = txtContactContractTitle.Text
	DBCmd.Parameters.Add("@txtCompanyStreet", SqlDbType.VarChar).Value = txtCompanyStreet.Text
	DBCmd.Parameters.Add("@txtCompanyCity", SqlDbType.VarChar).Value = txtCompanyCity.Text
	DBCmd.Parameters.Add("@txtCompanyState", SqlDbType.VarChar).Value = txtCompanyState.Text
	DBCmd.Parameters.Add("@txtCompanyZip", SqlDbType.VarChar).Value = txtCompanyZip.Text
	DBCmd.Parameters.Add("@txtCompanyContractContactTelephone", SqlDbType.VarChar).Value = txtCompanyContractContactTelephone.Text
	DBCmd.Parameters.Add("@txtCompanyContractContactFAX", SqlDbType.VarChar).Value = txtCompanyContractContactFAX.Text
	DBCmd.Parameters.Add("@txtCompanyContractContactCell", SqlDbType.VarChar).Value = txtCompanyContractContactCell.Text
	DBCmd.Parameters.Add("@txtCompanyContractContactEmail", SqlDbType.VarChar).Value = txtCompanyContractContactEmail.Text
	DBCmd.Parameters.Add("@txtContactProgram", SqlDbType.VarChar).Value = txtContactProgram.Text
	DBCmd.Parameters.Add("@txtContactProgramTitle", SqlDbType.VarChar).Value = txtContactProgramTitle.Text
	DBCmd.Parameters.Add("@txtContactProgramAddress", SqlDbType.VarChar).Value = txtContactProgramAddress.Text
	DBCmd.Parameters.Add("@txtContactProgramCity", SqlDbType.VarChar).Value = txtContactProgramCity.Text
	DBCmd.Parameters.Add("@txtContactProgramState", SqlDbType.VarChar).Value = txtContactProgramState.Text
	DBCmd.Parameters.Add("@txtContactProgramZip", SqlDbType.VarChar).Value = txtContactProgramZip.Text
	DBCmd.Parameters.Add("@txtContactProgramPhone", SqlDbType.VarChar).Value = txtContactProgramPhone.Text
	DBCmd.Parameters.Add("@txtContactProgramFAX", SqlDbType.VarChar).Value = txtContactProgramFAX.Text
	DBCmd.Parameters.Add("@txtContactProgramCell", SqlDbType.VarChar).Value = txtContactProgramCell.Text
	DBCmd.Parameters.Add("@txtContactProgramEmail", SqlDbType.VarChar).Value = txtContactProgramEmail.Text
	DBCmd.Parameters.Add("@txtWorksitesRequestedNumber", SqlDbType.VarChar).Value = txtWorksitesRequestedNumber.Text
	DBCmd.Parameters.Add("@ddlOrgHasContract", SqlDbType.VarChar).Value = ddlOrgHasContract.Text
	DBCmd.Parameters.Add("@ddlConfirmUnderstand", SqlDbType.VarChar).Value = ddlConfirmUnderstand.Text
	'DBCmd.Parameters.Add("@ApplicationDateEmployer", SqlDbType.SmallDateTime).Value = GetDate() 'A Parameter is no used here. The GetDate() function is in the INSERT "VALUE" statement
	Dim priKeyEmployers As Integer = DbCmd.ExecuteScalar()
	
	txtPriKeyEmployerID.Text = priKeyEmployers
	txtForeignKeyWsEmployerID.Text = priKeyEmployers
	txtForeignKeyPosEmployerID.Text = priKeyEmployers
	
	' The rest below is original code.
Catch exp As Exception
	Response.Write(exp)
End Try
'Close Database connection 
'and Dispose Database objects 
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing 
Open in New Window 

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20378461
This is what the stored procedure would look like:
Create Procedure usp_YourProcedureNameGoesHere
		@txtPriKeyEmployerID varchar(50), 
		@txtEmployerName varchar(50), 
		@ddlOrganizationType varchar(50), 
		@txtFedID varchar(50), 
		@txtContactContract varchar(50), 
		@txtContactContractTitle varchar(50), 
		@txtCompanyStreet varchar(50), 
		@txtCompanyCity varchar(50), 
		@txtCompanyState varchar(50), 
		@txtCompanyZip varchar(50), 
		@txtCompanyContractContactTelephone varchar(50), 
		@txtCompanyContractContactFAX varchar(50), 
		@txtCompanyContractContactCell varchar(50), 
		@txtCompanyContractContactEmail varchar(50), 
		@txtContactProgram varchar(50), 
		@txtContactProgramTitle varchar(50), 
		@txtContactProgramAddress varchar(50), 
		@txtContactProgramCity varchar(50),	
		@txtContactProgramState varchar(50), 
		@txtContactProgramZip varchar(50), 
		@txtContactProgramPhone varchar(50), 
		@txtContactProgramFAX varchar(50),
		@txtContactProgramCell varchar(50), 
		@txtContactProgramEmail varchar(50), 
		@txtWorksitesRequestedNumber varchar(50), 
		@ddlOrgHasContract varchar(50), 
		@ddlConfirmUnderstand varchar(50)
 
AS
 
SET NOCOUNT ON
 
INSERT Employers(
	keyEmployerID, txtEmployerName, ddlOrganizationType, txtFedID, txtContactContract, txtContactContractTitle, 
	txtCompanyStreet, txtCompanyCity, txtCompanyState, txtCompanyZip, txtCompanyContractContactTelephone, 
	txtCompanyContractContactFAX, txtCompanyContractContactCell, txtCompanyContractContactEmail, 
	txtContactProgram, txtContactProgramTitle, txtContactProgramAddress, txtContactProgramCity, 
	txtContactProgramState, txtContactProgramZip, txtContactProgramPhone, txtContactProgramFAX, 
	txtContactProgramCell, txtContactProgramEmail, txtWorksitesRequestedNumber, ddlOrgHasContract, 
	ddlConfirmUnderstand, ApplicationDateEmployer
	) 
VALUES (@txtPriKeyEmployerID, @txtEmployerName, @ddlOrganizationType, @txtFedID, @txtContactContract, @txtContactContractTitle, 
	@txtCompanyStreet, @txtCompanyCity, @txtCompanyState, @txtCompanyZip, @txtCompanyContractContactTelephone, 
	@txtCompanyContractContactFAX, @txtCompanyContractContactCell, @txtCompanyContractContactEmail, 
	@txtContactProgram, @txtContactProgramTitle, @txtContactProgramAddress, @txtContactProgramCity,	
	@txtContactProgramState, @txtContactProgramZip, @txtContactProgramPhone, @txtContactProgramFAX,
	@txtContactProgramCell, @txtContactProgramEmail, @txtWorksitesRequestedNumber, @ddlOrgHasContract, @ddlConfirmUnderstand,getdate()
	)
 
Select	SCOPE_IDENTITY() priKeyEmployers

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20378470
Just change the lengths of the stord procedure parameters to match your table.
0
 
LVL 1

Author Comment

by:megnin
ID: 20379444
Thank you acperkins!

I'll give that a try at work tomorrow.  I think putting the main SQL Insert commands into a stored procedure will help a lot.

Just so I'm clear on where what goes...

The "Let's try that again:" code stays in the .asp.vb code file and then where would I call or execute the stored procedure from?

Hmmm, it looks like I may should call the stored procedure *instead* of the "Let's try that again:" code.  Correct?  Forgive me, I am just starting to learn this stuff.
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 31411506
Awesome!  Thanks.
0
 
LVL 1

Author Comment

by:megnin
ID: 20385143
acperking, I've been struggling with that for quite a while.  Thank you very much for the complete and working solution.

It works perfectly.
0
 
LVL 1

Author Comment

by:megnin
ID: 20385562
There appears to be "duplication of tasks" between the vb code and the stored procedure, like:

DBCmd = New SqlCommand("SET NOCOUNT ON; INSERT INTO Employers(txtEmployerName
in the vb.
and:

AS
 SET NOCOUNT ON
 INSERT Employers(
      keyEmployerID,

in the SP.

Are both complete sets of commands necessary?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20386587
It is either one or the other not both.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question