Solved

Update problem, VB.NEt windows application

Posted on 2004-04-21
15
191 Views
Last Modified: 2010-04-24
Hi,

Here is my problem: I am writing a windows application using VB.NET

On my form I have an unbound combo box which pulls only the EmployeeID from my Table (eg. tblEmp), then on my data set I pass that parameter to pull all the information from that table, it works fine, each time I select an EmpID it pulls all the records from that table, but i can't update it.

If I make any changes it doesn't upgrade, I checked my datadapter it has an update command already! what to do?

Here is what I did for update button:

        Try
            Me.OleDAd_ViewPermAll.Update(Me.DsViewAllPerm1, "DCC_TBL_EmpIDPassW")
        Catch
            MsgBox("Error Saving file", MsgBoxStyle.Exclamation)
        End Try

Any Help? if you need my update command, let me know please..

thanks

0
Comment
Question by:nibirkhan
15 Comments
 
LVL 5

Expert Comment

by:tgannetts
ID: 10886526
Are you using a CommandBuilder with your data adapter?

This will detect the rowstate of each row in your table and determine the correct Update method and generate the required SQL statement, ie Update, Insert or Delete:

You just have to add the following:

Dim cb as OleDbCommandBuilder

...

Try
     cb = new OleDbCommandBuilder(Me.OleDAd_ViewPermAll)
     Me.OleDAd_ViewPermAll.Update(Me.DsViewAllPerm1, "DCC_TBL_EmpIDPassW")
Catch
     MsgBox("Error Saving file", MsgBoxStyle.Exclamation)
End Try

Tom.
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 10891078
I don't know what is commnad bulder but I configured my data Adapter which has all four method(Select, Delete, Insert and Update),

I tired to use

"Dim cb as OleDbCommandBuilder"   it shows error(un recognized) on my editor.

Can you explain me more?

thanks
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10892791
When using the Update Method of a DataAdapter, all that is really happening is that the rowstate of each row in the datatable specified (or array of datarow objects) is checked to see if any update is required. If it is determined that some change has taken place, then the DataAdapter will attempt to action the relevent command for that row, eg Update etc. All of this is done on a row-by-row basis.

The data adapter does not generate the commands itself. Instead it will attempt to use a SQL command provided for it. You ultimately have two choices:

1. Use the CommandBuilder
2. Create parameterized Commands for each command type, i.e Update, Delete, Insert

If you do not use one of these methods then the Update method will not work.

The CommandBuilder (OleDBCommandBuilder for the OleDbDataAdapter and SQLCommandBuilder if you are using a SQLDataAdapter) is the simpliest of the two methods. It effectively generates the SQL command for each row, so you don't have to create a Command with parameters.

The following is an example of using a CommandBuilder with a SQLDataAdapter:

Dim cn as new SQLConnection(strAConnectionString)
Dim da as new SQLDataAdapter("SELECT * FROM Publishers", cn)
Dim ds as new Dataset()
Dim cb as SQLCommandBuilder

da.Fill(ds, "Publishers")

'Create command Builder
cb =  new SQLCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
da.DeleteCommand = cb.GetDeleteCommand

'Do something to the rows here

'Perform update on Database
da.Update(ds, "Publishers")

Hope this is of some use

Tom
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 10892999
Hi Tom,

I guess I got the logic here, I am using OleDBDataAdapter,

Could you please tell me what to do with my "Save" button click event, I guess I don't have built update command, please walk me through as I am new in VB.NET

thanks
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10893391
You don't really need to add much to the code you already have:

1. Make sure you have included an 'Imports System.Data.OleDb' statement.
2. Add the following declaration code to the Save Button click sub:

     Dim cb as OleDbCommandBuilder

3. Update your Try statement in the Save Button Click sub:

   Try
        cb = new OleDbCommandBuilder(Me.OleDAd_ViewPermAll)
        Me.OleDAd_ViewPermAll.Update(Me.DsViewAllPerm1, "DCC_TBL_EmpIDPassW")
   Catch
        MsgBox("Error Saving file", MsgBoxStyle.Exclamation)
   End Try  

If you are still having problems, then can you show the code you are using for the Save Button click sub.

Tom.
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 11000508
Hi Tom,
Sorry for late reply. Here it is...

'form loads
Private Sub DCC_Admin_ViewPermission_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Fill the Select Employee ComboBox
        Me.DsCmbEmp1.Clear()
        Me.da_selectEmp.Fill(Me.DsCmbEmp1)

        'fill the parameter and the dataset behind the form
        Me.OleDAd_ViewPermAll.SelectCommand.Parameters("Emp_ID").Value = Me.cmbEmpID.Text
        Me.DsViewAllPerm1.Clear()
        Me.OleDAd_ViewPermAll.Fill(Me.DsViewAllPerm1)

    End Sub

'combo box parameter , on index changed it finds relative value    
Private Sub cmbEmpID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbEmpID.SelectedIndexChanged
        'fill the parameter and the dataset behind the form
        Me.OleDAd_ViewPermAll.SelectCommand.Parameters("Emp_ID").Value = Me.cmbEmpID.Text
        Me.DsViewAllPerm1.Clear()
        Me.OleDAd_ViewPermAll.Fill(Me.DsViewAllPerm1)
    End Sub

' Save records with this button below
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Me.OleDAd_ViewPermAll.Update(Me.DsViewAllPerm1)
        Catch
            MessageBox.Show("Error Saving file", MsgBoxStyle.Exclamation)
        End Try
    End Sub
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:tgannetts
ID: 11004218
Try the following:

' Save records with this button below
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     Dim cb as OleDbCommandBuilder    

     Try
          cb = new OleDbCommandBuilder(Me.OleDAd_ViewPermAll)
          Me.OleDAd_ViewPermAll.UpdateCommand = cb.GetUpdateCommand
          Me.OleDAd_ViewPermAll.InsertCommand = cb.GetInsertCommand
          Me.OleDAd_ViewPermAll.DeleteCommand = cb.GetDeleteCommand

          Me.OleDAd_ViewPermAll.Update(Me.DsViewAllPerm1)
     Catch
          MessageBox.Show("Error Saving file", MsgBoxStyle.Exclamation)
     End Try
End Sub

Tom
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 11010191
Hi Tom,

I am getting an error, OleDbCommandBuilder  is not defined even though I used :

Dim cb as OleDbCommandBuilder    

, help me please...

--Khan

0
 
LVL 5

Expert Comment

by:tgannetts
ID: 11012839
Is this error at runtime or designtime?

Tom
0
 
LVL 2

Author Comment

by:nibirkhan
ID: 11016684
design time, the "OleDbCommandBuilder" gets underlined with blue color, and when you take the mouse on it, it gives the error msg.

thanks..

--khan  
0
 
LVL 5

Accepted Solution

by:
tgannetts earned 250 total points
ID: 11021103
The OleDbCommandBuilder class is held in the System.Data.OleDb namespace. Do you have an Imports reference to this namespace at the top of your code? If not, you would either have to add the Imports line (Imports System.Data.OleDb) or use Dim cb as OleDb.OleDbCommandBuilder.

Tom.
0
 
LVL 2

Assisted Solution

by:tobyphase
tobyphase earned 250 total points
ID: 11043791
Khan

I'm not sure if Tom has confused you with the OleDBCommand builder. His is definitely a valid way to access data but is not necessary if, as you say, you have already had your update command generated for you in Visual studio - since that is exactly what it uses itself to generate its code

But Tom also mentions the fact that the update command will only update rows if the rowstate is set to deleted modified or added and this may be where your answer lies. It may not be finding any rows to update - can you add a line in your button handler as below

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

messagebox.show(Me.DsViewAllPerm1.GetChanges.Rows.Count)

            Me.OleDAd_ViewPermAll.Update(Me.DsViewAllPerm1)
        Catch
            MessageBox.Show("Error Saving file", MsgBoxStyle.Exclamation)
        End Try
    End Sub

This will show you how many rows it thinks it has to update before you call the update command. If it shows as 0 it reckons there are no rows to update so the problem does not lie with your dataadapter or update command at all

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 11094369
Are you still having a problem with this?

Bob
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now