Update a list box

[sbandalli]

Thanks a lot Siddhart, Your answers are really helping me a lot.....

[sbandalli]

Hello Siddharth,

I accidently deleted my database , So i created a database again and created my table again and also wrote new storedprocedure which is used to store the textbox items in the Database column categoryname.

here is the method:

void SavetoDB()
{
SqlDataAdapter categoryDataAda pter = new SqlDataAdapter( new SqlCommand("SEL ECT categoryname,da tecreated FROM Category", connection));

categoryDataAda pter.InsertComm and = new SqlCommand("dat abaseinsert", connection);
SqlCommand cmdInsert = categoryDataAda pter.InsertComm and;
cmdInsert.Comma ndType = CommandType.Sto redProcedure;
cmdInsert.Param eters.Add(new SqlParameter("@ categoryname", SqlDbType.VarCh ar,50,"category name"));
cmdInsert.Param eters.Add(new SqlParameter("@ datecreated", SqlDbType.VarCh ar,50,"datecrea ted"));
categoryDataAda pter.FillSchema (cDS, SchemaType.Sour ce);
DataTable pTable = cDS.Tables["Table"];
//pTable.TableNam e = "Category";

// Inserting the data from the textbox into dataset

DataRow cOrderRow = cDS.Tables["Category"].NewRow();
cOrderRow["categoryna me"] = textBox1.Text;
cOrderRow["datecreate d"] = DateTime.Now;
cDS.Tables["Category"].Rows.Add(cOrde rRow);

// Updating the Dataset
categoryDataAda pter.Update(cDS , "Category") ;

} //end of funtion savetoDB

But now when i run the program ,and after entering item in the textbox it says sotred procedure not found and the execution stops at categoryDataAda pter.Update(cDS , "Category") ; //

Earlier it was working fine before me deleting the DB, now I have created exacly like i did before,but still giving this error.Help....p lease

[sbandalli]

Hello Siddharth,

Ignore the previous question, I got it right (about the error), but still my database takes only one character , i checked the length of the categoryname in properties, it is set to 1, that is the reason why its taking only one character (as you had told) ,but how do I change it?? its not allowing me to change....

Thanks a lot......

[babai28]

Try this on the Query Analyser of the SQL DB.

ALTER TABLE Category
ALTER COLUMN CategoryName varchar(80)

Which version of MS SQL are you using ?

[sbandalli]

Hello Siddharth,

Have few more doubts,

Intially I have a textbox1 when i write item to the textbox and click save button it goes to database category column and saves in the db.

here is my code:
void SavetoDB()
{

SqlDataAdapter categoryDataAda pter = new SqlDataAdapter( new SqlCommand("SEL ECT categoryname,da tecreated FROM Category", connection));

categoryDataAda pter.InsertComm and = new SqlCommand("dat abaseinsert", connection);
SqlCommand cmdInsert = categoryDataAda pter.InsertComm and;
cmdInsert.Comma ndType = CommandType.Sto redProcedure;
cmdInsert.Param eters.Add(new SqlParameter("@ categoryname", SqlDbType.VarCh ar,50,"category name"));
cmdInsert.Param eters.Add(new SqlParameter("@ datecreated", SqlDbType.VarCh ar,50,"datecrea ted"));
categoryDataAda pter.FillSchema (cDS, SchemaType.Sour ce);
DataTable pTable = cDS.Tables["Table"];


// Inserting the data from the textbox into dataset

DataRow cOrderRow = cDS.Tables["Category"].NewRow();
cOrderRow["categoryna me"] = textBox1.Text;
cOrderRow["datecreate d"] = DateTime.Now;
cDS.Tables["Category"].Rows.Add(cOrde rRow);

// Updating the Dataset
categoryDataAda pter.Update(cDS , "Category") ;

} //end of funtion savetoDB
The above function is working fine.......

next I have to populate the listbox with the with the items in the DB category column
so I have a method called popoulatelistbo x
here is the code :
void populatelistbox ()
{




SqlDataAdapter da = new SqlDataAdapter( "Select * from category", connection);
listBox1.Items. Clear();
try
{
cDS.Clear();
da.Fill(cDS, "Category") ;

listBox1.DataSo urce = cDS.Tables["Category"];
listBox1.Displa yMember = "categoryna me";
}
catch (Exception ex)
{
MessageBox.Show (ex.Message);
}



} //end of populate listbox method

This is also working fine........... ..........

Now if I have to change an item in the listbox after it populates ,the user should be able to do
this i am taking care in doubleclick even of the save button
here is the code:

private void listBox1_Double Click(object sender, System.EventArg s e)
{


if (listBox1.Selec tedItems.Count != 0)
{
globalindex = listBox1.Select edIndices[0];
textBox1.Text = cDS.Tables["Category"].Rows[globalindex]["categoryna me"].ToString();
textBox1.Select All();
}


}

Here is the problem:

suppose the llistbox has item Electricity and if the user wishes to change it as energy, If I double click on electricity and when Electricity appears in the textbox and If i change it to Energy,its not updating Electricity, instead its writing Energy as an new item in the Listbox as well as Database table.

How do correct this????

Thanks a lot Siddharth...... ....

[babai28]

hehehe.....
Its simple....becau se your user is clicking the same button as "Adding" to save the change he made.
On clicking the "Add" button the method "SaveToDB() " is being called. Isn't that simple to understand.
How can the method "SaveToDB() " update the value..it adds the value and hence the error. Am I right? You need to provide another button for edit or you need to handle the click of the "Save" button in two different ways. For editing and for adding.
By the way did the Database Column problem got solved with the code i provided yesterday?

Regards,
Siddhartha

[babai28]

Just a request. Try to keep your posts a bit more properly formatted.
You can include the code inside the [CODE] tag "

 //PUT CODE HERE

"

Regards,
Siddhartha

[sbandalli]

I will try that double clicking the item in the listbox and editing it later......

I was trying to bind my combobox in my datagridview to my categoryname column in my Category table. This is how I tried,not working :(

void bindcombobox()
        {
            SqlDataAdapter da = new SqlDataAdapter("Select * from category", connection);
   cDS.Clear();
   da.Fill(cDS, "Category");
   DataGridViewComboBoxColumn combocolumn = new     DataGridViewComboBoxColumn();
          
            combocolumn.DataSource = cDS.Tables["Category"];
            combocolumn.DisplayMember = "categoryname";
           combocolumn.ValueMember = "categoryname";*/
}

I dont know where I am going wrong...

And regarding that DB length thing....I was able to change the Length property of categoryname, but still the same, I had same problem with datecreated column and I changed datecreated length (as per ur suggestion) and now datecreated is working fine,but I dont know whats wrong with this categorname,but will try to figureout...


Thanks a lot....
take care...

[babai28]

Check out this link.

[sbandalli]

hello Siddharth,
I tried the link which you had sent,but still not working, is there any other resource,

I have a combobox column in my datagridview, in my combobox I should be able to display all the items in the categoryname column of the Category column. if the categoryname has food,electricit y the combobox column should be able to give pulldown which has food ,electricity so that the user can select one.

Thanks a lot..

[sbandalli]

Hello Siddharth,

Even my updating the category table when the user double clicks in the listbox is also not working.

This is how I am trying......

void SavetoDB()
        {
           //Inserting the data from the textbox into dataset

            DataTable pTable = cDS.Tables["Table"];
            DataRow cOrderRow = cDS.Tables["Category"].NewRow();
            cOrderRow["categoryname"] = textBox1.Text;
            cOrderRow["datecreated"] = DateTime.Now;
            cDS.Tables["Category"].Rows.Add(cOrderRow);

            if (textBox1.Text != String.Empty && globalindex == -1)
            {

                SqlDataAdapter categoryDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT categoryname,datecreated FROM Category", connection));

                categoryDataAdapter.InsertCommand = new SqlCommand("databaseinsert", connection);
                SqlCommand cmdInsert = categoryDataAdapter.InsertCommand;
                cmdInsert.CommandType = CommandType.StoredProcedure;
                cmdInsert.Parameters.Add(new SqlParameter("@categoryname", SqlDbType.VarChar, 50, "categoryname"));
                cmdInsert.Parameters.Add(new SqlParameter ("@datecreated", SqlDbType.VarChar, 50, "datecreated"));
                categoryDataAdapter.FillSchema(cDS, SchemaType.Source);
               

              


                // Updating the Dataset
                categoryDataAdapter.Update(cDS, "Category");


            }

            else if (globalindex != -1)
            {

                SqlDataAdapter categoryDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT categoryname,datecreated FROM Category", connection));

                categoryDataAdapter.UpdateCommand = new SqlCommand("databaseupdate", connection);
                SqlCommand cmdupdate = categoryDataAdapter.UpdateCommand;
                cmdupdate.CommandType = CommandType.StoredProcedure;
                cmdupdate.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int, 12));
                cmdupdate.Parameters.Add(new SqlParameter("@categoryname", SqlDbType.VarChar, 50));







                //categoryDataAdapter.FillSchema(cDS, SchemaType.Source);
               // DataTable pTable = cDS.Tables["Table"];


                // Inserting the data from the textbox into dataset
                Object []array = new Object[2];

                
         
                array[1] = textBox1.Text;
                array[0] = listBox1.SelectedIndex;



                /*DataRow cOrderRow1 =*/
                cDS.Tables["Category"].LoadDataRow(array,LoadOption.OverwriteChanges);

               // cOrderRow["categoryname"] = textBox1.Text;
                //cOrderRow["categoryid"] = listBox1.SelectedIndex;
                //cDS.Tables["Category"].Rows.Add(cOrderRow);

                // Updating the Dataset
                //categoryDataAdapter.Update(cDS, "Category"); //calls stored proc

            }

        } //end of funtion savetoDB

Here is my stored Procedure:

create procedure databaseupdate (@index int ,@newcategoryna me nvarchar)
as update Category
set categoryname = @newcategorynam e
where categoryid = @index;

when the user double click in the listbox and tries to alter i am checking if the globalindex !=1, then the old categoryname has to be replaced with the new category name in the listbox as well as in my categoryname column of the Categorytable.

Siddharth, I am really new to this Object Oreiented concepts and .NET,so all together I am lost...

Please guide me on this...

Thanks a lot...

[sbandalli]

Hello Siddharth,

I would like also to tell u my Category table columns

1. categoryid which incriments automatically as I save something to DB
2. categorname, which takes value form textbox1.txt
3. datecreated which takes value form DataTime.Now

Thaks a lot

[babai28]

Hi,
Reseting the globalIndex back to -1 after you have finished updating, should be done. Do that and check.
The second condition should be:

else if (textBox1.Text != String.Empty && globalindex != -1)

You would not want a category to be edited to a blank text. Would you?

Why don't you declare the categoryDataAda pter at a global level and define the
update, delete and insert command once and for all in the Form1 constructor.
Why to declare dataAdapters again and again as you update, delete or insert records? To summarize, things to be done are:
1) Declare the dataAdapter object at the class level.
2) Define the Update, Insert, Delete commands of the Adapter in the form Constructor or form load event.
3) Reset the global Index after you edit/delete a record.

Regards,
Siddhartha

[sbandalli]

Hello Siddharth,

Here is the code to update the listbox and categoryname in Category table.

 else if (textBox1.Text != String.Empty && globalindex != -1)
            {
             SqlCommand cmdupdate = new SqlCommand("databaseupdate",connection); 
cmdupdate.CommandType = 
CommandType.StoredProcedure; 
cmdupdate.Parameters.Add(
new SqlParameter("@categoryid", SqlDbType.Int, 12));
cmdupdate.Parameters.Add(new SqlParameter("@categoryname", SqlDbType.VarChar, 50)); 

// when I say @categoryid and @categoryname, should I tell the column name
 //of the DB table, I am not sure how this SQLParameter class works

Object []array = new Object[2]; 
array[1] = textBox1.Text;
array[0] = listBox1.SelectedIndex;
//Start DataBase Update 
cmdupdate.Parameters[0].Value=array[0];
cmdupdate.Parameters[1].Value=array[1];
int i=cmdupdate.ExecuteNonQuery(); 
/*([B]Here its giving me an error saying that "Procedure  function 'databaseupdate' expects parameter '@globalindex', which was not supplied.) */[/B]
//End Of database Update 

cDS.Tables[
"Category"].LoadDataRow(array,LoadOption.OverwriteChanges);//(I am not sure how it works)

                cDS.AcceptChanges();

Here is my procedure:

create procedure databaseupdate (@categoryid int,@newcategor yname nvarchar)
as update Category
set categoryname = @newcategorynam e
where categoryid = @categoryid;

Thanks a lot Siddharth

[babai28]

For showing the data in a datagridView Combo box try the following code in the page load event method:

SqlDataAdapter da = new SqlDataAdapter ("Select * from category",finConnection);            
            listBox1.Items.Clear();
            try
            {
                ds.Clear();
                da.Fill(ds, "Category");
                //listBox1.DataSource = ds.Tables["Category"];
                //listBox1.DisplayMember = "CategoryName";  
                dataGridView1.DataSource = ds.Tables["Category"];
                dataGridView1.Columns.Remove("CategoryId");

                DataGridViewComboBoxColumn listCol = new DataGridViewComboBoxColumn();
                listCol.HeaderText = "Category";
                listCol.DisplayIndex = 0;

                listCol.DataPropertyName = "CategoryId";
                listCol.DataSource = ds.Tables["Category"];
                listCol.DisplayMember = "CategoryName";
                listCol.ValueMember = "CategoryId";
                dataGridView1.Columns.Add(listCol);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

I have tried the code at my side, its working fine, so don't tell me this is not working. The code was clearly present in the link provided by me earlier. Please check for the column names as they may be different at your side. The text in the code that is within quotes are column names.

Regards,
Siddhartha