Every VB form need its own connection? 2

[stillwillyboy]

When using VB 2005 and a SQL 2005 DB, does every VB form need its own connection to the DB or if one form in the project is connected, are all forms automatically connected through the one form?

TIA

Bill

 

[TipGiver]

#1. You should public declare your connection in order to be visible to other classes(forms).

or

#2. Declare private and pass the appropriate info from a class to another, through constructor or any property.

 

[stillwillyboy]

I've declared the Class as Public and the Sub as Private as follows:

Public Class frmConnectToSQLTest
    Private m_cnADONetConnection As New System.Data.SqlClient.SqlConnection()
    Private m_daDataAdapter As SqlClient.SqlDataAdapter
    Private m_cbCommandBuilder As SqlClient.SqlCommandBuilder
    Private m_dtClients As New DataTable
    Private m_rowPosition As Integer = 0

    Private Sub frmConnectToSQLTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        m_cnADONetConnection.ConnectionString = _
    "Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator"

        m_cnADONetConnection.Open()

The above code is on a start up form (FormA). Below the above code is currently some SQL to reach the DB. I want to move the SQL code to the other various forms.

How do I get FormB, FormC, etc. to access the underlying DB through the above code?

TIA.

Bill

 

[chrissie1]

Actually, you should use a Datalayer(DAL°. By DAL we mean a dll or a collection of classes that do nothing more then fill datatable or bussinessobjects and return them to your code. This way you will have less redundant code. If anybody is interested in learning more about this technique then you should read the books by Rockford Lothka. Or you can download his framework, not that it will make much sense without the book. But he layers his code the way it should be (nearly). His framework is even Service Oriented.

Christiaan Baes
Belgium

"My new site" - Me

 

[TipGiver]

chrissie1,

DAL- that exactly i meant by my 'public declare'. This is what i do in a single DLL. The propertied/methods/functions are even shared so can be referenced directly without any init statement.

 

[stillwillyboy]

Thanks for the help, but both answers are over my head at this point. I'm just getting into SQL and VB.

I'll keep working at it though.

Bill

 

[Dashley]

Bill,

If you'll create a module in you project that looks like this (I named it getdata).

Imports System.Data.SqlClient

Module GetData

    Public Function get_data(ByVal SqlGetData As String) As String
        Dim Conn As SqlConnection = New SqlConnection("your connection here")
        Dim cmd2 As New SqlCommand
        Dim senddataback As String

        If Conn.State = ConnectionState.Closed Then Conn.Open()
        With cmd2
            .Connection = Conn
            .CommandType = CommandType.Text
            .CommandText = (SqlGetData)
        End With

        senddataback = cmd2.ExecuteScalar

        Conn.Dispose()
        cmd2.Dispose()

        Return senddataback


    End Function

End Module

You can call this function from any page and have it return data. I tested this with a button clik event. Here it is below.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyStr As String
        Dim MyData As String


        MyStr = ("select lastname from users where userid=bla bla bal")

        MyData = get_data(MyStr)

    End Sub

You can add a form2 using the same event and test that form also.

This can be modified to pass a dataset back.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyStr As String
        Dim last As String
        Dim First As String

        Dim MyDS As DataSet = New DataSet("Giveitaname")

        MyStr = ("select firstname, lastname from users where userid=2222")

        MyDS = get_data(MyStr)

        First = MyDS.Tables("table1").Rows(0).Item(0)

        last = MyDS.Tables("table1").Rows(0).Item(1)

        MsgBox(First & " " & last)
    End Sub

'/// module

   Public Function get_data(ByVal SqlGetData As String) As DataSet
        Dim Conn As SqlConnection = New SqlConnection("your connection here")

        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)


        Dim MYds As New DataSet

        DA1.Fill(MYds, "table1")

        Return MYds


    End Function

The datalayer is the ultimate way to go as Christiaan suggested. To help you visualize a datalayer - If you took the function I have above and made it a completly seperate application it could be viewed as a (very basic) datalayer. Your app would talk to it. It would go get the data and return it to you app. You app would never actually touch the data at the database level. This is a good thing for security and also if you have multiple apps that need data. You wouldnt have to recode each app everytime you needed differen data etc..
Google n-tier Architecture, 3 tier Architecture or multi tier Architecture if you want to research it.


Hope this helps

Dan

 

[ThatRickGuy]

I our data layer we have a pile of classes. Each class represents an "object" in the data base (a customer, a lease, an asset, an invoice, an employee, etc) or a more complexe assembly of objects (all of the information associated with collection information, or payment information, etc). Each of these classes then have a child class in the business object layer. This is where we add business logic to each object. For instance, out Lease business object inherits the lease data object, but it adds functionality like BuildPaymentSchedule and GetBillingAddress.

We also have a series of classes and functions that handle all of the searching, sorting, inserting, updating and deleting for us. So we can do things like:

dim objLease as new (basenamespace).(do/boLibrary).BusinessObjects.Lease()
objLease.Find("LeaseNumber", SQLOperator.equals, "123456")
objLease.Description = "This is Jimmy's lease!"
objLease.save
objLease = nothing

It's way overkill for a small project, but our libraries cross over numerous apps (both internal and 3rd party). The ease of understanding allowed us to spin up a new employee (with no .Net experience) in a mater of days, as opposed to weeks-months.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[stillwillyboy]

Dan,

When I try to run the code, I get the message that “‘Type SqlConnection’ is not defined” and
“‘Type SqlDataAdapter’ is not defined”. Do I have the code placed in the right places?

‘Created a new module and named it GetData
Imports System.Data.SqlClient
Module GetData

    Public Function get_data(ByVal SqlGetData As String) As String
        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim cmd2 As New SqlCommand
        Dim senddataback As String

        If Conn.State = ConnectionState.Closed Then Conn.Open()
        With cmd2
            .Connection = Conn
            .CommandType = CommandType.Text
            .CommandText = (SqlGetData)
        End With

        senddataback = cmd2.ExecuteScalar

        Conn.Dispose()
        cmd2.Dispose()

        Return senddataback
    End Function

End Module
-----------
‘Created From1. Has the following code:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyStr As String
        Dim MyData As String

        MyStr = ("select lastname from users where userid=1000")
        MyData = get_data(MyStr)
    End Sub
End Class
----------------
‘Created Form2. Has the following code:
Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyStr As String
        Dim last As String
        Dim First As String

        Dim MyDS As DataSet = New DataSet("GiveItaName")

        MyStr = ("select firstname, lastname from users where userid=1000")
        MyDS = get_data(MyStr)

        First = MyDS.Tables("table1").Rows(0).Item(0)
        last = MyDS.Tables("table1").Rows(0).Item(1)

        MsgBox(First & " " & last)
    End Sub

    Public Function get_data(ByVal SqlGetData As String) As DataSet
‘error pointing to line below:
        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")

‘error pointing to line below:
        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)

        Dim MYds As New DataSet

        DA1.Fill(MYds, "table1")
        Return MYds
    End Function

Thanks.

 

[ThatRickGuy]

Make sure you have a reference to System.Data

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[stillwillyboy]

I’ve adjusted my code to try and reference a “real” DB with “real” information. I have checked the DB name, the table names and the field names. I have checked the column references (1) and (2) for each table. 1 & 2 are the LastName and FirstName respectively in the table “tbl_ClntBasicInfo” and Average and Fringe respectively in “tbl_ClntPayRates”. Column (0) is the client number, in this code = 1000. I have verified that there is a record with the client number 1000.

This is my new code. When I click the button on Form1, I keep getting the error message “Object reference not set to an instance of an object.” At the line “FirstName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(1)” . If I am having a glitch with Form1, I’m guessing I’ll have the same with Form2.

‘GetData module
Imports System.Data
Imports System.Data.SqlClient
Module GetData

    Public Function get_data(ByVal SqlGetData As String) As String
        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim cmd2 As New SqlCommand
        Dim senddataback As String

        If Conn.State = ConnectionState.Closed Then Conn.Open()
        With cmd2
            .Connection = Conn
            .CommandType = CommandType.Text
            .CommandText = (SqlGetData)
        End With

        senddataback = cmd2.ExecuteScalar

        Conn.Dispose()
        cmd2.Dispose()

        Return senddataback
    End Function
End Module

‘Form1 code
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyStrName As String
        Dim MyData As String
        Dim LastName As String
        Dim FirstName As String
        Dim MyDSName As DataSet = New DataSet("Last and First Name")

        MyStrName = ("select LastName, FirstName from tbl_ClntBasicInfo where ClientNumber=1000")
        MyData = get_data(MyStrName)

        FirstName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(1)
        LastName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(2)

    End Sub
End Class

‘Form2 code
Imports System.Data
Imports System.Data.SqlClient

Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyStrWage As String
        Dim Avg As String
        Dim Fringe As String

        Dim MyDSWage As DataSet = New DataSet("Wage Rates")

        MyStrWage = ("select average, fringe from tbl_ClntPayRates where userid=1000")
        MyDSWage = get_data(MyStrWage)

        Avg = MyDSWage.Tables("tbl_ClntPayRates").Rows(0).Item(1)
        Fringe = MyDSWage.Tables("tbl_ClntPayRates").Rows(0).Item(2)

        MsgBox("Average wage is " & Avg & " Fringe is " & Fringe)
    End Sub

    Public Function get_data(ByVal SqlGetData As String) As DataSet

        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)
        Dim MYds As New DataSet

        DA1.Fill(MYds, "table1")
        Return MYds
    End Function

End Class

------------
Also, should the following code be in the module for GetData? But if so, what table name is used in place of “ DA1.Fill(MYds, "table1")“

Public Function get_data(ByVal SqlGetData As String) As DataSet

        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)
        Dim MYds As New DataSet

        DA1.Fill(MYds, "table1")
        Return MYds
    End Function

As you can tell, I am a bit confused at this point.

TIA,

Bill

 

[caoamo]

I don't see anywehre in form1 where you fill the MyDSName dataset...Therefore it won't contain anything, thus the null reference exception.

As a side note, according to msdn, using cmd.ExecuteScalar() only returns the item contained in the first row and first column of the resultant data from your SQL. Thus since col(0) is the client number, all that senddataback will return is 1000, in this code.

I'm fairly new to ADO.NET, so can one of the experts here confirm or correct me?

 

[stillwillyboy]

I have corrected two lines of code. I had the LastName and FirstName in the wrong order. The corrected code is:

 LastName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(1)
 FirstName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(2)

When I run the code and get the error message and hover the cursor over the MyData variable, it gives me the value of "Smith" which is correct, so I know I'm connecting to the DB properly (I think), so it's a matter of displaying the information.

As to:

      senddataback = cmd2.ExecuteScalar

I'm not sure of what to use at this point. Eventually, I want to be able to enter the client number in a text box and have the client's info appear on a form from different tables, i.e. BasicInfo, Address, PayRates, etc. If the client is new, I'll add their info. Kinda like a DB should.

Bill

 

[caoamo]

OK, I missed the fields in your select statment. Still, Mydata will only pull the first column of your results, which is why you get "Smith".

MyDSName is initialized, but never filled via a DataAdapter. You should be filling the dataset just like you do on form2, with the line:

DA1.Fill(MYds, "table1")

 

[Dashley]

Bill

caoamo is right. scalar only returns a single column.
I was just using it as a sample. I sent the dataset sample with it alos.

Whats the status have you got it working

dan

 

[stillwillyboy]

Caomao/Dan,

Not working yet.

What should I use in place of “senddataback = cmd2.ExecuteScalar“?

It seems to me that using “Public Function get_data(ByVal SqlGetData As String) As DataSet” in both forms (or all forms for that matter) defeats the purpose of Class programming, but I’m sure I’m missing something along the line here.

My code is now as follows:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataTableCollection

Module GetData

    Public Function get_data(ByVal SqlGetData As String) As String
        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim cmd2 As New SqlCommand
        Dim senddataback As String

        If Conn.State = ConnectionState.Closed Then Conn.Open()
        With cmd2
            .Connection = Conn
            .CommandType = CommandType.Text
            .CommandText = (SqlGetData)
        End With

        senddataback = cmd2.ExecuteScalar

        Conn.Dispose()
        cmd2.Dispose()

        Return senddataback
    End Function

End Module
---------------
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataTableCollection

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyStrName As String
        Dim MyDataName As String
        Dim LastName As String
        Dim FirstName As String
        Dim MyDSName As DataSet = New DataSet("Last and First Name")

        MyStrName = ("select LastName, FirstName from tbl_ClntBasicInfo where ClientNumber=1000")
        MyDataName = get_data(MyStrName) ‘<<<receive error message “Value of type System.Data.DataSet cannot be converted to String”

        LastName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(1)
        FirstName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(2)
    End Sub
    
Public Function get_data(ByVal SqlGetData As String) As DataSet
        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)
        Dim MYds1 As New DataSet

        DA1.Fill(MYds1, "tbl_ClntBasicInfo")
        Return MYds1
    End Function
End Class
Imports System.Data
Imports System.Data.SqlClient
----------------------------
Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyStrWage As String
        Dim MyDataWage As String
        Dim Avg As String
        Dim Fringe As String
        Dim MyDSWage As DataSet = New DataSet("Wage Rates")

        MyStrWage = ("select average, fringe from tbl_ClntPayRates where userid=1000")
        MyDataWage = get_data(MyStrWage)  ‘<<<receive error message “Value of type System.Data.DataSet cannot be converted to String”

        Avg = MyDSWage.Tables("tbl_ClntPayRates").Rows(0).Item(1)
        Fringe = MyDSWage.Tables("tbl_ClntPayRates").Rows(0).Item(2)

        MessageBox.Show("Average wage is " & Avg & " Fringe is " & Fringe)
    End Sub

    Public Function get_data(ByVal SqlGetData As String) As DataSet

        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim DA2 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)
        Dim MYds2 As New DataSet

        DA2.Fill(MYds2, "tbl_ClntPayRates")
        Return MYds2
    End Function

End Class

Thanks for the help so far.

Bill

 

[Dashley]

Bill

The function getdata should not be in the forms.(It could be but not in this disuccion)

When you look at the sample you'll see the function is in a module called getdata. ("Module GetData")

That means you should add a module to the project and put the function there.

In you project explorer right click your project name, click add new item, select module, give it a name. I called it getdata. Inside the module after its created put the function get_data. That way when any form(s) call the single function and it runs from the module. So aside from the IDE folders you should see 2 forms and one module in you project explorer

The error" ‘<<<receive error message “Value of type System.Data.DataSet cannot be converted to String”

means just what it says. You cant convert a dataset into a string type because the dataset type is dataset as opposed to string. Get rid of Dim MyDataWage as string.

Replace MyDataWage = get_data(MyStrWage)

adataset = get_data(MyStrWage)

Your returning the data back as a dataset from the funciton so it has to return to a dataset (not a string).

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyStr As String
        Dim last As String
        Dim First As String
        Dim MyDS As DataSet = New DataSet("Giveitaname")

        MyStr = ("select firstname, lastname from users where userid=2222")

        MyDS = get_data(MyStr)

        First = MyDS.Tables("users").Rows(0).Item(0)

        last = MyDS.Tables("users").Rows(0).Item(1)

        MsgBox(First & " " & last)
    End Sub

'/// module

   Public Function get_data(ByVal SqlGetData As String) As DataSet
        Dim Conn As SqlConnection = New SqlConnection("your connection here")

        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)


        Dim MYds As New DataSet

        DA1.Fill(MYds, "users")

        Return MYds


    End Function

If you returning more than 1 column of one row of data then you cant use scalar. Pass it back in a dataset like shown in this response. Are you looking to handle just a lastname and first name or are you talking multiple rows of data?

 

[stillwillyboy]

First, thanks for your help and patience.

I understand what the error " ‘<<<receive error message “Value of type System.Data.DataSet cannot be converted to String” is telling me. I do not understand why it is telling me this.

I thought that the function GetData was supposed to retrieve the data requested by the SQL string “MyStrName = ("select LastName, FirstName from tbl_ClntBasicInfo where ClientNumber=1000")” and convert the returned data to a data set.

Once the data is retrieved, it would be placed in the dataset that has been declared in the line “Dim MyDSName As DataSet = New DataSet("Last and First Name")” using the line “MyDSName = get_data(MyStrName)”
-------
I will be returning a bunch of information: Names, address, city, state, zip, pay rates, vacation balance, etc. from various tables that are related by the PK “ClientNumber”. In the situation of Client Info, I will want the info to be displayed in text boxes on a form for each client when their number is entered, with the exception being History records once I get things going, which will show multiple pay periods for the client.

There will also be some job info, but the process/display of info I believe will be similar to the Client info.

There will be a timesheet entry form that will use both Client and Job info to calculate pay. This is not a “Hour times wage = pay” type system. It will be using piece rates per unit and other types of pay.

I was trying to start small and build on what I learned. Here’s my updated code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataTableCollection

Module GetData
    Public Function get_data(ByVal SqlGetData As String) As String
        Dim Conn As SqlConnection = New SqlConnection("Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator")
        Dim cmd2 As New SqlCommand
        Dim DA1 As SqlDataAdapter = New SqlDataAdapter(SqlGetData, Conn)
        'Dim senddataback As String
        Dim MYds As New DataSet

        If Conn.State = ConnectionState.Closed Then Conn.Open()
        With cmd2
            .Connection = Conn
            .CommandType = CommandType.Text
            .CommandText = (SqlGetData)
        End With

        'senddataback = cmd2.ExecuteScalar
        DA1.Fill(MYds, "tbl_ClntBasicInfo")

        Conn.Dispose()
        cmd2.Dispose()

        Return MYds

    End Function
End Module
‘---------------------
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataTableCollection
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyStrName As String
        'Dim MyDataName As DataSet
        Dim LastName As String
        Dim FirstName As String
        Dim MyDSName As DataSet = New DataSet("Last and First Name")

        MyStrName = ("select LastName, FirstName from tbl_ClntBasicInfo where ClientNumber=1000")
        '        MyDataName = get_data(MyStrName)
        'MyDSName = get_data(MyStrName)
        MyDSName = get_data(MyStrName)

        LastName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(1)
        FirstName = MyDSName.Tables("tbl_ClntBasicInfo").Rows(0).Item(2)
    End Sub
   
End Class
‘---------------------------
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataTableCollection

Public Class Form2

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyStrWage As String
        'Dim MyDataWage As String
        Dim Avg As String
        Dim Fringe As String
        Dim MyDSWage As DataSet = New DataSet("Wage Rates")

        MyStrWage = ("select average, fringe from tbl_ClntPayRates where userid=1000")
        'MyDataWage = get_data(MyStrWage)
        MyDSWage = get_data(MyStrWage)

        Avg = MyDSWage.Tables("tbl_ClntPayRates").Rows(0).Item(1)
        Fringe = MyDSWage.Tables("tbl_ClntPayRates").Rows(0).Item(2)
‘going to display in text boxes, not a message box
‘        MessageBox.Show("Average wage is " & Avg & " Fringe is " & Fringe)
    End Sub

    
End Class

Since I am wanting infor in text boxes, using the dataadapter, shouldn’t I have text boxes and code on my forms such as:

Private sub ShowClientInformation ()
If m_dsClients.Rows.Count = 0 then
txtLastName.text = “”
txtFirstName.text = “”
Exit Sub
End If

txtLastName.text = m_dsClients.Rows(m_rowPosition)(“LastName”).ToString()
txtFirstName.text = m_dsClients.Rows(m_rowPosition)(“FirstName”).ToString()
End Sub

I’m pulling the above from the book “Sam’s Teach Yourself VB 2005”

Thanks.

 

[earthandfire]

The problem as I see it is that you are trying to use one Function in a Module to return a number of different datatypes.

I've put together a very quick alternative, which will hopefully point you in a more flexble direction.

Firstly for this example you will need a form with a button and a textbox.

The code on the form is:

	Private Sub ShowResult(ByVal s As String, ByVal success As Boolean, ByVal datatype As String)

		If success Then MessageBox.Show(s) Else MessageBox.Show("Failed to convert to: " + datatype)

	End Sub


	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

		Dim s As String = Nothing
		Dim i As Integer = Nothing
		Dim b As Boolean = Nothing
		Dim d As DateTime = Nothing

		Dim success As Boolean

		success = Class1.GetData(TextBox1.Text, s)
		ShowResult(s, success, "string")
		success = Class1.GetData(TextBox1.Text, i)
		ShowResult(s, success, "integer")
		success = Class1.GetData(TextBox1.Text, b)
		ShowResult(s, success, "boolean")
		success = Class1.GetData(TextBox1.Text, d)
		ShowResult(s, success, "datetime")

	End Sub

You will also need a new Class (not a Module)

Public Class Class1


	Public Overloads Shared Function GetData(ByVal input As String, ByRef output As String) As Boolean

		output = input
		Return True

	End Function

	Public Overloads Shared Function GetData(ByVal input As String, ByRef output As Integer) As Boolean

		Return Integer.TryParse(input, output)

	End Function

	Public Overloads Shared Function GetData(ByVal input As String, ByRef output As Boolean) As Boolean

		Return Boolean.TryParse(input, output)

	End Function

	Public Overloads Shared Function GetData(ByVal input As String, ByRef output As DateTime) As Boolean

		Return DateTime.TryParse(input, output)

	End Function

End Class

All this does is take a string entered into a textbox and attempt to return the value as:
1 = string
2 = integer
3 = boolean
4 = datetime

As the functions are all shared the class does NOT need to be instantiated and as such all gthe function will be directly available from any form.

The function simply return a boolean to indicate success or failure of the conversion.

They all have the same name, but as they take different parameters they are Overloaded.

Note also that I have used ByRef for the second parameter in each case - this enables the target variable to be passed in to the function and means that I can have the boolean success flag for the function return value.




Hope this helps.