calling a VB.net Function for SQL recordset

Question

Beginner here

I have the following code which I would like to call using a button called findCustomerBTN

   Public Function Execute(ByVal sqlQuery As String) As ADODB.Recordset

    If SecuritySSPIchkbx.Checked Then
        chk = "TRUE"
    Else chk = "FALSE"
    End If
    builder.DataSource = ServerBox.Text
    builder.InitialCatalog = DatabaseBox.Text
    builder.UserID = Username.Text
    builder.Password = Password.Text
    builder.IntegratedSecurity = chk
    MessageBox.Show(builder.ConnectionString)

    Using sqlConnection1 As New SqlConnection(builder.ConnectionString)

        sqlConnection1.Open()
        Try
            command = New SqlCommand(sqlQuery, sqlConnection1)
            adapter.SelectCommand = command
            adapter.Fill(ds, "Create DataView")
            adapter.Dispose()
            command.Dispose()
            sqlConnection1.Close()

            dv = ds.Tables(0).DefaultView
            DataGridView1.DataSource = dv



        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Using

End Function

How do I call this function:

     Private Sub findCustomerBTN_Click(sender As Object, e As EventArgs) Handles
sqlquery = "Select * from customers where name = 'Smith'"
call function?
    End Sub

I have googled but I can't wrap my head around how a function works any pointers to help me understand would be great thanks

Answer 1

In VB, a function is a block of code that returns a value. Your code does not return a value, and the type of query execution you're carrying out will never return an ADODB.RecordSet - that's an ancient technology remeniscent of the VB6 era, and you're using a much more modern data access strategy (ADO.NET, DataTables and DataAdapter) though it's not the latest and greatest.

To offer a run through of your code, and the other issues it has:

• Execute is a pretty bland name - go for something more specific, just incase you run the wrong execute and some poor prisoner ends up in front of the firing squad
• Your function takes an sql string as a parameter to run, but then overwrites it with a fixed string, so there isn't much point offering it as a parameter in the first place. I could call Execute("SELECT * FROM kittens") expecting to get some cute data back, and all I get is the same old customer
• Avoid calling MessageBox.Show in any code that shoudl reasonably be expected to run quietly and repeatedly, otherwise the user is going to get hella annoyed. If youre putting this here for debugging purposes, learn how the visual studio debugger works instead
• Your code runs an sql query and assigns the resulting data table data to the datasource of a grid, so that the grid will show the data. There's absolutely no need for this code to be a function (and in c# it wouldnt even compile because it doesn't return a value

What are functions? What do they do? They take some inuts and return some outputs:

Public Function AddTheseTwo(a as Integer, b as Integer) As Integer
  Return a + b
End Function

They are called like this:

Dim sum = AddTheseTwo(2, 3)

I.e. you give the name of the function and the input values, which can be variables, and store the result (usually, because you want to use it). Here's a code of yours that is a sub - a block of code that doesn't return a value

Private Sub findCustomerBTN_Click(sender As Object, e As EventArgs) Handles
    Execute("Select * from customers where name = 'Smith'")
End Sub

• It's not linked to your button click, because there's nothing afte rthe Handles keyword. It should be something like Handles findCustomerBTN.Click. You can mash that button all day and nothing will happen
• It called Execute but didn't store the return value (because it didn't need to, because Execute doesn't return anything, so Execute should have been declared as a sub, not a function)

Edit:

You mentioned you want the function to return a datatable:

Public Function GetDataTable(ByVal sqlQuery As String) As DataTable 'need to Imports System.Data if you haven't already

    If SecuritySSPIchkbx.Checked Then
        chk = "TRUE"
    Else chk = "FALSE"
    End If
    'better to declare builder in this function, not elsewhere
    builder.DataSource = ServerBox.Text
    builder.InitialCatalog = DatabaseBox.Text
    builder.UserID = Username.Text
    builder.Password = Password.Text
    builder.IntegratedSecurity = chk
    MessageBox.Show(builder.ConnectionString)

    Using sqlConnection1 As New SqlConnection(builder.ConnectionString)

        sqlConnection1.Open()
        Try
            'note: better to declare adapter and command in this function too
            command = New SqlCommand(sqlQuery, sqlConnection1)
            adapter.SelectCommand = command
            Dim dt as New DataTable
            adapter.Fill(dt)
            adapter.Dispose()
            command.Dispose()
            sqlConnection1.Close()

            Return dt

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Using
  Return Nothing 'a function has to return something from all possible code paths, even if it's Nothing :)
End Function

And then you call it like this, perhaps:

Private Sub findCustomerBTN_Click(sender As Object, e As EventArgs) Handles whateverbutton.Click
    'you can set a datatable as a datasource, doesn't have to be the datatable.defaultview
    myDataGRidView.DataSource = GetDataTable("Select * from customers where name = 'Smith'")
End Sub

I recommend you turn on the options for Strict/Explicit etc, to encourage better coding practices. By default VB is quite loose, letting you use variables that havent been declared (autodeclaring variable names that are a typo of another variable name etc), automatically returning Nothing for you from functions etc - it's these little auto's that will later lead to bugs and frsutrations. Computer programming si a precise art; turn on all options to force yourself to be as precise as possible

Answer 2

You can call Execute function using this code:

Private Sub findCustomerBTN_Click(sender As Object, e As EventArgs) Handles
    Execute("Select * from customers where name = 'Smith'")
End Sub

You also have to remove this line from Execute function:

sqlQuery = ("select * from ac_billbook where ref = '900123'")

Please follow Steve suggestion to read a good book about programming in VB.NET.