Connet to SQL using VB6 forms via Internet

We have one system that is developed using Visual Basic 6. The system connects to MS SQL database using ODBC configuration. We have another branch that has to access the same database. We've got an Internet connection with a speed of 2MB, as we tried to connect using Virtual Private Network (VPN), but the problem we faced that the system became very slow over the Internet, its taking half an hour to open one form. Is there any way to connect to the database and via the Internet using VB6 Forms? Or its not useful as its not a web based tool.

Hi,

it′s hard for us to tell where the bottleneck in your application design is. But as of your description the application is not designed for slow connections to the database. To find out exactly what your problem is, you will either have to through the code to see which data is requested from the server, or let the profiler runn to see where the latency is based on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

connection strings

This is probably a very basic question and I apologise for asking. Can an app developed with VWD using SQLexpress be used with server2000? if so how do I modify the connection string.

hi,

a long as no particular keywords for SQL Server 2005 are used (say, the unsupported "MARS_Connection=yes;", and the like), the connection string is quite similar.. please have a look at http://www.connectionstrings.com/ for samples..

regards

|||Also have a look at the driver, if you install the application on a non SQL System, the SNAC will probably not exist. YOu will either have to ship it within your application, or use a MADC driver which is installed on the most systems.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

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.

connection string error

OK - I have seen lots posted on this, but the behavior is very strange, and I am trying to understand:

I am using Visual Basic 2003 with SQLEXPRESS 2005 to connect to a database and to develop a desktop application. My connection string is:

Conn_Str = "Data Source=.\SQLExpress;AttachDbFilename=" & _
Directory.GetCurrentDirectory & "\CWW.mdf;" & _
"Trusted_Connection=Yes;"
I get the following error:

"An attempt to attach an auto-named database for file C:\Documents and Settings\Brian\My Documents\Compressor Program Development\bin\CWW.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

However, if I copy the database to anywhere else on the "c:\" drive, the connection works, as in the following:

Conn_Str = "Data Source=.\SQLExpress;AttachDbFilename=" & _
"C:\temp\CWW.mdf;" & _
"Trusted_Connection=Yes;"

What I have found is that ist works everywhere on my computer except in the following location:

"c:\documents and settings\Brian", which includes My Documents, Desktop folders.

Why is this?

Any help would be great - I can work around this by moving my development folder to the "c:\" drive. Since my ultimate application directory will be the "Program Files" directory I should be fine.

hi,

bridress wrote:

Conn_Str = "Data Source=.\SQLExpress;AttachDbFilename=" & _
Directory.GetCurrentDirectory & "\CWW.mdf;" & _
"Trusted_Connection=Yes;"

did you try "AttachDbFilename=|DataDirectory|\CWW.mdf;" ?

regards

|||Thanks for the response -

Yes I did - I even tried the following to shift the default DataDirectory:

AppDomain.CurrentDomain.SetData("DataDirectory", Directory.GetCurrentDirectory)

Proj_ConnStr="DataSource=.\SQLExpress;AttachDbFilename=|DataDirectory|\CWW.mdf; _ Trusted_Connection=Yes;"

I still had no success. It seems I should be able to put the db file in any directory I choose and not be restricted like this?

|||

hi,

please verify the Windows account running the SQLExpress is granted enought NTFS permissions on the specified path...

regards

|||

The first time you run your code it attaches the database and used the path provided as the Name of the database. You will get failures in subsequent attempts because the database is already attached unless you have detached the database somewhere else in your code.

AttachDbFilename is mostly useful when working with User Instances, which you're not using, so I'm unclear why your're using this keyword. Why aren't you just attaching your database once and then connecting to it normally?

Mike

connection string error

OK - I have seen lots posted on this, but the behavior is very strange, and I am trying to understand:

I am using Visual Basic 2003 with SQLEXPRESS 2005 to connect to a database and to develop a desktop application. My connection string is:

Conn_Str = "Data Source=.\SQLExpress;AttachDbFilename=" & _
Directory.GetCurrentDirectory & "\CWW.mdf;" & _
"Trusted_Connection=Yes;"
I get the following error:

"An attempt to attach an auto-named database for file C:\Documents and Settings\Brian\My Documents\Compressor Program Development\bin\CWW.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

However, if I copy the database to anywhere else on the "c:\" drive, the connection works, as in the following:

Conn_Str = "Data Source=.\SQLExpress;AttachDbFilename=" & _
"C:\temp\CWW.mdf;" & _
"Trusted_Connection=Yes;"

What I have found is that ist works everywhere on my computer except in the following location:

"c:\documents and settings\Brian", which includes My Documents, Desktop folders.

Why is this?

Any help would be great - I can work around this by moving my development folder to the "c:\" drive. Since my ultimate application directory will be the "Program Files" directory I should be fine.

hi,

bridress wrote:

Conn_Str = "Data Source=.\SQLExpress;AttachDbFilename=" & _
Directory.GetCurrentDirectory & "\CWW.mdf;" & _
"Trusted_Connection=Yes;"

did you try "AttachDbFilename=|DataDirectory|\CWW.mdf;" ?

regards

|||Thanks for the response -

Yes I did - I even tried the following to shift the default DataDirectory:

AppDomain.CurrentDomain.SetData("DataDirectory", Directory.GetCurrentDirectory)

Proj_ConnStr="DataSource=.\SQLExpress;AttachDbFilename=|DataDirectory|\CWW.mdf; _ Trusted_Connection=Yes;"

I still had no success. It seems I should be able to put the db file in any directory I choose and not be restricted like this?

|||

hi,

please verify the Windows account running the SQLExpress is granted enought NTFS permissions on the specified path...

regards

|||

The first time you run your code it attaches the database and used the path provided as the Name of the database. You will get failures in subsequent attempts because the database is already attached unless you have detached the database somewhere else in your code.

AttachDbFilename is mostly useful when working with User Instances, which you're not using, so I'm unclear why your're using this keyword. Why aren't you just attaching your database once and then connecting to it normally?

Mike