471,066 Members | 1,232 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

How to Connect to MYSQL5.1 from VS.Net2003

3
hi, am having problem connecting to MYSQL5.1 from VS.Net.the error is database reeor:System.Data.SqlClient.SqlException:An error occur while establishnig a connection to the server. Am Using Visual Studio.Net 2003, IIS 5.0 and MySql5.1. am trying to retreve a new record from a database using a webservice.

here is the sample code

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services


<System.Web.Services.WebService(Namespace:="http ://tempuri.org/MyWebService1/Service1")> Public Class MyDataService1
Inherits System.Web.Services.WebService

#Region " Web Services Designer Generated Code "

Public Sub New()
MyBase.New()
'This call is required by the Web Services Designer.
InitializeComponent()
'Add your own initialization code after the InitializeComponent() call

End Sub

'Required by the Web Services Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Web Services Designer
'It can be modified using the Web Services Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
'CODEGEN: This procedure is required by the Web Services Designer
'Do not modify it using the code editor.
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

#End Region

' WEB SERVICE EXAMPLE
' The HelloWorld() example service returns the string Hello World.
' To build, uncomment the following lines then save and build the project.
' To test this web service, ensure that the .asmx file is the start page
' and press F5.
'
'<WebMethod()> _
'Public Function HelloWorld() As String
' Return "Hello World"
'End Function

<WebMethod()> Public Function GetLastID() As String

Dim LastID As Integer
Dim sConn As String = "SERVER =localhost;DATABASE =db1 ;UID =root ;PWD = webserver ;"
Dim sComm As String = "SELECT ID FROM Table2 WHERE ID = MAX(ID)"

Dim conn As New SqlConnection(sConn)
Dim comm As New SqlCommand
Try
conn.Open()
comm.CommandText = (sComm)
comm.CommandType = CommandType.Text

If (comm.ExecuteScalar() Is Nothing) Then
Return comm.ExecuteScalar

Else
Return comm.ExecuteScalar
LastID = comm.ExecuteScalar

End If

Catch ex As SqlException
Return "Database error:" + ex.ToString

Catch ex As Exception
Return "error:" + ex.ToString
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()

End If

End Try

End Function
<WebMethod()> Public Function GetmaxID() As String

Dim MaxID As Integer
Dim sConn As String = "SERVER = localhost;DATABASE = db2 ;UID =root ;PWD = webserver;"
Dim sComm As String = "SELECT ID FROM Table1 WHERE ID = MAX(ID)"

Dim conn As New SqlConnection(sConn)
Dim comm As New SqlCommand
Try
conn.Open()
comm.CommandText = (sComm)
comm.CommandType = CommandType.Text

If (comm.ExecuteScalar() Is Nothing) Then
Return comm.ExecuteScalar

Else
Return comm.ExecuteScalar
MaxID = comm.ExecuteScalar

End If

Catch ex As SqlException
Return "Database error:" + ex.ToString

Catch ex As Exception
Return "error:" + ex.ToString
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()

End If

End Try

End Function
<WebMethod()> Public Function DectectNewRecord() As DataSet

Dim sConn As String = "SERVER = localhost;DATABASE = db1;UID = root;PWD = webserver;"
Dim sComm As String = "SELECT * FROM Table2 WHERE ID = MAX(ID)"

Dim LastID As Integer = GetLastID()
Dim MaxID As Integer = GetmaxID()
'test to confirm if there is new record
'if true it open connection into database1 and retrieve the record
'put it into a dataset

If (LastID > MaxID) Then
Dim da As New SqlClient.SqlDataAdapter(sConn, sComm)
Dim ds As New DataSet
da.Fill(ds, "NewRecord")
Return ds
Else
MsgBox("No New Record Found")
End If


End Function


End Class
Oct 15 '07 #1
3 1283
RedSon
5,000 Expert 4TB
Moving from Programming challenges and IT case studies.
Oct 15 '07 #2
Plater
7,872 Expert 4TB
What line of code is actually causing there exception to show?
And what other information is given about it? (There should be more, like "Connection refused" or "connection timeout" or something)
Oct 15 '07 #3
Shashi Sadasivan
1,435 Expert 1GB
Are you able to create an ODBC connection to MySql ?

Maybe you have not installed the MySql .net connector yet.

once you are done with that...have a look at the
connection strings

cheers
Oct 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Sarah Tanembaum | last post: by
reply views Thread by Centurion | last post: by
reply views Thread by christian | last post: by
4 posts views Thread by cieletmer | last post: by
reply views Thread by Zal | last post: by
reply views Thread by ojorus | last post: by
4 posts views Thread by opswordfish | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.