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();
}
}
}
}