Database Connection Question [Resolved]

[ajirwin]

I'm in the process of learning VB.Net and I have been rewriting some of my VB6 projects. I'm having a problem understanding ADO.NET. Can I just call my database connection from a module like I did in VB6. And if so how do I make it work in my other modules that do my business functions.

Examples:

VB6 Database connection

Public objAccessConnection As adodb.Connection
Public rsAccess As adodb.Recordset
Public mbAddNewFlag As Boolean
Public mbEditFlag As Boolean
Public mbDataChanged As Boolean
 
Public Sub DBConnection()
On Error GoTo CheckError_Connection
Set objAccessConnection = New adodb.Connection
objAccessConnection.CursorLocation = adUseClient
objAccessConnection.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\ConcorddB.mdb;Jet OLEDB:Database Password="
Exit Sub
CheckError_Connection:
MsgBox Err.Description, 16, "Error message. "
Resume Next
End Sub

.NET Data connection

Public Sub DBCon()
Dim myConnString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & VB6.GetPath & "\ConcorddB.mdb;Jet OLEDB:Database Password=" ' Put here the connection string
Dim myConnection As New System.Data.OleDb.OleDbConnection(myConnString) 'Create a new connection
myConnection.Open() ' Open Connection
End Sub

Business Function
VB6

Function GetCustName(iCol As Integer, dDocket As Double) As String
Dim sName As String, rsName As adodb.Recordset
sName = "SELECT CustName FROM [Orders(m)] WHERE OrderID = " & dDocket & ""
Set rsName = New adodb.Recordset
rsName.Open sName, objAccessConnection, adOpenKeyset, adLockReadOnly
If rsName.EOF Then
    GetCustName = "False"
Else
    GetCustName = rsName!CustName
End If
rsName.ActiveConnection = Nothing
End Function

.NET

Function GetCustNameNet(ByRef iCol As Short, ByRef dDocket As Double) As String
Dim sNameN As String = "SELECT CustName FROM [Orders(m)] WHERE OrderID = " & dDocket & ""
Dim rsNameN As New System.Data.OleDb.OleDbCommand(sNameN, myconnection) ' Stuck here
rsNameN.Open(sNameN, objAccessConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly)
        If rsNameN.EOF Then
            GetCustNameNet = "False"
        Else
            GetCustNameNet = rsNameN.Fields("CustName").Value
        End If
End Function

I got stuck on the connection to the database in the .NET version.

Can any one point me in the right direction.

[Pirate]

I'm using this little function created by me .I'll start you .It opens , saves data against access db.

Public Class Class1
    '########################################################
    'Category :Database
    'Subject : Save Data to Access Database
    '########################################################
 
    '~~~~~~~~~~~~~~Created by Pirate at [url]WWW.VBFORUMS.COM[/url] <[url]http://WWW.VBFORUMS.COM[/url]> ~~~~~~~~~~~~
   
    'Libraries needed for running this Code
        Imports System.Data
        Imports System.Data.OleDb
 
    'open Connection to the Database with Password
    Dim MyPath As String = Application.StartupPath & "\__mydb__.mdb"
    Dim MyPassword As String = "passme"
    Dim StrSQL As String = "Select * From MyTable"
    Dim MyConnection As New 
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & 
MyPath & ";Jet OLEDB:Database Password=" & MyPassword)
        MyConnection.Open()
 
    'This Saves data to a DataSet and then uses update method
    'against Database Source File
    Dim MyCommand As New OleDbCommand(StrSQL, MyConnection)
    Dim MyDataset As DataSet = New DataSet()
    Dim MyAdapter As New OleDb.OleDbDataAdapter(StrSQL, MyConnection)
 
 
        MyAdapter.Fill(MyDataset, "MyTable")
    Dim MyDataRow As DataRow = MyDataset.Tables("MyTable").NewRow
    'Fill the data in Four Columns in the Database (Fields)
        MyDataRow("1_Column") = Textbox1.Text
        MyDataRow("2_Column") = Textbox2.Text
        MyDataRow("3_Column") = Textbox3.Text
        MyDataRow("4_Column") = Textbox4.Text
        MyDataset.Tables("MyTable").Rows.Add(MyDataRow)
        MyAdapter.Update(MyDataset, "MyTable")
        MessageBox.Show("Data Saved..", "Saved",
 
    'if you want to write the data as well to XML file then you
    'include(this)before update method
        MyDataset.WriteXml(Application.StartupPath & "\_MyXMLFile_.XML")
    End Sub
 
End Class

enjoy !

[Mr.No]

How about this?

.NET Data connection

Public Function getDBConnection () as OleDbConnection
Dim myConnString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & VB6.GetPath & "\ConcorddB.mdb;Jet OLEDBatabase Password=" ' Put here the connection string
Dim myConnection As New System.Data.OleDb.OleDbConnection(myConnString) 'Create a new connection
myConnection.Open() ' Open Connection
   Return myConnection
End Function

.NET

Function GetCustNameNet(ByRef iCol As Short, ByRef dDocket As Double) As String
Dim sNameN As String = "SELECT CustName FROM [Orders(m)] WHERE OrderID = " & dDocket & ""
' Dim rsNameN As New System.Data.OleDb.OleDbCommand(sNameN, myconnection) ' Stuck here
 
Dim myconn as OLEDBConnection
 
myconn = getDBConnection()
 
Dim rsNameN As New System.Data.OleDb.OleDbCommand(sNameN, myconn) 
 
rsNameN.Open(sNameN, objAccessConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly)
        If rsNameN.EOF Then
            GetCustNameNet = "False"
        Else
            GetCustNameNet = rsNameN.Fields("CustName").Value
        End If
 
myconn.close() ' Don't forget to close connection
 
End Function

[ajirwin]

Thank you Mr.No that was just what I was looking for. Sorry Pirate, I had already read what you had and it had got me started.