Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006

    Unanswered: Connection string for VBEE to SQL 2005

    I have SQL 2005 and I wanted to write a little program in Visual Basic 2005 Express Edition (VBEE) to carry out some processes. While away I built my user form, but when I got back I found that VBEE refuses to connect to SQL Server 2005 and is only configured to link to SQL Express.

    The data source is set to .\SQLEXPRESS in the advanced properties of the Add Connection form in the Data Source Configuration Wizard and you can not change it.

    I read in MSDN ID 329721 that you can create a data layer that will allow you to use the wizards, but that is as far as it went and is above my programming skills.

    So my only options are use VB.Net 2003 Standard edition, use connected mode or give up and it was only supposed to be a quick fix. Had anyone else had this same issue and developed a work around?

  2. #2
    Join Date
    Aug 2006
    I have entered the following

    PHP Code:
    Imports System.Data.SqlClient
    Imports System
    Public Class Form1
    Public SQLPubsConnString As String "Provider=SQLOLEDB;Data Source=TOTALLY-PBN-FS;Integrated Security=SSPI;Initial Catalog=Sword of Destiny Back End_Data.MDF"

    Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgsHandles MyBase.Load
            Dim cn 
    As New OleDbConnection
    .ConnectionString SQLPubsConnString

    End Sub 

    and I get this error:

    Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    I have no idea what this mean, I tried to connect to the help files and nothing, any clues would be appreciated

  3. #3
    Join Date
    Aug 2006
    Quick rant, why did Microsoft not include SQL 2005 within the Add Connection in VBEE, it is totally stupid?

  4. #4
    Join Date
    May 2004
    to connect to sql2005, you can use a connstr like this (assuming windows auth)

    server=MYSERVER; database=MYDB; trusted_connection=yes;

    many more examples here:

  5. #5
    Join Date
    Aug 2006
    Many thanks, I tried it and it asks for a provider, I then enter one and it comes up with the same message. I had already located that excellent site and I have tried various permutations and no joy. I am using Windows Authorization by the way.

    Sadly it looks like I will have to continue using my Access MDB front end that keeps getting ODBC timeouts which is not solvable.

  6. #6
    Join Date
    Aug 2006
    I worked out what it was while lying in bed, and sorted it this morning, it seems that as I had stored the project on a network drive it was tripping on security, as soon as I moved the project to My Documents the connection string worked, doh!!!

  7. #7
    Join Date
    Dec 2005
    you can store in your web.config

    <add key="ConnectionString"
     value="Server=(local);Database=yourDataBaseName;User ID=;Password=;Trusted_Connection=True" 
    the keywords (local) works for localhost

  8. #8
    Join Date
    Aug 2006
    Thanks for your advice I will remember that as I will be setting up something for the Web, in the end I did this:

        Public WithEvents cnn As New SqlConnection
        Public SQLPubsConnString As String = "data source=TOTALLY-PBM-FS;Initial Catalog=Sword of Destiny Back End;Integrated Sec
     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim SQLGetControlFormData As String
            Dim SQLcommand As New SqlCommand
            SQLGetControlFormData = "Select ID, [Character Name], Month, Year From dbo.[Control Form]"
            cnn.ConnectionString = SQLPubsConnString
            SQLcommand.Connection = cnn
            SQLcommand.CommandText = SQLGetControlFormData
            Dim DRControlForm As SqlDataReader = SQLcommand.ExecuteReader
            Do While DRControlForm.Read()
    I can see the data in the immediate window, so its there, my next challenge is to work out how to get the data into the Form. And to do something like this which I Was doing in Access:

    '        GetUnitIDSQL = "SELECT [Unit Details].ID, [Unit Details].[Unit Name], [Unit Details].[Unit Type], [Unit Details].[Paid By], [Unit Details].Country, " & _
    '        "[Unit Details].[Province based], [Unit Details].[Training Level], [Unit Details].[Location], [Unit Details].[Wages PM] " & _
    '        "FROM [Unit Details] " & _
    '        "WHERE ((([Unit Details].[ID])=" & UNitID & ") AND (([Unit Details].[Unit Type])=" & "'" & UnitType & "'" & ") " & _
    '        "AND (([Unit Details].[Paid By])=" & CharID & ") AND (([Unit Details].Country)=" & "'" & Country & "'" & ") AND (([Unit Details].[Province based])=" & "'" & Province & "'" & "));"
    '        qdf2 = db.CreateQueryDef("Hewitt5734", GetUnitIDSQL)
    '        rs2 = db.OpenRecordset("Hewitt5734", Type:=dbOpenSnapshot)
    '        With rs2
    '            CountB = .RecordCount
    '            If CountB = 1 Then
    '                UnitName = .Fields("Unit Name")
    '                WagesPM = .Fields("Wages PM")
    '                StartSector = .Fields("Location")
    '            End If
    '        End With
    '        'db.OpenRecordset("Hewitt5734").Close
    '        db.QueryDefs.Delete("Hewitt5734")
    I do not intend to change any data I bring into the form, I want to use an Update SQL Query at the end to add new records in another table and update certain related records in another Table. As I can't use disconnected mode with this which is with the Wizards working of course then I have to use connected mode.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts