Access DB and ASP/VB website integration

[samZin]

Hi there

i am just new to this forum and hoping to get some geeks to help us out

Here is the problem

We have a website on our own server (win 2003 sever)
website www.jpsequipment.com
We decided to transfer all data including the database to a different webhosting company server.We did transfer all files and the website.
Website is up and runing but not the database.
DB is in MS access
The current location of the website is with the database problem is
http://www.jpsequipment.netfirms.com/jpsequipment.com

I did all researchs on the net but it looks like whatever I do it comes with an error

Do we have to start all over again to design new web siteÉ

Here is one of the pages that has a wrong string

Hoping of helpppppppp
sem

+++++++++++++++++
Imports System.Data

Partial Class list
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Call LoadList()
End Sub

Protected Sub LoadList()
Dim strSql As String = String.Empty
Select Case Request.QueryString("type")
Case 0 ' Groups
strSql = "SELECT Products.ProdID, Products.TypeID, Categories.Category AS SubCategory, Products.Description FROM Products LEFT JOIN Categories ON Products.CatID = Categories.CatID WHERE Webable = True AND Products.ProdID IN (SELECT ProdID FROM GroupListingsIndex WHERE GroupID = " & Request.QueryString("id") & ") ORDER BY TypeID, Category, Products.ProdID"
litSubTitle.Text = "<h3>" & GetGroupListing(Request.QueryString("id")) & "</h3>"
Case 1 ' Machines
strSql = "SELECT Products.ProdID, Products.TypeID, SubCategories.SubCategory, Products.Description FROM Products LEFT JOIN SubCategories ON Products.SubCatID = SubCategories.SubCatID WHERE Webable = True AND Products.TypeID = " & Request("type") & " AND Products.CatID = " & Request.QueryString("id") & " ORDER BY SubCategory, Products.ProdID"
litSubTitle.Text = "<h3>" & GetCategory(Request.QueryString("id")) & "</h3>"
Case 2 ' Tanks
strSql = "SELECT Products.ProdID, Products.TypeID, SubCategories.SubCategory, Products.Description, TankSizes.Gallons FROM (Products LEFT JOIN SubCategories ON Products.SubCatID = SubCategories.SubCatID) INNER JOIN TankSizes ON Products.ProdID = TankSizes.ProdID WHERE Webable = True AND Products.TypeID = " & Request("type") & " AND Products.CatID = " & Request("id")
If Not Session("TankSizeRange") Is Nothing Then
strSql &= " AND Gallons " & Session("TankSizeRange")
End If
strSql &= " ORDER BY TankSizes.Gallons, SubCategory, Products.ProdID"
litSubTitle.Text = "<h3>Tank: " & GetCategory(Request.QueryString("id")) & "</h3>"
End Select

Dim da As New OleDb.OleDbDataAdapter(strSql, "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=722524464; Data Source=" & Server.MapPath("Database/database.mdb") & ";")
Dim dt As New DataTable

Try
da.Fill(dt)
Catch ex As Exception
litMessage.Text = "<em>An error has occured. Please try again later.</em>"
da.SelectCommand.Connection.Close()
Exit Sub
End Try

dt.Columns.Add("ImgPath")

For Each dr As DataRow In dt.Rows
Dim file As New IO.FileInfo(Server.MapPath("Pictures\Thumbs\") & dr("ProdID") & ".0.jpg")

If file.Exists Then dr("ImgPath") = "Pictures\Thumbs\" & file.Name Else dr("ImgPath") = "images/noimage.gif"

dr("Description") = dr("Description").ToString.Replace(vbCrLf, "<br />")
Next

'Call RenderList(dt)
repList.DataSource = dt
repList.DataBind()
End Sub

Protected Function GetCategory(ByVal pintID As Integer) As String
Dim cmd As New OleDb.OleDbCommand("SELECT Category FROM Categories WHERE CatID = " & pintID)
cmd.Connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=722524464; Data Source=" & Server.MapPath("Database/database.mdb") & ";")

Try
cmd.Connection.Open()
GetCategory = cmd.ExecuteScalar.ToString
Catch ex As Exception
GetCategory = Nothing
Finally
cmd.Connection.Close()
End Try
End Function

Protected Function GetGroupListing(ByVal pintID) As String
Dim cmd As New OleDb.OleDbCommand("SELECT GroupListing FROM GroupListings WHERE GroupID = " & pintID)
cmd.Connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=722524464; Data Source=" & Server.MapPath("Database/database.mdb") & ";")

Try
cmd.Connection.Open()
GetGroupListing = cmd.ExecuteScalar.ToString
Catch ex As Exception
GetGroupListing = Nothing
Finally
cmd.Connection.Close()
End Try
End Function

Protected Sub RenderList(ByVal pDt As DataTable)
Dim sb As New Text.StringBuilder

sb.Append("<ul id=""ThumbList"">")
sb.Append(vbCrLf)

For Each dr As DataRow In pDt.Rows
sb.Append("<li><a href=""product.aspx?id=")
sb.Append(dr.Item("ProdID"))
sb.Append(""">")

sb.Append("<div class=""ThumbDiv""><img src=""")
sb.Append(dr.Item("ImgPath"))
sb.Append(""" /><span>Click for more info</span></div>")

sb.Append("<p><strong>#")
sb.Append(dr.Item("ProdID"))
sb.Append(" - ")
If dr.Item("TypeID") = "2" Then sb.Append("Tank - ")
sb.Append(dr.Item("SubCategory"))
sb.Append("</strong></p>")

sb.Append("<p>")
sb.Append(dr.Item("Description"))
sb.Append("</p>")

sb.Append("<br class=""clear"" /></a></li>")
sb.Append(vbCrLf)
Next

sb.Append("</ul>")

'litList.Text = sb.ToString

'<li><a href="product.aspx?id=<%#DataBinder.Eval(Container.DataItem, "ProdID")%>">
'<div class="ThumbDiv"><img src="<%#DataBinder.Eval(Container.DataItem, "ImgPath")%>" /><span>Click for more info</span></div>
'<p><strong>#<%#DataBinder.Eval(Container.DataItem, "ProdID")%> - <%#DataBinder.Eval(Container.DataItem, "SubCategory")%></strong></p>
'<p><%#DataBinder.Eval(Container.DataItem, "Description")%></p>
'<br class="clear" />
'</a></li>
End Sub
End Class

[SheSaidImaPregy]

Next time try to put all your code in code blocks using [ code ] and [ /code ]. You should also end up changing a bunch of stuff to protect yourself from SQL injection, that could destroy your database anyway. Instead of pulling queries directly from the querystring, you should put them into a parameter and add them that way. Regardless, you would never have to redesign or redevelop a whole new site. The worse you would have to do is create the database all over again in your current web host. If you still have your old host, connect to your database and export it. Then, under your new host, import the SQL file to create your database, fields, and all your data once again.

Sorry, didn't see it was an Access Database. You will need to update your connection to the database file. From what it looks like, it needs to be in this directory: "db/Database/" and labeled database.mdb (db/Database/database.mdb). Make sure that "db" is a folder right in your root directory, and inside "db" there is a folder labeled "Database". Inside the sub-folder "Database", put your mdb file labeled "database". The directory to your mdb should be:

db/Database/database.mdb

However, you should get someone to do your custom errors so people cannot see all your code. Also, you should get someone to update your code here and there to make it more stable.