NEWBIE - Insert data from ASP.Net application into mutiple SQL tables

•

What is DaniWeb IT Discussion Community?

You're currently browsing the ASP.NET section within the Web Development category of DaniWeb, a massive community of 456,528 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,803 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our ASP.NET advertiser: Lunarpages ASP Web Hosting

Thread Tools Display Modes

Views: 6523 | Replies: 2

•

Join Date: Oct 2007

Posts: 3

Reputation: punkazz is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

punkazz

Offline

Newbie Poster

NEWBIE - Insert data from ASP.Net application into mutiple SQL tables

Oct 1st, 2007

Hi everyone! I am very new to programming and SQL. I have created (designed) an application for a school project; that now needs to be coded to be functional. I have been able to pull data from SQL without a problem; however, I am stuck on inserting data into sql.

Brief synopsis of app (since i have no code for insert) a user selects a "request" type; based on that different fields appear on the page and the user has to fill in SEVERAL different fields which need to feed into SEVERAL different tables within my SQL Database. On top of this, many of the tables PK needs to auto increment when a new record is added.

Can someone please point me in the write direction? Where do I start? I am writing this in C# and need help with the code on that side along with the insert commands.

Thank you!

•

Join Date: May 2006

Location: Bellevue, WA

Posts: 1,548

Reputation: Infarction has a spectacular aura about

Rep Power: 8

Solved Threads: 51

Infarction Infarction is offline

Offline

Battle Programmer

Re: NEWBIE - Insert data from ASP.Net application into mutiple SQL tables

Oct 1st, 2007

Here's 2 sample ways to do it (I used two tables, each of which have a PK and either an int or an nvarchar column):
1) Have several INSERT commands that, when the form is submitted, each pick out their own data from the form and submit them. The code would look something like this:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack) // form submitted
        {
            SqlConnection sc = null;
            SqlCommand command = null;
            try
            {
                sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
                sc.Open();
                string query1 = String.Format(@"INSERT INTO Numbers(number) VALUES({0})",
                                    numberInput.Text);
                command = new SqlCommand(query1, sc);
                command.ExecuteNonQuery();
                string query2 = String.Format(@"INSERT INTO Strings(string) VALUES('{0}')",
                                    stringInput.Text);
                command = new SqlCommand(query2, sc);
                command.ExecuteNonQuery();
            }
            finally // clean up
            {
                if(command != null)
                    command.Dispose();
                if(sc != null)
                    sc.Close();
            }
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack) // form submitted
        {
            SqlConnection sc = null;
            SqlCommand command = null;
            try
            {
                sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
                sc.Open();
                string query1 = String.Format(@"INSERT INTO Numbers(number) VALUES({0})",
                                    numberInput.Text);
                command = new SqlCommand(query1, sc);
                command.ExecuteNonQuery();
                string query2 = String.Format(@"INSERT INTO Strings(string) VALUES('{0}')",
                                    stringInput.Text);
                command = new SqlCommand(query2, sc);
                command.ExecuteNonQuery();
            }
            finally // clean up
            {
                if(command != null)
                    command.Dispose();
                if(sc != null)
                    sc.Close();
            }
        }
    }

2) Have a stored procedure in the database that does all the inserts for you. The code would look kind of like this:
C#:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack) // form submitted
        {
            SqlConnection sc = null;
            SqlCommand command = null;
            try
            {
                sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
                sc.Open();
                command = new SqlCommand("InsertData", sc);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@number", numberInput.Text);
                command.Parameters.AddWithValue("@string", stringInput.Text);
                command.ExecuteNonQuery();
            }
            finally // clean up
            {
                if(command != null)
                    command.Dispose();
                if(sc != null)
                    sc.Close();
            }
        }
    }
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack) // form submitted
        {
            SqlConnection sc = null;
            SqlCommand command = null;
            try
            {
                sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True");
                sc.Open();
                command = new SqlCommand("InsertData", sc);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@number", numberInput.Text);
                command.Parameters.AddWithValue("@string", stringInput.Text);
                command.ExecuteNonQuery();
            }
            finally // clean up
            {
                if(command != null)
                    command.Dispose();
                if(sc != null)
                    sc.Close();
            }
        }
    }

SQL:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE PROCEDURE dbo.InsertData
    (
    @number INT,
    @string NVARCHAR(50)
    )
AS
    INSERT INTO Numbers(number)
        VALUES(@number);
 
    INSERT INTO Strings(string)
        VALUES(@string);
 
    RETURN
CREATE PROCEDURE dbo.InsertData
    (
    @number INT,
    @string NVARCHAR(50)
    )
AS
    INSERT INTO Numbers(number)
        VALUES(@number);
        
    INSERT INTO Strings(string)
        VALUES(@string);
        
    RETURN

Note that I didn't validate my data in either of these. Also, the connection string should be in web.config, but I was too lazy to add it. Also note that using a SProc (stored procedure) requires EXECUTE permissions for your database, which, while you probably have it, isn't always the case. The sproc only requires one query to the database though, so it's better peforming; it's also "more secure" but validation is always a good idea (better yet, lack thereof is always a bad idea).

Oh, and for the auto increment property, MSSQL (aka T-SQL) uses the IDENTITY(start, increment) property in the column definition. If you're using Visual Studio, you can also open the table definition, and for the column properties for your PK set the identity property as well.

Another note about my code (especially if you're new to ASP.NET): the condition at the beginning checks to make sure the page is being reached from a form submission. It doesn't necessarily validate that it's from the same page (you can postback to a different URL), and I didn't code it to reset the form.

Hope this gets you started