deanlee17
asked on
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.
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>
ASKER
Hi,
Thanks for the reply. Tried your suggestion but no change.
Dean
Thanks for the reply. Tried your suggestion but no change.
Dean
Try this:
jppinto
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
ASKER
Ok so my code is now....
<script runat="server">
sub Page_Load;
Dim myConnectionString As String = "Provider=MySQLProv;Data Source=deansghot5722com523 3_Deano123 .db;User Id=deansghot5722com5233_De ano123; Password=deansghot5722com5 233_Lee123 ;"
Dim SQL As String = "SELECT * FROM customers;"
Using connection As New SqlConnection(myConnection String)
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
<script runat="server">
sub Page_Load;
Dim myConnectionString As String = "Provider=MySQLProv;Data Source=deansghot5722com523
Dim SQL As String = "SELECT * FROM customers;"
Using connection As New SqlConnection(myConnection
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
Remove the semi-colon after the sub Page_Load statement
ASKER
Still have an error :(
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.
ASKER
<%@ 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.
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.
ASKER
Hi Mozz,
I am using htmlkit so im not getting any dropdown options lol. Could you explain namespaces and why i need it?
Thanks
I am using htmlkit so im not getting any dropdown options lol. Could you explain namespaces and why i need it?
Thanks
ASKER
Same error still tho :(
Namespaces help organize code into collections of classes:
http://www.startvbdotnet.com/language/
Sorry, try this one:
<%@ Import Namespace="System.Data.Sql Client" %>
Ref: http://www.startvbdotnet.com/ado/sqlserver.aspx
http://www.startvbdotnet.com/language/
Sorry, try this one:
<%@ Import Namespace="System.Data.Sql
Ref: http://www.startvbdotnet.com/ado/sqlserver.aspx
ASKER
New error now :)
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.
Check out www.connectionstrings.com for connection string information and drivers for MySql. SqlConnection is specific for MSSQL only....ConnectionStrings.
ASKER
Ok cool. Well I am hosting using Hostinguk.net so I couldnt actually install drivers could I?
Most likely not no....check out this page:
http://www.connectionstrings.com/mysql
and check the usage of the MySql.Data.MySqlClient.MyS qlConnecti on class instead of System.data.SqlClient. It should behave in much the same way as the SqlConnection.
http://www.connectionstrings.com/mysql
and check the usage of the MySql.Data.MySqlClient.MyS
ASKER
Excellent. Just breaking for dinner....
ASKER
New error :(
Remember that you need to use the MySql.Data.MySqlClient.MyS qlConnecti on 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.
ASKER
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>
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.
ASKER
Damn it's hard to get your head around .net lol
It can be yes :) but once you start to get the pattern of things, other things fall into place quicker.
ASKER
I may have to go back to square 1 and start again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok i shall try it when I get home. Cant really do it at work mate.
Thanks.
Thanks.
Any update?
ASKER
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)
Unless of course I get time to work on it today or tomorrow at work ;o)
ASKER
Ok ive had another go at it, we now have the following error:
http://www.the-lees.co.uk/blar.aspx
Thanks
http://www.the-lees.co.uk/blar.aspx
Thanks
Make sure when you do your imports they are in the form of:
<%@ Imports Namespace="System.ServiceP rocess" %>
<%@ Imports Namespace="System" %>
<%@ Imports Namespace="Microsoft.Data. Odbc" %>
You are missing the Namespace="".
<%@ Imports Namespace="System.ServiceP
<%@ Imports Namespace="System" %>
<%@ Imports Namespace="Microsoft.Data.
You are missing the Namespace="".
ASKER
Hmmmm its still finding an error with the imports.
Put the Page directive first, and try just Import instead of Imports plural..sorry.
ASKER
Ah excellent, now we have a slightly different error. We're getting there :)
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
http://www.java2s.com/Code/VB/Database-ADO.net/OdbcConnectionDemo.htm
ASKER
I had Microsoft.Data.Odbc, ive now changed it to System.Data.Odbc.
Another error...
http://www.the-lees.co.uk/blar.aspx
Another error...
http://www.the-lees.co.uk/blar.aspx
well, we are getting farther!
Can you post your code as it is right now so we can see what the command variable is?
Can you post your code as it is right now so we can see what the command variable is?
ASKER
Hi Mozz,
Hope you well. Current code attached....
Thanks,
Dean
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>
ASKER
Thats fine. Thanks rkworlds
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.
dbconn=New OleDbConnection("Provider=
Try:
dbconn=New OleDbConnection("Provider=