Insert records into database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Insert records into database

Author

Topic

becky.b
Starting Member

4 Posts

Posted - 2011-02-06 : 07:31:35

Hi all,
currently I am doing an online store project using an example from one of your tutorials. In the grid view is populated with data and at the below of the grid view, there is a row of empty fields populated below. After user have enter all the necessary info and clicks on the Add New link, the data will be inserted into the database and it will also be displayed in the grid view itself.

The software that I am using is called: MS VS 2008 C#
The database that I am using is called: MS SQL Server 2008

This is the database.cs for inserting records into the database:

public bool Insert_ItemsRecords(string id, string itemName, string itemDesc)  
{  
SqlCommand cmd_ItemsList = new SqlCommand();  
  
cmd_ItemsList.CommandText = "[VRM].[INSERT_ItemsRecords]";  
cmd_ItemsList.CommandType = CommandType.StoredProcedure;  
cmd_ItemsList.Parameters.Clear();  
  
SqlParameter sqlParaID = new SqlParameter("@id", SqlDbType.VarChar, 10);  
sqlParaID.Value = id;  
cmd_ItemsList.Parameters.Add(sqlParaID);  
  
SqlParameter sqlParaItemName = new SqlParameter("@itemName", SqlDbType.VarChar, 100);  
sqlParaItemName.Value = itemName;  
cmd_ItemsList.Parameters.Add(sqlParaItemName);  
  
SqlParameter sqlParaItemDesc = new SqlParameter("@itemDesc", SqlDbType.VarChar, 1000);  
sqlParaItemDesc.Value = itemDesc;  
cmd_ItemsList.Parameters.Add(sqlParaItemDesc);  
  
return executeNotQuery(cmd_ItemsList);  
}

This is the stored procedure code:

ALTER PROCEDURE [OS].[INSERT_ItemsRecords]  
@id varchar(10),  
@itemName varchar(100),  
@itemDesc varchar(1000)  
  
  
AS  
INSERT INTO OS.Items (id, itemName, itemDesc)  
VALUES (@id, @itemName, @itemDesc)

This is the business logic code for retrieving and inserting of new records:

//Bind the GridView to with the Database returned records 
    private void BindGrid(bool Reload) 
    { 
      DataTable dtItemRecords = null; 
      if (Reload) 
      //get from database and bind to GV 
      { 
        dtItemRecords = osdb.Get_ItemRecords(tbSearchItemName.Text, tbSearchItemID.Text).Tables[0]; 
        ViewState[viewStateGVName] = dtItemRecords; 
      } 
      else 
      {    
        dtItemRecords = ViewState[viewStateGVName] as DataTable; 
      } 
  
      if (dtItemRecords != null) 
      { 
        gvItems.Columns[GVCHKBOX].Visible = true; 
        gvItems.DataSource = ViewState[viewStateGVName]; 
        gvItems.AllowSorting = true; 
        gvItems.DataBind(); 
      } 
      else 
      { 
        dtItemRecords.Rows.Add(dtItemRecords.NewRow()); 
  
        ViewState[viewStateGVName] = dtItemRecords; 
        gvItems.AllowSorting = false; 
        gvItems.DataSource = ViewState[viewStateGVName]; 
        gvItems.DataBind(); 
  
        //hide the checkbox and edit columns 
        gvItems.Columns[GVCHKBOX].Visible = false; 
        gvItems.Columns[GVEDITBTN].Visible = false; 
  
  
        int TotalColumns = gvItems.Rows[0].Cells.Count; 
        gvItems.Rows[0].Cells.Clear(); 
        gvItems.Rows[0].Cells.Add(new TableCell()); 
        gvItems.Rows[0].Cells[0].ColumnSpan = TotalColumns; 
        gvItems.Rows[0].Cells[0].Text = "No Record Found"; 
      } 
  
  
  
    } 
  
protected void gvItems_RowCommand(object sender, GridViewCommandEventArgs e)  
{  
if (e.CommandName.Equals("AddNew"))  
{  
TextBox txtID = (TextBox)gvItems.FooterRow.FindControl("txtID");  
TextBox txtItemName = (TextBox)gvItems.FooterRow.FindControl("txtItemName");  
TextBox txtItemDesc= (TextBox)gvItems.FooterRow.FindControl("txtItemDesc");  
  
  
  
osdb.Insert_ItemsRecords(txtID.Text, txtItemName.Text, txtItemDesc.Text);  
 BindGrid(true); 
      } 
      else 
      { 
        BindGrid(false); 
      } 
}//Bind the GridView to with the Database returned records 
    private void BindGrid(bool Reload) 
    { 
      DataTable dtItemRecords = null; 
      if (Reload) 
      //get from database and bind to GV 
      { 
        dtItemRecords = osdb.Get_ItemRecords(tbSearchItemName.Text, tbSearchItemID.Text).Tables[0]; 
        ViewState[viewStateGVName] = dtItemRecords; 
      } 
      else 
      {    
        dtItemRecords = ViewState[viewStateGVName] as DataTable; 
      } 
  
      if (dtItemRecords != null) 
      { 
        gvItems.Columns[GVCHKBOX].Visible = true; 
        gvItems.DataSource = ViewState[viewStateGVName]; 
        gvItems.AllowSorting = true; 
        gvItems.DataBind(); 
      } 
      else 
      { 
        dtItemRecords.Rows.Add(dtItemRecords.NewRow()); 
  
        ViewState[viewStateGVName] = dtItemRecords; 
        gvItems.AllowSorting = false; 
        gvItems.DataSource = ViewState[viewStateGVName]; 
        gvItems.DataBind(); 
  
        //hide the checkbox and edit columns 
        gvItems.Columns[GVCHKBOX].Visible = false; 
        gvItems.Columns[GVEDITBTN].Visible = false; 
  
  
        int TotalColumns = gvItems.Rows[0].Cells.Count; 
        gvItems.Rows[0].Cells.Clear(); 
        gvItems.Rows[0].Cells.Add(new TableCell()); 
        gvItems.Rows[0].Cells[0].ColumnSpan = TotalColumns; 
        gvItems.Rows[0].Cells[0].Text = "No Record Found"; 
      } 
  
  
  
    } 
  
protected void gvItems_RowCommand(object sender, GridViewCommandEventArgs e)  
{  
if (e.CommandName.Equals("AddNew"))  
{  
TextBox txtID = (TextBox)gvItems.FooterRow.FindControl("txtID");  
TextBox txtItemName = (TextBox)gvItems.FooterRow.FindControl("txtItemName");  
TextBox txtItemDesc= (TextBox)gvItems.FooterRow.FindControl("txtItemDesc");  
  
  
  
osdb.Insert_ItemsRecords(txtID.Text, txtItemName.Text, txtItemDesc.Text);  
 BindGrid(true); 
      } 
      else 
      { 
        BindGrid(false); 
      } 
}

When I debug in and clicks on the Add New, there is error indicating:
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

This sentence is highlighted in red:
osdb.Insert_ItemsRecords(txtID.Text, txtItemName.Text, txtItemDesc.Text);
BindGrid(true);

How do I set the osdb to a valid instance? Thks!

arraysys
Starting Member

12 Posts

Posted - 2011-02-09 : 14:12:14

could be as simple as try setting the property of the datagridview to:
Allow user to add rows = false.

Array

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources