OLEDB and ADODB with SQL2008R2 from VB6 program

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()
leop1212Asked:
Who is Participating?
 
shorvathConnect With a Mentor Commented:
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
 
CodeCruiserCommented:
>but having a problem writing back to DB.

Do you get any errors?
0
 
Anthony PerkinsCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
leop1212Author Commented:
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
 
shorvathCommented:
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
 
shorvathConnect With a Mentor Commented:
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
 
leop1212Author Commented:
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
 
Anthony PerkinsCommented:
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
 
shorvathCommented:
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
 
leop1212Author Commented:
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
All Courses

From novice to tech pro — start learning today.