none
Unable to write to access db in VB.Net 2008 (migrating from VB6) RRS feed

  • Question

  • Hi all,

    I am in the process of migrating from VB6 to VB.Net 2008 but there are so many ways to do this, maybe you can point out the best way to do this without using the built-in controls. In other words I want to do the below by using "RECORD-SETS/DATA-SETS" via string code.

    I can connect and load the name/text fields from the database using data readers, but editing and/or adding new rows to an existing table has me confused.

    I want to update the recordset to database rs.update, rs.movenext equivalents.

    Thanks in advance


        Dim iCntr As Integer
        Dim sTmp As String
        '------------------------------
        Set rS = New Recordset
        rS.Open "SELECT Count(*) From Navigation Where PageNum = " & Val(txtCurPage.Text), dbConnStrg
        sTmp = rS.Fields(0)
        rS.Close
        Set rS = Nothing
       
        Set rS = New Recordset
       
        If sTmp = 1 Then
            rS.Open "Select * From Navigation Where PageNum = " & Val(txtCurPage.Text), dbConnStrg, _
                adOpenStatic, adLockOptimistic
                rS.Fields.Item(0).Value = txtCurPage.Text
                rS.Fields.Item(1).Value = txtYes.Text
                rS.Fields.Item(2).Value = txtNo.Text
                rS.Fields.Item(3).Value = txtCont.Text
                rS.Fields.Item(4).Value = txtHow.Text
                rS.Fields.Item(5).Value = txtWhy.Text
                rS.Fields.Item(6).Value = txtParts.Text
                rS.Fields.Item(7).Value = txtFig.Text
        Else
            rS.Open "Navigation", dbConnStrg, adOpenStatic, adLockOptimistic
            rS.AddNew
            rS.Fields.Item(0) = txtNode.Text
                rS.Fields.Item(0).Value = txtCurPage.Text
                rS.Fields.Item(1).Value = txtYes.Text
                rS.Fields.Item(2).Value = txtNo.Text
                rS.Fields.Item(3).Value = txtCont.Text
                rS.Fields.Item(4).Value = txtHow.Text
                rS.Fields.Item(5).Value = txtWhy.Text
                rS.Fields.Item(6).Value = txtParts.Text
                rS.Fields.Item(7).Value = txtFig.Text
        End If
       
        rS.Update
       
        rS.Close
        Set rS = Nothing
       
        '------------------------------
       
                    Set rS = New Recordset
                    rS.Open "Select * From rdoBttns Where PageNum = " & _
                      Val(txtCurPage.Text) & " ORDER BY rdoIndex", _
                      mConn, adOpenDynamic, adLockPessimistic
                     
                    rS.MoveFirst
                    Do While Not rS.EOF
                        iCntr = rS.Fields(1)
                        rS.Fields.Item(2) = optBtn(iCntr).Caption
                        rS.Fields.Item(3) = optBtn(iCntr).Tag
                        rS.Update
                        rS.MoveNext
                    Loop
                   
                    rS.UpdateBatch
                   
                    rS.Close
                    Set rS = Nothing

    Saturday, March 10, 2012 12:25 AM

Answers

  • It's not that I'm dense, but I have a strange learning disability. Once I see a working example of what I need, the flood gates open??? It's so weird, and sometimes I amaze myself???

    With VB6 and ADO a coworker showed me an example, and then days later I was doing such advance routines that he accused me of deceiving him and was angry with me. It took me a few days to convince him that I was being honest.

    rS.Open "Select * From Navigation Where PageNum = " & _
                                               Val(txtCurPage.Text), dbConnStrg, _
                                               adOpenStatic, adLockOptimistic
                rS.Fields.Item(0).Value = txtCurPage.Text
                rS.Fields.Item(1).Value = txtYes.Text
                rS.Fields.Item(2).Value = txtNo.Text
                rS.Fields.Item(3).Value = txtCont.Text
                rS.Fields.Item(4).Value = txtHow.Text
                rS.Fields.Item(5).Value = txtWhy.Text
                rS.Fields.Item(6).Value = txtParts.Text
                rS.Fields.Item(7).Value = txtFig.Text
     
                rS.Update

                rS.Close
                Set rS = Nothing

    BTW, I am currently reading the links and will do the example project


    • Edited by JCorona Tuesday, March 13, 2012 10:54 PM
    • Marked as answer by JCorona Wednesday, March 14, 2012 10:16 PM
    Tuesday, March 13, 2012 10:53 PM

All replies

  • In ADO you scan sequentially through the rows of the recordset using the ADO MoveNext method. In ADO.NET, rows are represented as collections, so you can loop through a Ado.net DataTable as you would through any collection, or access particular rows via ordinal or primary key index.

    One thing ADO.NET does not have an inherent cursor object, but instead includes data classes that provide the functionality of a traditional cursor. For example, the functionality of a forward-only, read-only cursor is available in the ADO.NET DataReader object.

    Below link may help you to understand how you can insert/update/delete using ado.net Adopter.

    http://msdn.microsoft.com/en-us/library/33y2221y.aspx

    Migrating Code and Concepts from ADO "Classic" to ADO.NET is here.

    http://msdn.microsoft.com/en-us/library/ms971478.aspx


    Lingaraj Mishra

    Saturday, March 10, 2012 4:33 AM
  • See if the below tutorial helps. It implements ADO.NET:

    http://www.homeandlearn.co.uk/NET/nets12p1.html


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 13, 2012 3:33 PM
  • It's not that I'm dense, but I have a strange learning disability. Once I see a working example of what I need, the flood gates open??? It's so weird, and sometimes I amaze myself???

    With VB6 and ADO a coworker showed me an example, and then days later I was doing such advance routines that he accused me of deceiving him and was angry with me. It took me a few days to convince him that I was being honest.

    rS.Open "Select * From Navigation Where PageNum = " & _
                                               Val(txtCurPage.Text), dbConnStrg, _
                                               adOpenStatic, adLockOptimistic
                rS.Fields.Item(0).Value = txtCurPage.Text
                rS.Fields.Item(1).Value = txtYes.Text
                rS.Fields.Item(2).Value = txtNo.Text
                rS.Fields.Item(3).Value = txtCont.Text
                rS.Fields.Item(4).Value = txtHow.Text
                rS.Fields.Item(5).Value = txtWhy.Text
                rS.Fields.Item(6).Value = txtParts.Text
                rS.Fields.Item(7).Value = txtFig.Text
     
                rS.Update

                rS.Close
                Set rS = Nothing

    BTW, I am currently reading the links and will do the example project


    • Edited by JCorona Tuesday, March 13, 2012 10:54 PM
    • Marked as answer by JCorona Wednesday, March 14, 2012 10:16 PM
    Tuesday, March 13, 2012 10:53 PM
  • Hi JCorona,

    Welcome to MSDN Forum.

    I'm glad to hear that you have solved the issue, and thanks for your sharing.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 15, 2012 1:54 AM
    Moderator