.NET Connect to Remote SQLserver

[jtubre2]

Hi all,

I have been searching the net for days and cannot find a viable solution to my problem. I'm sure it's something simple I'm missing, because it usually is.

I am writing an application that I would like to connect to a remote SQL Server hosted by GoDaddy. They provide me with the Server Name (host), IP, etc - everything you need to establish a connection with VB.net - be it with the SQLadapter (SQLconnection) wizard or through code using connection strings.

BOTH methods (code and .net connection wizard) give me 'SQL Server does not exist or access is denied' error (sqlstate: 08001, sql server error: 17).

The only thing I can come up with so far is that 'Allow Remote Connections' is turned off inside the server; however, that wouldn't make any sense at all because GoDaddy's customers couldn't get any use out of the server they offer.

My code:

Dim strConnection As String = "Data Source=remoteserver1234; Initial Catalog=database123; User ID=user123; Password=pass123;"
Dim conn As New SqlConnection(strConnection)
Dim strCommand As String = "SELECT * FROM Table" 'WHERE Table.Fname = " & strFname
Dim dataAdapter As New SqlDataAdapter(strCommand, conn)
Dim ds As New DataSet
dataAdapter.Fill(ds, "Table")

The connection fails when I try using the wizard as well, so even if this code is incorrect (which I doubt since I've referenced it on several different sites) - something is still off. Pulling hair out at this point - any help is VERY much appreciated. Thanks!

 

[VB GOD]

myConnection = New SqlConnection("server=YourDatabase IP, Port11.11.111.11,2433;uid=YourUserId;pwd=yourPassword;database=YourDatabaseName")
       ''establishing connection. you need to provide password for sql server

        myConnection.Open()
        ''opening the connection

        myCommand = New SqlCommand("SELECT dbo.aspnet_Membership.LoweredEmail, dbo.aspnet_Users.UserName FROM dbo.aspnet_Membership INNER JOIN dbo.aspnet_Users ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId", myConnection)
        
Dim dr As SqlDataReader = myCommand.ExecuteReader()

However, I would recommend doing it the right way. You should create two classes that handle your database connections and queries.
Create a folder in your project called BLL and DAL
BLL=(Business Logic Layer)
DAL=(Data Access Layer)

In the DAL folder create a class for each database you will be connecting to. I call my classes DataAccess"DatabaseName".vb

In the BLL folder create a class for each group of actions you will be doing to the database. i.e.. customers.vb, products.vb and so on.
This is where you will put your sql actions. i.e.. update customer, add new customer, delete customer and so on..

On your SQL server create the stored procedures you need to accomplish the above actions.

Once you have that all set. It will only take a few lines of code to connect and execute your sql queries
Examples:

Dim EmailList As SqlDataReader = Storefronts.GetBirthdayEmailAddresses("mmedalia2D846C09AC835543A6DD47D0BD51D54ED")

bind to a database object or

While EmailList.Read
   something = EmailList.Item("email").ToString()
End While[/COLOR]

To insert a customer..

Dim i As Storefronts = New Storefronts
        i.Birthday = Today.Date
        i.DestinationID = "0000003333"
        i.EmailAddress = "someone@cplanet.com"
        i.UserName = "Jimbo"
        Storefronts.InsertBirthdayEmailAddress(i)

Your DAL class shoul look like this:

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient

Namespace StoreFront.DAL

    Public Class DBAccessStorefront
        Private cmd As IDbCommand = New SqlCommand()
        Private strConnectionString As String = ""
        Private handleErrors As Boolean = False
        Private strLastError As String = ""

        Public Sub New()
             Dim objConnectionStringSettings As New SqlConnectionStringBuilder("server=YourDatabase IP, Port11.11.111.11,2433;uid=YourUserId;pwd=yourPassword;database=YourDatabaseName")

            strConnectionString = objConnectionStringSettings.ConnectionString
            Dim cnn As New SqlConnection()
            cnn.ConnectionString = strConnectionString
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
        End Sub

        Public Function ExecuteReader() As IDataReader
            Dim reader As IDataReader = Nothing
            Try
                Me.Open()
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return reader
        End Function

        Public Function ExecuteReader(ByVal commandtext As String) As IDataReader
            Dim reader As IDataReader = Nothing
            Try
                cmd.CommandText = commandtext
                reader = Me.ExecuteReader()
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return reader
        End Function

        Public Function ExecuteScalar() As Object
            Dim obj As Object = Nothing
            Try
                Me.Open()
                obj = cmd.ExecuteScalar()
                Me.Close()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return obj
        End Function
        Public Function ExecuteScalar(ByVal commandtext As String) As Object
            Dim obj As Object = Nothing
            Try
                cmd.CommandText = commandtext
                obj = Me.ExecuteScalar()
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return obj
        End Function

        Public Function ExecuteNonQuery() As Integer
            Dim i As Integer = -1
            Try
                Me.Open()
                i = cmd.ExecuteNonQuery()
                Me.Close()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function

        Public Function ExecuteNonQuery(ByVal commandtext As String) As Integer
            Dim i As Integer = -1
            Try
                cmd.CommandText = commandtext
                i = Me.ExecuteNonQuery()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return i
        End Function
        Public Function ExecuteDataSet() As DataSet
            Dim da As SqlDataAdapter = Nothing
            Dim ds As DataSet = Nothing
            Try
                da = New SqlDataAdapter()
                da.SelectCommand = CType(cmd, SqlCommand)
                ds = New DataSet()
                da.Fill(ds)
            Catch ex As Exception
                If (handleErrors) Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return ds
        End Function
        Public Function ExecuteDataSet(ByVal commandtext As String) As DataSet
            Dim ds As DataSet = Nothing
            Try
                cmd.CommandText = commandtext
                ds = Me.ExecuteDataSet()
            Catch ex As Exception
                If handleErrors Then
                    strLastError = ex.Message
                Else
                    Throw
                End If
            End Try
            Return ds
        End Function

        Public Property CommandText() As String
            Get
                Return cmd.CommandText
            End Get
            Set(ByVal value As String)
                cmd.CommandText = value
                cmd.Parameters.Clear()
            End Set
        End Property

        Public ReadOnly Property Parameters() As IDataParameterCollection
            Get
                Return cmd.Parameters
            End Get
        End Property

        Public Sub AddParameter(ByVal paramname As String, ByVal paramvalue As Object)
            Dim param As SqlParameter = New SqlParameter(paramname, paramvalue)
            cmd.Parameters.Add(param)
        End Sub

        Public Sub AddParameter(ByVal param As IDataParameter)
            cmd.Parameters.Add(param)
        End Sub

        Public Property ConnectionString() As String
            Get
                Return strConnectionString
            End Get
            Set(ByVal value As String)
                strConnectionString = value
            End Set
        End Property

        Private Sub Open()
            cmd.Connection.Open()
        End Sub

        Private Sub Close()
            cmd.Connection.Close()
        End Sub

        Public Property HandleExceptions() As Boolean
            Get
                Return handleErrors
            End Get
            Set(ByVal value As Boolean)
                handleErrors = value
            End Set
        End Property

        Public ReadOnly Property LastError() As String
            Get
                Return strLastError
            End Get
        End Property

        Public Sub Dispose()
            cmd.Dispose()
        End Sub

    End Class
End Namespace

And your BLL classes should look like this depending on your needs.

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports CplanetEmailApp.StoreFront.DAL

Namespace StorefrontCL.BOL
    Public Class Storefronts

        Private sStorefrontName As String
        Private sPackages As String
        Private sDestinationId As String
        Private sUserName As String
        Private dBirthday As Date
        Private sEmailAddress As String

        Public Property StorefrontName() As String
            Get
                Return sStorefrontName
            End Get
            Set(ByVal value As String)
                sStorefrontName = value
            End Set
        End Property

        Public Property Packages() As String
            Get
                Return spackages
            End Get
            Set(ByVal value As String)
                spackages = value
            End Set
        End Property

        Public Property DestinationID() As String
            Get
                Return sDestinationId
            End Get
            Set(ByVal value As String)
                sDestinationId = value
            End Set
        End Property
        Public Property UserName() As String
            Get
                Return sUserName
            End Get
            Set(ByVal value As String)
                sUserName = value
            End Set
        End Property
        Public Property Birthday() As Date
            Get
                Return dBirthday
            End Get
            Set(ByVal value As Date)
                dBirthday = value
            End Set
        End Property
        Public Property EmailAddress() As String
            Get
                Return sEmailAddress
            End Get
            Set(ByVal value As String)
                sEmailAddress = value
            End Set
        End Property
        Public Shared Function GetStoreFronts(ByVal DestinationId As String) As SqlDataReader
            Dim db As DBAccessStorefront = New DBAccessStorefront

            db.Parameters.Add(New SqlParameter("@DestinationId", DestinationId))
            Return db.ExecuteReader("GetStoreFronts")'Stored Procedure Name


        End Function
        Public Shared Function GetBirthdayEmailAddresses(ByVal DestinationId As String) As SqlDataReader
            Dim db As DBAccessStorefront = New DBAccessStorefront

            db.Parameters.Add(New SqlParameter("@DestinationId", DestinationId))
            Return db.ExecuteReader("GetBirthdayEmailAddresses")'Stored Procedure Name


        End Function
        Public Shared Function InsertBirthdayEmailAddress(ByVal p As Storefronts) As String
            Dim db As DBAccessStorefront = New DBAccessStorefront

            Dim objParam As SqlParameter = New SqlParameter("@iMySearchID", 0)
            objParam.Direction = ParameterDirection.Output

            db.Parameters.Add(New SqlParameter("@UserName", p.UserName))
            db.Parameters.Add(New SqlParameter("@Birthday", p.Birthday))
            db.Parameters.Add(New SqlParameter("@EmailAddress", p.EmailAddress))
            db.Parameters.Add(New SqlParameter("@DestinationId", p.DestinationID))
            'db.Parameters.Add(New SqlParameter("@TagLogo", p.TagLogo))

            db.Parameters.Add(objParam)
            Dim retval As Integer = db.ExecuteNonQuery("InsertBirthdayEmailAddress") 'Stored Procedure Name
            If retval = 1 Then
                Return objParam.Value.ToString
            Else
                Return -1
            End If

        End Function
    End Class
End Namespace