Solved

OLEDB and ADODB with SQL2008R2 from VB6 program

Posted on 2013-01-08
10
3,234 Views
Last Modified: 2013-01-17
We do have accounting program based on SQL 2005 and shipping base on Custom VB6 program.
Shipping VB 6 program is pointing to accounting Database and  getting Sales Order info and then writing back to accounting once data is processed. We now upgrading accounting program to run on SQL 2008R2.  While testing connection to a new system we re-pointed VB program to new SQL server 2008R2.
We  can still pull data out but getting an error on writing back from VB to SQL. We tested security rights and it is not a problem.
Shipping Client machine with VB 6 program is running on XP SP3.
The procedure code is below I think it is using OLEDB and ADO
Some parts of the code prior to this sub references to old Foxpro SQL compatibility
Like this stament
       Set rsfox = cmdSQL.Execute

Summary: VB 6 program works fine connecting to SQL 2005 DB for read and write while with 2008 it can read but having a problem writing back to DB.

What is the easiest way to make this old code to work with SQL2008R2?
Do I need an SQL tools installed on VB client XP box?
---------------------------------------------------------------------------------------------------

Private Sub Upload_Data()
       Dim i As Integer
       Dim cnn1 As ADODB.Connection
       Dim rsfox As ADODB.Recordset
       Dim cmdSQL As ADODB.Command
       Dim PRM1 As ADODB.Parameter
       Dim PRM2 As ADODB.Parameter
       Dim PRM3 As ADODB.Parameter
       Dim PRM4 As ADODB.Parameter
       Dim PRM5 As ADODB.Parameter
       Dim PRM6 As ADODB.Parameter
       
       Dim strCnn As String
       Dim strSQL As String
   
       ' Open a connection.
       Set cnn1 = New ADODB.Connection
       'strCnn = "DSN=ERPTEST"
       'cnn1.Open strCnn
       strCnn = "Provider=SQLOLEDB.1;Password=my_password;Persist Security Info=True;User ID=sa;Initial Catalog=My_data;Data Source=My+SQL2008R"
       
       cnn1.open strCnn
       cnn1.CursorLocation = adUseClient
       
       
       ' Open a recordset based on a command object.
       Set cmdSQL = New ADODB.Command
       Set cmdSQL.ActiveConnection = cnn1
       cmdSQL.CommandText = "My_updateSp"
       cmdSQL.CommandType = adCmdStoredProc
       cmdSQL.CommandTimeout = 15
       
       Set PRM1 = New ADODB.Parameter
       PRM1.Type = adVarChar
       PRM1.Size = 7
       PRM1.Direction = adParamInput
       PRM1.value = "employe" 'Date$
       cmdSQL.Parameters.Append PRM1
       
       Set PRM2 = New ADODB.Parameter
       PRM2.Type = adInteger
       'PRM2.Size = 1
       PRM2.Direction = adParamInput
       PRM2.value = 1
       cmdSQL.Parameters.Append PRM2
       
       Set PRM3 = New ADODB.Parameter
       PRM3.Type = adVarChar
       PRM3.Size = 10
       PRM3.Direction = adParamInput
       PRM3.value = Right(Space(10) + lblOrderNumber.Caption, 10)
       cmdSQL.Parameters.Append PRM3
       
       Set PRM4 = New ADODB.Parameter
       PRM4.Type = adSmallInt
       'RM4.Size = 10
       PRM4.Direction = adParamInput
       PRM4.value = 1
       cmdSQL.Parameters.Append PRM4
       
       Set PRM5 = New ADODB.Parameter
       PRM5.Type = adSmallInt
       'RM4.Size = 10
       PRM5.Direction = adParamInput
       PRM5.value = 1
       cmdSQL.Parameters.Append PRM5
       
       'parent item
       Set PRM6 = New ADODB.Parameter
       PRM6.Type = adVarChar
       PRM6.Size = 30
       PRM6.Direction = adParamInput
       PRM6.value = 1
       cmdSQL.Parameters.Append PRM6
       
       ' Open a recordset based on an SQL string.
       Set rsfox = New ADODB.Recordset
       Set rsfox = cmdSQL.Execute
       
       
       rownumber = 0
       While Not rsfox.EOF
        rownumber = rownumber + 1
        flxOrders.AddItem Trim(Str(rownumber)) + vbTab + Trim(rsfox.Fields(0)) + vbTab + Trim(rsfox.Fields(1)) + vbTab + rsfox.Fields(2) + vbTab + rsfox.Fields(3) + vbTab + rsfox.Fields(4) + vbTab + rsfox.Fields(5)
        rsfox.MoveNext
       Wend
   
    flxOrders.rows = rownumber
   
   
End Sub
 
Private Sub CloseBox()
0
Comment
Question by:leop1212
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 9

Accepted Solution

by:
shorvath earned 500 total points
ID: 38756847
I would try SQL Native Client....

Use this connection string...

strCnn = "Provider=SQLNCLI10;Server=My+SQL2008R;Database=My_data;DataTypeCompatibility=80;User Id=sa;Password=my_password;"

Open in new window


See this link for more details
http://msdn.microsoft.com/en-us/library/bb964722.aspx
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38756901
>but having a problem writing back to DB.

Do you get any errors?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38761559
We  can still pull data out but getting an error on writing back from VB to SQL.
Please post the exact error message and where it is triggered.
0
 

Author Comment

by:leop1212
ID: 38763571
I just tried strCnn = "Provider=SQLNCLI10;Server=My+SQL2008R;Database=My_data;DataTypeCompatibility
however it doesn't have userID and password and still getting same error

there is no error in the event log
only the error from my VB prtogram which you can see at the bottom of the attached promgram

sgBox "Problem with Update MYProg-Retry Get Orders to Ship", vbCritical, " My program  Connection Error"
VB6prog.txt
0
 
LVL 9

Expert Comment

by:shorvath
ID: 38764044
You did not read it correctly

DataTypeCompatibility=80;


Try:

strCnn = "Provider=SQLOLEDB.1;Password=my_passw;Persist Security Info=True;User ID=sa;Initial Catalog=My_data;Data Source=my_SQL2008R2;DataTypeCompatibility=80;
"
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Assisted Solution

by:shorvath
shorvath earned 500 total points
ID: 38764106
Opps,  copied wrong line from your code....

Use this line

strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my_SQL2008R2;Database=My_data;DataTypeCompatibility=80;"
0
 

Author Comment

by:leop1212
ID: 38764238
when I am using your string i am getting an error even when i try to read data fom the souce

strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my_SQL2008R2;Database=My_data;DataTypeCompatibility=80;"

while with my original i only have a problem writing to it.
 strCnn = "Provider=SQLOLEDB.1;Password=my_password;Persist Security Info=True;User ID=sa;Initial Catalog=My_data;Data Source=My+SQL2008R"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38765616
there is no error in the event log
only the error from my VB prtogram which you can see at the bottom of the attached promgram

Let's try this again.  You are saving the result of Err.Description somewhere using a call to SaveFailLog in your Error Handler.  See if you can post that.  Your generic message produced by the MsgBox in your Error Handler is useless to us.

Further consider changing your Error Handler to save all the ADO errors, not just the last one.  For this you use the ADO Errors collection.
0
 
LVL 9

Expert Comment

by:shorvath
ID: 38767269
What is the name of your server  My+SQL2008R  or my_SQL2008R2 ?

if its my_SQL2008R2

strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my_SQL2008R2;Database=My_data;DataTypeCompatibility=80;"

if it's My+SQL2008R

strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my+SQL2008R2;Database=My_data;DataTypeCompatibility=80;"
0
 

Author Comment

by:leop1212
ID: 38789730
i tried ths SQLNCLI10 string but no luck.
it will not even read the data and I can't get eny error mesage beside the custom error in VB code. I can write via ODBC from another program to 2008 SQL but can't write with this VB 6 program.
 Shorvath you definetly tried hard to help me I will open another question
maybe you can come up with another idea.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now