Asp.net webform in C# insert SQL table identity as fk

See more:

C#

ASP.NET

SQL-Server

WebForms

I'm working on an ASP.NET website in C#. The idea is to insert data into a SQL Server database through the website.

I have a stored procedure for retrieval of identity and writing it to another table as foreign key. My problem is how to write web form code in C# & ASP.NET, because everything I wrote has some errors in it.

This is procedure and works fine.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TEST]
	// Table1 
	@col1 NVARCHAR(MAX)
	,@col2 NVARCHAR(MAX)
	,@col3 NVARCHAR(MAX)
	,@col4 NVARCHAR(MAX)
	//Table 2 
	@FileName VARCHAR(50)
	,@FilePath VARCHAR(200)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Table_Var TABLE (ID1 INT)

	INSERT INTO Table1 (
		col1,col2,col3,col4
		)
	OUTPUT inserted.ID1
	INTO @Table_Var(ID1)
	SELECT @col1,@col2,@col3,@col4

	INSERT INTO Table2 (
		colID,FileName,FilePath
		)
	SELECT ID1,@FileName,@FilePath
	FROM @Table_Var
END

ID1 is identity in Table1 and foreign key in Table2 in column col.

Does anyone have an idea? Thanks!

What I have tried:

The idea is to have some columns for details about products (Table 1) and in the other table I will store pictures of products (Table2).

This is the code that's not working:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e) { }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string query = "TEST"; int ID; string connect = @"Server=.\SQLExpress;Database=database;Trusted_Connection=Yes;";
        using (SqlConnection conn = new SqlConnection(connect))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@col1", col1.Text);
                cmd.Parameters.AddWithValue("@col2", col2.Text);
                cmd.Parameters.AddWithValue("@col3", col3.Text);
                cmd.Parameters.AddWithValue("@col4", col4.Text);
                cmd.Parameters.Add("@ID1", SqlDbType.Int, 0, "ID1");
                cmd.Parameters["@ID1"].Direction = ParameterDirection.Output; conn.Open();
                cmd.ExecuteNonQuery();
                ID = (int)cmd.Parameters["@ID1"].Value;
            }
            conn.Close();
            conn.Dispose();
        }
    }
    protected void btnUploadImage_Click(object sender, EventArgs e)
    {
        if (FileUploadImage.PostedFile != null)
        {
            string FileName = Path.GetFileName(FileUploadImage.PostedFile.FileName);
            FileUploadImage.SaveAs(Server.MapPath("image/" + FileName));
            String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["databaseConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            string strQuery = "insert into Table2(colID, FileName, FilePath)" + " values(@ID, @FileName, @FilePath)";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.Add("@ID");
            cmd.Parameters.AddWithValue("@FileName", FileName);
            cmd.Parameters.AddWithValue("@FilePath", "image/" + FileName);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con; try
            {
                con.Open(); cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                con.Close(); con.Dispose();
            }
        }
    }
}