Query Help Please

[alexanderjames]

Hi All

I have adapted a connection string i used alot in vb6 to what, after much research i think it should look like now. I added some code to a button, i dont get any errors and no result in the textbox, please any pointers in the righ direction will be much appreciated.

Imports System.Data
Module modMain
    Public MyConn As New OleDb.OleDbConnection()
    Public Da As OleDb.OleDbDataAdapter
    Public rs As New DataTable
    Public us As New DataSet
    Public Query As String
    Public Sub ActiveReadServer(ByVal Query As String)
        MyConn = New OleDb.OleDbConnection()
        Da = New OleDb.OleDbDataAdapter(Query, MyConn)
        rs = New DataTable

        MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test App\data.accdb;Jet OLEDB:Database Password=hello;"
        MyConn.Open()

        'rs.open(Query, MyConn, adOpenStatic, adLockReadOnly)
    End Sub
    Public Sub ActiveUpdateServer(ByVal Query As String)
        MyConn = New OleDb.OleDbConnection()
        Da = New OleDb.OleDbDataAdapter(Query, MyConn)
        us = New DataSet

        MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test App\data.accdb;Jet OLEDB:Database Password=hello;"
        MyConn.Open()
        'us.Open(Query, MyConn, , adLockBatchOptimistic)
    End Sub
End Module

Thats from modMain.

 Private Sub cmdGet_ClickEvent(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGet.ClickEvent
        ActiveUpdateServer("SELECT First_Name FROM Customers")
        txtName.Text = us.Tables("Customers").Rows(0).Item("First_Name")
    End Sub

Many Thanks in advance
AJ

[sapator]

Am a little tired but if i see correctly after " MyConn.Open()" you do nothing.
Thus you set your querystring an open the database...So?...Where do you fetch your data?

[jmcilhinney]

Follow the Database FAQ link in my signature and check out my Retrieving And Saving Data thread from there. It has code examples for the most common ADO.NET scenarios, including using a DataAdapter to retrieve and save data. You need to call Fill on your DataAdapter to populate a DataTable first. You can then edit the data in that table as you see fit, e.g. bind it to a DataGridView or some other controls. When you're finished editing you then call Update on the DataAdapter to save the changes in the DataTable back to the database.

[alexanderjames]

Hi Guys

thank you for your reply,I remarked that piece out as rs.open doesnt exist anymore and trying to find out how to get the same kind of result. Only been using .net for 4 days now and trying to get my head around all the differences, like in VB6 binding a control to a datasource was a big no no, and the tutorials i have read on accessing a database, it seems that the entire connection is coded everytime it is needed. All rather confusing but i suppose i'll get used to it eventually.

AJ

[jmcilhinney]

The main difference between VB6 ADO and ADO.NET is that ADO.NET follows good OOP practice, which includes the "Single Responsibility Principle". VB6 tended to use one super class to do an entire job, e.g. the Recordset. In ADO.NET, each part of the data access process is handled by a separate class with a single responsibility. There's one class to handle the connection, one to handle the SQL command, one to store data for a single table, one to store multiple tables, etc, etc. Like I said, my Retrieving & Saving Data thread in the CodeBank shows which classes to use and how to use them in the most common ADO.NET scenarios.

[alexanderjames]

Thank you Jmcilhinney

Your thread was of brilliant use and that solves alot of my questions with regards to connecting to a database. My final question is would it be good coding practice in .NET to write a module for the connection or just code everything as i need it.

Many Thanks
AJ

[jmcilhinney]

Thank you Jmcilhinney

Your thread was of brilliant use and that solves alot of my questions with regards to connecting to a database. My final question is would it be good coding practice in .NET to write a module for the connection or just code everything as i need it.

Many Thanks
AJ

When starting out, most people just put all there data access code into their forms because it's easiest. Extracting the data access code into a single location and then accessing it in that location from the multiple places that need to use does make for a little more work up front but leads to far greater maintainability. In larger projects, it's best to extract the data access code into its own project, then reference that DLL in your application. The ideal level of abstraction depends on the project. There's no point spending days creating multiple layers for a simple project you're working on by yourself, but doing so in large projects with teams of developers working on them can save lots of time in the long run.

[alexanderjames]

Thank you again Jmcilhinney

You have been a tremendouse help, you are right this my first app im doing in .net and its a small one for a client and it will be much less hassle to code the con as i need it as it.

Many Thanks again for your advice
AJ

[jmcilhinney]

Thank you again Jmcilhinney

You have been a tremendouse help, you are right this my first app im doing in .net and its a small one for a client and it will be much less hassle to code the con as i need it as it.

Many Thanks again for your advice
AJ

That's particularly true if you use the Data Source wizard. You can add a Data Source to your project and point the wizard to a database and it will generate all the connection, schema and query information. You can then drag tables and columns from the Data Sources window onto your forms and many components will be created fro you and some code generated for you. It's a very quick and easy way to get up and running. As you get more advanced though, you'll want to do more (perhaps all) manually so that you can abstract your data access and have finer control in the places that you need it.

[alexanderjames]

Thanks again Jmcilhinney

I started playing and reading up on that today and seems a good way to get started with .net, i remember starting like that in vb6 by adding the adodc control to the form and pointing it to the db, so will start like this too and grow it from there.

Many Thanks
AJ