|
Hi, I'm using ODBC to query a database that can be selected by the user at runtime. This query "SELECT * FROM MSysObjects WHERE Type = 1 AND NOT Name LIKE 'MSys%'" will return a list of table names fine, but unfortunately, persmissions on the ACCESS DB must be set to ADMIN for this query to succeed. Is there another way to get table names from a DB without having to first alter persmissions? or is there a programatic way to alter permissions?
Thanks.
"Ergo huffabo et puffabo et tuam domum inflabo" ait magnus malus lupus.
"Non per comam men-men-menti!" ait porcellus.
|
|
|
|
|
Hi, I seemed to have solved it using SQLTables() function....Thanks anyway.
"Ergo huffabo et puffabo et tuam domum inflabo" ait magnus malus lupus.
"Non per comam men-men-menti!" ait porcellus.
|
|
|
|
|
Getting now "Unable to load odbcji32.dll" message box.
Even in "Data Sources (ODBC)" can't configure MS Access DB anymore. What's up?   
Thanks for ideas in advance!
|
|
|
|
|
currently I use the following pseudocode for updating
AddNew(...);
SetFieldValues(Field, Value);
Update(...);
Now I'm updating a LOT of data whereas it takes about 1 minute to finish an update of the database. Is this not untypical for database updates that affect 1000's of records or are their ways to optimize updates of this nature so the user isn't stuck waiting for so long?
thanks!
|
|
|
|
|
That is probably the slowest method of updating data. Other options are:
* Place all inserts inside a single transaction -- will give some speed improvement
* Use SQL insert commands instead, using a precompiled command with parameters. This is very efficient and my recommended choice.
* Use a bulk upload tool, such as BCP, or SQL DTS packages
.NET 2.0 will include better bulk data support, but until then you are stuck with the above options.
my blog
|
|
|
|
|
wot is the latest service pack or update for sql server 2000.
|
|
|
|
|
Check This[^] it might be the final one.
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
I'm wondering what others do when they are updating records in a database. In the application I have I am deleting all records and adding a fresh set. I do this simply because either 1 record to all records could be effected and to keep track of effected records means more code. However is it worth to code an object to keep track of changes or is it simply easier to erase and rebuild?
simply curious.
|
|
|
|
|
Well usually data is linked in some way to other data, and in that case it makes sense to go to the trouble of updating existing records.
If your data lives on its own, with no other data that links to it, then I see no problem with what you are doing.
my blog
|
|
|
|
|
How do I find the column (index) number at runtime of a particular DataGrid column if I know the column header string
//somthing like the following
int x = datagrid.column["ID"];
|
|
|
|
|
This question would be better asked in either c# or asp.net forum...it really has nothing to do with sql or ADO.
How about this (untested):
<br />
int x;<br />
bool bFoundIt=false;<br />
for(int i=0;i<datagrid.Columns.Count && !bFoundIt;i++){<br />
<br />
if (datagrid.Columns[i].HeaderText == "ID"){<br />
x=i;<br />
bFound=true;<br />
}<br />
}<br />
Seems like the kind of thing you could do in a more OO way too...like override the Columns collection...but I won't suggest it without trying it myself first.
Hope this helps,
Bill
|
|
|
|
|
Is there something I can use w/ ADO.NET/Access to determine the value of a primary key on a row that was just inserted? I do this all the time w/ SQL using @@Identity and an output parameter, just wondering if it was possible in Access.
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Hi there. Here's a link[^] to another forum thread that shows an example of doing the same thing - Select @@Identity - in Microsoft Access. This should help you.
|
|
|
|
|
Hi,
I'd like to write a stored proc, which will acomplish the following: I have a users table. I want to update a record with a given ID. Bevor updating the record, I'd like to check whether the DateModified Field of the record equals the @DateModified Parameter, which should be passed to the procedure. Here is what I came up with. I'm actually lacking any experiences with SP's. Here goes:
<br />
CREATE PROCEDURE updateUsers<br />
@ID int, <br />
@Name varchar(50),<br />
@DateModified datetime<br />
AS<br />
<br />
-- compare the datemodified<br />
DECLARE @LastDateModified datetime<br />
SET @LastDateModified = SELECT [Users.DateModified] FROM [Users] WHERE [Users.ID] = @ID<br />
<br />
IF (@LastDateModified <> @DateModified)<br />
BEGIN<br />
RETURN 1<br />
END<br />
ELSE<br />
BEGIN<br />
UPDATE [Users]<br />
SET<br />
Name = @Name,<br />
DateModified = GETDATE()<br />
WHERE<br />
ID = @ID<br />
GO<br />
<br />
RETURN 0<br />
END<br />
<br />
Can anybody help?
Thanks in advance,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Your procedure code should work OK for what you're trying to do. It may have timing issues on a high-volume system if two updates hit at the same time, however. (If another process performs an update on that record between your initial select and your actual update, then your safety check will break.)
Here's an optimized version without the timing problem:
CREATE PROCEDURE updateUsers
@ID int,
@Name varchar(50),
@DateModified datetime
AS
UPDATE
[Users]
SET
Name = @Name,
DateModified = GETDATE()
WHERE
ID = @ID
AND DateModified = @DateModified
IF @@ROWCOUNT = 0
RETURN 1
ELSE
RETURN 0
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
GAH, my extra linefeeds are being stripped! Pfft
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi All,
I have two tables;
master (p_key, father,...)
detail (f_key,childname,...)
populated thus;
master:
1, John,...
2, Jason,...
3, Williams,...
Detail:
1, Steve,...
2, Greg,...
2, Alice,...
3, Mike,...
3, Irma,...
I need to display them as follows in the browser;
1, John, Steve, ...
2, Jason, Greg;Alice, ...
3, Williams, Mike;Irma, ...
I am using ASP 3.0 & MSSQL 7.0 How do I do that ?
Thanks in Advance,
Gangadhara Sanya
|
|
|
|
|
I don't want you to think I'm ignoring you, since I've responded to the posts above and below yours.
It's been so long since I've worked with SQL Server 7 that I'm not quite certain any more what limitations it has in certain areas. (I work with SQL Server 2000.)
The problem is, there are two "tricks" I would use for your particular issue. One of them I know for a fact you can't use on SQL Server 7 (User-Defined Functions.) That can be worked around with a stored procedure, though. The other trick is a way to build a string that's a concatenation of columns from multiple records in a query. That one I don't know if it works in SQL 7.
I'll give you a code snippet of the second trick that you can play with:
DECLARE @PKey int
DECLARE @ChildNames nvarchar(1024)
SET @PKey = 2
SET @ChildNames = STRING(0)
SELECT
@ChildNames = @ChildNames + childname + ";"
FROM
detail
WHERE
f_key = @PKey
SELECT @ChildNames AS childname_concat
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
SQL is not good at presenting data in the format that you want. The best is to get all the data from the database, like this:
SELECT p_key, father, childname
FROM master
INNER JOIN detail ON detail.f_key = master.p_key
ORDER BY master.p_key
The data will look like
1, John, Steve
2, Jason, Greg
2, Jason, Alice
...
You can then process this data in your code, to make it appear the way that you want.
There is a little trick to processing the data -- as you loop through the recordset, keep a local variable that refers to the last p_key value, and when it differs from the current p_key value, then you know that you need to start a new line.
my blog
|
|
|
|
|
Im making a program wherein data is to be retrieved based from the database being selected. My list of database files use the same DataSourceName. Im using SQLConfigDataSource function. And ODBC_CONFIG_SYS_DSN as one of its parameters since only the DBQ attribute or the database path is changing. When I select one database file for the first time then, loads it, the data retrieved is correct. However, when I select another database file for the second time, the data retrieved is the same as the data from the first database file I selected. This means that my recordset was not refreshed. But when I checked the ODBC Administrator, it is updated based on my selection. My problem is I dont know if Im missing other functions or include files in order to refresh the recordset. I tried calling the GetDefaultConnect() function before opening the recordset but the result is still the same... please help me with this problem. Im using the CRecordset....
Does anyone care to help? pls.............
|
|
|
|
|
It's a problem with ODBC that I've seen before.
ODBC configurations are often cached in memory. They're read once and the settings are held in memory until the application exits. This is to improve performance (marginally) be not re-reading the same data from the same text file over and over and over again.
The down side is the problem you've run into. One solution would be to create a new DSN on-the-fly for each connection, with unique names, and use the newly created DSN each time. The only time you'd have caching headaches this way is if you reuse the same DSN name for two different connections.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Im making a program wherein data is to be retrieved based from the database being selected. My list of database files use the same DataSourceName. Im using SQLConfigDataSource function. And ODBC_CONFIG_SYS_DSN as one of its parameters since only the DBQ attribute or the database path is changing. When I select one database file then, loads it, the data retrieved is correct. However, when I select another database file, the data retrieved is the same as the data from the first database file I selected. This means that my recordset was not refreshed. But when I checked the ODBC Administrator, it is updated based on my selection. My problem is I dont know if Im missing other functions or include files in order to refresh the recordset. I tried calling the GetDefaultConnect() function before opening the recordset but the result is still the same... please help me with this problem. Im using the CRecordset....
Does anyone care to help? pls.............
|
|
|
|
|
Hi :
I need to store and retreive a BLOB from and into an Oracle 9i DataBase.
I'm developing an ASP.NET application an i need to display in a web form an image stote as a BLOB.
Does any one have an idea of HOW-TO do that
if you have C# code example i'll really apreciate it!!
thanks
|
|
|
|
|
Using VS 2003
When I select the proper DataSource and DisplayMember in the property window of VS for a combo box does anyone know why when I run the local application clicking on the combo box doesn't provide the actual drop down box NOTE: if I select a different table in that same DS it works I have previewed data, schema, keys, etc
************ Details Follow *********************
1. I Connect to a MS-Access dB, create a DataAdapter (DA1) for Table1 and a DateSet (DS) with Table1
2. I populate a Datagrid and associated Textboxes (TB -Text) w/ the DS.DA1 - all O’K
3. I then add some Combo boxes (CB) to form
4. I drag and drop a Table2 (the dropdown choices for the CB) to create DA2
5. I Configure DA2, "Generate DS" for DA2 using the “Existing DS” and Preview data. The DS Schema shows DA1 and DA2 – all appears O.k.
6. For the CB, I "select" from dropdown DS.DA2 (ie Table2) as the DataSource and a given Table2 column for the DisplayMember
When I run the program no CB drop down available from DS.DA2 (table2). Of course, if I select DS.DA1 for the DataSource it works perfectly. Any Ideas?
Steve
|
|
|
|
|
I have a database under development. For testing I created a couple of tables with the user account 'test_schema' which is their owner.
If I use the SQL below to query the structure of the table as 'sa' then some of the fields come back as null whereas if I perform the same query as 'test_schema' I get all the fields correctly.
DECLARE @table_name varchar(128);
SET @table_name = 'single_pkey';
SELECT cols.COLUMN_NAME as column_name,
cols.DATA_TYPE as type,
cols.CHARACTER_MAXIMUM_LENGTH AS length,
CASE WHEN cols.IS_NULLABLE = 'No' THEN 0 ELSE 1 END as is_nullable,
CASE WHEN pks.column_name IS NULL THEN NULL ELSE cols.ORDINAL_POSITION END AS primary_key,
cols.ORDINAL_POSITION as position,
pks.column_name as pks_column_name -- For testing
FROM INFORMATION_SCHEMA.COLUMNS cols
LEFT OUTER JOIN (SELECT kcu.COLUMN_NAME as column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = @table_name
AND CONSTRAINT_TYPE='PRIMARY KEY') as pks
ON pks.column_name = cols.COLUMN_NAME
WHERE TABLE_NAME = @table_name
ORDER BY cols.ORDINAL_POSITION
As the 'sa' user the [primary_key] column in the result set is always NULL when the owner if 'test_schema'.
Does anyone have any ideas that might help me out?
[If I've missed off some important piece of information then let me know - I'm sure I am just missing something obvious, but I just can't see it right now]
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
