Solved

OLEDB and ADODB with SQL2008R2 from VB6 program

Posted on 2013-01-17
3
509 Views
Last Modified: 2013-01-28
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.

i tried native SQL string but it didn't even read from 2008r

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


 ATTACHED IS A PIECE OF VB6 CODE WHICH IS FAILING
VB6prog.txt
0
Comment
Question by:leop1212
3 Comments
 
LVL 2

Expert Comment

by:MMTadmin
ID: 38789859
Work out what the error is!

uploaderror:
Msgbox err.description

Also who ever wrote that must have loved to type

Creating parameters is not that long winded

  Set cmd = New Command
  With cmd
    .Parameters.Append .CreateParameter("Status", adTinyInt, adParamInput, 1,  value)
  End With
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 38790509
You are missing a ';' after "User ID=sa".  What is 'DataTypeCompatibility'?

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

This is the format I use from http://www.connectionstrings.com/sql-server-2008 :

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;
0
 

Author Comment

by:leop1212
ID: 38794580
i did use correct syntax
strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my_SQL2008R2;Database=My_data;DataTypeCompatibility=80;"

and when I install SQL client tools on XP box it did read data via SQLNCL10 string but still getting an error on writing back. (no VB error) just a message box from the program
which is at the bottom of the VB code.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…

746 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

8 Experts available now in Live!

Get 1:1 Help Now