[RESOLVED] update datagrid problem

[KingSatan]

okim doing and update on a dataset which is to return menu information

everything works fine but when updated it only saves the first symbol to the database

heres the update method on the main class


public void UpdateDGMI(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
intCName0 =(int)(dgUMenuItems.DataKeys[e.Item.ItemIndex]);

System.Web.UI.WebControls.TextBox cName1 = new System.Web.UI.WebControls.TextBox();
cName1 = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("txtMenuItems");

strCName = cName1.Text.ToString();

System.Web.UI.WebControls.TextBox cName2 = new System.Web.UI.WebControls.TextBox();
cName2 = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("txtPriceRange");

strCName2 = cName2.Text.ToString();

System.Web.UI.WebControls.TextBox cName3 = new System.Web.UI.WebControls.TextBox();
cName3 = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("txtDescription");

strCName3 = cName3.Text.ToString();

dgUMenuItems.EditItemIndex = -1;
mi.UpdateMenuItems(strCName, strCName2, strCName3, intCName0);

BindDataDG();
}


heres my update command in my custom class


public void UpdateMenuItems(string strCName, string strCName2, string strCName3, int intCName0)
{
sqlconn.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];//connection string in the configuration settings
SqlCommand UpdateMItems = new SqlCommand("spUpdateMenu",sqlconn);
UpdateMItems.CommandType = CommandType.StoredProcedure;

UpdateMItems.Parameters.Add(new SqlParameter("@ID",SqlDbType.Int, 4));
UpdateMItems.Parameters["@ID"].Value = intCName0;

UpdateMItems.Parameters.Add(new SqlParameter("@ItemName",SqlDbType.NVarChar, 40));
UpdateMItems.Parameters["@ItemName"].Value = strCName;

UpdateMItems.Parameters.Add(new SqlParameter("@PriceRange",SqlDbType.NVarChar));
UpdateMItems.Parameters["@PriceRange"].Value = strCName2;

UpdateMItems.Parameters.Add(new SqlParameter("@Description",SqlDbType.NVarChar,195));
UpdateMItems.Parameters["@Description"].Value = strCName3;
oconn();
UpdateMItems.ExecuteNonQuery();
cconn();
}
public void dsAcceptChanges()
{
Getds().AcceptChanges();
}
void oconn(){sqlconn.Open();}
void cconn(){sqlconn.Close();}
public DataSet Getds() { return dsUI; }
public string strtable() { return strtbl; }

}

so i was just wondering why it only puts one symbol in the database?

[jmcilhinney]

You're calling UpdateItems once and UpdateItems calls ExecuteNonQuery once and ExecuteNonQuery updates one record.

If you have a DataTable containing changes then to save all those changes to the database you use an SqlDataAdapter with your SqlCommand assigned to its UpdateCommand. You do NOT set the Value property of any of your SqlParameters. You specify their SourceColumn from the DataTable and then when you call the adapter's Update method it will loop through the rows, get the required data, assign it to the parameters then execute the command.

[KingSatan]

thx will try
but will you give an example also?

[jmcilhinney]

SqlConnection con = new SqlConnection("connection string here");
SqlDataAdapter adp = new SqlDataAdapter("SELECT ID, Name FROM MyTable", con);
SqlCommand cmd = new SqlCommand("UPDATE MyTable SET Name = @Name WHERE ID = @ID", con);

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name");
cmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID");
adp.UpdateCommand = cmd;

DataTable dt = new DataTable();

adp.Fill(dt);

// Edit rows in dt here.

adp.Update(dt);

[KingSatan]

does it make too much of a diff if im not using a data table?
using a datagrid
also they're in two seperate classes

so how will it know any of the values to insert into the db?
aslo the real problemo is that it only saves the very first letter or number in the edit item index

[jmcilhinney]

A DataGrid is a control for displaying data. How did you retrieve the data in the first place?

[KingSatan]

heres what populates and updates the datagrid its in its own class




using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.Configuration;
namespace TulalipCasinoMenus
{
public class MItemPop
{
public int intDsDet;
DataSet dsUI = new DataSet();
SqlConnection sqlconn = new SqlConnection();
string strSpDet, strUSpDet;//a string which is assigned a value that determines which stored procedure to run.
string strtbl;//the string which holds the text value of the table name that will be displayed in the datagrid on the public page


public MItemPop()
{}
public void PopulateMenuItems(int drDet)
{
SqlCommand GetMItems = new SqlCommand("spGetAllMenuItems",sqlconn);//the name of the command determined by which row was selected
GetMItems.CommandType = CommandType.StoredProcedure;//type of command
sqlconn.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];//connection string in the configuration settings
if(drDet.Equals(-1))
{
strSpDet="spGetOne";
strtbl="tblMenuItems";
}
else
{
GetMItems.Parameters.Add("@intRestaurantID", SqlDbType.Int, 4);
GetMItems.Parameters["@intRestaurantID"].Value=drDet;
strtbl="tblMenuItems";
}
SqlDataAdapter da = new SqlDataAdapter(GetMItems);//da and ds
oconn();
GetMItems.ExecuteNonQuery();
da.Fill(dsUI,strtbl);//trying to fill the data adapter
cconn();}
public void UpdateMenuItems(string strCName, string strCName2, string strCName3, int intCName0)
{
sqlconn.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];//connection string in the configuration settings
SqlCommand UpdateMItems = new SqlCommand("spUpdateMenu",sqlconn);
UpdateMItems.CommandType = CommandType.StoredProcedure;

UpdateMItems.Parameters.Add(new SqlParameter("@ID",SqlDbType.Int, 4));
UpdateMItems.Parameters["@ID"].SourceColumn = "intMenuItemID";
UpdateMItems.Parameters["@ID"].Value=intCName0;

UpdateMItems.Parameters.Add(new SqlParameter("@ItemName",SqlDbType.NVarChar, 40));
UpdateMItems.Parameters["@ItemName"].SourceColumn = "chrMenuItem";
UpdateMItems.Parameters["@ItemName"].Value=strCName.ToString();

UpdateMItems.Parameters.Add(new SqlParameter("@PriceRange",SqlDbType.NVarChar, 50));
UpdateMItems.Parameters["@PriceRange"].SourceColumn = "chrPriceRange";
UpdateMItems.Parameters["@PriceRange"].Value=strCName2.ToString();

UpdateMItems.Parameters.Add(new SqlParameter("@Description",SqlDbType.NVarChar, 195));
UpdateMItems.Parameters["@Description"].SourceColumn = "chrDescription";
UpdateMItems.Parameters["@Description"].Value=strCName3.ToString();

oconn();
UpdateMItems.ExecuteNonQuery();
cconn();
}
public void dsAcceptChanges()
{
Getds().AcceptChanges();
}
void oconn(){sqlconn.Open();}
void cconn(){sqlconn.Close();}
public DataSet Getds() { return dsUI; }
public string strtable() { return strtbl; }

}
}



heres the main class in which all the datagrid and dropdown list functions and methods are.



using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace TulalipCasinoMenus
{
/// <summary>
/// Summary description for Public_Page.
/// </summary>
public class Public_Page : System.Web.UI.Page

MNamePop mn=new MNamePop();
MItemPop mi = new MItemPop();
DataSet dsMNP;
public int drDet;
private void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
drDet = ddlURestaurants.SelectedIndex;
BindDataRS();
BindDataDG();
}
}
private void BindDataRS()
{
dsMNP = mn.Getds();
this.mn.ddlpop();
ddlURestaurants.DataSource=dsMNP;
ddlURestaurants.DataMember="tblRestaurants";
ddlURestaurants.DataTextField="chrRestaurantName";
ddlURestaurants.DataBind();
drDet = ddlURestaurants.SelectedIndex;
}
void BindDataDG()
{
mi.dsAcceptChanges();
mi.PopulateMenuItems(drDet);
dgUMenuItems.DataSource=mi.Getds();
dgUMenuItems.DataMember=mi.strtable();
dgUMenuItems.DataKeyField="intMenuItemID";
dgUMenuItems.DataBind();
drDet = ddlURestaurants.SelectedIndex;
}
public void EditDGMI(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
// We use CommandEventArgs e to get the row which is being clicked
// This also changes the DataGrid labels into Textboxes so user can edit them
dgUMenuItems.EditItemIndex = e.Item.ItemIndex;
// Always bind the data so the datagrid can be displayed.
BindDataDG();
}
public void CancelDGMI(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
// All we do in the cancel method is to assign '-1' to the datagrid editItemIndex
// Once the edititemindex is set to '-1' the datagrid returns back to its original condition
dgUMenuItems.EditItemIndex = -1;
BindDataDG();
}
#region
[web designer generated code and other crap]
#endregion

private void ddlURestaurants_SelectedIndexChanged(object sender, System.EventArgs e)
{
drDet = ddlURestaurants.SelectedIndex;
BindDataDG();
}
public string strCName, strCName2, strCName3;
public int intCName0;
public void UpdateDGMI(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
// System.Web.UI.WebControls.TextBox cName = new System.Web.UI.WebControls.TextBox();
// cName = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("id");

intCName0 =(int)(dgUMenuItems.DataKeys[e.Item.ItemIndex]);

System.Web.UI.WebControls.TextBox cName1 = new System.Web.UI.WebControls.TextBox();
cName1 = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("txtMenuItems");

strCName = cName1.Text.ToString();

System.Web.UI.WebControls.TextBox cName2 = new System.Web.UI.WebControls.TextBox();
cName2 = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("txtPriceRange");

strCName2 = cName2.Text.ToString();

System.Web.UI.WebControls.TextBox cName3 = new System.Web.UI.WebControls.TextBox();
cName3 = (System.Web.UI.WebControls.TextBox) e.Item.FindControl("txtDescription");

strCName3 = cName3.Text.ToString();


mi.UpdateMenuItems(strCName, strCName2, strCName3, intCName0);
dgUMenuItems.EditItemIndex = -1;
BindDataDG();
}
}
}

i have one dropdown list and one datagrid
the user must select something from the dropdownlist to populate the datagrid i use the BindDataDG method which calls the MItemPop.PopulateDatagrid method in which


public void PopulateMenuItems(int drDet)
{
SqlCommand GetMItems = new SqlCommand("spGetAllMenuItems",sqlconn);//the name of the command determined by which row was selected
GetMItems.CommandType = CommandType.StoredProcedure;//type of command
sqlconn.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];//connection string in the configuration settings
if(drDet.Equals(-1))
{
strSpDet="spGetOne";
strtbl="tblMenuItems";
}
else
{
GetMItems.Parameters.Add("@intRestaurantID", SqlDbType.Int, 4);
GetMItems.Parameters["@intRestaurantID"].Value=drDet;
strtbl="tblMenuItems";
}
SqlDataAdapter da = new SqlDataAdapter(GetMItems);//da and ds
oconn();
GetMItems.ExecuteNonQuery();
da.Fill(dsUI,strtbl);//trying to fill the data adapter
cconn();}

is called

in regards to the original problem of only one character being stored when updated.
the problem was with my stored proc. i didsnt specify the length of each perameter

[jmcilhinney]

Well looky, looky:da.Fill(dsUI,strtbl);You're populating a DataTable.

[KingSatan]

??????????
dsUI is a dataset which is used by the main class to fill the datagrid on the main page
strtbl is the string which decides what table to be displayed


public int intDsDet;
DataSet dsUI = new DataSet();
SqlConnection sqlconn = new SqlConnection();
string strSpDet, strUSpDet;//a string which is assigned a value that determines which stored procedure to run.
string strtbl;//the string which holds the text value of the table name that will be displayed in the datagrid on the public page


public MItemPop()
{}
public void PopulateMenuItems(int drDet)
{
SqlCommand GetMItems = new SqlCommand("spGetAllMenuItems",sqlconn);//the name of the command determined by which row was selected
GetMItems.CommandType = CommandType.StoredProcedure;//type of command
sqlconn.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];//connection string in the configuration settings
if(drDet.Equals(-1))
{
strSpDet="spGetOne";
strtbl="tblMenuItems";
}
else
{
GetMItems.Parameters.Add("@intRestaurantID", SqlDbType.Int, 4);
GetMItems.Parameters["@intRestaurantID"].Value=drDet;
strtbl="tblMenuItems";
}
SqlDataAdapter da = new SqlDataAdapter(GetMItems);//da and ds
oconn();
GetMItems.ExecuteNonQuery();
da.Fill(dsUI,strtbl);//trying to fill the data adapter
cconn();}

i also did it this way because when the page is loaded the selected index of the dropdownlist (drDet holds this value) is always -1 so i made a lil if statement to fix it so when the pge loads instead of the user needing to select an item to populate the datagrid it automaticaly seletes the first item in the DB the user can still change which datagrid to select

[jmcilhinney]

What do you think a DataSet contains? It contains DataTables, which contain DataRows, which contain data. That line of code is populating a Datatable contained in the dsUI DataSet. This:da.Fill(dsUI,strtbl);is exactly equivalent to this:if (!dsUI.Tables.Contains(strtbl))
{
dsUI.Tables.Add(strtbl);
}

DataTable dtUI = dsUI.Tables(strtbl);

da.Fill(dtUI);

[KingSatan]

back on this issue again

here are the 3 methods im using to update the data seems real simple

same thing happens, only one char goes into the table and onlu one char is stored in the db


public void EditDGA(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{ // We use CommandEventArgs e to get the row which is being clicked
// This also changes the DataGrid labels into Textboxes so user can edit them
try
{
lblAError.Text = "";
dgAUpdates.EditItemIndex = e.Item.ItemIndex;
// Always bind the data so the datagrid can be displayed.
i = e.Item.ItemIndex;
ABindData("chrUpdate");

}
catch (Exception ex) { lblAError.Text = ex.Message.ToString() + er; }
}

public void UpdateDGA(object source, DataGridCommandEventArgs e)
{
try
{
/*these put the ptroper text into the proper strings so they may be tested
* to see if the data is ok to input to the DB */

intReID = (int)(dgAUpdates.DataKeys[e.Item.ItemIndex]);
System.Web.UI.WebControls.TextBox txtUpdate = new System.Web.UI.WebControls.TextBox();
txtUpdate = (System.Web.UI.WebControls.TextBox)e.Item.FindControl("txtEditUpdate");
strUpdate = txtUpdate.Text.ToString();


//tests the strings to see if any are blank
strchkblank1 = strUpdate;
chkblnk();
//the if statements that determine if the data can be inputted to the DB
if (isblank == true) { return; }
else
{
UpPop.UpdateUpdates(strUpdate, intReID);
dgAUpdates.EditItemIndex = -1;
ABindData("chrUpdate");

clearvars();
}
}

catch (Exception ex) { lblAError.Text = ex.Message.ToString() + er; }
}


ok this one is in a separate class
public void UpdateUpdates(string strCName, int intCName0)
{
ConnectionStringSettings settings = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"];
string connectionString = settings.ConnectionString;
sqlconn.ConnectionString = connectionString;

SqlCommand UpdateRItems = new SqlCommand("stpUpdateUpdate",sqlconn);
UpdateRItems.CommandType = CommandType.StoredProcedure;

UpdateRItems.Parameters.Add(new SqlParameter("@intUpdateID", SqlDbType.Int, 4, "intUpdateID"));
UpdateRItems.Parameters["@intUpdateID"].Value = intCName0;

UpdateRItems.Parameters.Add(new SqlParameter("@chrUpdate", SqlDbType.NVarChar, 30, "chrUpdate"));
UpdateRItems.Parameters["@chrUpdate"].Value = strCName;

OConn();
UpdateRItems.ExecuteNonQuery();
CConn();
AcceptChanges();
}

and then the data is bound(again)

void ABindData(string sortfield)
{
try
{

dsUpdates = UpPop.getdsU();
dsUpdates.Clear();
dsUpdates = UpPop.getdsU();

this.UpPop.UpdatePop();
DataView dv = dsUpdates.Tables[0].DefaultView;
dv.Sort = sortfield;
dgAUpdates.DataSource=dv;
dgAUpdates.DataMember = "chrUpdate";
dgAUpdates.DataBind();
}
catch(Exception ex){lblAError.Text=ex.Message.ToString()+er;}
}


ive put a watch on the variables and they come out the same as when i put em in

im pulling my hair out...... ust wanna get this done