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
