Solved

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

Posted on 2007-11-28
22
207 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now