The following code works ( apart from the CREATE TABLE query) if I use the stated connection string, but fails to work if I use the commented out connection strings.
Generally the CreateNewDataBase function works whether I use the commented out query or not.
But the general problem is that all connection strings that require a Password,a Max Buffer Size or Max Database Size fails to work, instead it throws an exception.
The detail of the exception thrown is as follows:
Description:Multiple-Step OLE BD operation generated errors.Check each OLE DB value, if available. No work was done;
Message:IDispatch error #3105
Could it be because I am using the version of sql server ce 3,5 that comes with VS?
Apart from this, when I use the connection string that does not include Password , Max Buffer Size and Max Database size, the program runs but trows an exception while trying to execute the CREATE TABLE query.
The detail of the exception thrown is:
Description:(null);
Message: Invalid pointer.
Please what is wrong with my CREATE TABLE query?
P.S.:
I have googled over the IDispatch error #3105 but have gotten no satisfactory information.
#include "stdafx.h"
#include<Ole2.h>
#include<tchar.h>
#include<strsafe.h>
#import "C:\Program Files\Common Files\System\Ado\msado15.dll" no_namespace rename("EOF","EndOfFile")
int CreateNewDataBase(TCHAR *szConnString,TCHAR *szNewDataSource,TCHAR *szNewPassword)
{
TCHAR szString[1000];
_ConnectionPtr Conn = NULL;
_CommandPtr Cmd = NULL;
try
{
Conn.CreateInstance(__uuidof(Connection));
Conn->ConnectionString = _bstr_t(szConnString);
Conn->Open(_bstr_t(L""),_bstr_t(L""),_bstr_t(L""),-1);
StringCbPrintf(szString,sizeof(szString),_T("CREATE DATABASE \"%s\""),szNewDataSource);
Cmd.CreateInstance(__uuidof(Command));
Cmd->ActiveConnection = Conn;
Cmd->CommandText = _bstr_t(szString);
Cmd->Execute(NULL,NULL,adCmdText);
Conn->Close();
}
catch(_com_error &e)
{
_bstr_t Error = e.Description();
TCHAR *szDescription = (TCHAR *)Error;
const TCHAR * szError = e.ErrorMessage();
StringCbPrintf(szString,sizeof(szString),_T("Description : %s ; Message : %s"),szDescription,szError);
MessageBox(NULL,szString,_T("Class Creation Error"),MB_OK);
if(Conn)
{
Conn->Close();
}
return 0;
}
return 1;
}
int CreateTables(TCHAR *szConnString,int iTermID,int iTestID)
{
_ConnectionPtr Conn = NULL;
_CommandPtr Cmd = NULL;
TCHAR szString[1000];
try
{
Conn.CreateInstance(__uuidof(Connection));
Conn->ConnectionString = _bstr_t(szConnString);
Conn->Open(_bstr_t(L""),_bstr_t(L""),_bstr_t(L""),-1);
StringCbPrintf(szString,sizeof(szString),_T("%s %s"),
_T("CREATE TABLE SchInfo (SchInfoID int IDENTITY(1,1) NOT NULL UNIQUE PRIMARY KEY, Name nvarchar(200) NOT NULL,Logo nvarchar(500),Street nvarchar (50) NOT NULL, Locality nvarchar(50) NOT NULL,"),
_T("City nvarchar(50) NOT NULL, State nvarchar(50) NOT NULL, Country nvarchar(50) NOT NULL, EMail nvarchar(50) UNIQUE, Phone nvarchar(20) UNIQUE);"));
Cmd->CommandText = _bstr_t(szString);
Cmd->Execute(NULL,NULL,adCmdText);
Conn->Close();
}
catch(_com_error &e)
{
_bstr_t Error = e.Description();
TCHAR *szDescription = (TCHAR *)Error;
const TCHAR * szError = e.ErrorMessage();
StringCbPrintf(szString,sizeof(szString),_T("Description : %s ; Message : %s"),szDescription,szError);
MessageBox(NULL,szString,_T("Class Creation Error"),MB_OK);
if(Conn)
{
Conn->Close();
}
return 0;
}
return 1;
}
int main()
{
::CoInitializeEx(NULL,COINIT_MULTITHREADED);
TCHAR szNewDataSource[] = _T("Test.sdf");
TCHAR szNewPassword[] = _T("Password");
TCHAR szOldDataSource[] = _T("C:\\Users\\user\\Documents\\MyDataBase.sdf");
int iTermID = 1, iTestID = 1;
TCHAR szOldPassword[] = _T("OldPassword");
TCHAR szConnString[1000];
StringCbPrintf(szConnString,sizeof(szConnString),_T("Provider='Microsoft.SQLSERVER.CE.OLEDB.3.5';Persist Security Info='FALSE';Data Source='%s';"),szOldDataSource);
if(CreateNewDataBase(szConnString,szNewDataSource,szNewPassword))
{
StringCbPrintf(szConnString,sizeof(szConnString),_T("Provider = 'Microsoft.SQLSERVER.CE.OLEDB.3.5'; Persist Security Info = 'FALSE'; Data Source = '%s';"),szNewDataSource);
if(CreateTables(szConnString,iTermID,iTestID))
{
MessageBox(NULL,_T("New database and table were successfully created."),_T("Database Creation Success"),MB_OK);
}
}
else
{
MessageBox(NULL,_T("Could not create database and table."),_T("Database Creation Failure"),MB_OK);
}
::CoUninitialize();
return 1;
}