hi, am having problem connecting to MYSQL5.1 from VS.Net.the error is database reeor:System.Da ta.SqlClient.Sq lException: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.Sql Client
Imports System.Web.Serv ices
<System.Web.Ser vices.WebServic e(Namespace:="h ttp://tempuri.org/MyWebService1/Service1")> Public Class MyDataService1
Inherits System.Web.Serv ices.WebService
#Region " Web Services Designer Generated Code "
Public Sub New()
MyBase.New()
'This call is required by the Web Services Designer.
InitializeCompo nent()
'Add your own initialization code after the InitializeCompo nent() call
End Sub
'Required by the Web Services Designer
Private components As System.Componen tModel.IContain er
'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.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
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.Disp ose()
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;DATA BASE =db1 ;UID =root ;PWD = webserver ;"
Dim sComm As String = "SELECT ID FROM Table2 WHERE ID = MAX(ID)"
Dim conn As New SqlConnection(s Conn)
Dim comm As New SqlCommand
Try
conn.Open()
comm.CommandTex t = (sComm)
comm.CommandTyp e = CommandType.Tex t
If (comm.ExecuteSc alar() Is Nothing) Then
Return comm.ExecuteSca lar
Else
Return comm.ExecuteSca lar
LastID = comm.ExecuteSca lar
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;DATAB ASE = db2 ;UID =root ;PWD = webserver;"
Dim sComm As String = "SELECT ID FROM Table1 WHERE ID = MAX(ID)"
Dim conn As New SqlConnection(s Conn)
Dim comm As New SqlCommand
Try
conn.Open()
comm.CommandTex t = (sComm)
comm.CommandTyp e = CommandType.Tex t
If (comm.ExecuteSc alar() Is Nothing) Then
Return comm.ExecuteSca lar
Else
Return comm.ExecuteSca lar
MaxID = comm.ExecuteSca lar
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 DectectNewRecor d() As DataSet
Dim sConn As String = "SERVER = localhost;DATAB ASE = 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.SqlDa taAdapter(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
here is the sample code
Imports System
Imports System.Data
Imports System.Data.Sql Client
Imports System.Web.Serv ices
<System.Web.Ser vices.WebServic e(Namespace:="h ttp://tempuri.org/MyWebService1/Service1")> Public Class MyDataService1
Inherits System.Web.Serv ices.WebService
#Region " Web Services Designer Generated Code "
Public Sub New()
MyBase.New()
'This call is required by the Web Services Designer.
InitializeCompo nent()
'Add your own initialization code after the InitializeCompo nent() call
End Sub
'Required by the Web Services Designer
Private components As System.Componen tModel.IContain er
'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.Diagnos tics.DebuggerSt epThrough()> Private Sub InitializeCompo nent()
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.Disp ose()
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;DATA BASE =db1 ;UID =root ;PWD = webserver ;"
Dim sComm As String = "SELECT ID FROM Table2 WHERE ID = MAX(ID)"
Dim conn As New SqlConnection(s Conn)
Dim comm As New SqlCommand
Try
conn.Open()
comm.CommandTex t = (sComm)
comm.CommandTyp e = CommandType.Tex t
If (comm.ExecuteSc alar() Is Nothing) Then
Return comm.ExecuteSca lar
Else
Return comm.ExecuteSca lar
LastID = comm.ExecuteSca lar
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;DATAB ASE = db2 ;UID =root ;PWD = webserver;"
Dim sComm As String = "SELECT ID FROM Table1 WHERE ID = MAX(ID)"
Dim conn As New SqlConnection(s Conn)
Dim comm As New SqlCommand
Try
conn.Open()
comm.CommandTex t = (sComm)
comm.CommandTyp e = CommandType.Tex t
If (comm.ExecuteSc alar() Is Nothing) Then
Return comm.ExecuteSca lar
Else
Return comm.ExecuteSca lar
MaxID = comm.ExecuteSca lar
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 DectectNewRecor d() As DataSet
Dim sConn As String = "SERVER = localhost;DATAB ASE = 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.SqlDa taAdapter(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
Comment