[wango]

When I do not send in a user id and password, I can create
a DSN and open a database (remote database on a remote server) and everything is fine.
(This works on all Windows 2000 nmachines that I know and some WIN NT 4.0 machines).

For those where the "SQLConnect(....)" ( in the 2nd function) fails, I checked the ODBC connection and
and found that a the Server was the correct one but the "Login ID" was "administrator".
Trying to proceed in the ODBC panel fails. Apparentl

To try to solve the problem, I try to include specifically a User ID and a password.
When I include "UID=sa" & "PWD=",
creating a DSN fails. can someone show mo please!! I have noticed that a lot of people have the
same problem as I have.



//Retrieving a List of All ODBC Data Sources
bool CSQLdbImportDlg::CreateODBCDataSourceName()
{

CString dsn_str;
CString server_str;
CString description_str;
CString database_str;
CString wholeString;
CString userIDStr;
CString dsnPassWordStr;

// for diagnostics only
int iCount = 0;
WORD iError = 1;
DWORD pfErrorCode;
char lpszErrorMsg[301];
WORD cbErrorMsgMax = 300;
RETCODE retcode;

BOOL bretcoderemovedsn;
BOOL bretcodeadddsn;
LPTSTR szDriver = "SQL Server";

// add datasource name if it exixts
dsn_str = "DSN=" + m_MyProgramaticDSN + "\0"; // m_MyProgramaticDSN = "MemexDataDSN"
LPTSTR lpszMyProgramaticDSN = new TCHAR[dsn_str.GetLength()+1];
_tcscpy(lpszMyProgramaticDSN, dsn_str);

server_str = "SERVER=" + m_cmbSQLservername + "\0";
LPTSTR lpszSQLservername = new TCHAR[server_str.GetLength()+1];
_tcscpy(lpszSQLservername, server_str);

LPTSTR lpszDATABASE = "DATABASE=VersaScanMeSQL\0";
LPTSTR lpszDESCRIPTION = "DESCRIPTION=Dynamically created datasetname\0";

//// I pass in a blank CString "m_sqlserverdsnpassword"
dsnPassWordStr = "PWD=" + m_sqlserverdsnpassword + "\0";
LPTSTR lpszPassWord = new TCHAR[dsnPassWordStr.GetLength()+1];
_tcscpy(lpszPassWord, dsnPassWordStr);


// I pass in m_sqlserverdsnuserid = "sa"
userIDStr = "UID=" + m_sqlserverdsnuserid + "\0";
LPTSTR lpszUSERID = new TCHAR[userIDStr.GetLength()+1];
_tcscpy(lpszUSERID, userIDStr);


// remove the local datasource name if it exixts
TCHAR szAttributesRemove[1028];
TCHAR* szSubStringRemove = &(szAttributesRemove[0]);

_tcscpy(szSubStringRemove, lpszMyProgramaticDSN);
szSubStringRemove = (TCHAR*) (((int) szSubStringRemove) + strlen(lpszMyProgramaticDSN)
+ sizeof(TCHAR));

// send in the server name
_tcscpy(szSubStringRemove, lpszSQLservername);
szSubStringRemove = (TCHAR*) (((int) szSubStringRemove) + strlen(lpszSQLservername) +
sizeof(TCHAR));

*szSubStringRemove = 0; // second null to end the list

// remove an existing DSN
bretcoderemovedsn = SQLConfigDataSource(NULL,
ODBC_REMOVE_SYS_DSN,
szDriver,
&(szAttributesRemove[0]) /*szAttributes*/);


// if removing a dsn fails
if (bretcoderemovedsn == 0) // || rc == SQL_SUCCESS_WITH_INFO)
{
while (SQL_NO_DATA != (retcode = SQLInstallerError(
iError ++,
& pfErrorCode,
lpszErrorMsg,
cbErrorMsgMax,
NULL )))

iCount++;
}

// Add the local datasource name
// _tstrlen does not exist
TCHAR szAttributes[1028];
TCHAR* szSubString = &(szAttributes[0]);

// the DSN
_tcscpy(szSubString, lpszMyProgramaticDSN);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszMyProgramaticDSN)
+ sizeof(TCHAR));

// description
_tcscpy(szSubString, lpszDESCRIPTION);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszDESCRIPTION) +
sizeof(TCHAR));

// server name
_tcscpy(szSubString, lpszSQLservername);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszSQLservername) +
sizeof(TCHAR));

// database
_tcscpy(szSubString, lpszDATABASE);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszDATABASE) +
sizeof(TCHAR));

//////////////////////////////////////////////////////////////////////
/*
// password
_tcscpy(szSubString, lpszPassWord);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszPassWord) +
sizeof(TCHAR));
// user id
_tcscpy(szSubString, lpszUSERID);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszUSERID) +
sizeof(TCHAR));
*/
//////////////////////////////////////////////////////////////////////

*szSubString = 0; // second null to end the list

bretcodeadddsn = SQLConfigDataSource(NULL,
ODBC_ADD_SYS_DSN,
szDriver,
&(szAttributes[0]) /*szAttributes*/);

// if adding a dsn fails
if (bretcodeadddsn == 0) // || rc == SQL_SUCCESS_WITH_INFO)
{
while (SQL_NO_DATA != (retcode = SQLInstallerError(
iError ++,
& pfErrorCode,
lpszErrorMsg,
cbErrorMsgMax,
NULL )))

iCount++;

// do not proceed beyond trying to dynamically create a dataset name
return false;
}

return true;
}//CreateODBCDataSourceName()

/*************************************************
Function
Odbc_DBase::Connect_ToInput
Description
This is the Odbc_DBase constructor.
**************************************************/
SQLRETURN CODBC_Dbase::Connect_ToDbase(CString &Db_Name_In, // "MemexDataDSN"
CString &strUserID, // "sa"
CString &strPassWord)// "" it is blank
{


CString cause_str;

// Declare The Local Memory Variables
SQLRETURN return_code = SQL_SUCCESS;

// mmk 01.03.2002
// The default user id for SQl Server is "sa"
SQLCHAR DBUser[10]; // = "sa"; // *** hard coded user name


// there is no pswd
SQLCHAR DBPswd[10]; // = _T(""); // *** hard coded password


strcpy((char *) DBUser, strUserID);
strcpy((char *) DBPswd, strPassWord);

SQLINTEGER Terminator;
Terminator = SQL_NTS;

// Connect to access database
// Keep this handle open for as long as you are accessing data
if (ConnHandle != NULL)
{

// data set name
LPTSTR lpszDataSetName = new TCHAR[Db_Name_In.GetLength()+1];

_tcscpy(lpszDataSetName, Db_Name_In);

// connect/open the database
return_code = SQLConnect(ConnHandle,
(SQLCHAR*) lpszDataSetName,
SQL_NTS,
DBUser,
SQL_NTS,
DBPswd,
SQL_NTS);

TCHAR lpError[1024];

// if the sql connection failed
if ((return_code == SQL_ERROR) || (return_code == SQL_INVALID_HANDLE))
{
// delete this before quitting due to failure to connect
delete [] lpszDataSetName;

// Following provides diagnotic for function
cause_str =
CString("Most likely the connection failed due to a wrong server name.\n")
+ CString("The server picked must have the [VersascanMeSQL] database in it.\n\n");

SQLErrors(SQL_HANDLE_DBC, ConnHandle, lpError, cause_str);
return (return_code);
}

delete [] lpszDataSetName;
}

// Return last ODBC return code to calling program.
return(return_code);
}