By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,048 Members | 475 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,048 IT Pros & Developers. It's quick & easy.

How to Connect to MYSQL5.1 from VS.Net2003

P: 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
Share this Question
Share on Google+
3 Replies

RedSon
Expert 5K+
P: 5,000
Moving from Programming challenges and IT case studies.
Oct 15 '07 #2

Plater
Expert 5K+
P: 7,872
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
Expert 100+
P: 1,435
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.