Help how to delete a data in data grid..

[ranz01]

hi.. can you please help me with my program on how to delete the file in data grid and view the data into another window .. thanks

here's the pic and the file.. thanks.

 

[murer]

Delete in Customer Form

Hi

...how to delete the file in data...

I'm not shure, but I guess you want to delete a record in the access-table. Because the CustomerID is a Text-Type you will have to enclose the where-value in "

objCommand.CommandText = "Delete From lamesa1 where [CustomerID]= "" & Val(Me.DataGrid1.CurrentRowIndex) & """"

Suggestions:
- Do not use Me.DataGrid1.CurrentRowIndex for the where-value: this is not the customer-id. Also, do not use index.custad for the where-value. This is just rigth as long the datagrid is not sorted by the user in the datagrid
- Use the CommandBuilder instead of creating the sql-commands by yourself
sample:

            Dim daAccess As New OleDbDataAdapter
            Dim cmd As New OleDbCommand(strSel, connDB)
            daAccess = New OleDbDataAdapter(cmd)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daAccess)
            daAccess.UpdateCommand = cb.GetUpdateCommand
            daAccess.InsertCommand = cb.GetInsertCommand
            daAccess.DeleteCommand = cb.GetDeleteCommand
            daAccess.Update(dt)

also look at : http://vb-helper.com/howto_net_datagrid.html or http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommandbuilder.aspx
- Also you should think about your database-design. Define ID's as index
- Do not use physical path in the connection-strings. As you move your project into an other location, you will have to edit your project, because the database-connection will rise errors.

Hope to help
regards
murer

 

[ranz01]

sir thanks for the help .... but i really dont understand the commandbuilder because our professor did not taught us that and he just taught us how to build a database through Oledb.. thanks sir.. for your help.. can you please post the exact string that you've used in my program in the delete button.. thanks... regards

 

[murer]

Hi

I used the following code :

        objCommand = New OleDbCommand
        objCommand.Connection = objConn

        Dim strCustomID As String = DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)
        objCommand.CommandText = "Delete From lamesa1 where [CustomerID]= """ & strCustomID & """"

        Customerds.lamesa1.Rows.RemoveAt(DataGrid1.CurrentRowIndex)

        objConn.Open()
        Try
            objCommand.ExecuteNonQuery()
            MessageBox.Show("Record has been Deleted...")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        objConn.Close()
        objCommand.Parameters.Clear()

Hope this helps

regards
murer

 

[ranz01]

i put the code that you've send me.. it delete's the data but once you close it and open the customer form again its there again... it always comes back and not deleting it permanently... thanks again for the help..


regards..
ranz01

 

[murer]

Hi

I checked the records in the ranz.mdb and in my case the programm works fine and the records stay deleted also when I restart the programm.

Therefore please check:
1. run your programm and delete records in the customer form. Stay in the programm and open the ranz.mdb with msaccess. See if the records are deleted in the table lamesa1.
2. I'm working with VSS2005 and had once a similar problem. When you implement the access-db's with VSS wizards, VSS keeps the path of the origin-mdb. The origin path is not necessarily the path to mdb in your project-folder. VSS will copy the database from the origin-source into the project-folder each time you compile the project. This could be the reason why you will see all the "deleted" records after you restart your programm within VSS. Because your programm is working with the copy of ranz.mdb and VSS will restore the origin mdb with a compilation. But I can't tell where to find the switch to supress this behaviour in VSS2003.
3. Can you check the objCommand.CommandText in the debug-mode?? The content should be something like ""Delete From lamesa1 where [CustomerID]= "3""
If you copy this string into an new query in msaccess, you cann verify the syntax and the result of the delete-command.
4. Instead of running the programm within VSS you could also locate the exe-file in the ...\ice management\bin-directory and start it in the explorer without any VSS influences. So the origin-mdb shold not be restored by the VSS.

Hope this helps
regards
murer

 

[ranz01]

i got new errors when i copy paste it and work the program at our house.. here's the error sir...

 

[murer]

Hi

The problem is that you do not open (objConn.Open()) the connection before executing the nonquery. Anyway: why do you define the DeleteCommand ??

this is the code I tested with:

        Dim objConn As New OleDbConnection
        MyConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\temp\ice management\ice management\ranz.mdb")
        objConn = MyConn

        objCommand = New OleDbCommand
        objCommand.Connection = objConn

        Dim strCustomID As String = DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)
        objCommand.CommandText = "Delete From lamesa1 where [CustomerID]= """ & strCustomID & """"

        Customerds.lamesa1.Rows.RemoveAt(DataGrid1.CurrentRowIndex)

        objConn.Open()
        Try
            objCommand.ExecuteNonQuery()
            MessageBox.Show("Record has been Deleted...")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        objConn.Close()
        objCommand.Parameters.Clear()

(Change the mdb-path in the source-statement)
regards
murer

 

[ranz01]

i tried the one that you've game me and it gave me this error..
it so hard to programm a datagrid.. because of my professor did not teach us these datagrid things..

 

[murer]

Hi

According to your printsrcreen in the previous posting, you have a statement "objCommand.ExecuteNonQuery()" before you open the connection with "objConn.Open()". So therefore I'm not shure you using the same code as I do.
Could you set a breakpoint (f.e. at line "objCommand.Connection = objConn") before you start the application and run the programm in debug-mode? line per line (F11) and see at which line you get this error. then please pick a printscreen.

regards
murer

 

[ranz01]

sir... thank you so much... i copied you objCommand.Connection = objConn and it works now.. can you please help me with the view button... i cant make it work.. i like it to display the customer form again and have it filled with the customer information that i've selected in the datagrid.. thanks again so much sir

regards
ranz

 

[murer]

can't make it work

Hi

what is the problem ?? Please tell me what you can't get work?? I'v found no code under your view-button

regards
murer

 

[ranz01]

here's the code i last used with listbox.. i cant make it work in datagrid..

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        OrderNumber = Val(Me.TextBox1.Text)
        Customer = Me.TextBox2.Text
        Address = Me.TextBox3.Text
        ContactNumber = Me.TextBox4.Text
        OrderType = Me.TextBox5.Text
        Qty = Val(IIf(Me.txtQty.Text = "", 0, CInt(Val(txtQty.Text))))
        Unit = LocalUnit
        Dim frm As New WindowsApplication12.Form2
        frm.Show()

    End Sub

i wanted it to display the records of the customer or the order..

here's my first program that i used the listbox..

hope that you could again help me to make the listbox view button work in my datagrid view button.. thanks alot again sir.. thank you so much..

regards
ranz

 

[ranz01]

can any one help me pls..

 

[murer]

Help in programming

so then .... I will help if you permit first a few remarks to your coding.
Usually for each form you get a Class.
f.e. You create a form called "Customer" you will also get a "Public Class Customer". If you later rename the form, the Class name remains the origin name. So if you create in the windows designer a form, you should name the form correctly and also rename the class to the same name as the form. Otherwise you (or me and others) get very confused. In your project for example you have a form called Customer, header text is "customer Form" and the according class is "CustomerForm". Unfortunately you have also a form called CustomerForm and class of that is called "Form3". This makes code reading very diffcult. I name the forms as follows: frmCustomer oder frmCustomerEdit and the class has the same name.
Next is the naming of objects in forms. Do not use the default naming of windows designer, f.e. textbox1, textbox2 and so on. This is ok for very short test-programms containing in maximum two or three objects. Please use names like txtCustomerID, txtFirstName etc. I prefer the naming of a textbox with the prevalue "txt" (for textbox) and the corresponding fieldname in the Database. In this way I have a chance to remember the object naming and know which value from the database is displayed in that textbox. I'm shure your talked about at your school....

So then let's do some carefully corrections:
- Form CustomerForm and the according class is named "CustomerForm"
- Form Customer and the according class is named "Customer"
- Add a module call mdlInitialize.mdl. This will be used for declaring some very public variables where are available over all the classes
-Place the Statement "Public strCustomerIdView As String" to the mdlInitialize
- I let your textboxes named as they are

the Button VIEW in then Customer-Form gets the following:

    Private Sub btnview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnview.Click

        'First you have to save the CustomID in a public variable, so the CustomerForm
        ' knows which Customer it has to show
        'therefore you should define a Public CustomerID: Public strCustomerIdView as string
        strCustomerIdView = DataGrid1.Item(DataGrid1.CurrentRowIndex, 0)

        'declare the CustomerForm
        Dim frmCustomerView As New CustomerForm

        'Show the customerForm
        frmCustomerView.Show()



    End Sub

where the CusomerForm Load-Events is as followed:

    Private Sub CustomerForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim objConn As New OleDb.OleDbConnection
        MyConn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\temp\ice management\ice management\ranz.mdb")
        objConn = MyConn

        objConn.Open()


        Dim strSQL As String = "Select * From lamesa1 where [CustomerID]= """ & strCustomerIdView & """"
        Dim da1 As New OleDbDataAdapter(strSQL, objConn)
        Dim dtCustom As New DataTable

        da1.SelectCommand.CommandText = strSQL

        'Fill Datatable
        Try
            da1.Fill(dtCustom)
        Catch ex As Exception
            MsgBox("ERROR: Could not fill datatable" & vbCrLf & ex.ToString)
        End Try


        Select Case dtCustom.Rows.Count
            Case 0
                'should not occure because a Customer is shown in the previous form
                MsgBox("No Records for Customer found")
            Case 1

                Dim arow As DataRow = dtCustom.Rows(0)
                TextBox1.Text = arow.Item("CustomerID")
                TextBox2.Text = arow.Item("FirstName")
                TextBox3.Text = arow.Item("LastName")
                TextBox4.Text = arow.Item("Address")
                TextBox5.Text = arow.Item("City")
                TextBox6.Text = arow.Item("Phone")
                TextBox7.Text = arow.Item("Email")
            Case Else
                'should not occure, becaus eonly one customerID should be in the database
                'BUT: In your Database CustomerID is duplikated
                MsgBox("Duplikates for CustomerID = " & strCustomerIdView)
        End Select




    End Sub

Anything eles ???
Regards
murer