none
Help changing this function into vb.net?!?! RRS feed

  • Question

  • I am trying to convert VBA code in VB.net and I have made it to a point but I can't convert resultset into vb.net. RS was dim as resultset in VBA thought i could just change it to dataset but am getting errors?

    Function GetG(sDB As String, sServ As String, sJob As String) As String

            'sDB = Database name, sServ = Server\Instance, path = job.path
            Dim conString As String = ("driver={SQL Server};server = " & TextBox1.Text & " ; uid = username;pwd=password:database = " & TextBox2.Text)
            Dim RS As DataSet
            Dim conn As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand


            conn.Open()

            cmd = New SqlCommand("SELECT [ID],[Name] FROM dbo.PropertyTypes")
            Do While Not RS.Tables(0).Rows.Count = 0
                If RS.Fields(1).Value = sJob Then
                    GetG = RS.Fields(0).Value
                    GetG = Mid(GetG, 2, 36)
                    Exit Do
                End If

                DataSet.MoveNext
            Loop
            conn.Close

        End Function
    Wednesday, April 3, 2019 3:21 PM

All replies

  • Which line has the error and what errors are you getting?

    Search Documentation

    SerialPort Info

    Multics - An OS ahead of its time.

     "Those who use Application.DoEvents have no idea what it does

        and those who know what it does never use it."    former MSDN User JohnWein

    Wednesday, April 3, 2019 3:31 PM
  • everything in the do loop where it starts with RS such as '.Fields' and '.tables' as this was taken from vba it seems vb.net doesn't like it?
    Wednesday, April 3, 2019 3:46 PM
  • For VB.NET use the following pattern, note I did not use your function as I simply wanted you to get the concept.

    Code compiled with Option Strict On, Option Infer On

    Dim sJob As String = "TODO"
    Dim GetG = ""
    Dim ConnectionString As String = "TODO"
    
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
    
        Dim selectStatement = "SELECT [ID],[Name] FROM dbo.PropertyTypes"
    
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
    
            cn.Open()
    
            Dim reader = cmd.ExecuteReader()
            ' did we get data back?
            If reader.HasRows Then
                ' iterate rows
                While reader.Read()
                    ' look for specific value
                    If reader.GetString(1) = sJob Then
                        GetG = reader.GetInt32(0).ToString()
                        GetG = Mid(GetG, 2, 36)
                        Exit While
                    End If
                End While
            End If
    
        End Using
    End Using


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, April 3, 2019 3:55 PM
    Moderator
  • Hi,

    replace the code below:

    vba

      cmd = New SqlCommand("SELECT [ID],[Name] FROM dbo.PropertyTypes")
            Do While Not RS.Tables(0).Rows.Count = 0
                If RS.Fields(1).Value = sJob Then
                    GetG = RS.Fields(0).Value
                    GetG = Mid(GetG, 2, 36)
                    Exit Do
                End If
    
                DataSet.MoveNext
            Loop

    vb.net

     Dim dt As New DataTable
            Dim sda As SqlDataAdapter = New SqlDataAdapter("SELECT [ID],[Name] FROM dbo.PropertyTypes", conn)
            sda.Fill(dt)
            For i = 0 To dt.Rows.Count - 1
                If dt.Rows(i)(1).ToString = sJob Then
                    GetG = dt.Rows(i)(0).ToString
                    GetG = Mid(GetG, 2, 36)
                    Exit For
                End If
            Next

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 4, 2019 2:28 AM
  • everything in the do loop where it starts with RS such as '.Fields' and '.tables' as this was taken from vba it seems vb.net doesn't like it?

    With RS you mean probably RecordSet (and not resultset which is something different). 

    With .Net Microsoft also changed the way databases are used in programs. The way to use database changes were huge. One of them was that a DataTable (the most looking to a RecordSet) is never direct updated (disconnected access) and a RecordSet (connected access) is direct if you set a field. 

    If you really want to convert your VBA database code, than you can set a reference to DOA and use your code mostly as in VBA. However, don't expect any help. I don't know about Dbasnett, but the others gave replies showing they did really not understand your problem.

    The one who knows about it is not anymore active in this forum. 

    What to do then, yea most will tell you to go the 2002 direction of the DataSet. But I think you better can create a new thread and describe your problem without any code.  


    Success
    Cor


    Thursday, April 4, 2019 5:30 PM