Why I Can't update table (dbf) - Database Discussion Boards

David Salter14-Sep-04 10:59

14-Sep-04 10:59

Supposedly OleDb is faster thsn Odbc because it has to go through fewer layers between your application and database.

In my experience however, this hasn't been the case - they appear to be equal, with the Odbc giving better support for Oracle (although Oracle now provide ODP.NET which should adjust things).

If you google for OLEDB v's ODBC, you'll see several discussions of this sort.

http://www.google.co.uk/search?q=OleDB+vs+ODBC&hl=en&lr=&ie=UTF-8&start=0&sa=N

store your internet favourites online - www.my-faves.co.uk

Re: OdbcDataAdapter OR OleDbDataAdapter

cmk14-Sep-04 15:58

cmk

14-Sep-04 15:58

I'm pretty sure the original OLE DB used to call the ODBC drivers.
Now i think both are developed in parallel - neither depends on the other.

I had always heard/thought the opposite, ODBC is a simpler C API design that has fewer layers. I tend to believe this as i've seen internal COM calling diagrams - i can't believe it is a thinner API.

I still consider ODBC to be a cleaner/faster interface, but OLE DB is more abstract - it allows connections to excel spreadsheets, text files, ...

I tend to still use ODBC for most projects. The only place i'm actually using OLE DB right now is for WinCE development - and that's because it doesn't support ODBC.

...cmk

Save the whales - collect the whole set

Re: OdbcDataAdapter OR OleDbDataAdapter

Salil Khedkar14-Sep-04 20:19

Salil Khedkar

14-Sep-04 20:19

Hey thanks Davey... This will help! Rose | [Rose]

Re: OdbcDataAdapter OR OleDbDataAdapter

Salil Khedkar14-Sep-04 22:32

Salil Khedkar

14-Sep-04 22:32

Thanks guys. I got some amount of info but it mainly described the concept behind OLEDB and ODBC technologies, like:

ODBC is Open Data Base Connectivity, which is a connection method to data sources and other things. It requires that you set up a data source, or what's called a DSN using an SQL driver or other driver if connecting to other database types. Most database systems support ODBC.
OLE is Object Linking and Embedding. OLEDB is partly distinguished from OLE itself, now called
"automation".

OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI
based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2,
mySQL etal. In many cases the OLEDB components offer much better performance than the older ODBC.
OLEDB is a different type of data provider that came about with MS's Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM as of SQL 7.0.

However my quetion is more related with the .NET classes provieded for the same. If I don't want to get tied to a specific database, what is the better choice: OdbcDataAdapter OR OleDbDataAdapter in terms of performance and scalability?

RowUpdated event,, info ????

Md Saleem Navalur13-Sep-04 2:44

Md Saleem Navalur

13-Sep-04 2:44

Hi,

Iam using OleDbCommand to insert/update data in the database (SQL 2000).

IS it possible to handle "RowUpdated event" without using OleDbDataAdapter ??

Are there any other methods for handling the updated row event ?

I Need this to get the value of an Auto increment field.

Any help will be greatly appreciated.

Thanx
saleem

CDaoDatabase connect string in Windows XP

olis12-Sep-04 5:34

olis

12-Sep-04 5:34

Hello.

I try to Open CDaoDatabase object in Windows XP (the user has the Excel
2000), and I use the "Excel 8.0" connect string. It returns the following
error "Couldn't Find Installable ISAM". BUT the same code works in Windows
2000. I tried the "Excel 9.0" and "Excel 10.0", but without success...

CDaoDatabase* p_daoDB = NULL;

p_daoDB = new CDaoDatabase;
p_daoDB->Open(_T("SCCardOS.XLS"), // file name
FALSE, // exclusive
FALSE, // read only
_T("Excel 8.0;")); // connect string

I found the post with the same problem, but without solution ;(
I need some tips for solving...

Thank you in advance.

Reporting Service

architechnology.de11-Sep-04 5:00

architechnology.de

11-Sep-04 5:00

In the used Dataset (e. g. 'Dataset1') we have some colums with GUIDs /DSNs.

Within the Report-Layout we want to render the related Values to these GUIDs (e.g. the name to an Adress-GUID), which are kept in another table (e.g. 'adresses').

For example, we used the FoxPro-Report Engine in earlier times and there was a Command named 'SEEK', which pointed the cursor to en other destination! Is there something comparable in Reporting Service or how does it work the best way?

Thanks in advance for help !

Why I Can't update table (dbf)

delemur11-Sep-04 3:29

delemur

11-Sep-04 3:29

Hi. (Firstly, sorry for my English)
I have a set of .dbf files (tables) in directory
I want to work with this files as database with ADO.NET
(c#)
I have no problem when reading data, but I can't change it.
For example, i have a file "test.dbf" in folder "d:\db".
The test.dbf is a table with 2 columns
- CODE (number[4])
- NAME (character[20])
The table has a one record (0,"joan")
I need to add a new record to this table (1,"alex")

Code:

<br />
string TableName		= "test";<br />
string SelectString		= "SELECT * FROM " + TableName;<br />
string DirectoryName	= @"d:\db";<br />
string ConnectString	= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +<br />
	DirectoryName + ";Extended Properties=dBase 5.0;";<br />
<br />
OleDbConnection myConn			= new OleDbConnection(ConnectString);<br />
OleDbDataAdapter myDataAdapter	= new OleDbDataAdapter();<br />
myDataAdapter.SelectCommand		= new OleDbCommand(SelectString, myConn);<br />
<br />
myConn.Open();<br />
<br />
DataSet ds = new DataSet();<br />
myDataAdapter.Fill(ds, TableName);<br />
<br />
DataTable t = ds.Tables[TableName];<br />
<br />
//code to modify data in DataSet here<br />
DataRow dr = t.NewRow();<br />
dr["CODE"] = "1";<br />
dr["NAME"] = "alex";<br />
t.Rows.Add(dr);<br />
<br />
myDataAdapter.Update(ds, TableName); // ERROR OCCURS HERE<br />
myConn.Close();<br />

When I run "Update" method of OleDbAdapter
error occurs.

"Update requires a valid InsertCommand when passed DataRow collection with new rows."

I asked about this problem in russian forums many times,
but nobody can answer me exactly.
I understatnd that I have to specify the InsertCommand of OleDbDataAdapter but how I have to do it I don't.

Could you answer me, what "InsertCommand" must be or (it would be better) give an examle.
Thank you.

Re: Why I Can't update table (dbf)

sreejith ss nair11-Sep-04 23:50

sreejith ss nair

11-Sep-04 23:50

hi,

Here you are trying to update your data table with out any update command.

Suppose if you want to call like this

DataRow dr = t.NewRow();
dr["CODE"] = "1";
dr["NAME"] = "alex";
t.Rows.Add(dr);

myDataAdapter.Update(ds, TableName); // ERROR OCCURS HERE
myConn.Close();

You need to mention the update command. Other wise
it won't update the data.
So first define an update command for your data adapter.
This is also true for Delete, insert etc.

**************************
S r e e j i t h N a i r
**************************

Writing ActiveX Controls

DustinMiles10-Sep-04 11:33

DustinMiles

10-Sep-04 11:33

Are there any good resources out there for learning how to write ActiveX Controls? I am looking primarily for ones dealing with VC++ or C# (although from what I've read, traditional ActiveX isn't possible in managed code, only with .NET Framework installed on the client machine). I have searched a good amount on the web, but have been unsuccessful thus far in finding anything too useful to the Beginner.

Thanks.

Re: Writing ActiveX Controls

Steven Campbell10-Sep-04 18:37

Steven Campbell

10-Sep-04 18:37

This is not really the best forum for this question, but I'll answer as best I can...

By far the easiest language to develop ActiveX controls in is VB6.

You are correct that it is not possible to develop ActiveX controls in managed code (at least without some unsupported hacks). There are .NET equivalents though, both on the ASP.NET side and the WinForms side.

my blog

Is there a performance difference?

Marc Clifton10-Sep-04 8:13

Marc Clifton

10-Sep-04 8:13

between:

SELECT * FROM
Blah1 as T1
INNER JOIN
Blah2 as T2
ON T1.ID=T2.ID
INNER JOIN
Blah3 as T3
ON T1.Id=T3.ID ;

vs.

SELECT *
FROM Blah1 as T1 ,Blah2 as T2,Blah3 as T3
WHERE T1.ID=T2.ID AND T1.ID=T3.ID ;

I've never really understood the advantage of the first syntax, and I'm always using the second syntax.

Marc

MyXaml
Advanced Unit Testing

Re: Is there a performance difference?

Grimolfr10-Sep-04 9:15

Grimolfr

10-Sep-04 9:15

The advantage of the first syntax is legibility. It visually and logically separates the evaluations that are specifically used to define the join(s) from the evaluations that are only used to filter the joined results.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: Is there a performance difference?

je_gonzalez10-Sep-04 10:45

je_gonzalez

10-Sep-04 10:45

If you have access to SQL Query Analyzer, type each statement and then Ctrl + L (Display Extimated Execution Plan).

This will display what steps the server will do and when you move the mouse pointer over each step it will tell you the estimated cost in CPU cycles, using the database current stats.

Re: Is there a performance difference?

Steven Campbell10-Sep-04 18:33

Steven Campbell

10-Sep-04 18:33

The first syntax is far superior in my mind, for the following reasons:
1) legibility
2) ease of maintenance - easily add/remove a join/condition
3) flexibility - it is easier to construct complex queries this way, especially those containing outer joins and/or complex joins.

I do think it is possible that the 1st syntax will lead to better optimized queries, because it gives the optimizer explicit information on what is a join and what is part of the WHERE clause. In a simple query it probably makes no difference, but once the query becomes complex, I'm sure it helps the query optimizer to know these things.

Final point on legibility - I usually write my queries something like this

SELECT * 
FROM Blah1 as T1
   INNER JOIN Blah2 as T2 ON T1.ID=T2.ID
   INNER JOIN Blah3 as T3 ON T1.Id=T3.ID

which is much more legible.

my blog

Re: Is there a performance difference?

Mike Ellison12-Sep-04 5:35

Mike Ellison

12-Sep-04 5:35

Hi all. For what it's worth, the WHERE clause syntax for creating joins was part of the ASNI SQL/86 standard. The INNER JOIN / OUTER JOIN syntax became part of the ASNI SQL/92 standard.

In addition to the other comments made, the JOIN syntax also allows for more functionality, such as the FULL OUTER JOIN supported in some RDBMSs.

How do I clear a datasource from a bound control?

Bart-Man10-Sep-04 4:05

Bart-Man

10-Sep-04 4:05

Greetings,

I have a combo box which I have bound to a datasource, and I want to remove the datasource so it doesn't have anything. I have to use it display some seperate text information.

(ie) How I set the datasource.
comboboxA.DataSource=DataSetTest;
comboboxA.DisplayMember="TableField1";

You can clear a datasource if you bind to a binding manager like this, and show 1 field at a time.
(ie) binding the object
comboboxA.DataBindings.Add("Text", DataSetTest, "TableField1");

(ie) To remove the binding you call.
comboboxA.DataBindings.Clear();

Is there an equivalent for removing a binding on a control while using a datasource?

Thanks in advance for any help! Smile | :)

Re: How do I clear a datasource from a bound control?

sreejith ss nair11-Sep-04 23:55

sreejith ss nair

11-Sep-04 23:55

hi,

Try this,

Combo.DataSource=null;

**************************
S r e e j i t h N a i r
**************************

tSQL query

partt10-Sep-04 2:57

partt

10-Sep-04 2:57

I have this code below, but I want the "Available" column to be the @@Rowcount specific to the GroupID. Right now with what I figured below, it subtracts the rowcount from both GroupID 859 and 860.

CREATE PROCEDURE usp_Groups
@StudyNo char(16)
AS
declare @Fill table (GroupID varchar(5), TesterID char(7))
declare @Ct table (GroupID varchar(5), TesterID char(7), Filled varchar(4))
declare @Out table (GroupID varchar(5), TesterID char(7), Filled varchar(4))

insert @Fill
SELECT DISTINCT tGroup.GroupID, tResultQue.TesterID
FROM tGroup INNER JOIN
tResultQue ON tGroup.GroupID = tResultQue.GroupID
where (tResultQue.QueStatusID >= '20') and
(tResultQue.QueStatusID <= '22') and tGroup.StudyNo = @StudyNo

insert @Ct
select distinct F.GroupID, F.TesterID, @@Rowcount as Filled
from @Fill F
Group by F.GroupID, F.TesterID

insert @Out
select distinct F.GroupID, F.TesterID, Ct.Filled
from @Fill F left join @Ct Ct on (F.GroupID = Ct.GroupID)

SELECT distinct tGroup.GroupID, tGroup.ScheduleStamp, tFacility.FacilitiesDescription, tGroup.NotToExceed, (tGroup.NotToExceed- Gonzo.Filled) as Available
FROM tGroup INNER JOIN
tFacility ON tGroup.FacilityID = tFacility.FacilityID left join @Out Gonzo on (tGroup.GroupID = Gonzo.GroupID) left join @Fill J on (tGroup.GroupID = J.GroupID) left join @Ct Cet on (tGroup.GroupID = Cet.GroupID)
where tGroup.StudyNo = @StudyNo
GO
*************************************************
This is what the output looks like:
GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available
-----------------------------------------------------------------
859|2004-02-24|Building A|15|11
860|2004-02-24|Building A|10|6
861|2004-05-04|Building A|10|NULL
***************************************************
This is what it should look like:
GroupID|ScheduleStamp|FacilitiesDescription|NotToExceed|Available
-----------------------------------------------------------------
859|2004-02-24|Building A|15|12
860|2004-02-24|Building A|10|9
861|2004-05-04|Building A|10|NULL

Thank You!

Re: tSQL query

partt10-Sep-04 3:06

partt

10-Sep-04 3:06

Oops, ok, problem solved.
Thank you.

Getting Server List.

Sumit Domyan9-Sep-04 21:17

Sumit Domyan

9-Sep-04 21:17

Hello All
I want to get a list of all available SQL servers. And then the databases under each server.
Any Solution?

Sumit Domyan
Software Developer
ANALEC INFOTECH

Re: Getting Server List.

David Salter9-Sep-04 22:39

David Salter

9-Sep-04 22:39

The followig links should show you how to get a list of all SQL Servers (or all servers of a particuler type) on your network.

http://www.codeproject.com/csharp/servercombobox.asp[^]

http://codeproject.com/database/serverenum.asp[^]

If you want a list of all the databases in a particular SQL Server instance, use the following SQL

select catalog_name from information_schema.schemata

store your internet favourites online - www.my-faves.co.uk

not detecting local MSDE

Steven Dahlin9-Sep-04 17:00

Steven Dahlin

9-Sep-04 17:00

I have been using the code found in the article: "Finding SQL Servers on the Network" to find any local MSDE/SQL Server installation as well as any remote sites. However, I have found that sometimes after MSDE is installed and the OS has been rebooted and the services indicate that it is up and running I have found that the code will not detect the local MSDE installation.

Sometimes, if I wait a while it will then show up. Sometimes it will never show up. Does anyone have any ideas why this might be??

Thanks,
Steve

Re: not detecting local MSDE

Christian Graus9-Sep-04 17:28

Christian Graus

9-Sep-04 17:28

MSDE takes a while to start, maybe that's your problem ?

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Extremely slow performance of OleDbDataAdapter.Fill method

rotarinn9-Sep-04 7:05

rotarinn

9-Sep-04 7:05

Hi, I am having a big problem with performance of the OleDbDataAdapter.Fill method on other computers than mine. I am using VisualStudio.NET 2003 to develop my application and it works fine on my computer, both in debug mode and when I build a release version and run that. However, if I set up the release version on another computer, it is extremely slow.

This is the code that fills my dataset

public DsDef getDefFiltered(int nIdVersion)
{
	createDefDataAdaptersFiltered(nIdVersion);
	//My strongly typed DataSet is DsDef
	DsDef dsDef = new DsDef();
	daApplication.Fill(dsDef.tblApplication);
	daLineType.Fill(dsDef.tblLineType);
	daVersion.Fill(dsDef.tblVersion );
	//and a few other da..Fill methods

	return dsDef;
}

My dataadapters are created like this. The conn object is an OleDbConnection object:

daApplication = new OleDbDataAdapter("SELECT * FROM tblApplication", conn);

None of the tables that are being fetched with the dataadapters have very many rows, the may vary from 10 to 150, so the amount of data shouldn't be the problem.

On my development machine this method takes 1.4 seconds but on another machine it takes 19.1 seconds. The same difference in performance applies to all other methods that use the OleDbDataAdapter.Fill method.

I have installed the .NET Framework on all machines and tried with both MDAC 2.7 SP1 and MDAC 2.8 installed, but still have this terrible performance.

Do you guys have any idea what might be causing this?? All suggestions are appreciated.

-Rotarinn

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.