PDA

View Full Version : need help!



gtadi123
04-10-2002, 08:49 AM
hi,
my task goes like this.............


Through user input I've to update the sqlserver database (table).
I don't know how to establish ODBC connectivity.
Any ideas please!!!!!!!!!!!!!!!!!!!!
Thanks,
Gtadi

LewisQ
04-10-2002, 09:19 AM
Sample:



szSQL = "select App_Name, PCName from Installations";


//Create ADO Connection Object to connect to the SQL server
szADOObjID = "ADODB.Connection";
set pADOObj = CreateObject(szADOObjID);

svDriver = "Microsoft Access Driver (*.mdb)";
svUserPassword = "password";
svDatabaseName = "\\\\nt254\\stdapps\\ACCESS\\install_info.mdb";

//Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "DBQ=" + svDatabaseName;

//Open the ADO Connection
pADOObj.Open(szConnString);

//Create the ADO Command object to execute the script
szADOCommObjID = "ADODB.Command";
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj.ActiveConnection = pADOObj;

//Execute the call to run the script
pADOCommObj.CommandText = szSQL;
set oRs = pADOCommObj.Execute();

while (! oRs.EOF)
szAppName = oRs.Fields("App_Name").Value;
szPCName = oRs.Fields("PCName").Value;
// szFirstName = oRs.Fields("first_name").Value;
// szLastName = oRs.Fields("last_name").Value;

szMessage = "App Name = " + szAppName + ": PC Name = " + szPCName;

MessageBox(szMessage, INFORMATION);
oRs.MoveNext();

endwhile;

oRs.Close();
pADOObj.Close();

gtadi123
04-10-2002, 01:38 PM
hi,
Thank you very much for the code. How to use this code ?
Can you pl.give me steps to execute the code?
I did like this:
I put the entire code in a function and I tried to call that function . But I'm getting lot of errors
pl. suggest me!!
Thanks,
Gtadi

LewisQ
04-10-2002, 02:06 PM
Note several things:

- The sample code is for accessing an Access database thru ADO. You are going to be sure to have MDAC installed.

- Make sure you declare all variable in your function. SOme are OBJECT.

- I don't know what the connection strings should be for SQLServer. We are an Oracle shop. Access is just for testing.

- For this to work, you must have a database with the same structure than mine.

- Use the code as an example but, you will have to chnage it.

Hope it helps,

qiaoli
12-04-2002, 02:24 PM
Hi Luiz,

Can you give us some sample about Oracle DB connection and creating table space? I tried to create a tablespace using following codes. But I failed in open a connection.

function CreateOracleDB(hMSI)
STRING szADOCommObjID, szADOConnObjID, szConnSting;
string svDriver, svServerName, svUserName, svUserPassword, svDatabaseName;
OBJECT pADOConnObj, pADOCommObj;
begin
svDriver = "Oracle in OraHome81";
svServerName = "Myserver";
svUserName = "MyName";
svUserPassword = "aaaaa";
svDatabaseName = "MyDB";
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = "ADODB.Connection";
set pADOConnObj = CreateObject(szADOConnObjID);

// Create the SQL string to complete the connection
szConnSting = "driver={" + svDriver + "};";
szConnSting = szConnSting + "server=" + svServerName + ";";
szConnSting = szConnSting + "uid=" + svUserName + ";";
szConnSting = szConnSting + "pwd=" + svUserPassword;

// Open the ADO Connection
try
pADOConnObj.Open(szConnSting);

// Create the ADO Command object to execute the script
szADOCommObjID = "ADODB.Command";
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj.ActiveConnection = pADOConnObj;
try
// Execute the call to create the database
pADOCommObj.CommandText = "Create database " + svDatabaseName;
pADOCommObj.Execute();
MessageBox("DB_EXIST",INFORMATION);
catch
MessageBox("DB_NOT_EXIST",INFORMATION);
endcatch;
pADOConnObj.Close();

catch
MessageBox("DB_ERROR_INFORMATION",INFORMATION);
endcatch;

end;


Thanks a lot.

qiaoli
12-06-2002, 02:24 PM
I found it. I made these two changes.
1.svDriver = " Microsoft ODBC for Oracle"; instead of svDriver = "Oracle in OraHome81";
2. pADOCommObj.CommandText = "CREATE TABLESPACE " + svDatabaseName + ¡° datafile 'd:\\oracle\\¡± + svDatabaseName + ¡°.ora' SIZE 10M"; instead of pADOCommObj.CommandText = "Create database " + svDatabaseName;

Now I met another problem: How to detect if tablespace exists?

Thanks for helps.