asked on

Program Created in VB 6.0 now causing errors

The program was create in vb 6.0. TOday I found out that the trial version installed by the previous admin had ran out and I could not access my databases. I installed the express version due to the size of the databases being uner 400mb. I pulled up the code (mind you I am terrible at programming) and this is the lines I believe that are producing the problem. It appears to me that it may be using the wrong driver and I do not know the name for the sql express driver

ConnectionString(2) = "Provider=SQLOLEDB.1;Password=start;Persist Security Info=True;User ID=PlanningProcess;Initial Catalog=PlanningProcess;Data Source=FILESERV3;"

ConnectionString(3) = "Provider=SQLOLEDB.1;Password=start;Persist Security Info=True;User ID=PlanningProcess;Initial Catalog=Rodenstock;Data Source=FILESERV3;"

ConnectionString(5) = "UID=PlanningProcess;PWD=start;DATABASE=" & DATABASE_NAME & ";SERVER=" & SERVER_NAME & ";DRIVER={SQL Server};"

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nmark80

ASKER

I updated the code and it stated that the named pipes provider could not open a connection. Any other thoughts would be greatly appreciated.

Nmark80

ASKER

This is the code above what I pasted previously:

Public Sub GetConnectionStrings()
Const SERVER_JDE As String = "JDEENT"
Const SERVER_NAME As String = "FILESERV3"
Const DATABASE_NAME As String = "PlanningProcess"
Dim DATABASE_JDE As String
DATABASE_JDE = Trim(GetFromINI("SQLERVER", "JDE_DBName"))
Dim JDETableLabel As String
JDETableLabel = Trim(GetFromINI("SQLERVER", "JDE_TableLabel"))
Dim dtpStartDate As Date
Dim UserName As String
dtpStartDate = Now
UserName = GetMyUserNameA

3_S

Is the program running on the same computer as the database?
If on different sever you must first allow remote connections.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
1. Launch the SQL Server Configuration Manager from the "Microsoft SQL
Server 2005 CTP" Program menu
2. Click on the "Protocols for SQLEXPRESS" node,
3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable"

Can you give version of sql express

Nmark80

ASKER

I just downloaded sql express today. We had a power outtage and when it rebooted the trial version of sql server 2005 I was using is no longer working. The database is rather small so I was trying to use express to possibly get around this. I do not know much about the program and i didnt write it. I did get the odbc connection from my pc to work and it tested successfully to this database on a different server. I have enabled the tcp/ip and the naming pipes as well.

Anthony Perkins

>>I updated the code and it stated that the named pipes provider could not open a connection. <<
Try setting the connection to explicitly open with TCP/IP by adding "Network Library=DBMSSOCN" as in:
ConnectionString(2) = "Provider=SQLNCLI;Server=FILESERV3;Database=PlanningProcess;Uid=PlanningProcess;Pwd=start;Network Library=DBMSSOCN;"
ConnectionString(3) = "Provider=SQLNCLI;Server=FILESERV3;Database=Rodenstock;Uid=PlanningProcess;Pwd=start;Network Library=DBMSSOCN;"
ConnectionString(5) = "Provider=SQLNCLI;Server=" & SERVER_NAME & "FILESERV3;Database=" & DATABASE_NAME & ";Uid=PlanningProcess;Pwd=start;Network Library=DBMSSOCN,"

Nmark80

ASKER

No such host is known, I am just surprised that from switching from the full version to express it made all of my odbc connections invalid as well as all of the ado connection strings in the code.

3_S

You had to do a restore of the database I presume?
Did you also did a restore of the master database? (are the sql users still present)
You are using Sql user account (in opposition to the windows authentication)

This is not enabled by default:
Use management studio express
right click on the server (in the left panel) and click properties
choose security and under server authentication choose SQL Server and Windows Authentication mode

Nmark80

ASKER

I didnt have to restore the databases. They were still out there. I believe they existed previously and were just updated to 2005. So i went into sql express and attached them via the managment studio

3_S

Ok if you had to attach then your users should still be there
did you look if SQL Server and Windows Authentication mode is checked?

Nmark80

ASKER

Yeah I went in and it is now enabled and I also restarted the sql server service and it still cannot find the host.

3_S

-check the log of your sql server. do you see any error?
-is your server listening on port 1433 (default port) and your program is trying to connect on this port?
-server properties -> Connections is allow remote connections to this server checked?

3_S

Else I can only recommend you to read and try the following

SQL Server 2005 Connectivity Issue Troubleshoot - Part I
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
SQL Server 2005 Connectivity Issue Troubleshoot - Part II
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx