Insert records into database

[peggie1990]

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:

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:

//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);

Thanks:)

[Momerath]

One (or more) of the objects osdb, txtID, txtItemName or txtItemDesc is null.

[peggie1990]

Sorry i dont get what u mean. Would you mind explain to me again?
thanks:)

[Mitja Bonca]

Your code looks very confused.
From beginning (from the top down):

Your 1st method "Insert_ItemRecords() method is a returned type of boolean, but you return SqlCommand object - this is not good at all.
At the bottom where you call this method, you simply call it , without the return type. If a Method is a boolean return type you should do it like:

void bool InsertMethod(//parameters in here//)
{
   //some code, whihc MUST return true, or false;
    if(something)
        return true;
    else
        return false;
}

//from where you call the upper method:
bool bChecking = InsertMethod(//parameters in here//);
if(bChecking)
{
     MessageBox("Inserting succeeded.");
}
else
     MessageBox("Inserting NOT succeeded.");

OR is your want to do in inserting, you better NOT USE a return type of boolean. But I would not suggest you to use sometihng like this. You better stick to the upper example (of mine).

Hope this helps explaining your issue.
Mitja

[peggie1990]

If I do not want to make use of boolean. Is it that I modify at the database.cs to something else? cause I do not know how to modify the codes above.

[Mitja Bonca]

I would suggest you not to use boolean return type of the method, because it has no point here. You simpy have to pass the appropriate parameters to the insertion method, and do the insertion - using sqlCommand`s executeNonQuery() method. Thats it.

Tak a look at this example:

//calling inset method from your class:
InsertingData(//put parameters in here to pass to the method bellow//);

//insert method:
private void InsertingData(string name, string password, string email)
    {
      string connString = @"server=x;uid=y;pwd=z;database=xyz"; //this is an example, you need your own
      using (SqlConnection sqlConn = new SqlConnection(connString))
      {
        string query = String.Format(@"INSERT INTO Users VALUES (@id, @name, @password, @email)");
        using (SqlCommand cmd = new SqlCommand(query, sqlConn))
        {
          cmd.CommandType = CommandType.Text;
          cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; //FIND YOUR NEW ID IF YOU HAVE THIS COLUMN IN DATABASE
          cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = name;
          cmd.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = password;
          cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = email;
          cmd.Connection.Open();
          try
          { cmd.ExecuteNonQuery(); }
          catch (Exception ex)
          { 
            MessageBox.Show(ex.Message); 
          }
          finally
          { cmd.Connection.Close(); }
        }
      }
    }

Mitja