CODEWITHSUNDEEP
mysqlasp.netvb.net
Eggybread
Eggybread

Reputation: 359

Reading data from MySQL to VB

I'm just learning and working on a site using Asp.Net/VB/SQL.. I'm trying to read a full row of data from SQL to manipulate in my VB code. I so far have this..

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT  shopName
       ,shopLogo
       ,addressLine1
       ,city
       ,postcode
       ,phoneNumber
       ,pointsPerPound
       ,maxPoints
       ,info

FROM tblShopKeeper
WHERE orderID = @shopID
END

Which selects the data but how do I pass it back to VB ? I have this for the code..

Public Function SelectShop(ByVal orderString As Guid) As String
    Dim DBConnect As New DBConn
    Using db As DbConnection = DBConnect.Conn("DBConnectionString")
        Dim cmd As SqlCommand = DBConnect.Command(db, "SelectShop")
        cmd.Parameters.Add(New SqlParameter("shopID",         SqlDbType.uniqueIdentifier, ParameterDirection.Input)).Value = orderString

        db.Open()
        Dim shopName As String
        Dim shopLogo As String
        Dim addressLine1 As String
        Dim city As String
        Dim postcode As String
        Dim phoneNumber As String
        Dim pointsPerPound As Integer
        Dim maxPoints As Integer
        Dim info As String

        Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        While DR.Read
            shopName = DR("shopName")
            shopLogo = DR("shopLogo")
            addressLine1 = DR("addressLine1")
            city = DR("city")
            postcode = DR("postcode")
            phoneNumber = DR("phoneNumber")
            pointsPerPound = DR("pointsPerPound")
            maxPoints = DR("maxPoints")
            info = DR("info")
        End While

        DR.Close()
        DR = Nothing
        cmd.Dispose()
        cmd = Nothing
        db.Dispose()
        db.Close()

        Return shopName
    End Using
End Function

And I'm calling this simply with.. SelectShop(shopID)

The "Return" statement only allows me to pass one field back.. How do I pass all fields back to use in the code please ?

Be gentle.. I'm just a novice :-) Many thanks.

Upvotes: 2

Views: 95

Answers (1)

Sami
Sami

Reputation: 3800

Create a class "Shop" 

Public Class Shop

   public property ShopName as String
   public property ShopLogo as String
   public property addressLine1 as String
   ...
End Class

Change Function return type to Shop

Public Function SelectShop(ByVal orderString As Guid) As Shop

Update function code snippet as below:

 Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

 Dim objShop as New Shop()

    While DR.Read
        objShop.ShopName = DR("shopName")
        objShop.ShopLogo = DR("shopLogo")
        ...    

    End While

... 'Your code

    Return shopName

Upvotes: 1

Related Questions