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};"

Nmark80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
It should be:
ConnectionString(2) = "Provider=SQLNCLI;Server=FILESERV3;Database=PlanningProcess;Uid=PlanningProcess;Pwd=start;"
ConnectionString(3) = "Provider=SQLNCLI;Server=FILESERV3;Database=Rodenstock;Uid=PlanningProcess;Pwd=start;"
ConnectionString(5) = "Provider=SQLNCLI;Server=" & SERVER_NAME & "FILESERV3;Database=" & DATABASE_NAME & ";Uid=PlanningProcess;Pwd=start;"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nmark80Author Commented:
I updated the code and it stated that the named pipes provider could not open a connection. Any other thoughts would be greatly appreciated.
0
Nmark80Author Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

3_SCommented:
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
0
Nmark80Author Commented:
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.
0
Anthony PerkinsCommented:
>>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,"
0
Nmark80Author Commented:
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.
0
3_SCommented:
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
0
Nmark80Author Commented:
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
0
3_SCommented:
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?
0
Nmark80Author Commented:
Yeah I went in and it is now enabled and I also restarted the sql server service and it still cannot find the host.
0
3_SCommented:
-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?
0
3_SCommented:
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


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.