Solved

OLEDB and ADODB with SQL2008R2 from VB6 program

Posted on 2013-01-08
10
3,459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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
 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

617 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