ADO API Problem.

[Forexsurfr]

Can someone help me with the below ADO API? I am a new programmer and am having problems programming a SQL Server connection. I have found various connection strings and continue to run into problems with the syntax. Thank you in advance.


#include "stdafx.h"
#include <iostream>
#include <string>

#import "C:\Program files\Common Files\System\Ado\msado15.dll" rename("EOF", "ADOEOF")
//---------------------------------------------------------------------------------------------------------------------------------------------
std::string outputashex(unsigned long l)
{
char buffer[1024];
::itoa(l, buffer, 16);
return buffer;
} ;
//---------------------------------------------------------------------------------------------------------------------------------------------
void main()
{
HRESULT hr;
CoInitialize(NULL);
try
{
ADODB::_ConnectionPtr connection;
hr = connection.CreateInstance(__uuidof(ADODB::Connection));
if (FAILED(hr))
{
throw _com_error(hr);
}
//--------------------------------------------------------------------------------------------------------------------------------------------
ADODB::_RecordsetPtr recordset;
hr = recordset.CreateInstance(__uuidof(ADODB::Recordset));
if (FAILED(hr))
{
throw _com_error(hr);
}
//--------------------------------------------------------------------------------------------------------------------------------------------
connection->CursorLocation = ADODB::adUseClient;
//--------------------------------------------------------------------------------------------------------------------------------------------
connection->Open("Provider=sqloledb;Server=.\SQLExpress;AttachDbFilename=c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WattsALoan.mdf;Database=WattsALoan;Trusted_Connection=Yes;")


//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Open("CREATE TABLE mytable (value NVARCHAR(255))",
//connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,
//ADODB::adLockReadOnly, ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Open("INSERT INTO mytable VALUES ('Hello')",
//connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,
//ADODB::adLockReadOnly, ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Open("INSERT INTO mytable VALUES ('Goodbye')",
//connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,
//ADODB::adLockReadOnly, ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
recordset->Open("SELECT * from Customers",
connection.GetInterfacePtr(),
ADODB::adOpenForwardOnly, ADODB::adLockReadOnly,
ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
while(!recordset->ADOEOF)
{
_variant_t var;
var = recordset->Fields->GetItem(L"value")->GetValue();
std::cout << static_cast<char *>(_bstr_t(var.bstrVal))
<< std::endl;
recordset->MoveNext();
};
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Close();
//recordset->Open("DROP TABLE mytable", connection.GetInterfacePtr(),
//ADODB::adOpenForwardOnly, ADODB::adLockReadOnly,
//ADODB::adCmdText);
}
catch(_com_error &e)
{
std::cerr << ::outputashex(hr) << ":"
<< static_cast<char *>(e.Description());
}
catch(...)
{
std::cerr << "Unhandled Exception";
};

}

[ventsyv]

Your connection string looks incorrect. In particular your Server attribute looks incorrect and I don't think you need the AttachDbFilename attribute either. Try this:

Code: Select all

Data Source = {PC_NAME}\{SERVER_INSTANCE_NAME}; Initial Catalog = {My_Database_Name}; Integrated Security = true


There seems to be a couple of "competing" connection string syntaxes, probably left over by legacy stuff and/or different providers (oracle might support one attributes, MS others etc).

In my experience the attributes above do work fine and I think are the recommended. Here is some explanation of how to use them:

Data Source = Server
Initial Catalog = Database
Integrated Security = Trusted Connection.
{PC_NAME} = IP address or name of server machine
{SERVER_INSTANCE_NAME} - the name of the server instance. SqlExpress by default uses SQLEXPRESS as instance name, SQL Server Standard uses nameless instance (so you only use the pc name to connect)
{My_Database_Name} - obviously the name of the db you are trying to connect to
Integrated Security - the value of this attribute can be "yes", "true" or "SSPI" (I don't think it is case sensitive).

Edit:
If you do need to attach the database file, you'll need to escape your backslashes, so "C:\myfile" will be "C:\\myfile" (Same for the Data Source attribute)
I also think think that you don't need a ";" following the last value.

[Forexsurfr]

Sorry for not including the build errors:

------ Build started: Project: Trade OptimizerV15, Configuration: Debug Win32 ------
Compiling...
stdafx.cpp
Compiling...
Trade OptimizerV15.cpp
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(15) : warning C4996: 'itoa': The POSIX name for this item is deprecated. Instead, use the ISO C++ conformant name: _itoa. See online help for details.
c:\program files\microsoft visual studio 9.0\vc\include\stdlib.h(862) : see declaration of 'itoa'
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'S' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'P' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'M' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'M' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'M' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'D' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : warning C4129: 'W' : unrecognized character escape sequence
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(52) : error C2660: 'ADODB::Connection15::Open' : function does not take 1 arguments
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(70) : error C2146: syntax error : missing ';' before identifier 'recordset'
Build log was saved at "file://c:\Documents and Settings\mcertini\My Documents\Visual Studio 2008\Projects\TradeOptimizerV6\Trade OptimizerV15\Trade OptimizerV15\Debug\BuildLog.htm"
Trade OptimizerV15 - 2 error(s), 8 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

[ventsyv]

Those errors are pretty self explanatory. If you wrote all this code, you should be able to debug it quite easily:

As I said, you need to escape your backslashes

Code: Select all

"Provider=sqloledb;Server=.\SQLExpress;AttachDbFilename=c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL  ....
"Provider=sqloledb;Server=.\\SQLExpress;AttachDbFilename=c:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL ...


You are using a deprecated version of itoa function:

Code: Select all

std::string outputashex(unsigned long l)
{
char buffer[1024];
::itoa(l, buffer, 16);
return buffer;
} ;

std::string outputashex(unsigned long l)
{
char buffer[1024];
std::_itoa(l, buffer, 16);
return buffer;
} ;


Finally, your connection.open call is missing semicolon at the end.

[Forexsurfr]

ventsyv,

Thank you for the reply. I have corrected my code and this now works. Though I have a problem with _itoa as it is not a member of the standard library.

Much appreciated for the above correction.

[ventsyv]

itoa is the function provided by the standard library. At some point the function was deemed inadequate and was replaced by MS with _itoa. The _itoa is more secure as it prevents buffer overflow attacks (as far as I remember). Those were in turn replaced by _itoa_s which is even more secure. There are different version of that function _i64toa handles 64 bit int etc.
The problem is that you'll have to compile against the MS version of the libraries. I think for your purposes using itoa will be OK.

[Alvaro]

itoa is the function provided by the standard library.

No: There is nothing standard about itoa. See http://en.wikipedia.org/wiki/Itoa

The standard library has sprintf and ostringstream that can do this job.

[Forexsurfr]

Listed below is the original program almost debugged completely. It took much work to figure out the proper syntax for the connection though with help from people on this board as well as others on other boards, I was able to piece it together. I am now encountering one error. The error is listed below:

Thank you ventsyv, let me know if the below correction I made is correct. My program is still has a problem.

------ Build started: Project: Trade OptimizerV15, Configuration: Debug Win32 ------
Compiling...
Trade OptimizerV15.cpp
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(42) : error C2664: 'ADODB::Connection15::Open' : cannot convert parameter 4 from 'const char [25]' to 'long'
There is no context in which this conversion is possible
Build log was saved at "file://c:\Documents and Settings\mcertini\My Documents\Visual Studio 2008\Projects\TradeOptimizerV6\Trade OptimizerV15\Trade OptimizerV15\Debug\BuildLog.htm"
Trade OptimizerV15 - 1 error(s), 0 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========


// Trade OptimizerV15.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include <iostream>
#include <string>
#include <stdio.h>
#include <stdlib.h>

#import "C:\\Program files\\Common Files\\System\\Ado\\msado15.dll" rename("EOF", "ADOEOF")
//---------------------------------------------------------------------------------------------------------------------------------------------
std::string outputashex(unsigned long l)
{
char buffer[1024];
_itoa_s(l, buffer, 16);
return buffer;
} ;
//---------------------------------------------------------------------------------------------------------------------------------------------
void main()
{
HRESULT hr;
CoInitialize(NULL);
try
{
ADODB::_ConnectionPtr connection;
hr = connection.CreateInstance(__uuidof(ADODB::Connection));
if (FAILED(hr))
{
throw _com_error(hr);
}
//--------------------------------------------------------------------------------------------------------------------------------------------
ADODB::_RecordsetPtr recordset;
hr = recordset.CreateInstance(__uuidof(ADODB::Recordset));
if (FAILED(hr))
{
throw _com_error(hr);
}
//--------------------------------------------------------------------------------------------------------------------------------------------
connection->CursorLocation = ADODB::adUseClient;
//--------------------------------------------------------------------------------------------------------------------------------------------

connection->Open("Provider=SQLOLEDB;","Data Source=PCD-LT-MCERTINI\\SQLEXPRESS;","Initial Catalog=WattsALoan.mdf;","Integrated Security=SSPI");

//--------------------------------------------------------------------------------------------------------------------------------------------
//L"", ADODB::adConnectUnspecified);
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Open("CREATE TABLE mytable (value NVARCHAR(255))",
//connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,
//ADODB::adLockReadOnly, ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Open("INSERT INTO mytable VALUES ('Hello')",
//connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,
//ADODB::adLockReadOnly, ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Open("INSERT INTO mytable VALUES ('Goodbye')",
//connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,
//ADODB::adLockReadOnly, ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
recordset->Open("SELECT * from Customers",
connection.GetInterfacePtr(),
ADODB::adOpenForwardOnly, ADODB::adLockReadOnly,
ADODB::adCmdText);
//--------------------------------------------------------------------------------------------------------------------------------------------
while(!recordset->ADOEOF)
{
_variant_t var;
var = recordset->Fields->GetItem(L"value")->GetValue();
std::cout << static_cast<char *>(_bstr_t(var.bstrVal))
<< std::endl;
recordset->MoveNext();
};
//--------------------------------------------------------------------------------------------------------------------------------------------
//recordset->Close();
//recordset->Open("DROP TABLE mytable", connection.GetInterfacePtr(),
//ADODB::adOpenForwardOnly, ADODB::adLockReadOnly,
//ADODB::adCmdText);
}
catch(_com_error &e)
{
std::cerr << ::outputashex(hr) << ":"
<< static_cast<char *>(e.Description());
}
catch(...)
{
std::cerr << "Unhandled Exception";
};

}

[ventsyv]

connection->Open("Provider=SQLOLEDB;","Data Source=PCD-LT-MCERTINI\\SQLEXPRESS;","Initial Catalog=WattsALoan.mdf;","Integrated Security=SSPI");

As far as I remember this is one big string. So try:

connection->Open("Provider=SQLOLEDB;Data Source=PCD-LT-MCERTINI\\SQLEXPRESS;Initial Catalog=WattsALoan.mdf;Integrated Security=SSPI");

I thought stdlib is part of the standard... I guess not.

[Forexsurfr]

ventsyv,

When I change the quotes within the string so that the whole string gets evaluated, I get an error message that states:

------ Build started: Project: Trade OptimizerV15, Configuration: Debug Win32 ------
Compiling...
Trade OptimizerV15.cpp
c:\documents and settings\mcertini\my documents\visual studio 2008\projects\tradeoptimizerv6\trade optimizerv15\trade optimizerv15\trade optimizerv15.cpp(43) : error C2660: 'ADODB::Connection15::Open' : function does not take 1 arguments
Build log was saved at "file://c:\Documents and Settings\mcertini\My Documents\Visual Studio 2008\Projects\TradeOptimizerV6\Trade OptimizerV15\Trade OptimizerV15\Debug\BuildLog.htm"
Trade OptimizerV15 - 1 error(s), 0 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

[ventsyv]

Well, that simply means Open takes more than one parameter. I thought it takes one but I guess I'm mistaken. Search online or msdn to find out what the prototype for that function is.