ASP.net database connection problem

Hi Experts,

This is my first time using the .net technology and im trying to connect to a database and retrieve data. I have attached a copy of my code. The error message can be found here but I cannot find the problem.
http://www.the-lees.co.uk/blar.aspx


Many Thanks,

Dean.

<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="C#" Debug="true" %>

<script  runat="server">
sub Page_Load;
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=MySQLProv;Data Source=deansghot5722com5233_Deano123.db;User Id=deansghot5722com5233_Deano123; Password=deansghot5722com5233_Lee123;" ))
dbconn.Open()
sql="SELECT * FROM customers"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
</script>

<html>
<body>

<form runat="server">
<asp:Repeater id="customers" runat="server">

<HeaderTemplate>
<table border="1" width="100%">
<tr bgcolor="#b0c4de">
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr bgcolor="#f0f0f0">
<td><%#Container.DataItem("Companyname")%> </td>
<td><%#Container.DataItem("Contactname")%> </td>
<td><%#Container.DataItem("Address")%> </td>
<td><%#Container.DataItem("City")%> </td>
</tr>
</ItemTemplate>

<FooterTemplate>
</table>
</FooterTemplate>

</asp:Repeater>
</form>

</body>
</html>

Open in new window

deanlee17Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TheMozzConnect With a Mentor Commented:
try using the .Net Odbc connection first:

http://www.netadmintools.com/art434.html

It isn't the most optimal way to do it, but in a hosted environment, optimal isn't always available...
0
 
TheMozzCommented:
It appears you have an extra right paren on this line:

dbconn=New OleDbConnection("Provider=MySQLProv;Data Source=deansghot5722com5233_Deano123.db;User Id=deansghot5722com5233_Deano123; Password=deansghot5722com5233_Lee123;" ))

Try:

dbconn=New OleDbConnection("Provider=MySQLProv;Data Source=deansghot5722com5233_Deano123.db;User Id=deansghot5722com5233_Deano123; Password=deansghot5722com5233_Lee123;" )
0
 
deanlee17Author Commented:
Hi,

Thanks for the reply. Tried your suggestion but no change.

Dean
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jppintoCommented:
Try this:

jppinto
Dim myConnectionString As String = "Provider=MySQLProv;Data Source=deansghot5722com5233_Deano123.db;User Id=deansghot5722com5233_Deano123; Password=deansghot5722com5233_Lee123;"

Dim SQL As String = "SELECT * FROM customers;"

Using connection As New SqlConnection(myConnectionString)
   Dim command As New SqlCommand(SQL, connection)
   connection.Open()
   customers.DataSource = command.ExecuteReader()
   customers.DataBind()
   connection.Close()
   command = Nothing
End Using

Open in new window

0
 
deanlee17Author Commented:
Ok so my code is now....

<script  runat="server">
sub Page_Load;

Dim myConnectionString As String = "Provider=MySQLProv;Data Source=deansghot5722com5233_Deano123.db;User Id=deansghot5722com5233_Deano123; Password=deansghot5722com5233_Lee123;"

Dim SQL As String = "SELECT * FROM customers;"

Using connection As New SqlConnection(myConnectionString)
   Dim command As New SqlCommand(SQL, connection)
   connection.Open()
   customers.DataSource = command.ExecuteReader()
   customers.DataBind()
   connection.Close()
   command = Nothing
End Using

end sub
</script>

And i have a different error message: Compiler Error Message: CS1002: ; expected


Thanks
0
 
TheMozzCommented:
Remove the semi-colon after the sub Page_Load statement
0
 
deanlee17Author Commented:
Still have an error :(
0
 
TheMozzCommented:
You have it set to compile as a c# application :) Look at line 2, make sure your project is set to be a vb.net application and compile it again.
0
 
deanlee17Author Commented:
Well spotted Mozz. New error now...
http://www.the-lees.co.uk/blar.aspx
Thanks
0
 
TheMozzCommented:
<%@ Import Namespace="System.Data" %>

In Visual studio there should be a squiggly line under SqlConnection. If you hover your mouse over SqlConnection, you should see a red underscore under the first or last character i think it is. Mouse over that and it will show you a dropdown of choices such as "Imports System.Data", choosing one of those will add that code to the file so you don't need to go digging around for namespaces - very handy thing.
0
 
deanlee17Author Commented:
Hi Mozz,

I am using htmlkit so im not getting any dropdown options lol. Could you explain namespaces and why i need it?

Thanks
0
 
deanlee17Author Commented:
Same error still tho :(
0
 
TheMozzCommented:
Namespaces help organize code into collections of classes:

http://www.startvbdotnet.com/language/

Sorry, try this one:

<%@ Import Namespace="System.Data.SqlClient" %>

Ref: http://www.startvbdotnet.com/ado/sqlserver.aspx
0
 
deanlee17Author Commented:
New error now :)

0
 
TheMozzCommented:
We are getting farther, that is always a good sign!

Check out www.connectionstrings.com for connection string information and drivers for MySql. SqlConnection is specific for MSSQL only....ConnectionStrings.com also has links to drivers and such as well.
0
 
deanlee17Author Commented:
Ok cool. Well I am hosting using Hostinguk.net so I couldnt actually install drivers could I?
0
 
TheMozzCommented:
Most likely not no....check out this page:

http://www.connectionstrings.com/mysql

and check the usage of the MySql.Data.MySqlClient.MySqlConnection class instead of System.data.SqlClient. It should behave in much the same way as the SqlConnection.
0
 
deanlee17Author Commented:
Excellent. Just breaking for dinner....
0
 
deanlee17Author Commented:
New error :(
0
 
TheMozzCommented:
Remember that you need to use the MySql.Data.MySqlClient.MySqlConnection class instead of SqlConnection. The current compilation error comes from removing the Imports System.Data.SqlClient. Try using MySqlConnection instead of SqlConnection. Also remember to put the correct imports statement in the page for MySql.Data.MySqlClient.
0
 
deanlee17Author Commented:
Ok so ive got this far, but im still struggling...
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="vb" Debug="true" %>
<%@ Import Namespace="MySql.Data.MySqlClient.MySqlConnection" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<%@ Import Namespace="mysql.data" %>


<script  runat="server">

sub Page_Load

Dim myConnectionString As String = "Provider=MySQLProv;Data Source=deansghot5722com5233_Deano123.db;User Id=deansghot5722com5233_Deano123; Password=deansghot5722com5233_Lee123;"

Dim SQL As String = "SELECT * FROM customers;"

Using connection As New MySqlConnection(myConnectionString)
   Dim command As New MySqlConnection(SQL, connection)
   connection.Open()
   customers.DataSource = command.ExecuteReader()
   customers.DataBind()
   connection.Close()
   command = Nothing
End Using

end sub
</script>

<html>
<body>

<form runat="server">
<asp:Repeater id="customers" runat="server">

<HeaderTemplate>
<table border="1" width="100%">
<tr bgcolor="#b0c4de">
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr bgcolor="#f0f0f0">
<td><%#Container.DataItem("Companyname")%> </td>
<td><%#Container.DataItem("Contactname")%> </td>
<td><%#Container.DataItem("Address")%> </td>
<td><%#Container.DataItem("City")%> </td>
</tr>
</ItemTemplate>

<FooterTemplate>
</table>
</FooterTemplate>

</asp:Repeater>
</form>

</body>
</html>

Open in new window

0
 
TheMozzCommented:
The hosting company may not have the MySql assemblies loaded on the server. If you have the MySql .dll's you could try copying those up into the bin folder of your web site. If the host company won't allow that, you should also be able to connect to MySql using the default OdbcConnection.
0
 
deanlee17Author Commented:
Damn it's hard to get your head around .net lol
0
 
TheMozzCommented:
It can be yes :) but once you start to get the pattern of things, other things fall into place quicker.
0
 
deanlee17Author Commented:
I may have to go back to square 1 and start again.
0
 
deanlee17Author Commented:
Ok i shall try it when I get home. Cant really do it at work mate.

Thanks.
0
 
jppintoCommented:
Any update?
0
 
deanlee17Author Commented:
Sorry mate, I have to leave this until the weekend. But I shall be working on it over the weekend and will update you guys then.

Unless of course I get time to work on it today or tomorrow at work ;o)
0
 
deanlee17Author Commented:
Ok ive had another go at it, we now have the following error:

http://www.the-lees.co.uk/blar.aspx

Thanks
0
 
TheMozzCommented:
Make sure when you do your imports they are in the form of:

<%@ Imports Namespace="System.ServiceProcess" %>
<%@ Imports Namespace="System" %>
<%@ Imports Namespace="Microsoft.Data.Odbc" %>

You are missing the Namespace="".
0
 
deanlee17Author Commented:
Hmmmm its still finding an error with the imports.
0
 
TheMozzCommented:
Put the Page directive first, and try just Import instead of Imports plural..sorry.
0
 
deanlee17Author Commented:
Ah excellent, now we have a slightly different error. We're getting there :)
0
 
TheMozzCommented:
Does your Import statement for odbc say Microsoft.Data.Odbc or System.Data.Odbc? Check out this demo:

http://www.java2s.com/Code/VB/Database-ADO.net/OdbcConnectionDemo.htm
0
 
deanlee17Author Commented:
I had Microsoft.Data.Odbc, ive now changed it to System.Data.Odbc.

Another error...

http://www.the-lees.co.uk/blar.aspx
0
 
TheMozzCommented:
well, we are getting farther!

Can you post your code as it is right now so we can see what the command variable is?
0
 
deanlee17Author Commented:
Hi Mozz,

Hope you well. Current code attached....

Thanks,
Dean
<%@ Page Language="vb" Debug="true" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.ServiceProcess" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data.Odbc" %>




<script  runat="server">

sub Page_Load

Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=hostname;" & _
"DATABASE=deansghot5722com5233_Deano123.db;" & _
"UID=deansghot5722com5233_Deano123;" & _
"PASSWORD=deansghot5722com5233_Lee123;" & _
"OPTION=3;"
Dim MyConnection As New OdbcConnection(MyConString)
MyConnection.Open()
customers.DataSource = command.ExecuteReader()
customers.DataBind()
connection.Close()
command = Nothing
End Using
MyConnection.Close()

 


end sub
</script>

<html>
<body>

<form runat="server">
<asp:Repeater id="customers" runat="server">

<HeaderTemplate>
<table border="1" width="100%">
<tr bgcolor="#b0c4de">
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr bgcolor="#f0f0f0">
<td><%#Container.DataItem("Companyname")%> </td>
<td><%#Container.DataItem("Contactname")%> </td>
<td><%#Container.DataItem("Address")%> </td>
<td><%#Container.DataItem("City")%> </td>
</tr>
</ItemTemplate>

<FooterTemplate>
</table>
</FooterTemplate>

</asp:Repeater>
</form>

</body>
</html>

Open in new window

0
 
deanlee17Author Commented:
Thats fine. Thanks rkworlds
0
 
Kumaraswamy RCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
All Courses

From novice to tech pro — start learning today.