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

[punkazz]

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!

[Infarction]

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:

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#:

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:

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 :)

[punkazz]

THANK YOU! that was great help! :) I did it just a bit different but couldn't have done it without your response...now i get to go work on pulling the data back :S