Connecting To A Database.

Hi all:

I'm trying to connect to a access database with vb.net, i have a fully functioning site i just need to connect a few pages that i've made in vb.net to my database. I have a register page to be able to register on the site which will be stored in a database then 2 login pages one user login which stores user name & password and one admin login which has only one password and username stored on a database. also a shopping cart where the user can select a product and it adds the cost and name of the product to a database. I'm just a bit stuck in how to connect them all together.

here are my codes:

This code is for the user login and admin login, just wondering also will i need to create another vb.net page seperate to the user login that hold the admin login and password?

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=" & AppDomain.CurrentDomain.BaseDirectory & "App_Data\Business.mdb")
	Dim numberattempts As Integer
	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
			 
		If Not IsPostBack Then ' if its not the first time
			ViewState.Add("attempts", 0) ' set attempts = 0
		End If
	End Sub
	
	Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
		numberattempts = ViewState("attempts") + 1
		ViewState.Add("attempts", numberattempts)
		txtError.Text = numberattempts
		If numberattempts > 3 Then
			Response.Redirect("index.htm")
		End If
		
		Dim strGet As String
		strGet = "select * from Security where ct_username = '" & txtusername.Text & "'"
		Dim daValidate As New OleDbDataAdapter(strGet, Conn)
		Dim dsValidate As New DataSet()
		
		daValidate.Fill(dsValidate, "dsItems")
		
		Try
			If txtusername.Text = dsValidate.Tables("dsItems").Rows(0).Item("ct_username") Then
				If txtpassword.Text = dsValidate.Tables("dsItems").Rows(0).Item("ct_password") Then
					Session("Login") = txtusername.Text
					Response.Redirect("MaintainProductCoded.aspx", False)
					'False required otherwise a catch error is tiggered
				Else
					txtError.Text = "Password Invalid"
				End If
			End If
		Catch
			txtError.Text = "SignOn Not Found"
		End Try
		
	End Sub
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
	<title>Untitled Page</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
	 <asp:Label ID="Label1" runat="server" Text="Username" Width="65px"></asp:Label>
	<asp:TextBox ID="txtusername" runat="server"></asp:TextBox><br />
	 <asp:Label ID="Label2" runat="server" Text="Password" Width="65px"></asp:Label>
	 <asp:TextBox ID="txtpassword" runat="server"></asp:TextBox> <br />
	<br />
	  <asp:TextBox ID="txtError" runat="server" Width="166px"></asp:TextBox><br />
	<br />
	<asp:Button ID="btnLogin" runat="server" Text="Login" />
	</div>
	</form>
</body>
</html>

once the admin logs in they will be directed to this page where they can edit the product information

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 <script runat="server">
	 
	 Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & AppDomain.CurrentDomain.BaseDirectory & "APP_Data\Business.mdb")
	Dim intRowNumber As Int16
	Dim strPhoto As String
	
	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
		
		If Not IsPostBack Then
			txtUser.Text = Session("logon")
			ViewState.Add("RowNumber", -1)
			GetNextRecord()
		End If
		txtMessage.Text = ""
	End Sub
	
	Public Sub GetNextRecord()
		Dim strGet As String
		strGet = "select * from products"
		Dim daValidate As New OleDbDataAdapter(strGet, Conn)
		Dim dsValidate As New DataSet()
		
		 daValidate.Fill(dsValidate, "dsItems")
		
		intRowNumber = ViewState("RowNumber") + 1
		
		Try
			txtProductID.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("ProductID")
			txtDescription.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Description")
			txtPrice.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Price")
			txtStockLevel.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("stocklevel")
			txtPhoto.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			strPhoto = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			imgPhoto.ImageUrl = strPhoto
			
		Catch ex As Exception
			
			txtMessage.Text = "Could not go further forward"
			intRowNumber = intRowNumber - 1
			
		Finally
			ViewState.Add("RowNumber", intRowNumber)
			
		End Try
	End Sub
   
	 Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		 GetNextRecord()
	 End Sub
	 
	 Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		 Dim strGet As String
		 strGet = "select * from Products"
		 Dim daValidate As New OleDbDataAdapter(strGet, Conn)
		 Dim dsValidate As New DataSet()
		 
		 daValidate.Fill(dsValidate, "dsItems")
		 
		 intRowNumber = ViewState("RowNumber") - 1
		 
		 Try
			 txtProductID.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("ProductID")
			 txtDescription.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Description")
			 txtPrice.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Price")
			 txtStockLevel.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("stocklevel")
			 txtPhoto.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			 strPhoto = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			 imgPhoto.ImageUrl = strPhoto
			
		 Catch ex As Exception
			
			 txtMessage.Text = "Could not go further forward"
			 intRowNumber = intRowNumber - 1
			
		 Finally
			 ViewState.Add("RowNumber", intRowNumber)
			
		 End Try
	 End Sub
	
	Protected Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		Dim strGet As String
		strGet = "select * from Products"
		Dim daValidate As New OleDbDataAdapter(strGet, Conn)
		Dim dsValidate As New DataSet()
		
		daValidate.Fill(dsValidate, "dsItems")
		
		intRowNumber = 0
		Try
			txtProductID.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("ProductID")
			txtDescription.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Description")
			txtPrice.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Price")
			txtStockLevel.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("stocklevel")
			txtPhoto.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			strPhoto = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			imgPhoto.ImageUrl = strPhoto
			
		Catch ex As Exception
			txtMessage.Text = "No records to display"
			
		Finally
			ViewState.Add("RowNumber", intRowNumber)
			
		End Try
	End Sub
	
	Protected Sub btnLast_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		Dim strGet As String
		strGet = "select * from Products"
		Dim daValidate As New OleDbDataAdapter(strGet, Conn)
		Dim dsValidate As New DataSet()
		
		daValidate.Fill(dsValidate, "dsItems")
		
		intRowNumber = dsValidate.Tables("dsItems").Rows.Count - 1
		Try
			txtProductID.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("ProductID")
			txtDescription.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Description")
			txtPrice.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Price")
			txtStockLevel.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("stocklevel")
			txtPhoto.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			imgPhoto.ImageUrl = strPhoto
			
		Catch ex As Exception
			
			txtMessage.Text = "No records to display"
		Finally
			ViewState.Add("RowNumber", intRowNumber)

		End Try
	End Sub
	
	
	 Protected Sub btnNew_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		 txtProductID.Text = ""
		 txtDescription.Text = ""
		 txtPrice.Text = ""
		 txtStockLevel.Text = ""
		 txtPhoto.Text = ""
		 imgPhoto.ImageUrl = ""
	 End Sub

	 Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		 If txtDescription.Text = "" Then
			 txtMessage.Text = "Please enter a Description"
			 txtDescription.Focus()
			 Exit Sub
		 ElseIf txtPrice.Text = "" Then
			 txtMessage.Text = "Please enter a Price"
			 txtPrice.Focus()
			 Exit Sub
			 'etc
		 End If
		 'Note if we format the price field above for currency, we will need to remove
		 'the $ sign using the Cdec function before we save the data to the database
		 Dim sqlCommand As New OleDb.OleDbCommand
		 sqlCommand.Connection = Conn
		 sqlCommand.Connection.Open()
		 
		 Try
			 sqlCommand.CommandText = "UPDATE Products SET" & _
			 "Description = '" & txtDescription.Text & "'," & _
			 "Price = " & txtPrice.Text & "," & _
			 "StockLevel = " & txtStockLevel.Text & "," & _
			 "Photo = '" & txtPhoto.Text & "'" & _
			 "WHERE ProductID = " & txtProductID.Text
			 
			 sqlCommand.ExecuteNonQuery()
			 txtMessage.Text = "Entry Successfully Changed"
			 
		 Catch ex As Exception
			 sqlCommand.CommandText = "insert into Products " & _
			 "(Description,Price,StockLevel,Photo) Values " & "('" & _
			 txtDescription.Text & "'," & _
			 txtPrice.Text & "," & _
			 txtStockLevel.Text & ",'" & _
			 txtPhoto.Text & "')"
			 
			 sqlCommand.ExecuteNonQuery()
			 txtMessage.Text = "Entry Succesfully Added"
			 

		 End Try
		 sqlCommand.Connection.Close()
	 End Sub
	 
	 Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		 Dim intRowsDeleted As Integer
		 Dim sqlCommand As New OleDbCommand
		 sqlCommand.Connection = Conn
		 sqlCommand.CommandText = "Delete from Products where ProductID = " & txtProductID.Text
		 sqlCommand.Connection.Open()
		 Try
			 intRowsDeleted = sqlCommand.ExecuteNonQuery
			 If intRowsDeleted = 0 Then
				 txtMessage.Text = "No entry found"
			 Else
				 txtMessage.Text = "Entry successfully deleted"
			   
			 End If
			
		 Catch ex As Exception
			 txtMessage.Text = "Problems with Deleting Record:" & Err.Description
			
		 End Try
		 sqlCommand.Connection.Close()
	 End Sub

	 Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		 Dim strGet As String
		 strGet = "select * from Products WHERE description like '%" & txtSearch.Text & "%'"
		 Dim daValidate As New OleDbDataAdapter(strGet, Conn)
		 Dim dsValidate As New DataSet()
		
		 daValidate.Fill(dsValidate, "dsItems")
		
		 intRowNumber = 0
		 Try
			 txtProductID.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("ProductID")
			 txtDescription.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Description")
			 txtPrice.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Price")
			 txtStockLevel.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("stocklevel")
			 txtPhoto.Text = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			 strPhoto = dsValidate.Tables("dsitems").Rows(intRowNumber).Item("Photo")
			 imgPhoto.ImageUrl = strPhoto
			
		 Catch ex As Exception
			 txtMessage.Text = "No records to display"
			
		 Finally
			 ViewState.Add("RowNumber", intRowNumber)
		 End Try
	 End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
	<title>Untitled Page</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:Label ID="User" runat="server" Text="User"></asp:Label>
		<asp:TextBox ID="txtUser" runat="server"></asp:TextBox><br />
		<asp:Label ID="Label2" runat="server" Text="ProductID"></asp:Label>
		<asp:TextBox ID="txtProductID" runat="server"></asp:TextBox><br />
		<asp:Label ID="Label3" runat="server" Text="Description"></asp:Label>
		<asp:TextBox ID="txtDescription" runat="server"></asp:TextBox><br />
		<asp:Label ID="Label4" runat="server" Text="Price"></asp:Label>
		<asp:TextBox ID="txtPrice" runat="server"></asp:TextBox><br />
		<asp:Label ID="Label5" runat="server" Text="Stocklevel"></asp:Label>
		<asp:TextBox ID="txtStockLevel" runat="server"></asp:TextBox><br />
		<asp:Label ID="Label6" runat="server" Text="FileName"></asp:Label>
		<asp:TextBox ID="txtPhoto" runat="server"></asp:TextBox><br />
		<br />
		<asp:Image ID="imgPhoto" runat="server" Height="104px" Width="136px" ImageUrl="~/Images/photo2.jpg" /><br />
		<br />
		<asp:TextBox ID="txtMessage" runat="server"  Width="328px"></asp:TextBox><br />
		<br />
		<asp:Button ID="btnFirst" runat="server" onclick="btnFirst_Click" Text="First" />
		<asp:Button ID="btnNext" runat="server" Text="Next" onclick="btnNext_Click" />
		<asp:Button ID="btnPrevious" runat="server" Text="Previous" onclick="btnPrevious_Click" />
		<asp:Button ID="btnLast" runat="server" Text="Last" onclick="btnLast_Click"/><br />
		<asp:Button ID="btnNew" runat="server" Text="New" onclick="btnNew_Click" />
		<asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
		<asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" /><br />
		<asp:Button ID="btnSearch" runat="server" Text="Search" onclick="btnSearch_Click" />
		<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox><br />
		<a href="index.htm">Home</a>
		</div>
	</form>
</body>
</html>

This code is for the shopping cart

<%@ Page Language="VB" MaintainScrollPositionOnPostback="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

	Protected Sub GridView1_SelectedIndexChanged1(ByVal sender As Object, ByVal e As System.EventArgs)
		Dim sngProductid, sngPrice As Single
		Dim txtDescription, txtList As String
		
		txtTotalPrice.Text = Format(GridView1.SelectedRow.Cells(3).Text + CInt(txtTotalPrice.Text), "Currency")
		
		sngProductid = GridView1.SelectedRow.Cells(1).Text()
		sngPrice = GridView1.SelectedRow.Cells(2).Text()
		txtDescription = Gridview1.SelectedRow.Cells(3).Text()
		
		txtList = sngProductid & " " & txtDescription & " " & sngPrice
		txtMessage.Text = txtMessage.Text & vbCrLf & txtList
	End Sub
	
	Protected Sub btnList_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		txtTotalPrice.Text = "0"
		txtMessage.Text = ""
	End Sub
		
	Protected Sub btnSubmitOrder_Click(ByVal sender As Object, ByVal e As System.EventArgs)
		'Note that you need to write the order and customer details here
	End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
	<title>Select Products</title>
</head>

<body>
	<form id="form1" runat="server">
	<div>
		 
	<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
	DataKeyNames="Product1" DataSourceID="AccessDataSource1"
	onselectedIndexChanged="GridView1_SelectedIndexChanged1"
	Width="768px" AllowSorting="True" PageSize="4">
	<Columns>
	<asp:CommandField ShowselectButton="True" />
	<asp:BoundField DataField="ProductId" HeaderText="ProductId" ReadOnly="True" SortExpression="Description" />
	<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" DataFormatString="{0:C}"
	ApplyFormatInEditMode="True" HtmlEncode="False" />
	<asp:BoundField DataField="StockLevel" HeaderText="StockLevel" SortExpression="StockLevel" />
	<asp:ImageField DataImageUrlField="Photo" HeaderText="Image">
	</asp:ImageField>
	</Columns>
	</asp:GridView>
	<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Business.mdb"
	DeleteCommand="DELETE FROM [Products] WHERE [ProductId] = ?" InsertCommand="INSERT INTO [Products] ([ProductId], [Description], [Price], [StockLevel], [Photo], [PhotoImage]) VALUES (?, ?, ?, ?, ?, ?,)"
	SelectCommand="SELECT * FROM [Products]" UpdateCommand="UPDATE [Products] SET [Description = ?, [Price] = ?, [StockLevel] = ?, [Photo] = ?, [PhotoImage = ? WHERE [ProductId] = ?">
	<DeleteParameters>
	<asp:Parameter Name="ProductId" Type="String" />
	</DeleteParameters>
	<UpdateParameters>
	<asp:Parameter Name="Description" Type="String" />
	<asp:Parameter Name="Price" Type="Decimal" />
	<asp:Peermeter Name="StockLevel" Type="Int32" />
	<asp:Parameter Name="Photo" Type="String" />
	<asp:Parameter Name="PhotoImage" Type="Object" />
	<asp:Parameter Name="ProductId" Type="string" />
	</UpdateParameters>
	<InsertParameters>
	<asp:Parameter Name="ProductId" Type="String" />
	<asp:Parameter Name="Description" Type="String" />
	<asp:Parameter Name="Price" Type="Decimal" />
	<asp:Parameter Name="StockLevel" Type="Int32" />
	<asp:Parameter Name="Photo" Type="String" />
	<asp:Parameter Name="PhotoImage" Type="Object" />
	</InsertParameters>
	</asp:AccessDataSource>
		  <br />
	 <asp:Label ID="Label2" runat="server" Text="Orders"></asp:Label>
	<asp:TextBox ID="txtMessage" runat="server" TextMode="MultiLine"></asp:TextBox>
	  <asp:Button ID="btnList" runat="server" onclick="btnList_Click" Text="Clear Orders" /><br />
	  <br />
	<br />
	 <asp:Label ID="Label1" runat="server" Text="Total Price"></asp:Label>
	  
	<asp:TextBox ID="txtTotalPrice" runat="server" Text="Total Price"></asp:TextBox>
	<br />
	<asp:Button ID="btnSubmitOrder" runat="server" onclick="btnSubmitOrder_Click" Text="SubmitOrder" />
	  <br />
				
	  
	<br />
	<a href="index.html">Home</a>  <br />
	<br />
	 
	<br />
	</div>
	</form>
</body>
</html>

This is the code for the products page to view the products page without the shopping cart.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
	<title>Untitled Page</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
			AutoGenerateColumns="False" CellPadding="4" DataKeyNames="ProductID" DataSourceID="AccessDataSource1"
			ForeColor="#333333" GridLines="None">
			<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
			<Columns>
				<asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"
					ReadOnly="True" SortExpression="ProductID" />
				<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
				<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
				<asp:BoundField DataField="Stocklevel" HeaderText="Stocklevel" SortExpression="Stocklevel" />
			</Columns>
			<RowStyle BackColor="#EFF3FB" />
			<EditRowStyle BackColor="#2461BF" />
			<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
			<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
			<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
			<AlternatingRowStyle BackColor="White" />
		</asp:GridView>
		<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/business.mdb"
			SelectCommand="SELECT * FROM [Products]"></asp:AccessDataSource>
		<br />
		<a href="index.htm">Home</a></div>
	</form>
</body>
</html>

my database is called db2.mdb and my linking databases are:

"admin" for the admin login and password.

"register" for the public to sign up and resister.

"login" for public to login directed view the shopping cart page and select a product.

"events" showing the products before registering and loging in.

hopefuly you can understand what im trying to acheive and hoping you can give me some help on how to connect all this together.

Thank You

Brett.

connecting to a database using vb.net and access.

0 Replies - 1614 Views - Last Post: 14 June 2008 - 10:32 PM Rate Topic:

#1 Randall

connecting to a database.

General Discussion

Programming Help

Web Development

Follow & Share

Give It A Try!

VB.NET Tutorials

Reference Sheets

VB.NET Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing