Hi, I am very new to c# and I toying with the idea of using stored procedures to insert values from two textboxes in to two related tables. Table 1 Model (ModelID PK, ModelName, ManufacturerID FK) - Table 2 Manufacturer (ManufacturerID PK, ManufacturerName) both tables are related by the ManufacturerID.

The idea is to insert the @ManufacturerName in to the Manufacturer table and then Insert @ModelName and related ManufacturerID in to the Model Table. I have created a stored procedure as follows

ALTER PROCEDURE dbo.InsertModelManufacturer
	
	(
	@ModelName varchar(50),
	@ModelID int,
	@ManufacturerName varchar(50)
	)
	AS

BEGIN TRANSACTION

DECLARE @ManufacturerID int

		INSERT INTO Manufacturer(ManufacturerName)
	VALUES (@ManufacturerName)
		
	IF @@ERROR <> 0
	BEGIN 
	ROLLBACK
	RETURN
	END

	SELECT @ManufacturerID=SCOPE_IDENTITY()

	INSERT INTO Model(ModelName, ManufacturerID)
	VALUES (@ModelName, @ManufacturerID)

	
	IF @@ERROR <> 0
	BEGIN
	ROLLBACK
	RETURN
	END

	COMMIT

Here is my c# code

SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();

private void btnSave_Click(object sender, EventArgs e)
        {
            command.Parameters.Add("@ModelName", SqlDbType.VarChar, 50).Value = txtModel.Text;
            command.Parameters.Add("@ManufacturerName", SqlDbType.VarChar, 50).Value = txtManufacturer.Text;
            
            try
            {
                connection.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=etc, etc;
                connection.Open();

                if (connection.State == ConnectionState.Open)
                {
                    command = new SqlCommand("dbo.InsertModelManufacturer", connection);

                    command.CommandType = CommandType.StoredProcedure;

                    command.Connection = connection;

                    int rows = command.ExecuteNonQuery();

                    if (rows > 0)

                        MessageBox.Show("Record Saved");
                    else
                        MessageBox.Show("Failed to save record");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("An error has occured!" + ex);
            }
            finally
            {
                connection.Close();
            }

          }

I have tried adding = NULL in the stored procedures which doesn't seem to work and not only that, I would like to maintain integrity so the user must add values to the textbox.

HELP, I have searched but I cannot come up with a definitive answer - am I missing something or have something coded incorrectly?

I would suggest to move the lines

command.Parameters.Add("@ModelName", SqlDbType.VarChar, 50).Value = txtModel.Text;
            command.Parameters.Add("@ManufacturerName", SqlDbType.VarChar, 50).Value = txtManufacturer.Text;

just before

int rows = command.ExecuteNonQuery();

Hope this helps

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.