|
Hi there. Well, sure, you could use the sql UPDATE statement to modify existing records. In your circumstance, you may wish to write code that selects the set of records from your source, then loop through that set issuing UPDATE statements for each record to modify the data.
|
|
|
|
|
Mike:
Does that mean I need a SELECT statement, than a loop to read records, and than a UPDATE statement to modify the records? How do I SELECT and read the records on temporary basis to compare the data?
|
|
|
|
|
I am trying to load very large amounts of data into a table (Access for now, SQL server later) from a C++/ADO application. There are about 200,000 rows consisting of 7 columns, all numbers. I have used the regular ADO RecordSet object AddNew method (~15 minutes), same but disconnecting and then reconnecting the RecordSet with a batch update (~5 minutes), and finally creating a SQL INSERT statement for direct execution (~5 minutes). I have removed most indexes on the database to facilitate insertions.
Is there a faster way to do this? Given that the computations to create the data only take 10 seconds, it seems like a waste to spend 15 minutes uploading it to a database.
Thanks
Mark Jackson
|
|
|
|
|
SQL should be a lot faster. And if network speed is a bottleneck consider running the application on the server.
SQL insert is not the fastest way of doing things. Consider looking into bulk insert methods.
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
FWIW, I got around this by creating a huge buffer to hold the data, pasting it to the clipboard, and using a COM object to get Access to paste the data. It is ugly as it appears you cannot import the Access library due to bugs, so I used old-fashioned COM. It was worth the effort as program run time went from over 5 minutes to under 40 seconds.
Mark Jackson
void mrgVaRProdList::uploadList(void)
{
#define APPEND_LINE_SIZE 120
mrgDataSrc * ds;
mrgRecSet rs;
list<mrgVaRPoint *>::iterator iter;
int i;
unsigned long _l, counter;
Date bd, d1, d2;
CString str, buf;
char * chr;
size_t bufLen;
HGLOBAL h;
HWND wh = GetConsoleHWND();
ds = _mParent->getDataObj();
rs.setDataSrc(ds);
bd.setDate(_mParent->getAsOfDate());
str = bd.getDateStr();
i = (int)_uploadList.size();
bufLen = i * APPEND_LINE_SIZE;
h = GlobalAlloc(GHND, bufLen);
try
{
if (!OpenClipboard(wh))
{
throw("OpenClipboard");
}
if (!EmptyClipboard())
{
throw("EmptyClipboard");
}
rs.ExecSQL("Delete * from VaRData");
iter = _uploadList.begin();
counter = 1;
_l = 0;
chr = (char *)GlobalLock(h);
while (iter != _uploadList.end())
{
buf.Format("%u\t%s\t", counter, str);
buf.AppendFormat("%u\t%u\t", (*iter)->idOne, (*iter)->idTwo);
d1.setDate((*iter)->bukOne);
d2.setDate((*iter)->bukTwo);
buf.AppendFormat("%s\t%s\t", d1.getDateStr(), d2.getDateStr());
buf.AppendFormat("%f\t%f\t%f\r\n", (*iter)->rho, (*iter)->sigmaOne, (*iter)->sigmaTwo);
for (i = 0; i < buf.GetLength(); i++)
{
chr[_l] = buf.GetAt(i);
_l++;
}
counter++;
iter++;
}
GlobalUnlock(h);
if (SetClipboardData(CF_TEXT, h) == NULL)
{
throw("SetClipboardData");
}
CloseClipboard();
}
catch(_com_error e)
{
PrintADOError(_T("ADO Error mrgVaRProdList.uploadList() : "), &e);
}
catch(const char *c)
{
LPVOID lpMsgBuf;
FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM |
FORMAT_MESSAGE_IGNORE_INSERTS,
NULL,
GetLastError(),
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), // Default language
(LPTSTR) &lpMsgBuf,
0,
NULL
);
str.SetString((LPTSTR)lpMsgBuf);
LocalFree(lpMsgBuf);
cout << "Error in mrgVaRProdList.uploadList()-" << c << ":" << str.AllocSysString() << endl;
}
try
{
// our ID's
CLSID clsid;
IUnknown *pUnk;
IDispatch *pDisp;
IDispatch *pDispDoCmd;
DISPID dispid_DoCmd;
DISPID dispid_OpenTable;
DISPID dispid_RunCommand;
//parameter setup
DISPPARAMS dispparamsNoArgs = {NULL, NULL, 0, 0};
OLECHAR FAR* szFunction;
// for getting/passing results
HRESULT hr;
VARIANT varResult;
// arguments for methods
VARIANT varArgs[1];
DISPPARAMS dpArgs;
BSTR bstrTemp;
CLSIDFromProgID(L"Access.Application", &clsid);
// Get an interface to the running instance, if any..
hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);
if (hr < 0)
{
// TODO - we need to start an instance
throw("Error GetActiveObject");
}
// Get IDispatch interface for Automation...
hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pDisp);
if (hr < 0)
throw("Error QueryInterface - IID_IDispatch");
// Release the no-longer-needed IUnknown...
pUnk->Release();
szFunction = OLESTR("DoCmd");
hr = pDisp->GetIDsOfNames(IID_NULL, &szFunction, 1, LOCALE_USER_DEFAULT, &dispid_DoCmd);
if (hr < 0)
{
pDisp->Release();
throw("Error GetIDsOfNames for DoCmd");
}
hr = pDisp->Invoke(dispid_DoCmd, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET,
&dispparamsNoArgs, &varResult, NULL, NULL);
if (hr < 0)
{
pDisp->Release();
throw("Error Invoke for DoCmd");
}
pDispDoCmd = varResult.pdispVal;
szFunction = OLESTR("OpenTable");
hr = pDispDoCmd->GetIDsOfNames(IID_NULL, &szFunction, 1, LOCALE_USER_DEFAULT, &dispid_OpenTable);
if (hr < 0)
{
pDisp->Release();
pDispDoCmd->Release();
throw("Error GetIDsOfNames for OpenTable");
}
bstrTemp = ::SysAllocString(OLESTR("VaRData"));
varArgs[0].vt = VT_BSTR;
varArgs[0].bstrVal = bstrTemp;
dpArgs.cArgs = 1;
dpArgs.cNamedArgs = 0;
dpArgs.rgvarg = varArgs;
//Invoke the OpenTable Method
hr = pDispDoCmd->Invoke(dispid_OpenTable, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpArgs, NULL, NULL, NULL);
::SysFreeString(bstrTemp);
if (hr < 0)
{
pDisp->Release();
pDispDoCmd->Release();
throw("Error Invoking OpenTable");
}
szFunction = OLESTR("RunCommand");
hr = pDispDoCmd->GetIDsOfNames(IID_NULL, &szFunction, 1,
LOCALE_USER_DEFAULT, &dispid_RunCommand);
if (hr < 0)
{
pDisp->Release();
pDispDoCmd->Release();
throw("Error GetIDsOfNames RunCommand");
}
varArgs[0].vt = VT_I2;
varArgs[0].iVal = 0x26; // acCmdPasteAppend
dpArgs.cArgs = 1;
dpArgs.cNamedArgs = 0;
dpArgs.rgvarg = varArgs;
hr = pDispDoCmd->Invoke(dispid_RunCommand, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpArgs, NULL, NULL, NULL);
// release interface pointers
pDispDoCmd->Release();
pDisp->Release();
}
catch(_com_error e)
{
PrintADOError(_T("ADO Error mrgVaRProdList.uploadList() : "), &e);
}
catch(const char *c)
{
cout << "Error in mrgVaRProdList.uploadList()-" << c << endl;
}
return;
}
|
|
|
|
|
i need to use the column names instead of passing magic numbers in my code (i.e. the column position in the select statement).. but it seems that i can't(??) do this??
OracleDataReader reader..;
1st way: NOT READABLE
while (reader.Read())
{
// get the value of 1st column for current row
if (!reader.IsDBNumm(0))
string s = reader.GetString(0);
..
}
2nd way: i can use reader["column_name"], but there is no overload function to test for null value!!! --> STILL NOT READABLE!!!??
while (reader.Read())
{
// get the value of 1st column for current row
if (!reader.IsDBNumm(0))
string s = reader["name"];
..
}
so.. how can i get rid of the magic numbers???
g.
|
|
|
|
|
Hi
Please help me out here with some hierarchies. It’s blowing my mind.
Here’s the table…
CREATE TABLE [dbo].[Tree] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[Lineage] [varchar] (50) NULL ,
[Name] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
Here’s some data…
SET IDENTITY_INSERT Tree ON
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (1, NULL, '.0001.', 'Root')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (2, 1, '.0001.0002.', 'I')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (3, 1, '.0001.0003.', 'H')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (4, 1, '.0001.0004.', 'G')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (5, 2, '.0001.0002.0005.', 'F')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (6, 2, '.0001.0002.0006.', 'E')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (7, 2, '.0001.0002.0007.', 'D')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (8, 4, '.0001.0004.0008.', 'C')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (9, 4, '.0001.0004.0009.', 'B')
INSERT INTO Tree (ID, ParentID, Lineage, Name) VALUES (10, 3, '.0001.0003.0010.', 'A')
SET IDENTITY_INSERT Tree OFF
You have the above tree data. You can ignore the lineage if you don’t want to work with it.
There problem is as follows…
Please sort the tree nodes according to the hierarchy, BUT the nodes must also be in ALPHABETICAL order.
So “SELECT * FROM Tree ORDER BY Lineage” will look like so…
Root
I
F
E
D
H
A
G
C
B
But I need it to be sorted in that hierarchy but also alphabetically like so
Root
G
B
C
H
A
I
D
E
F
Can someone figure this out for me please?
Regards
Dirk
|
|
|
|
|
Sounds like homework 
Try:
SELECT Name FROM Tree ORDER BY (Lineage + Name) DESC
|
|
|
|
|
Just a comment. Think a little more about your 'lineage' field. It seems like you are trying to re-create a character representation for data that already exists through the 'ID' and 'ParentID' fields. I think if you create a view that self joins the table based on the ParentID column you might be farther ahead.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
Hoping someone can help me. I am trying to implement a custom built paging control. I need to retrieve the total number of records as well as only select the number of records I require from the stored procedure and extract these two variables using a sqlDataAdapter.
My sql query is as follows ... would prefer not to use "tomato sauce" but this is the only solution I can think of - however i'm still not getting results .....
And how do I extract these two variables into my sqldataAdapter ...?????
Any advices would be very welcome .. thank you.
My stored procedure ...
alter PROCEDURE [dbo].[prc_tbUser_GetAllPaged1]
--create variable for NumRec
@From int,
@To int
AS
DECLARE @SQL varchar
DECLARE @SQLCount varchar
DECLARE @sSQL varchar
DECLARE @NumRec varchar
set @NumRec = @To - @From
set @SQLCount = 'Declare @TotalRecords int, @NumRec int '
set @SQLCount = @SQLCount + 'SELECT @TotalRecords = count(UserId) from tbUser'
set @SQL = 'SELECT a.Name, a.UserID, a.Login, b.Name as OrganisationName, c.Description as Status
FROM tbUser a
INNER JOIN tbOrganisation b
ON a.OrganisationID = b.OrganisationID
INNER JOIN tbStatus c
ON a.StatusID = c.StatusID'
set @sSQL = @SQLCount + ' SELECT top ' + @NumRec + ' @TotalRecords as NumRecords ' + @SQL
print @sSQL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--prc_tbUser_GetAllPaged1 20,40
and this is the sqlDataAdapter ...
connection = new SqlConnection( Configuration.ConnectionString );
command = new SqlCommand( "prc_tbUser_GetAllPaged1", connection );
command.CommandType = CommandType.StoredProcedure;
//From value
command.Parameters.Add( "@From", SqlDbType.Int ).Value = iFrom;
//To value
command.Parameters.Add( "@To", SqlDbType.Int ).Value = iTo;
//need to add another column to read total number of records return
command.Parameters.Add("@TotalRecords", SqlDbType.Int);
// Open the connection
connection.Open();
//create the data adapter
SqlDataAdapter oDA = new SqlDataAdapter(command);
//Gets the parameters set by the user when executing an SQL SELECT statement.
oDA.GetFillParameters();
//execute the reader
oDA.Fill(oDS,iFrom,iTo - iFrom, "tbUser");
return oDS
|
|
|
|
|
Hi everybody
I have created an installer using NSIS,but I am realy having trouble finding the code to attach the database within the installer.I dont know if its maybe my RegKey or what,but the database does not want to attach.If anyone knows anything about NSIS,or maybe a diff RegKey that I can try,or even some coding to help me,please do.
Thanks so much.
Kind Regards
Mattie20
Hello.Thanks for your help
|
|
|
|
|
Hi,
Can anybody tell me how I can get nested transactions going with ADO.Net and the SqlClient classes?
Any articles or sample code would be helpfull.
thanks
Wouter
|
|
|
|
|
hi,
What is the SQL querry for not having negative values in the table?
thanks,jijo
Jijo kuruvila
software developer
trivandrum
|
|
|
|
|
john kuruvila wrote:
What is the SQL querry for not having negative values in the table?
When you create the table you set a constraint like this:
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
thanx....
Jijo kuruvila
software developer
trivandrum
|
|
|
|
|
i have an sql table.it has one column totalitems.it should not be negative values.how can i accomplish this task?
thanks in advance
|
|
|
|
|
When you create the table you set a constraint like this:
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
thank you,it worked well.When a user purchases some things the totalitems in ptable is decremented according to it.when totalitems in ptable becomes 0, user cannot update.this was the problem.yes, i solved it using consraints.can i make the validation at the client side ?
|
|
|
|
|
|
the userinterface has a textbox and a button.the user must enter the number of items he wishes to buy and then enter the submit button.the stock table in server side contains the TotalItems of a particular product.if TotalItems is zero,the user should not be allowed.I have added the check constraint in serverside "check(TotalItems>=0)" as you said and done it successfully.so the question is can it be done at client side?
|
|
|
|
|
|
I've posted a message here yesterday, but it seems that i was mis-understood.
I'm using vb6 ADO's Recordset.
i want to use the recordset's Filter property.
but the target column is numeric (the ID column of the table, int).
i want to filter the table, so i'll stay with the rows that begins with a certain number, just as a varchar column i'll use "LIKE" filter.
when trying to do it, i get an error "Filter can not be opened."
any ideas?
thanks
|
|
|
|
|
you could put a textbox on your form, and in the code, use the textboxes text property in your filter. create a varible and set the val(textbox) = to your varible and search that way
ex.
dim SQLSearch as integer
sqlSearch = Val(Text1.Text)
"SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
the more you type in the textbox, the closer your search will be
please note the use of the % in the SQL Query
you could even put your search into the textboxes textchange ()
if your using ado
ex. ado.recordsource = "SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
ado.refresh
so as you type, the the search gets smaller
Hope this helps
Help is great only if you ask correctly 
|
|
|
|
|
i built a database in excel sheet. everything seems to be ok except deleting records. i defined everything and opened database.
INSERT INTO works properly but when i use
Sql_string = "DELETE FROM test_base WHERE ID='"+ID_act+"'";
database.ExecuteSQL(sSql);
i get an error - "database error: could not delete from specified tables"
i have no clue why... anyone can provide some working code snippet?
|
|
|
|
|
Is the ID a string ? If it's an integer, try removing the quotes.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
