[RESOLVED] ADODB Recordset

[Nervotrep]

Hello,
I'm here again for some help.

Here is what i have,

Dim ConnectionString As String = "Provider=MSDASQL.1;Password=101098;Data Source=main;Persist Security Info=True"
 
        'Create my objects
        Dim conn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim rs As New ADODB.Recordset
 
        Try
            conn.Open(ConnectionString)
            cmd.ActiveConnection = conn
            cmd.CommandText = "SELECT customername FROM customers"
 
       'make a recordset here
 
        Catch ex As Exception
            Debug.WriteLine(ex.Message.ToString)
 
        End Try

I need help build a recordset from that query and displaying it in a datagrid or a flexgrid.

Thanks in advance!

[RobDog888]

Why not use ADO.NET and a DataGrid?

[Nervotrep]

could you please provide an example?

[RobDog888]

You need to import the SQL client namespace. Then set up a connection to your db.

Imports System.Data.SqlClient
 
Public Function DBConn() As SqlConnection
    '<CONNECT TO SQL DB>
    Dim oSQLCnn As SqlConnection
    Try
        oSQLCnn = New SqlConnection
        oSQLCnn.ConnectionString = "Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI;" 'Integrated Security
        oSQLCnn.Open()
        DBConn = oSQLCnn
    Catch exSQL As SqlException
        MessageBox.Show(exSQL.Errors.Item(0).Message, exSQL.Errors.Item(0).Number.ToString & ": " & _
        exSQL.Errors.Item(0).Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
        DBConn = Nothing
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
        DBConn = Nothing
    End Try
    '</CONNECT TO SQL DB>
End Function

[jmcilhinney]

All the data access examples in the 101 samples are ADO.NET except the one that explicitly says it uses ADO 2.6. Get the 101 samples from the sticky thread at the top of this very forum.

[RobDog888]

Then we need to make the connection and populate a datatable.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    Dim oSQLCnn As SqlConnection
 
    oSQLCnn = DBConn()
    Dim dt As New DataTable
    Dim da As New SqlDataAdapter("SELECT * FROM Table1", oSQLCnn)
    da.Fill(dt)
    Me.DataGrid1.DataSource = dt
    Me.DataGrid1.ReadOnly = True' Or False
    Me.DataGrid1.ColumnHeadersVisible = True
    Me.DataGrid1.CaptionVisible = False
 
End Sub

[Nervotrep]

thanks for ur reply.
But i have an access database i dont run a sql server. Any way to modify that to work with access?

[RobDog888]

Doh! I should have asked first but its not too much different. There is a OLEDBDataAdapter, OLEDBConnection, and OLEDBCommand. Dont have time right now to write an example, leaving for home, but if you cant get it or no one else posts one, then I will.

[Nervotrep]

looking forward to it
i've spent about 6 hours today trying to do 3 things... and still not much luck

-display query results in a datagrid or a flexgrid
-pull field values from a recordset
-update database

[jmcilhinney]

Wherever Rob has used "Sql", you would use "OleDb". You would also need to import the System.Data.OleDb namespace, where for Rob's code you would import SqlClient. You'll have to change the connection string for OleDb as well. Try www.connectionstrings.com

[Nervotrep]

Dim ConnString As String = "Provider=MSDASQL.1;Password=101098;Data Source=main;Persist Security Info=True"
 
        Dim Query As String = "SELECT customer FROM customers "
 
        'Create a connection object to connect to the database
        Dim conn As New OleDbConnection(ConnString)
        'Create a command object to run queries against the database
        Dim cmd As New OleDbCommand(Query, conn)
 
        'open the connection to the database
        conn.Open()
 
 
 
        'so now how do i execute the whole thing and bind the data to a datagrid
 
 
 
        'close the connection
        conn.Close()

is this anywhere close? no how would i get a recordset from there and have it be displayed in a datagrid?

[jmcilhinney]

Rob's code is:

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    Dim oSQLCnn As SqlConnection
 
    oSQLCnn = DBConn()
    Dim dt As New DataTable
    Dim da As New SqlDataAdapter("SELECT * FROM Table1", oSQLCnn)
    da.Fill(dt)
    Me.DataGrid1.DataSource = dt
    Me.DataGrid1.ReadOnly = True' Or False
    Me.DataGrid1.ColumnHeadersVisible = True
    Me.DataGrid1.CaptionVisible = False
 
End Sub

Your code should be to the effect of:

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    Dim con As New OleDb.OleDbConnection("connection string here")
    Dim dt As New DataTable
    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Table1", con)
    da.Fill(dt) 'Retrieve data into DataTable.
    Me.DataGrid1.DataSource = dt 'Bind DataGrid to DataTable.
    Me.DataGrid1.ReadOnly = True' Or False
    Me.DataGrid1.ColumnHeadersVisible = True
    Me.DataGrid1.CaptionVisible = False
 
End Sub