| T O P I C R E V I E W |
| jguenrdc |
Posted - 05/10/2006 : 1:21:08 PM
Origin Version (Select Help-->About Origin): OriginPro 7.5 SR6
Operating System: Windows XP Pro SP2
I would like to add all the rows of a worksheet into an SQL server database. I have the database set up, and I can run a query and put the results in a worksheet using the PutRecordset method. Now I would like to add data from a worksheet into the same database. I tried using the UpdateRecordset method, but it returns false, meaning there was an error. No data is added to the database. Can anyone give me some pointers? Is this what the UpdateRecordset method is supposed to do?
Jay |
| 10 L A T E S T R E P L I E S (Newest First) |
| andrew |
Posted - 05/17/2006 : 5:08:30 PM
Hi Jay,
Thanks! I missed the "Identity" case. I did not see your detail codes or DB structure, so I just tried some cases to look the cases may make this method failed.
If you can use "primary key" in your case, you remove data in DB first, or the DB is empty before you put data into DB?
Can you tell me a liitle more information about the cases you use Database related in Origin? As we want to do some improvement in DB in origin8.
Thanks, Andrew
|
| cpyang |
Posted - 05/17/2006 : 12:25:59 AM
Hi Jay
You can release a COM object by
ocrs = NULL;
CP
|
| jguenrdc |
Posted - 05/16/2006 : 4:30:44 PM
One more question:
In the code below, I call the Close method of the ocrs object (see the last line of code). However, when I try to work with the database using SQL Server's tools, it says the database is still being accessed. I have to close Origin. Is there a way to completely close the connection to the database from my code?
static Object ocrs;
void AddData(string strWks = "Data2")
{
ocrs = CreateObject("ADODB.Recordset");
if( !ocrs )
return;
string strConn;
strConn = "Provider=SQLOLEDB; Server=jayg; Database=flocis; User ID=jay; WSID=JAYG; Trusted_Connection=Yes";
string strQuery = "SELECT * FROM flocis.dbo.details";
ocrs.CursorLocation = 3;
ocrs.open( strQuery, strConn, 1, 3);
Worksheet wks(strWks);
if (!wks)
out_str("There is no such worksheet");
BOOL bRet = wks.UpdateRecordset(ocrs,0,-1,0,-1);
out_int("bRet = ", bRet);
ocrs.close();
}
Jay |
| jguenrdc |
Posted - 05/15/2006 : 3:09:54 PM
Andrew,
Thank you for your suggestions.
After doing a lot of testing, I have found that I am able to use a database that has a primary key and some columns that don't allow nulls.
However, I am not able to use a database that has a column set to "Identity", meaning SQL Server assigns a unique incrementing number to each record. Therefore, I will just do the numbering myself.
Thank you again for your help.
Jay |
| andrew |
Posted - 05/15/2006 : 10:12:14 AM
Hi Jay,
I think you codes are right, the key is the right DB setting.
1) No primary Key;
2) Need set "Allow Nulls" for each column
testing procedure:
1) I use a simple MS SQL DB, table name is Person2, 2 columns are text(size is 20), both set "Allow Nulls", no column set "Primary key".
2)I put some data in DB Before I run TestPutDataFromDB();
3)I put some data in worksheet efore I run TestAddDataToDB();
The following are my testing codes based on yours:
void TestPutDataFromDB()
{
Object ocrs, ocfield;
ocrs = CreateObject("ADODB.Recordset");
if( !ocrs )
{
out_str("fail to create Recordset object");
return;
}
string strConn, strQuery;
strConn="Provider=SQLOLEDB.1;Password=test;Persist Security Info=True;User ID=sa;Initial Catalog=test;Data Source=GZ01";
strQuery="Select dbo.person2.* From dbo.person2";
ocrs.CursorLocation = 3;
ocrs.open( strQuery, strConn, 1, 3);
Worksheet wks(Project.ActiveLayer());
if (!wks)
{
out_str("no active worksheet");
return;
}
if ( ! wks.PutRecordset(ocrs) )
{
out_str("fail to put data");
return;
}
out_str("Success!");
}
void TestAddDataToDB()
{
Object ocrs, ocfield;
ocrs = CreateObject("ADODB.Recordset");
if( !ocrs )
{
out_str("fail to create Recordset object");
return;
}
string strConn, strQuery;
strConn="Provider=SQLOLEDB.1;Password=test;Persist Security Info=True;User ID=sa;Initial Catalog=test;Data Source=GZ01";
strQuery="Select dbo.person2.* From dbo.person2";
ocrs.CursorLocation = 3;
ocrs.open( strQuery, strConn, 1, 3);
Worksheet wks(Project.ActiveLayer());
if (!wks)
{
out_str("no active worksheet");
return;
}
if ( !wks.UpdateRecordset(ocrs,0,-1,0,-1) )
{
out_str("fail to add data to DB");
return;
}
out_str("Success!");
}
Edited by - andrew on 05/15/2006 12:48:42 PM |
| jguenrdc |
Posted - 05/12/2006 : 5:08:45 PM
Hello, Andrew.
If I don't have a primary key in my database, would this be the correct code to add new records to the database from a worksheet called "Data2"?
static Object ocrs;
void AddData(string strWks = "Data2")
{
ocrs = CreateObject("ADODB.Recordset");
if( !ocrs )
return;
string strConn;
strConn = "Provider=SQLOLEDB; Server=jayg; Database=flocis; User ID=jay; WSID=JAYG; Trusted_Connection=Yes";
string strQuery = "SELECT * FROM flocis.dbo.details";
ocrs.CursorLocation = 3;
ocrs.open( strQuery, strConn, 1, 3);
Worksheet wks(strWks);
if (!wks)
out_str("There is no such worksheet");
BOOL bRet = wks.UpdateRecordset(ocrs,0,-1,0,-1);
out_int("bRet = ", bRet);
}
// Note: if I start with the worksheet "Data2" empty
// and use wks.PutRecordset(ocrs) instead of
// wks.UpdateRecordset(ocrs,0,-1,0,-1), I do get the
// data that is currently in the database in the
// worksheet, so I know my connection string is OK.
Jay |
| andrew |
Posted - 05/12/2006 : 3:51:52 PM
Hi Jay,
OK, in fact, UpdateRecordset method here works like AddNewRecordset. We will have added a new tracker to fix it in origin 80.
The reason you got false in calling this method maybe result from the DB you used here has set "primary key" in one field. You know DB not allow 2 same vaule data in the same field which is set as "primary key".
Becuase origin used for Data analysis, we did not use "primary key" in DB before.
Please try it again after you remove "primary key" in the DB.
If you have more question, please feel free to ask here.
Thanks, Andrew
Originlab Corp
|
| andrew |
Posted - 05/11/2006 : 10:19:55 PM
Hi Jay,
Could you please put your OC codes including all SQL here for both putrecordset and updaterecordset? More detail information will help me solve this issue earlier.
Or, I will give your a simple sample tomorrow.
Thanks!
Andrew
Originlab Corp |
| jguenrdc |
Posted - 05/11/2006 : 5:51:30 PM
Hello, Deanna.
My goal is to add new records to the database, not update existing ones.
The database is not read only. I am able to add data from another application. Also, I am able to add one record at a time using the ADO AddNew() method from Origin C.
I was hoping UpdateRecordset would add all the rows of the worksheet to the database as new records. Is that what it is supposed to do?
Jay |
| Deanna |
Posted - 05/10/2006 : 11:07:44 PM
Hello, Jay.
The UpdateRecordset method is used for putting the data from the worksheet into the recordset. I think it could help you to update the changes in the worksheet to the database.
I am not sure about what caused the error. Would it be that the database is read-only?
Deanna
OriginLab GZ Office |
|
|
