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:D .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 WWW.VBFORUMS.COM <http://WWW.VBFORUMS.COM> ~~~~~~~~~~~~

'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.