Solved

Update problem, VB.NEt windows application

Posted on 2004-04-21
15
190 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

10 Experts available now in Live!

Get 1:1 Help Now