Database

ALTER PROCEDURE dbo.NewInsertCommand
(
	@City nvarchar(50),
	@State nvarchar(50)
)
AS
	SET NOCOUNT ON;
	
	DECLARE @LocationID int;
INSERT INTO [Location] (City,State)
		VALUES (@City, @State)

SELECT @LocationID = SCOPE_IDENTITY()

ALTER PROCEDURE dbo.InsertUser 

	@FirstName varchar(25),
	@LastName varchar(25),
	@LocationID int
	
AS
	
	INSERT INTO [UserData] (LocationID, FirstName, LastName)
(SELECT @LocationID, @FirstName, @LastName)
	
	
	RETURN

Private Sub AddNewNames()
        FirstNameArray.Add(FirstName.Text)
        FirstNameArray.ToArray()
        LastNameArray.Add(LastName.Text)
        LastNameArray.ToArray()
    End Sub

 Private Sub InsertData()

        Try
            Dim Flag As Boolean = False
            'GetDBConnection()
            Dim cmd As New SqlCommand("NewInsertCommand", LetsGo.GetDBConnection)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem
            cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem
            cmd.ExecuteNonQuery()

            cmd = New SqlCommand("InsertUser", LetsGo.GetDBConnection)
            cmd.CommandType = CommandType.StoredProcedure
            For I As Integer = 0 To FirstNameArray.Count - 1
                cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstNameArray(I).ToString
                cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastNameArray(I).ToString
                cmd.ExecuteNonQuery()
            Next

            MsgBox("Data Saved Successfully !", MsgBoxStyle.Information)

CREATE PROCEDURE [dbo].[InsertLocation]
(
	@City nvarchar(50),
	@State nvarchar(50),
	@LocationID int out
)
AS
	INSERT INTO [Location] (City,State)
		VALUES (@City, @State)

	SELECT @LocationID = SCOPE_IDENTITY()
GO

CREATE PROCEDURE [dbo].[InsertUserData]
(
	@LocationID int,
	@FirstName nvarchar(25),
	@LastName nvarchar(25)
)
AS
	SET NOCOUNT ON;

	INSERT INTO [UserData] (LocationID, FirstName, LastName)
		(SELECT @LocationID, @FirstName, @LastName)
GO

private static void insertData()
{
    try
    {
        //Create connection string using SqlConnectionStringBuilder
        SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
        sb.DataSource = ".\\SQLEXPRESS";
        sb.InitialCatalog = "Database";
        sb.IntegratedSecurity = true;

        // Create SqlConnection
        using (SqlConnection cn = new SqlConnection(sb.ToString()))
        {
            // Open the connection
            cn.Open();

            // Create command to insert location
            SqlCommand insertLocationCommand = new SqlCommand("InsertLocation", cn);
            insertLocationCommand.CommandType = CommandType.StoredProcedure;
            // Add first parameter for City (nvarchar)
            insertLocationCommand.Parameters.Add("@City", SqlDbType.NVarChar).Value = "City1";
            // Add second parameter for State (nvarchar)
            insertLocationCommand.Parameters.Add("@State", SqlDbType.NVarChar).Value = "State1";
            // Add third parameter for LocationID (int)...
            insertLocationCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = 0;
            // ... and mark it as OUTPUT-Parameter
            insertLocationCommand.Parameters["@LocationID"].Direction = ParameterDirection.Output;
            // Execute command...
            insertLocationCommand.ExecuteNonQuery();
            // ... and get value of out parameter
            int locationID = (int)insertLocationCommand.Parameters["@LocationID"].Value;

            // Create command to insert user data
            SqlCommand insertUserDataCommand = new SqlCommand("InsertUserData", cn);
            insertUserDataCommand.CommandType = CommandType.StoredProcedure;
            // Add first parameter with new location id, add value (locationID)
            insertUserDataCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = locationID;
            // Add second parameter for FirstName (nvarchar), add value later in loop!
            insertUserDataCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar);
            // Add third parameter for LastName (nvarchar), add value later in loop!
            insertUserDataCommand.Parameters.Add("@LastName", SqlDbType.NVarChar);
            for (int i = 0; i < FirstNameArray.Count; i++)
            {
                // set new values
                insertUserDataCommand.Parameters["@FirstName"].Value = FirstNameArray[i].ToString();
                insertUserDataCommand.Parameters["@LastName"].Value = LastNameArray[i].ToString();

                // Execute command...
                insertUserDataCommand.ExecuteNonQuery();
            }

            Console.WriteLine("Data Saved Successfully !");
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        FirstNameArray.Clear();
        LastNameArray.Clear();
    }
}

(Status=true AND Response=false and @State=1) or (Status=false AND UserId>@Number and @State=2)