Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Regarding Data Reader & Cursor Usage

MAHEYMAHEY Posts: 58Member
Hi,
while we are using the connection to sql server, the data's are available with datareader, that's ok. fine. But after closing the connection I need the data's to be available for some other calculations.In VFP9 we will use connection, but after selecting the data's to curosr then we will close the connection and can be use that cursor anywhere in the form. So in Vb.net how to retrieve the data after closing the connection?
or Is it possible use cursor here? it available in vb.net?

THANKS


Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    What do you mean by cursor?

    Are you retrieving the data into a Dataset object? If that is the case, then you can just store the Dataset object in memory so you can retain the information you retrieved from the database.

    If you have no connection to the database, you cannot manipulate or query the data within. However, if you retreive data and store it in memory (whichever way, DataSet object, DataTable object, ArrayList) then you can play with that data in memory.
  • MAHEYMAHEY Posts: 58Member
    Hi SEAN,

    Well.

    As per your idea I need the following from you.....

    Dim Con As New SqlClient.SqlConnection

    Con.ConnectionString= "DataSource=0.0.0.0;InitialCatalog=DataBaseName;User Id=sa;Password=Password;"

    Con.Open()
    Dim DS As New DataSet

    Dim Query As String = "SELECT * FROM emp_master WHERE CAST(emp_id as varchar(10)) LIKE '" & kyprs & "%'"

    Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(Query, Con)
    DA.Fill(DS)

    ------------------
    now I gave

    con.close()

    --------------------------------

    now how to use this DA in other procedure.......?
    Can you give some coding with if conditiions......

    Thanks Again
    MAHESH


  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    I can explain the code you have posted here with some commenting, and show you how to use the data.

    Firstly, you are going to need to know a bit of information about your database. What the servername (ip address) is, the Database name, and the Username and Password to connect to that database.

    For this example, I will provide some "fake" examples that will give you an idea on how to reuse this in a real world scenario.

    For this example we are going to query some customer information from a database. The database connection information is as follows:

    Database name: "CUSTDB"
    Sql server address: 10.1.1.101
    Username: SA
    Password: 99iur13

    The database table will be called 'Customer' and it will have the following feilds:
    ID (int - autonumber)
    First_Name (nvarchar(32))
    Last_Name (nvarchar(32))
    Address (nvarchar(50))
    City (nvarchar(50))
    State (nvarchar(50))
    Postal_Code (nvarchar(15))

    To keep with general SQL DB standards, I have used underscores (the _ character) instead of spaces in field names. If I used spaces, I need brackets to reference fields, but this can get confusing, so stick with underscores for ease.

    Now, I have the nessesary information about my database to write some queries, grab data, and display it. I will display the contents of the database table in a listview.

    [code]
    Dim DBName As String = "CUSTDB"
    Dim DBServer As String = "10.1.1.101"
    Dim DBUser As String = "SA"
    Dim DBPassword As String = "99iur13"
    Dim ConStr As String
    ConStr = "DataSource=" & DBServer & ";InitialCatalog=" & DBName & ";User Id=" & DBUser & ";Password=" & DBPassword & ";"

    Dim Con As New SqlClient.SqlConnection
    Con.ConnectionString = ConStr
    Con.Open()

    Dim DS As New DataSet
    'Dim Query As String = "SELECT * FROM emp_master WHERE CAST(emp_id as varchar(10)) LIKE '" & kyprs & "%'"
    Dim Query As String = "SELECT * FROM Customer" 'This query grabs all records

    Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(Query, Con)
    DA.Fill(DS)

    Con.Close() 'Close the connection, we got the data and don't need it anymore

    'Only 1 table of data got returned, so our data is sitting in DS.Tables(0)
    Dim DT As DataTable = DS.Tables(0)

    'Assume now that we have a listview called ListView1
    ListView1.Columns.Clear()
    ListView1.Columns.Add("First Name", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("Last Name", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("Address", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("City", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("State", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("Postal Code", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("ID", 50, HorizontalAlignment.Left)
    'Now our columns are set up, lets verify that the listview has
    'some settings that will be required to view the data we are using
    'These settings are normally set in the designer view of the form
    ListView1.View = View.Details
    ListView1.GridLines = True
    ListView1.FullRowSelect = True
    ListView1.MultiSelect = False

    'This is a loop that will parse through all of the returned records
    'and add them to the listview

    For i As Integer = 0 To DT.Rows.Count - 1
    Dim xItem As ListViewItem
    xItem = New ListViewItem(DT.Rows(i).Item("First_Name").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("Last_Name").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("Address").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("City").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("State").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("Postal_Code").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("ID").ToString)
    ListView1.Items.Add(xItem)
    Next i
    [/code]
  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    I can explain the code you have posted here with some commenting, and show you how to use the data.

    Firstly, you are going to need to know a bit of information about your database. What the servername (ip address) is, the Database name, and the Username and Password to connect to that database.

    For this example, I will provide some "fake" examples that will give you an idea on how to reuse this in a real world scenario.

    For this example we are going to query some customer information from a database. The database connection information is as follows:

    Database name: "CUSTDB"
    Sql server address: 10.1.1.101
    Username: SA
    Password: 99iur13

    The database table will be called 'Customer' and it will have the following feilds:
    ID (int - autonumber)
    First_Name (nvarchar(32))
    Last_Name (nvarchar(32))
    Address (nvarchar(50))
    City (nvarchar(50))
    State (nvarchar(50))
    Postal_Code (nvarchar(15))

    To keep with general SQL DB standards, I have used underscores (the _ character) instead of spaces in field names. If I used spaces, I need brackets to reference fields, but this can get confusing, so stick with underscores for ease.

    Now, I have the nessesary information about my database to write some queries, grab data, and display it. I will display the contents of the database table in a listview.

    [code]
    Dim DBName As String = "CUSTDB"
    Dim DBServer As String = "10.1.1.101"
    Dim DBUser As String = "SA"
    Dim DBPassword As String = "99iur13"
    Dim ConStr As String
    ConStr = "DataSource=" & DBServer & ";InitialCatalog=" & DBName & ";User Id=" & DBUser & ";Password=" & DBPassword & ";"

    Dim Con As New SqlClient.SqlConnection
    Con.ConnectionString = ConStr
    Con.Open()

    Dim DS As New DataSet
    'Dim Query As String = "SELECT * FROM emp_master WHERE CAST(emp_id as varchar(10)) LIKE '" & kyprs & "%'"
    Dim Query As String = "SELECT * FROM Customer" 'This query grabs all records

    Dim DA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(Query, Con)
    DA.Fill(DS)

    Con.Close() 'Close the connection, we got the data and don't need it anymore

    'Only 1 table of data got returned, so our data is sitting in DS.Tables(0)
    Dim DT As DataTable = DS.Tables(0)

    'Assume now that we have a listview called ListView1
    ListView1.Columns.Clear()
    ListView1.Columns.Add("First Name", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("Last Name", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("Address", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("City", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("State", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("Postal Code", 50, HorizontalAlignment.Left)
    ListView1.Columns.Add("ID", 50, HorizontalAlignment.Left)
    'Now our columns are set up, lets verify that the listview has
    'some settings that will be required to view the data we are using
    'These settings are normally set in the designer view of the form
    ListView1.View = View.Details
    ListView1.GridLines = True
    ListView1.FullRowSelect = True
    ListView1.MultiSelect = False

    'This is a loop that will parse through all of the returned records
    'and add them to the listview

    For i As Integer = 0 To DT.Rows.Count - 1
    Dim xItem As ListViewItem
    xItem = New ListViewItem(DT.Rows(i).Item("First_Name").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("Last_Name").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("Address").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("City").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("State").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("Postal_Code").ToString)
    xItem.SubItems.Add(DT.Rows(i).Item("ID").ToString)
    ListView1.Items.Add(xItem)
    Next i
    [/code]
  • MAHEYMAHEY Posts: 58Member
    Wonderful, Thank You Sean !!!!

    "You Are Gift For The People Like Us"

    "Service To The ManKind Is The Way Of Piece & Brightness"

    Thanking Regards
    MAHESH
Sign In or Register to comment.