Adding to 2 different tables

Hi Everyone,

I have a page with a textbox and a dropdown list.
The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.

I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.

Here is the codebehind file:

Imports System.Data.SqlClientImports System.Web.ConfigurationPartialClass cms_Management_Company_NewCompanyInherits System.Web.UI.PageDim companyNameAs String Dim companyActiveAs Boolean Dim companyArchivedAs Boolean Dim companyDelegatesAs Integer Dim userForeNameAs String Dim userSurnameAs String Dim userEmailAs String Dim userUsernameAs String Dim userPasswordAs String Dim userActiveAs Boolean Dim userTypeIDAs Integer Dim companyIDAs Integer Dim iAs Integer Dim NoLoopsAs Integer Protected Sub btnSave_Click(ByVal senderAs Object,ByVal eAs System.Web.UI.ImageClickEventArgs)Handles btnSave.ClickDim conStringAs String = WebConfigurationManager.ConnectionStrings("General").ConnectionStringDim conAs New SqlConnection(conString)Dim cmdAs New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@.CompanyName, @.CompanyActive, @.CompanyArchived, @.CompanyDelegates)", con) cmd.Parameters.AddWithValue("@.CompanyName", companyName) cmd.Parameters.Item("@.CompanyName").Value = txtCompanyName.Text cmd.Parameters.AddWithValue("@.CompanyDelegates", companyDelegates) cmd.Parameters.Item("@.CompanyDelegates").Value = lstDel.SelectedValue cmd.Parameters.AddWithValue("@.CompanyActive", companyActive) cmd.Parameters.Item("@.CompanyActive").Value =True cmd.Parameters.AddWithValue("@.CompanyArchived", companyArchived) cmd.Parameters.Item("@.CompanyArchived").Value =False Using con con.Open() cmd.ExecuteNonQuery() con.Close()End UsingDim con2As New SqlConnection(conString)Dim cmd2As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@.UserForeName, @.UserSurname, @.UserEmail, @.UserUsername, @.UserPassword, @.UserActive, @.UserTypeID, @.CompanyID)", con2) cmd2.Parameters.AddWithValue("@.UserForeName", userForeName) cmd2.Parameters.Item("@.UserForeName").Value ="First Name - Delegate 1" cmd2.Parameters.AddWithValue("@.UserSurname", userSurname) cmd2.Parameters.Item("@.UserSurname").Value ="Surname - Delegate 1" cmd2.Parameters.AddWithValue("@.UserEmail", userEmail) cmd2.Parameters.Item("@.UserEmail").Value ="Email Address - Delegate 1" cmd2.Parameters.AddWithValue("@.UserUsername", userUsername) cmd2.Parameters.Item("@.UserUsername").Value ="Username - Delegate 1" cmd2.Parameters.AddWithValue("@.UserPassword", userPassword) cmd2.Parameters.Item("@.UserPassword").Value ="Password - Delegate 1" cmd2.Parameters.AddWithValue("@.UserActive", userActive) cmd2.Parameters.Item("@.UserActive").Value =True cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 2 cmd2.Parameters.AddWithValue("@.UserTypeID", userTypeID) cmd2.Parameters.Item("@.UserTypeID").Value = 1 Using con2 con2.Open()For i = 1To NoLoops cmd2.ExecuteNonQuery()Next i con2.Close()End Using Response.Redirect("~/cms/Management/Company/Company.aspx")End SubEnd Class

The other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)

I hope this makes sense.

Thank you very much guys.

Scott.

Hi,

To get the recently added record's ID use

SELECT SCOPE_IDENTITY()

and catch the returned value using

cmd.ExecuteScalar()

HTH
Regards

|||

Hi,

Thanks for the reply, where in the code would I put these elements? I am very new to .NET.

thanks again,

Scott.