How do I return data from SQL 2005 using VB2008 code?

I had wrote in a VB6 App to return Customer Information from SQL 2005 DB. I since used the Wizard to convert it but it doesn't work. I currently rewriting the whole App into VB2008 and would like to add this section back to return the data. I have included the VB6 code so you can see what I did. I know in the conversion there are some changes for instance like ISNULL() is changed to IsDbNULL().

This code is used after the user selects one of the items they want to use to look up information. One other thing this was Public Sub that I had created in a Module Called MainCode.bas.

I included it so you can see what I am working with to convert over. I tried creating the Rst and Con again but it right off the bat it tells me that ADODB.Recordset is not defined. So I am not sure we to go from here. I thought of doing an Import but again not sure what I could do to get it to work.

I also thought of using Handler DataSets but not sure how to write that so it pulls the data that I am wanting to put into Varibles to use in other places in the App.

Any help would be great. :-)
'Database Variables
Public Con As New ADODB.Connection  'ado connection
Public Rst As New ADODB.Recordset   'ado recordset
Public ConnString As String         'input connection string
Public DBOpen As Boolean            'Opens DB
Public RSTOpen As Boolean           'Opens RecordSet
Public sql As String                'sql statement variable
Public dsnstr As String             'Holds DSN connection info

Public Sub ADMIN_LOOKUP()

On Error GoTo Err_Admin_Lookup

*****There is code in here that looks to an ini to get connection information****
'Universal SQL Database Connection String for both centers. The varibles are set by the IssueHandler.ini.
ConnString = "Provider=" + ProviderSql + ";Data Source=" + SourceSql + ";Initial Catalog=" + IniCatalogSql + ";User Id=" + UserIdSql + ";Password=" + PasswordSql + ""     'connection string variable for ALL Center

DBOpen = False
RSTOpen = False

 Con.Open ConnString     'establish ADO connection
End If
DBOpen = True

If CustomerConfirm <> 1 Then  'lookup using NTID

Adminfrm.lblMessageBoard.Caption = "Not Working"


Else

******INI information build ******
sEntry$ = "SQL"             'Collecting Entry Info from IssueHandler.ini from the left side of "="
    sDefault$ = ""
    sRetBuf$ = String(256, 0)   '256 null characters
    iLenBuf% = Len(sRetBuf$)
    sFileName$ = IniLocation    'Varible that holds the ini file location
 'This function will return a number value from the IssueHandler.ini Entry Section right side of "="
    x = GetPrivateProfileString(sSection$, sEntry$, sDefault$, sRetBuf$, iLenBuf%, sFileName$)
 'sValue$ will contain the string from the Entry Section right side of "="
    sValue$ = left$(sRetBuf$, x)
    sql = sValue$       'Moves sValue$ to SQL varible

'set the SQL string
 If IsNumeric(CustomerLookup) = True Then
    sql = sql + "id = '" + CustomerLookup + "' "
    sql = sql + "or firstname = '" + CustomerLookup + "' "
    sql = sql + "or lastname = '" + CustomerLookup + "' "
    sql = sql + "or naname = '" + CustomerLookup + "' "
    sql = sql + "or email = '" + CustomerLookup + "' "
    sql = sql + "or centerloc = '" + CustomerLookup + "' "
    sql = sql + "or timezone = '" + CustomerLookup + "' "
    sql = sql + "or rights = '" + CustomerLookup + "' "
    sql = sql + "or vnet = '" + CustomerLookup + "' "
    
  Else
    sql = sql + "firstname = '" + CustomerLookup + "' "
    sql = sql + "or lastname = '" + CustomerLookup + "' "
    sql = sql + "or naname = '" + CustomerLookup + "' "
    sql = sql + "or email = '" + CustomerLookup + "' "
    sql = sql + "or centerloc = '" + CustomerLookup + "' "
    sql = sql + "or timezone = '" + CustomerLookup + "' "
    sql = sql + "or rights = '" + CustomerLookup + "' "
    sql = sql + "or vnet = '" + CustomerLookup + "' "
 End If

'execute the SQL statement and set its results to the RST recordset object
 Set Rst = Con.Execute(sql)

If Rst.EOF = True Then          'if there are no results from the query
    strMsg = "Please Select Customer's First or Last Name."
    Adminfrm.frmMessagBoard.ForeColor = &HFF&
    Adminfrm.lblMessageBoard.Caption = strMsg
    FirstName = "Not"
    LastName = "Working"
    'CLEAR_OUT
 Else
    
    If IsNull(Rst.Fields("id").Value) = False Then  'check for non-null first name
        CustomerId = Trim(Rst.Fields("id").Value)
    Else
        CustomerId = ""
    End If
    
    If IsNull(Rst.Fields("firstname").Value) = False Then  'check for non-null first name
        FirstName = Trim(Rst.Fields("firstname").Value)
    Else
        FirstName = ""
    End If
    
    If IsNull(Rst.Fields("lastname").Value) = False Then   'check for non-null last name
        LastName = Trim(Rst.Fields("lastname").Value)
    Else
        LastName = ""
    End If
    
    If IsNull(Rst.Fields("naname").Value) = False Then   'check for non-null NA User Name
        NameNa = Trim(Rst.Fields("naname").Value)
    Else
        NameNa = ""
    End If
    
    If IsNull(Rst.Fields("email").Value) = False Then   'check for non-null E-mail
        Email = Trim(Rst.Fields("email").Value)
    Else
        Email = ""
    End If
            
    If IsNull(Rst.Fields("timezone").Value) = False Then   'check for non-null tzone
        Tzone = Trim(Rst.Fields("timezone").Value)
    Else
        Tzone = ""
    End If
    
    If IsNull(Rst.Fields("rights").Value) = False Then   'check for non-null Customer Rights/Permission
        RightsCustomer = Trim(Rst.Fields("rights").Value)
    Else
        RightsCustomer = ""
    End If
    
    If IsNull(Rst.Fields("vnet").Value) = False Then   'check for non-null Customer Rights/Permission
        Vnet = Trim(Rst.Fields("vnet").Value)
    Else
        Vnet = ""
    End If
    
    If IsNull(Rst.Fields("centerloc").Value) = False Then   'check for non-null CenterLocation
        CenterLocation = Trim(Rst.Fields("centerloc").Value)
        
    Else
        CenterLocation = ""
        Desktopfrm.lblMessage.Visible = True 'Shows Error Message on Desktopfrm
        Desktopfrm.lblMessage.Caption = "Unable to load Customer information!!" 'Shows Error Message on Desktopfrm
    End If
    
    CustomerConfirm = 2
    
End If


If Rst.State = 1 Then
    Rst.Close               'Close the recordset
    Set Rst = Nothing       'Free the memory of the Rst object
    RSTOpen = False
End If

End If

Err_Admin_Lookup:
 If Err.Number <> 0 Then
    MsgBox "Admin Lookup Error: " & Err.description, vbSystemModal + vbExclamation, "Admin Lookup Error"
 End If
End Sub

Open in new window

Jon-LeachAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EYoungCommented:
In VB.net you no longer use the recordset approach.

Add these two lines to the start of your vb code:
Imports System.IO
Imports System.Data.SqlClient

Add these to a module:
    Public connSQL As System.Data.SqlClient.SqlConnection()
    Public cmdSQL As System.Data.SqlClient.SqlCommand

Add a new "Setting" called SQLConnection as a type (Connection):
Data Source=<SERVER NAME HERE>;Initial Catalog=<DATABASE NAME HERE>;Persist Security Info=True;User ID=<ID HERE>;Password=<PASSWORD HERE>


Here is an example of how to add (insert) records into a SQL table:
                                    Using connSQL As New SqlConnection(My.Settings.SQLConnection)
                                        mWork_String_1 = "INSERT INTO MAPS_GB" _
                                            & " (User_Name, InDesign_GB_File_Name, Item_Number, Item_Description, Item_Found)" _
                                            & " Values ('" & mUser_Name & "', '" & mWork_File_Name & "', '" & mItem_Number & "', '', 'N'" & ")"
                                        Using cmdSQL As New SqlCommand(mWork_String_1, connSQL)
                                            connSQL.Open()
                                            cmdSQL.ExecuteNonQuery()
                                            cmdSQL.Dispose()
                                            connSQL.Close()
                                            connSQL.Dispose()
                                        End Using
                                    End Using

Here is an example of how to change (Update) an existing record in SQL:
                                Using connSQL As New SqlConnection(My.Settings.SQLConnection)
                                    mWork_String_1 = "UPDATE MAPS_GB" _
                                        & " SET Item_Found = 'Y'" _
                                        & " WHERE User_Name = '" & mUser_Name & "'" _
                                        & " AND Item_Number = '" & mItem_Number & "'" _
                                        & " AND InDesign_GB_File_Name = '" & mWork_File_Name & "'"
                                    Using cmdSQL As New SqlCommand(mWork_String_1, connSQL)
                                        connSQL.Open()
                                        cmdSQL.ExecuteNonQuery()
                                        cmdSQL.Dispose()
                                        connSQL.Close()
                                        connSQL.Dispose()
                                    End Using
                                End Using
0
Jon-LeachAuthor Commented:
ok thxs I will try that you have an example for retriving the data and assigning it to a varible? Below is how I was doing it before. Is there an easier way?
If Rst.EOF = True Then          'if there are no results from the query
    strMsg = "Please Select Customer's First or Last Name."
    Adminfrm.frmMessagBoard.ForeColor = &HFF&
    Adminfrm.lblMessageBoard.Caption = strMsg
    FirstName = "Not"
    LastName = "Working"
    'CLEAR_OUT
 Else
    
    If IsNull(Rst.Fields("id").Value) = False Then  'check for non-null first name
        CustomerId = Trim(Rst.Fields("id").Value)
    Else
        CustomerId = ""
    End If


Addtional fields.....

Open in new window

0
EYoungCommented:
Here is how to retrieve data and assign it to a variable:

First put this code in your Module:
    Public connSQL As System.Data.SqlClient.SqlConnection()
    Public cmdSQL As System.Data.SqlClient.SqlCommand
    Public daCostSheet As System.Data.SqlClient.SqlDataAdapter
    Public dsCostSheet As DataSet
    Public dtCostSheet As DataTable
    Public rdCostSheet As System.Data.SqlClient.SqlDataReader
    Public drCostSheet As DataRow

Here is the code to retrieve and assign to variables:
        Using connSQL As New SqlConnection(My.Settings.SQLConnection)
            'mWork_String_1 = "SELECT * FROM tbl_CostSheet_Exports ORDER BY Year, Season, Offer_Id, ParentID, ItemNum"   (THIS COMMENTED LINE SHOWS WHAT IS IN THE STORED PROCEDURE)
            mWork_String_1 = "CostSheet_Exports_All"
            Using cmdSQL As New SqlCommand(mWork_String_1, connSQL)
                cmdSQL.CommandType = CommandType.StoredProcedure
                cmdSQL.CommandText = mWork_String_1
                'cmdSQL.Parameters.Add("@OfferCode", SqlDbType.Char).Value = mOfferCode
                connSQL.Open()
                cmdSQL.ExecuteNonQuery()

                daCostSheet = New SqlDataAdapter(cmdSQL)
                dtCostSheet = New DataTable
                daCostSheet.Fill(dtCostSheet)
                Dim drCostSheet As DataRow

                For Each drCostSheet In dtCostSheet.Rows
                        'Save off record to memory vars
                        mCurrent_ParentID = Trim(drCostSheet("ParentID").ToString)
                        mCurrent_ItemNum = Trim(drCostSheet("ItemNum").ToString)
                        mCurrent_ItemDescription = Trim(drCostSheet("ItemDescription").ToString)
                Next drCostSheet

                connSQL.Close()
            End Using
        End Using
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Jon-LeachAuthor Commented:
Thxs I will see what I do.
0
Jon-LeachAuthor Commented:
I am a little confused by this these lines....

Add a new "Setting" called SQLConnection as a type (Connection):
Data Source=<SERVER NAME HERE>;Initial Catalog=<DATABASE NAME HERE>;Persist Security Info=True;User ID=<ID HERE>;Password=<PASSWORD HERE>

I understand the "Data Source = ...." section of this but the first part I have tried to type it in as you have it. But nothing looks right, what am I missing here.? I know it's a simple thing but I am missing it :).
0
Jon-LeachAuthor Commented:
Am I suppose to do the following?

Dim Setting As New SQLConnection("Data Source=<SERVER NAME HERE>;Initial Catalog=<DATABASE NAME HERE>;Persist Security Info=True;User ID=<ID HERE>;Password=<PASSWORD HERE>
")
0
EYoungCommented:
These are the Application Settings.  The settings are in the properties window in the "Settings" section.  Right click on your project (bold type at top of Solution Explorer) and left click on Properties.  On the left side of your screen, click on Settings and enter a new setting as follows:

Name = SQLConnection
Type = (Connection String)
Scope = Application
Value = Data Source=<SERVER NAME HERE>;Initial Catalog=<DATABASE NAME HERE>;Persist Security Info=True;User ID=<ID HERE>;Password=<PASSWORD HERE>

Change the information within the <...> above to your names.
0
Jon-LeachAuthor Commented:
Cool, I see what your saying now. I am new to VB2008 still learning my way around the VS2008. Thxs I will add this and make the changes I need to connect my stuff and I will let you know how it goes.
0
Jon-LeachAuthor Commented:
It worked!!! Thanks for your help I attached the code and how I changed it a little to fit my needs, right now. I need to look into calling SQL stored proceedures and passing the varible to it. I am use to hard coding it. Something new to learn.
Public Sub CUSTOMER_INFO()
        Dim SQLStatement As String = "SELECT * FROM CUSTOMERINFO WHERE naname = '" & MyUserName & "'"
        Using connSQL As New SqlConnection(My.Settings.SQLConnection)
            Using cmdSQL As New SqlCommand
                cmdSQL.CommandText = SQLStatement
                cmdSQL.CommandType = CommandType.Text
                cmdSQL.Connection = connSQL
                connSQL.Open()
                cmdSQL.ExecuteNonQuery()

                daCostSheet = New SqlDataAdapter(cmdSQL)
                dtCostSheet = New DataTable
                daCostSheet.Fill(dtCostSheet)
                Dim drCostSheet As DataRow

                For Each drCostSheet In dtCostSheet.Rows
                    CustomerID = Trim(drCostSheet("id").ToString)
                    FirstName = Trim(drCostSheet("firstname").ToString)
                    LastName = Trim(drCostSheet("lastname").ToString)
                    NameNa = Trim(drCostSheet("naname").ToString)
                    Email = Trim(drCostSheet("email").ToString)
                    Tzone = Trim(drCostSheet("timezone").ToString)
                    RightsCustomer = Trim(drCostSheet("rights").ToString)
                    Vnet = Trim(drCostSheet("vnet").ToString)
                    CenterLocation = Trim(drCostSheet("centerloc").ToString)
                Next drCostSheet

                connSQL.Close()
            End Using
        End Using
    End Sub

Open in new window

0
EYoungCommented:
Congratulations.  Please accept this answer and post a new question.  Maybe I can help with that one also.
0
Jon-LeachAuthor Commented:
Very willing to help and explain.
0
EYoungCommented:
Please note that the above examples referring to "CostSheet" are references to the names of my tables.  You will want to change the names to your tables for better documentation. Just a suggestion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.