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

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.

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

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.

        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

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.

        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

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