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?I have entered the following

Imports System.Data.SqlClient
Imports System.Data.OleDb
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.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cn As New OleDbConnection
cn.ConnectionString = SQLPubsConnString
cn.Open()

End Sub

and I get this error:

Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, 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|||Quick rant, why did Microsoft not include SQL 2005 within the Add Connection in VBEE, it is totally stupid?|||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:
http://www.connectionstrings.com|||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.|||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!!!|||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|||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
cnn.Open()

SQLcommand.Connection = cnn
SQLcommand.CommandText = SQLGetControlFormData

Dim DRControlForm As SqlDataReader = SQLcommand.ExecuteReader

Do While DRControlForm.Read()
Debug.WriteLine(DRControlForm.Item("ID"))
combobox6.
Loop

DRControlForm.Close()
SQLcommand.Dispose()

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.