Update a list box

[sbandalli]

Thanks a lot......Please look at my next reply and give me some idea....

[babai28]

First tell me what is your data base?
Is it SQL or Access Database file or something else?
.Net offers a number of ways to perform Data Base operation. You may go for the DataAdapter and Dataset way. But since the way you are folllowing is least suited for data binding operation, I sugget you to go for connected way of dataBase operation. Go for a command object that would add the newly added item to the database.
I will only be able to help you after you tell me about the database (including table and all) structure you are following.

Regards,
Siddhartha

[sbandalli]

Hello Siddharth,

Thanks a lot for your help:

Right now what I am doing is :

I have a text box, When I enter items in textbox like Food, and click on Save button it copies it to Listbox, and If I wish to change Food Item to restaurant,I double click on Food(in the listbox) and Food appears in the Textbox and I can change Food to Restaurant(The Logic is as you had suggested previously).I am attaching the code here.

private void button2_Click(o bject sender, EventArgs e) //SAVE BUTTON
{
if (textBox1.Text == String.Empty)
{
panel1.Visible = true;
}



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

string itemToAdd = textBox1.Text;
listBox1.Items. Add(itemToAdd);
textBox1.Text = String.Empty;

}

else if (globalindex != -1)
{
listBox1.Items[globalindex] = textBox1.Text;

textBox1.Clear( );
}



}

----------
Double clicking event on Listbox

private void listBox1_Double Click(object sender, System.EventArg s e)
{
if (listBox1.Selec tedItems.Count != 0)
{
globalindex = listBox1.Select edIndices[0];
textBox1.Text = listBox1.Select edItems[0].ToString();

}
}


Now my next thing is When I click on Save button the Items shoule as well go the Database table(along with the Listbox which is already done).

The Database I am using is SQL Server 2005

Table Name : Category
Columns:
Category Id --Primary Key--type Int--Not Null (Category Id will automatically incriment the numbers 1,2 3.....as I enter stuff in the table.

Category Name: This column should have the Items which is entered in the textbox( When I click on Save the Item should be transfered to CategoryName column)


DateCreated: Should have the date when the used enters the CategoryName.

I dont know how to Create Database connection
I was refering to this website:

http://msdn.microsoft.com/en-us/library/ms973824.aspx

I have not tired it though...
In that one of the step is to Drag and Drop "generate DataSet "Control Under Data(toolbox). I was not able to find the "Generate Data Set toolbox"

I am using VS 2008.

I know its too lengthy to read,but since I am new to VS and C# I need some guidance like how to go with this...Thanks a lot for your help Siddharth.

[babai28]

Hi,
Reading long replies is not a problem.! :)
Well, from what ever I understood from your reply I concluded the following things:
1) You want to maintain a list of categories in the database.
2) You want to provide the add, edit and delete functionalities using a list box control.
3) You store the data in a table called Category which has an auto incremeting primary key.
If my understanding is correct, then I don't think there is a need for updating the added items in the list box. You may update it directly in the data base and reshow the table afresh at the front end. Why do you want to update the UI and the data base separately?
You may follow the following techniques:
1) Bind the list box to the table and then do the add/edit/delete operation in the db itself. This will automatically get reflected in the listbox.
2) Another more handy approach (if the table is not too big) is to write code to show records in the list box directly from the db table and every time you perform an operation (add/edit/delete) in the database just call the method of redisplaying the data afresh in the front end.

Always remember, there is no single approach to a problem statement. A solution which is good in one case may be the worst in another case. Its the developer's job to decide which solution will be best suited.

You may update the database using any of the followng ways:
1) Writing simple stored procedures.
2) Writing parameterized query.
3) Firing constructed query from the front end (Not recommended).

Whenever you want to edit or delete a particular item you will need to have its Category_Id so that you may write a db command like:
"Delete from category where category_id=2"
For this I suggest storing the Category_Id in the tag property of the listBox item.

Writing connection string and all won't be a trouble if you have internet. Just search in google about the things I have suggested. I am sure you will get something useful. Let me know if my post helps.
All the best and happy .Netting.

Regards,
Siddhartha

[babai28]

For reflecting data from an sql table to the front end list box.

This is a good link.

[sbandalli]

Hello Siddharth,

Thanks for the reply, Your answers are really helping me a lot..Thanks for your patience...

I was having a problem on stored procedure

When I click on my Save button( which saves the items in the textbox to Listbox as well as Database column) , in order to store in the database I am calling a stored procedure in my SQL Server 2005 database. Here is how I am doing.

void SavetoDB()
{
SqlCommand command = new SqlCommand("dat abaseinsert", connection);
command.Command Type = CommandType.Sto redProcedure;
SqlParameter p1 = new SqlParameter("@ categoryname", SqlDbType.NVarC har, 50, "categoryname") ;
SqlParameter p2 = new SqlParameter("@ datecreated", SqlDbType.DateT ime, 50, "datecreate d");
command.Paramet ers.Add(p1);
command.Paramet ers.Add(p2);


command.Paramet ers[0].Value= textBox1.Text;

command.Paramet ers[1].Value = DateTime.Now;

int i = command.Execute NonQuery();

}

When I call the stored procedure using above code, The database will have only the 1st letter of the textBox1.Text item( for example if I enter Food in my textbox1.txt, the database get only F)...I dont the problem,tried a lot..

Here is my Stored procedure:

create proc databaseinsert( @categoryname nvarchar,@datec reated datetime)
as insert into Category(catego ryname,datecrea ted)
values (@categoryname, @datecreated);


My table Name: Category
Columns
categoryname
categoryid
datecreated.

categoryid incriments automatically.

Thanks a lot Siddharth...

[babai28]

This is strange :)
Please check with the length of the CategoryName field. If you don't understand what I mean then please right click on the table in the business Studio and click create script. paste the script here. I guess you have made the length of the field 1. That is why SQL is taking only the first character and curtailing the rest of the text.
This may be a possibility, let me know if I am right.

Regards,
Siddhartha

[sbandalli]

when i click on Script Action to new query window it gives me a messages "There is not action to be Scripted". :(

I dont know how to check for the lenght of categoryname field.
but when i right click on columnname(cate goryname) it says nvarchar(50).


And also I wanted to copy the categoryname in my database column to the Listbox as soon as the form loads. I am not sure how to do databinding for listbox with DataAdapter and Dataset so that I can copy the categoryname to the listbox.Can you please help on this ...Thanks a lot again

[babai28]

Add the efollowing user directive :

using System.Data.SqlClient;

At the class level declare a dataSet

 DataSet ds = new DataSet();

In the form load event add the following code:

SqlDataAdapter da = new SqlDataAdapter("Select * from category", finConnection);//Replace finConnection with your connection object
            listBox1.Items.Clear();
            try
            {
                ds.Clear();
                da.Fill(ds, "Category");
                foreach (DataRow drow in ds.Tables["Category"].Rows)
                {
                    listBox1.Items.Add(drow["CategoryName"]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

See if this is working.

[babai28]

I see you will be having a tough time tying to edit items this way.
May be you switch to some different control. Or you go for data binding alternative.
Search in the internet for data binding in a listBox. the above solution i suggested will only be good for displaying the data in the listBox.

Regards,
Siddhartha

[babai28]

For binding try this:

SqlDataAdapter da = new SqlDataAdapter("Select * from category", finConnection);//Replace finConnection with your connection object 
          listBox1.Items.Clear();
            try
            {
                ds.Clear();
                da.Fill(ds, "Category");
                listBox1.DataSource = ds.Tables["Category"];
                listBox1.DisplayMember = "CategoryName";                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

But remember, once you bind the data, you will not be able to update the binded data in the Front end UI, You will need to update the data in the binding dataset, which will be reflected in the UI.

Regards,
Siddhartha

[babai28]

Since you have data bounded the listBox your listBox Double Click Event is to be handled like:

private void listBox1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (listBox1.SelectedItems.Count != 0)
            {
                globalIndex = listBox1.SelectedIndices[0];
                textBox1.Text = ds.Tables["Category"].Rows[globalIndex]["CategoryName"].ToString();
                textBox1.SelectAll();
            }
        }

All the best.!

[sbandalli]

Thanks Siddharth,

It worked, but as you told I will try to see which way I can update,bcoz I have to update listbox (I mean if the user wishes to update an item in the listbox ) I should be able to do so that it updates in DB also.

But one thing I was not able to understand is I could not see any Binding command in ur code,So how are you binding it to the Database column??

thanks again...

[babai28]

Buddy, I have posted three other posts too, Please refer to them for binding and all.

[babai28]

This line of code binds the data:-

listBox1.DataSource = ds.Tables["Category"];

Every bindable control has the property DataSource. It mentions the source of the data to which you can bind the control.
You may choose to bind the control to a data table, Data Column, Array of objects etc.

Regards,
Siddhartha