2 replies to this topic

[ken2010]

 //DELETE ITEMS IN DATABASE CODE BEGINS HERE

       private void btnDelete_Click(object sender, EventArgs e)

        {

            if (mainList.SelectedItems.Count < 0)

            {

                this.mainList.Items.RemoveAt(this.mainList.SelectedIndex);

                    string strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Sampleform.mdf;Integrated Security=True;User Instance=True";

                    SqlConnection sqlConnection = new SqlConnection(strConnection);

                try

                {

                    SqlCommand sqlCommand = sqlConnection.CreateCommand();

                    string strSelect = @"SELECT * FROM SAMPLE";

                    string strDelete = @"'DELETE FROM SAPMLE WHERE firstname =' +mainList.SelectedItems.ToString()," ;

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                    sqlDataAdapter.SelectCommand = new SqlCommand(strSelect, sqlConnection);

                    DataSet dataSet = new DataSet();

                    sqlDataAdapter.Fill(dataSet, "Sample");

                    DataTable dataTable = dataSet.Tables["Sample"];


                    SqlCommand sqlCommandDelete = new SqlCommand(strDelete, sqlConnection);

                    sqlCommand.Parameters.Add("@firstname", SqlDbType.NVarChar, 50, "firstname");

                    sqlCommand.Parameters.Add("@lastname", SqlDbType.NVarChar, 50, "lastname");

                    string strFandL = @"firstname = '@firstname' and lastname = '@lastname'";

                    foreach (DataRow dataRow in dataTable.Select(strFandL))

                    {

                        dataRow.Delete();

                    }

                    sqlDataAdapter.DeleteCommand = sqlCommand;

                    sqlDataAdapter.Update(dataSet, "Sample");


                    foreach (DataRow dataRow in dataTable.Rows)

                    {

                        mainList.Items.Add(dataRow["firstname"].ToString() + dataRow["lastname"].ToString());

                    }

                }

                catch(SqlException es){

                    MessageBox.Show(es.ToString());

                    Console.WriteLine("SQL exception caught" +es.ToString());

                }

                finally{

                    sqlConnection.Close();

                }

            }

           

       }

Please kindly help me figure out what is wrong with the delete statement. On the form page there are 2-textboxes(txtfirstname, txtlastname) that the values of these go into table(Sample) directly when button(btnCreate) is clicked. A listbox(mainList) is populated from the values stored in the table(Sample).
This is what I want to do: To delete a row from the listbox(mainList) when the item is selected and the button(btnDelete) is clicked it should delete it from the database. The code above has not achieve this purpose, though not showing any error but I know I have not done enough to make it work. What else should I do? thanks.

[Momerath]

string strDelete = @"'DELETE FROM SAPMLE WHERE firstname =' +mainList.SelectedItems.ToString()," ;

SelectedItems is a collection so you are getting the collection name here, rather than the name of the item selected. Use SelectedItem.

[lespauled]

More than that, there are syntax errors:

@"'DELETE FROM SAPMLE WHERE firstname =' +mainList.SelectedItems.ToString(),"

Your selected items should be referenced in a for each. Then use the value of the item, and create a list of items, comma separated. Then change the delete to an IN instead of an equals.