Databases in .NET

[sjn78]

Hi,

I am new to VB.Net and having a few troubles switching from VB6.

I have an access database and want to use it in the app.

What is the best way to connect to it, I mean what method.
I have tried many ways and have not successfully done it yet. I have looked at examples, help pages and nothing seems to work for me.

Could someone give me an idea on what to do. I have managed to create all of the data apdapters and those things needed to connect to a db.

I think the main problem is trying to populate the dataset.

If someone could give me a bit of code that works so I could see where I am going wrong, I would be very grateful.

Thanks for any help.

Steve

 

[chiph]

In order to get a set of rows back, all you need is a OleDbConnection object, an OleDbCommand, and a OleDbDataReader.

Dim MyConnection as OleDbConnection
Dim MyCommand as OleDbCommand
Dim MyDataReader as OleDbDataReader

Try
   Dim MyConnectString as string

   MyConnectString = "your connection string goes here"
   MyConnection = New OleDbConnection(MyConnectString)

   Dim MySql as string
   MySql =  " SELECT au_lname, au_fname"
   MySql += " FROM dbo.authors"
   MyCommand = New OleDbCommand()
   MyCommand.CommandType = CommandType.Text
   MyCommand.CommandText = MySql
   MyCommand.Connection = MyConnection

   MyDataReader = MyCommand.ExecuteReader

   While (MyDataReader.Read())
      Console.WriteLine MyDataReader.GetString(0) & " " & MyDataReader.GetString(1)
   Wend

Catch (Exception ex)
   ' Do something with error

Finally
   If Not MyDataReader Is Nothing Then
      MyDataReader.Close()
      MyDataReader = Nothing
   End If
   If Not MyCommand Is Nothing Then
      MyCommand = Nothing
   End If
   If Not MyConnection Is Nothing Then
      If MyConnection.State = ConnectionState.Open Then
         MyConnection.Close()
      End If
      MyConnection.Dispose()
   End If
End Try

See: ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.htm
for help on connection strings.

Be aware that I'm a C# guy, so there may be some syntax errors in this code.

Chip H.

 

[sjn78]

Thanks for that.

How do I go about to count the records in a table? In VB6, it was quite easy, but I havent managed to work it out yet for .NET

Also, what object do I use to navigate throught the records.

In VB6 it use to be movenext, movelast etc, using the recordset. What will the equivilant be in .NET

Thanks again

Steve

 

[sjn78]

Could someone look over this code and suggest if there is a better way to access a database.

Dim objDs As DataSet
Dim ObjDa As OleDbDataAdapter
Dim objRow As DataRow
Dim objTable As DataTable

Dim sSQL As String
Dim sName As String

sSQL = "SELECT * FROM COntacts "
objDs = New DataSet()

Dim cnstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\CMdata.mdb;"

ObjDa = New OleDbDataAdapter(sSQL, cnstring)

ObjDa.Fill(objDs, "Contacts&quot

MsgBox(objDs.Tables(0).Rows.Count)

For Each objRow In objDs.Tables(0).Rows
sName = (objRow("Surname&quot.ToString & CrLf)
MsgBox(sName)
Next



I have got it working and wanted to know if there are better ways.

Thanks

Steve

 

[sunaj]

------------------------------------------------------
Dim Conn As New OleDb.OleDbConnection"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\CMdata.mdb;&quot
Conn.Open()
Dim Comm As New OleDb.OleDbCommand("SELECT * FROM Contacts", Conn)
MessageBox.Show("Rec in DB : " & Convert.ToString(Comm.ExecuteScalar))
Conn.Close()
------------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'