Solved

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

Posted on 2010-09-14
12
238 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Jon-Leach
  • 7
  • 5
12 Comments
 
LVL 7

Assisted Solution

by:EYoung
EYoung earned 500 total points
ID: 33673346
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
 

Author Comment

by:Jon-Leach
ID: 33673414
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
 
LVL 7

Accepted Solution

by:
EYoung earned 500 total points
ID: 33673516
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:Jon-Leach
ID: 33673875
Thxs I will see what I do.
0
 

Author Comment

by:Jon-Leach
ID: 33674201
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
 

Author Comment

by:Jon-Leach
ID: 33674414
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
 
LVL 7

Assisted Solution

by:EYoung
EYoung earned 500 total points
ID: 33674441
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
 

Author Comment

by:Jon-Leach
ID: 33674600
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
 

Author Comment

by:Jon-Leach
ID: 33675401
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
 
LVL 7

Expert Comment

by:EYoung
ID: 33675501
Congratulations.  Please accept this answer and post a new question.  Maybe I can help with that one also.
0
 

Author Closing Comment

by:Jon-Leach
ID: 33675691
Very willing to help and explain.
0
 
LVL 7

Expert Comment

by:EYoung
ID: 33675925
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question