How to insert (add) a record to a database table

Administration Database
1

The following example shows how to create a database table and then populate that table with values from your form using code-behind.

First, you’ll need a table in a database. Use the following script to create a test table in a database of your choice (change the statement USE [Test_DB] to use the appropriate database):

USE [Test_DB]
GO

/******Object: Table [dbo].[TestTable] Script Date: 11/12/2009 12:05:36******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
 [ID] [int] NULL,
 [NAME] [nvarchar](255) NULL,
 [AMOUNT] [float] NULL,
 [DATE] [date] NULL
) ON [PRIMARY]

GO

Then, add the following code to your code-behind page so that it executes when an event is triggered on the web form (in this case, when a button called btnWriteToDb is clicked).

protected void btnWriteToDb_Click(object sender, EventArgs e)
{
 // Gather Data to Write
 string sID = this.REQ_USERS_ID.Text;
 string sName = this.REQ_USERS_NAME.Text;
 string sAmount = this.REQ_USERS_AMT.Text;
 string sDate = this.REQ_USERS_DATE.Text;

 // Create and Open a Database connection object(s)
 string sConnStr = SqlDataSource_TestTable.ConnectionString + "";
 string sConnProvider = SqlDataSource_TestTable.ProviderName;

 // Define the Insert Command
string sSql = "INSERT INTO [Test_DB].[dbo].[TestTable] ([ID] ,[NAME] ,[AMOUNT] ,[DATE]) VALUES (@id, @name, @amount, @date)";
// Execute the Insert within a Try/Catch Statement for Error Trapping
 try
 {
   using(SqlConnection sqlConn = new SqlConnection(sConnStr)) { 
    SqlCommand sqlCmd = new SqlCommand(sSql, sqlConn); 
    sqlCmd.Parameters.AddWithValue("@id", sID); 
    sqlCmd.Parameters.AddWithValue("@name", sName); 
    sqlCmd.Parameters.AddWithValue("@amount", sAmount); 
    sqlCmd.Parameters.AddWithValue("@date", sDate); 
    sqlCmd.Connection.Open(); 
    sqlCmd.ExecuteNonQuery(); 
  }
 }
 catch (Exception ex)
 {
 // Write an error to a label called REQ_ERROR
 this.REQ_ERROR.Text += Environment.NewLine + ex.Message;
 }
 finally { }
}

Important

The above code sample is intended to demonstrate the concept behind inserting a record into a database table and takes into consideration security vulnerabilities such as SQL injection. For information on how to prevent SQL injection flaws in your application, see http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet