Solved

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

Posted on 2010-09-14
12
233 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now