Access DB + Form + Display + very new to VB.NET

[mizzy]

Hi there,

I'm very new to VB.net.

Can some one tell me how can I access an Access database and display the information in this database on a form.

(I did this using VB 6.0 and it was a piece of cake, however .net seems to be completly different)

Thanks for your help,

 

[santosh1]

Here's one way using Oledb connection

Protected Const ConnectionString as string =
"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Access.mdb;


Private Sub ShowProducts()


Dim strID As String

Try



' Build Select statement to query product information from the products
' table
strSQL = "SELECT ProductID, " & _
" ProductName, " & _
" QuantityPerUnit, " & _
" UnitPrice, " & _
" UnitsInStock, " & _
" UnitsOnOrder, " & _
" ReorderLevel, " & _
" Discontinued, " & _
" SupplierID, " & _
" CategoryID " & _
"FROM Products " & _
"WHERE ProductID = ' & 1 & "'"


Dim cnA As OleDbConnection = New OleDbConnection(ConnectionString)
cnA.Open()
Dim myCommand As OleDbCommand = New OleDbCommand(strSQL, cnA)
Dim Reader As OleDbDataReader = myCommand.ExecuteReader()

If Reader.Read() Then
' Populate form with the data
txtProductID.Text = Reader.Item("ProductID&quot.ToString()
txtProductName.Text() = Reader.Item("ProductName&quot.ToString()
txtQtyPerUnit.Text() = Reader.Item("QuantityPerUnit&quot.ToString()
txtUnitPrice.Text() = Reader.Item("UnitPrice&quot.ToString()
txtUnitsInStock.Text() = Reader.Item("UnitsInStock&quot.ToString()
txtUnitsOnOrder.Text() = Reader.Item("UnitsOnOrder&quot.ToString()
txtReorderLevel.Text() = Reader.Item("ReorderLevel&quot.ToString()
chkDiscontinued.Checked = CType(Reader.Item("Discontinued&quot, Boolean)
strID = Reader.Item("SupplierID&quot.ToString()

End If
Reader.Close()
cnA.Close()
myCommand.Dispose()

Catch e As OledbException
MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error&quot

Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error&quot
End Try
End Sub

 

[santosh1]

Here's one way using Oledb connection

Protected Const ConnectionString as string =
"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Access.mdb;"


Private Sub ShowProducts()


Dim strID As String

Try



' Build Select statement to query product information from the products
' table
strSQL = "SELECT ProductID, " & _
" ProductName, " & _
" QuantityPerUnit, " & _
" UnitPrice, " & _
" UnitsInStock, " & _
" UnitsOnOrder, " & _
" ReorderLevel, " & _
" Discontinued, " & _
" SupplierID, " & _
" CategoryID " & _
"FROM Products " & _
"WHERE ProductID = ' & 1 & "'"


Dim cnA As OleDbConnection = New OleDbConnection(ConnectionString)
cnA.Open()
Dim myCommand As OleDbCommand = New OleDbCommand(strSQL, cnA)
Dim Reader As OleDbDataReader = myCommand.ExecuteReader()

If Reader.Read() Then
' Populate form with the data
txtProductID.Text = Reader.Item("ProductID&quot.ToString()
txtProductName.Text() = Reader.Item("ProductName&quot.ToString()
txtQtyPerUnit.Text() = Reader.Item("QuantityPerUnit&quot.ToString()
txtUnitPrice.Text() = Reader.Item("UnitPrice&quot.ToString()
txtUnitsInStock.Text() = Reader.Item("UnitsInStock&quot.ToString()
txtUnitsOnOrder.Text() = Reader.Item("UnitsOnOrder&quot.ToString()
txtReorderLevel.Text() = Reader.Item("ReorderLevel&quot.ToString()
chkDiscontinued.Checked = CType(Reader.Item("Discontinued&quot, Boolean)
strID = Reader.Item("SupplierID&quot.ToString()

End If
Reader.Close()
cnA.Close()
myCommand.Dispose()

Catch e As OledbException
MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error&quot

Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "General Error&quot
End Try
End Sub

 

[mizzy]

Hi Santosh1,

Thankyou very much for your assistance. Your code looks the business. However, do you think that there is an easier way to do this. In VB6 this was a drag and drop.
(I'm still trying to get the hang of programming)

Again thanks for your help,

 

[PankajBanga]

It is all about fundamentals, programming language is a matter of choice.

The sweetest way of display data on a form would be using Windows Forms DataGrid control. Follow the simple sample to populate DataGrid with data from access database.

This sample refers to the System.Data.OleDb Class Library namespaces and NWIND.mdb (access database).

Imports System.Data.OleDb

Dim strConn As String
Dim strSQL As String
Dim Conn As OleDbConnection

Dim objDA As OleDbDataAdapter
Dim objDS As New DataSet()

Try
'MAKE SURE TO CHANGE THE PATH OF NWIND.mdb AS PER ON YOUR MACHINE.
'Build the Connection strings.
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.mdb"

'Pass the Connection string to OleDbConnection object.
Conn = New OleDbConnection(strConn)

'Build the SQL strings.
strSQL = "SELECT EmployeeID, FirstName, LastName " & _
"FROM Employees "

'Initialize the OleDbDataAdapter with SQL and Connection string,
'and then use the OleDbAdapter to fill the DataSet with data.
objDA = New OleDbDataAdapter(strSQL, Conn)
objDA.Fill(objDS, "Employees&quot

'Bind the DataSet to DataGrid.
DataGrid1.SetDataBinding(objDS, "Employees&quot

Catch Excep As System.Exception
MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

If you want to learn more about DataGrid Class, visit the following link

http://msdn.microsoft.com/library/d...fSystemWebUIWebControlsDataGridClassTopic.asp

Email: pankajmsm@yahoo.com

 

[aharris2693]

PankajBanga,
I have done quite a few applications using asp, but I am now trying to teach myself VB.net, and am finding that it is a little more different than I thought. I have used your above code to fill a datagrid(datagrid1), but I get the error message, "Object Reference not set to an instance of an object." Being as unversed as I am in VB.Net, I have no idea what this means.
I realize this is kind of ambiguos, but any help would be greatly appreciated.

 

[mizzy]

Hi PankajBanga,

I was very new to VB.net(and still am) when I wrote this request.
The only way I learned how to do this was to purchase a book.
I purchased "Visual Basic.NET for Dummies". Its not great but I learned about databases in about two days by completing the exercises in the book.

The chapter in the book covering databases is about 150 pages long.

Regards,

 

[younguser]

I got "Object Reference not set to an instance of an object" message sometime. Try load code avove in form_load event.