CODEWITHSUNDEEP
sql.netvbavb.netbasic
OmegaChase
OmegaChase

Reputation: 3

Converting VBA function to VB.net to get sql data

I am trying to convert VBA code into 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 with the '.fields' and other options?

 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()

'This is where my problems are occuring

        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

Upvotes: 0

Views: 273

Answers (2)

Charles May
Charles May

Reputation: 1763

Based on my understanding and some guesswork, here is what I came up with for what I think you're wanting.

As I stated in my comment above, it appears you can just use a WHERE clause to get the exact record you want (assuming a single instance of sJob appears in the name column).

Build the connectionstring off the input arguments, not controls on your form. That is after all why you allow for arguments to be passed along. Also note that there is a SqlCommandBuilder object that may be of interest. But for now

Function GetG(sDB As String, sServ As String, sJob As String) As String
    'we'll pretend your connectionstring is correct based off of the sDB and sServ arguments
    Dim conStr As String = ("driver={SQL Server};server = " & sServ & " ; uid = username;pwd=password:database = " & sDB)
    'Create a connection and pass it your conStr
    Using con As New SqlConnection(conStr)
        con.Open() 'open the connection
        'create your sql statement and add the WHERE clause with a parameter for the input argument 'sJob'
        Dim sql As String = "SELECT [ID], [Name] FROM dbo.PropertyTypes WHERE [Name] = @job"
        'create the sqlCommand (cmd) and pass it your sql statement and connection
        Using cmd As New SqlCommand(sql, con)
            'add a parameter so the command knows what @job holds
            cmd.Parameters.Add(New SqlParameter("@job", SqlDbType.VarChar)).Value = sJob
            'Now that have the command built, we can pass it to a reader object 
            Using rdr As SqlDataReader = cmd.ExecuteReader
                rdr.Read()
                'i admin i'm a little confused here on what you are
                'trying to achieve so ID may not be what you are
                'really wanting to get a substring of.
                Return rdr("ID").ToString.Substring(2, 36)
            End Using
        End Using
    End Using
End Function

An example to see if this is working could be to call a messagebox do display the result. For this example, I'm going to pretend that TextBox3 holds the sJob you're wanting. With that knowledge, you could simply do:

MessageBox.Show(GetG(TextBox2.Text, TextBox1.Text, TextBox3.Text))

This should then produce the result in a messagebox.

Upvotes: 1

sergidb
sergidb

Reputation: 25

It seems that you're not filling your DataSet. So, when you try to loop through it, it's uninitialized or empty.

Check this answer to see an example: Get Dataset from DataBase

Upvotes: 0

Related Questions