Solved

ASP.net database connection problem

Posted on 2010-11-15
40
361 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:deanlee17
  • 21
  • 15
  • 2
  • +1
40 Comments
 
LVL 8

Expert Comment

by:TheMozz
ID: 34138994
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
 

Author Comment

by:deanlee17
ID: 34139016
Hi,

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

Dean
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34139023
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
 

Author Comment

by:deanlee17
ID: 34139080
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
 
LVL 8

Expert Comment

by:TheMozz
ID: 34139084
Remove the semi-colon after the sub Page_Load statement
0
 

Author Comment

by:deanlee17
ID: 34139100
Still have an error :(
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34139198
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
 

Author Comment

by:deanlee17
ID: 34139323
Well spotted Mozz. New error now...
http://www.the-lees.co.uk/blar.aspx
Thanks
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34139363
<%@ 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
 

Author Comment

by:deanlee17
ID: 34139420
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
 

Author Comment

by:deanlee17
ID: 34139434
Same error still tho :(
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34139485
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
 

Author Comment

by:deanlee17
ID: 34139506
New error now :)

0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34139570
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
 

Author Comment

by:deanlee17
ID: 34139603
Ok cool. Well I am hosting using Hostinguk.net so I couldnt actually install drivers could I?
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34139625
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
 

Author Comment

by:deanlee17
ID: 34139649
Excellent. Just breaking for dinner....
0
 

Author Comment

by:deanlee17
ID: 34140162
New error :(
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34140213
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:deanlee17
ID: 34140384
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
 
LVL 8

Expert Comment

by:TheMozz
ID: 34140417
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
 

Author Comment

by:deanlee17
ID: 34140711
Damn it's hard to get your head around .net lol
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34140890
It can be yes :) but once you start to get the pattern of things, other things fall into place quicker.
0
 

Author Comment

by:deanlee17
ID: 34145421
I may have to go back to square 1 and start again.
0
 
LVL 8

Accepted Solution

by:
TheMozz earned 500 total points
ID: 34145597
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
 

Author Comment

by:deanlee17
ID: 34145904
Ok i shall try it when I get home. Cant really do it at work mate.

Thanks.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34160042
Any update?
0
 

Author Comment

by:deanlee17
ID: 34162192
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
 

Author Comment

by:deanlee17
ID: 34162648
Ok ive had another go at it, we now have the following error:

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

Thanks
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34164535
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
 

Author Comment

by:deanlee17
ID: 34164742
Hmmmm its still finding an error with the imports.
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34164890
Put the Page directive first, and try just Import instead of Imports plural..sorry.
0
 

Author Comment

by:deanlee17
ID: 34171004
Ah excellent, now we have a slightly different error. We're getting there :)
0
 
LVL 8

Expert Comment

by:TheMozz
ID: 34173470
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
 

Author Comment

by:deanlee17
ID: 34173656
I had Microsoft.Data.Odbc, ive now changed it to System.Data.Odbc.

Another error...

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

Expert Comment

by:TheMozz
ID: 34174142
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
 

Author Comment

by:deanlee17
ID: 34185979
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
 

Author Comment

by:deanlee17
ID: 34368273
Thats fine. Thanks rkworlds
0
 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 34399037
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now