|
What database are you using? "User" is reserved keyword in SQL Server and may not be used without brackets ([User]). Good practice with SQL Server: use brackets with all table/column names.
Vagif Abilov
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
I'm using Access 2000.
But it's cool man, I've solved the problem, or SimonS solved the problem!
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
There's CDaoWorkspace::CompactDatabase() for MFC but is there a way to do it with OLE DB/ATL ?
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
You can use JRO for this. Create an instanse of "JRO.JetEngine " and call its function CompactDatabase (look in MSDN for more information on this function)
Philip Patrick
Web-site: www.stpworks.com
"Two beer or not two beer?" Shakesbeer
|
|
|
|
|
I know how to create a winform with a DataGrid which enables the user to change values in an Access DB, but now this DB is stored on a server, and I don't know how to create an OleDbConnection to this DB (in C#) because you can't use URLs in the constructor Can someone please help me out?
Thanks, Nicolas
|
|
|
|
|
Isn't it possible to do it as web service?
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Yes, I'm sorry, but I found a good article on this subject in the VS.Net Walktroughs ("Creating a distributed application"). It uses SQL Server, but that's not really a big prob .
THX, Nicolas
|
|
|
|
|
Is it possible to include spaces when you create a table
sSql="CREATE TABLE Graph (Date TEXT, Hour NUMBER,Temp_ºC NUMBER)";
For instance: Temp ºC instead of Temp_ºC
Thanks
|
|
|
|
|
José Luis Sogorb wrote:
sSql="CREATE TABLE Graph (Date TEXT, Hour NUMBER,Temp_ºC NUMBER)";
For instance: Temp ºC instead of Temp_ºC
Yes, enclose the "invalid" table name with [ and ] e.g.
sSql="CREATE TABLE Graph (Date TEXT, Hour NUMBER,[Temp ºC] NUMBER)";
Just also remember when you reference that table to put the [ and ] in. (You can use this technique for reserved table names as well, e.g. Order must be used as [Order].
Paul Watson Bluegrass Cape Town, South Africa
Ray Cassick wrote:
Well I am not female, not gay and I am not Paul Watson
|
|
|
|
|
Hello,
i have a problem that I am sure anyone who writes database Apps has come across. i am looking for the most elegant solution usnig ADO and an access MDB file, via C++
I have been working with ADO for quite a while now, but I am still unsure of the best way to do this.
I have a table which stores values from an email: TO, FROM and SUBJECT.
The TO field could contain multiple email addresses.
Currently I am storing them in a string, seperating each address with a comma.
This method works, but has these (and other) drawbacks:
1) When storing a list of emails, I must first format a string, sperating the emails with a comma
2) When Retrieving the list, I need to read the entire field and parse out the results into an internal list
3) SQL queries, for example, to check if an email exists get a bit complicated.
So, the question is, what alternative methods do you DB experts recommend?
As usual, thanks in advance,
Jeremy.
Jeremy Pullicino
Professional C++ Developer
Done any hacking lately?
|
|
|
|
|
I think it is better to make new table for emails and make relation with other tables.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Create a separate table for the TO entries. It would need just a primary key and the TO value. Each row would contain one addressee. Change the TO field in your original table to be a foreign key that points to the new TO table.
|
|
|
|
|
Has anybody used the .NET data provider for Oracle ?
How did you find it ?
Johan Lombaard
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former - Albert Einstein
|
|
|
|
|
Hi Johan,
Microsoft has released Oracle .NET Data Provider just recently. I have not tried them yet, but with some disgusting experience with OleDB Provider from IBMDADB2 driver and System.Data.OleDB, I have tried moving it to Microsoft.Data.ODBC. The transition was smooth and only one file needed to change.
Perhaps you can try Microsoft.Data.Odbc since it has much stable implementation perhaps.
deepak
|
|
|
|
|
Thanks deepak.
Since I posted my message, I've implemented the Oracle Data Provider in a data import app.
It works like a charm, and is 4X faster than the oledb provider in some cases.
Johan Lombaard
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former - Albert Einstein
|
|
|
|
|
Is there any way to do a multiple inserts in a stored procedure in SQL2000? Please help. Thanks.
|
|
|
|
|
RJS wrote:
Is there any way to do a multiple inserts in a stored procedure in SQL2000?
You could write a while loop inside the stored procedure, there are many ways of doing something like this.
Nick Parker
May your glass be ever full.
May the roof over your head be always strong.
And may you be in heaven half an hour before the devil knows you’re dead. - Irish Blessing
|
|
|
|
|
Can you give more details?
Are these just INSERTS into multiple tables?
Are the INSERTS dependent on each other?
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
Simon,
thanks for the reply.
The scenario is like this:
I have (let's say) 3 records or more in my .net page. Example:
"John", "123 Anywhere", "City1"
"Rudy", "345 Anywhere", "City2"
"Tom", "335 Anywhere", "City2"
...
...
Is it possible to do a multiple inserts in a stored procedure to save those 3 records or more in a table? If so, how do I pass the parameters since I will not know what is the maximum records that will be sent at once? Thanks.
|
|
|
|
|
What you would like to do is the basically pass a recordset to a stored procedure in SQL Server 2000. (?)
The only way I know of to do this is to create an Xml file, pass it as a text (ntext in .Net ) parameter to the stored procedure. And then use the OPENXML SQL statement, which enables you to treat an XML file as a table (check OPENXML in Books Online).
You can then easily use something like
INSERT INTO MyTable (val1, val2, val3)
SELECT val1, val2, val3
FROM OPENXML(...)
It very performant and works like a charm!
Hope this helps,
Morty
|
|
|
|
|
That's one way, Morten.
Another, which is very dependent on the type of data you're passing through, could pass a delimited list (a big string) to the stored proc and use some of the following code:
<br />
DECLARE @Array varchar(50)<br />
DECLARE @iStart int<br />
DECLARE @iEleFound int<br />
DECLARE @iMaxEleLen int<br />
DECLARE @iNextDelimIndex int<br />
DECLARE @iEleLen int<br />
<br />
SET @Array = '1,2,3,4,5,6,7,8,9,65,66,67,100,101,102,654321'<br />
SET @iStart = 0<br />
SET @iEleFound = 0<br />
SET @iMaxEleLen = 5<br />
SET @iNextDelimIndex=0<br />
SET @iEleLen =0<br />
<br />
print '---------'<br />
print 'LENGTH of string: ' + CAST(LEN(@Array) AS VARCHAR)<br />
while CHARINDEX ( ',' , @Array,@iStart ) > 0 -- for each element in the array<br />
begin<br />
SET @iEleLen =1 --default <br />
--if not ',' or ''<br />
if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0)<br />
begin--element found at this index<br />
<br />
SET @iNextDelimIndex = CHARINDEX ( ',' , @Array,@iStart+1) <br />
if(@iNextDelimIndex=0)<br />
begin<br />
SET @iNextDelimIndex = CAST (LEN(@Array) AS VARCHAR)+1<br />
print 'latest element is next:'<br />
end <br />
SET @iEleLen = @iNextDelimIndex - @iStart<br />
--DB function would occur here<br />
print 'NEXT DELIM LOCATION-->' + CAST(@iNextDelimIndex AS VARCHAR(10))<br />
print 'VALUE FOUND-->' + RTRIM( CAST(SUBSTRING(@Array, @iStart + 1, @iNextDelimIndex - @iStart-1) AS char(10)) ) + '<--'<br />
<br />
<br />
SET @iEleFound = @iEleFound + 1 <br />
<br />
<br />
end<br />
<br />
else --no element found at this index<br />
begin<br />
print 'do nothing'<br />
end<br />
<br />
SET @iStart = @iStart + @iEleLen<br />
<br />
end --if (CAST(SUBSTRING(@Array, @iStart + 1, 1) AS char(1)) <> ',') and not(len (SUBSTRING (@Array, @iStart + 1, 1)) = 0)<br />
<br />
print '------ ELEMENTS FOUND: ' + CAST(@iEleFound AS CHAR(2)) + ' ---------'<br />
Note: not fully tested, but should give you a starting point.
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
This is a followup to my previous question in the VisualC++ forum[^]. As no one was able to help me there (so far) and I have traced my problem to a failed SQLGetCursorName() call, I decided that this may be a better forum for my question.
I traced my problem into the CRecordSet::BuildUpdateSQL() function (Line 3394 of dbcore.cpp)
if (m_strCursorName.IsEmpty())
{
RETCODE nRetCode;
TCHAR szCursorName[MAX_CURSOR_NAME+1];
SWORD nLength = _countof(szCursorName)-1;
AFX_SQL_SYNC(::SQLGetCursorName(m_hstmt,
reinterpret_cast<SQLTCHAR *>(szCursorName), _countof(szCursorName), &nLength));
if (!Check(nRetCode))
ThrowDBException(nRetCode);
m_strCursorName = szCursorName;
}
m_strUpdateSQL += m_strCursorName;
Now according to MSDN[^]:
<quote>
Cursor names are used only in positioned update and delete statements (for example, UPDATE table-name ...WHERE CURRENT OF cursor-name). For more information, see "Positioned Update and Delete Statements" in Chapter 12: Updating Data. If the application does not call SQLSetCursorName to define a cursor name, the driver generates a name. This name begins with the letters SQL_CUR.
Note In ODBC 2.x, when there was no open cursor and no name had been set by a call to SQLSetCursorName, a call to SQLGetCursorName returned SQLSTATE HY015 (No cursor name available). In ODBC 3.x, this is no longer true; regardless of when SQLGetCursorName is called, the driver returns the cursor name.
SQLGetCursorName returns the name of a cursor whether or not the name was created explicitly or implicitly. A cursor name is implicitly generated if SQLSetCursorName is not called. SQLSetCursorName can be called to rename a cursor on a statement as long as the cursor is in an allocated or prepared state.
A cursor name that is set either explicitly or implicitly remains set until the StatementHandle with which it is associated is dropped, using SQLFreeHandle with a HandleType of SQL_HANDLE_STMT.
</quote>
So now I can assume that MyODBC is an ODBC version 2.x because of the exception thrown. Now my question is, where would I place a call to SQLSetCursorName() and where do I get the 'statementhandle' needed in the call?
And here I thought this would be easy
CPUA 0x5041
Sonork 100.11743 Chicken Little
"So it can now be written in stone as a testament to humanities achievments "PJ did Pi at CP"." Colin Davies
Within you lies the power for good - Use it!
|
|
|
|
|
Hello,
I'm using an .mdb file as a database for a project i'm working on. I have a series of words that I have to loop through and for each word I have to:
1) Check if the word is already in the table (SELECT COUNT(*) ... )
2) If it is, then update count (UPDATE ... )
3) If does not exists, create entry (INSERT INTO ... )
I'm using OLE DB's CDynamicAccessor with SQL queries but the search part (SELECT COUNT(*) WHERE word = 'blabla') is very slow. Is there a way to make this any faster? I need something like "find first occurence of... ".
By the way, maybe it's because I set the tables poorly (I didn't use indexes and such...) maybe the problem's there!
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
I never used CDynamicAccessor before, but you are hitting too much the database.
use:
UPDATE table SET count = count + 1 WHERE word = 'blabla'
Also, check if there is an index on the word field.
With OLEDB you should have something similar to ADO's RecordsAffected. Then, if RecordsAffected == 0, you execute INSERT INTO
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Success! It's faster but not enough... sometimes it will take up to 20 seconds to go through about 1700 words in a table that contains only 764 words (it will likely contain 10X that amount eventually).
I did set an index for the word field... is there anything else I could do to improve speed and performance?
Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|