Solved

Problems for Updating database through Datagrid update for Win App. ......urgent, please help!

Posted on 2004-09-21
8
143 Views
Last Modified: 2010-04-23
Hi, all,

I only found one way to make the update successful that is having the primary key field showing in the datagrid and using VB.net's build-in function to make data adapter, dataset and connection. This way I can use the method: OleDbDataAdapter1.Update(Ds1) to do all the insert, delete and update.

However, the records I need to display in the datagrid have to be based on a variable passed from the other form, say strScenarioName. So I have to mannuly write the SQL select statement cause the dataadapter wizard wouldn't recognized the variable. But using this way, the Da.Update(Ds) method won't work anymore. Do you think i need to hand write Update statement? Here is the code for loading and updating the datagrid.  Thanks

    Private da As OleDb.OleDbDataAdapter  'define dataAdapter's name. dataAdapter copies data from db to dataset
    Private ds As DataSet 'define dataset name

    Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            da.Update(ds, "Vendor")
            MsgBox("Succeeded!")
        Catch ex As Exception
            MsgBox("nothing")
        End Try

    End Sub
    Private Const strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data    
                                                         Source=C:\SCOPT\GPLogiModData-0815.mdb;"

    Private Sub Load1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Load1.Click
        Dim strSQL As String = "SELECT * FROM TM_Vendor WHERE TM_Vendor.ScenarioName = '" & x & "'"

        da = New OleDb.OleDbDataAdapter(strSQL, strConn)  
        ds = New DataSet  

        ds.Clear()
        da.Fill(ds, "Vendor")

        DataGrid1.DataSource = ds.Tables("Vendor")
        DataGrid1.SetDataBinding(ds, "Vendor")
    End Sub
0
Comment
Question by:kate_y
  • 5
  • 3
8 Comments
 
LVL 4

Expert Comment

by:gdexter
ID: 12114731
If you do not have a huge amount of data in the table you could use a Dataview to accomplish this.

'Class scope
Dim myDv As New System.Data.DataView


Dim strSQL As String = "SELECT * FROM TM_Vendor"

da = New OleDb.OleDbDataAdapter(strSQL, strConn)  
ds = New DataSet  

ds.Clear()
da.Fill(ds, "Vendor")

 Me.myDv.Table = ds.Tables("Vendor")
 Me.myDv.RowFilter =  String.Format("ScenarioName ={0}", x)
 Me.DataGrid1.DataSource = Me.myDv


You could also manually modify the Update and Insert Command Builder objects

In addtion you may need a CurrencyManager object for the DataView
0
 

Author Comment

by:kate_y
ID: 12115478
Hmm, actually the datagrid display part worked fine when manually make the SELECT statement like the follows.
Private Sub Load1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Load1.Click
        Dim strSQL As String = "SELECT * FROM TM_Vendor WHERE TM_Vendor.ScenarioName = '" & x & "'"
        da = New OleDb.OleDbDataAdapter(strSQL, strConn)  
        ds = New DataSet  
        ds.Clear()
        da.Fill(ds, "Vendor")
        DataGrid1.DataSource = ds.Tables("Vendor")
        DataGrid1.SetDataBinding(ds, "Vendor")
    End Sub

This update part didn't work.
Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            da.Update(ds, "Vendor")
            MsgBox("Succeeded!")
        Catch ex As Exception
            MsgBox("nothing")
        End Try

Do I have to create a update statement and assign it to da? I know the syntax for the regular update statement but how to assign value to the fields from datagrid?
0
 
LVL 4

Expert Comment

by:gdexter
ID: 12115643
I think this what you need

Dim cmdBuilder as New OleDbCommandBuilder


                 da.FillSchema(ds, SchemaType.Source, "Vendor")
                 da.Fill(ds, "Vendor")

                'configure the command builder

                cmdBuilder.DataAdapter = da
                cmdBuilder.QuotePrefix = "["
                cmdBuilder.QuoteSuffix = "]"

                da.UpdateCommand = cmdBuilder.GetUpdateCommand
                da.DeleteCommand = cmdBuilder.GetDeleteCommand
                da.InsertCommand = cmdBuilder.GetInsertCommand

                DataGrid1.DataSource = ds.Tables("Vendor")
                DataGrid1.SetDataBinding(ds, "Vendor")



0
 

Author Comment

by:kate_y
ID: 12116426
Sorry, but is this for load button or update button? tks.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:gdexter
ID: 12116681
You still need to give the Adapter a reference to the CmdBuilder to do an update it has nothing to do with the event it fires on.
0
 
LVL 4

Expert Comment

by:gdexter
ID: 12116764
Use that is the Load Button routine
0
 

Author Comment

by:kate_y
ID: 12143780
Sorry for the late response. So do I have to write a Update statement as the reference to the CmdBuilder? If so, I have problem with the Update statement's syntax. Could you give me a example on how to set the value of the datagrid to the table fields? BTW, I know the basic syntax (UPDATE table SET column = something).

thanks
0
 
LVL 4

Accepted Solution

by:
gdexter earned 500 total points
ID: 12144120
You should be able to use the Update statement that is auto-generated by the CmdBuilder object.

You may want to remove the DataAdapter that you created with the wizard and just create a new instance of the Adapter in code. Then use the CmdBuilder code that I posted before and see if the update will work.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

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

12 Experts available now in Live!

Get 1:1 Help Now