Re: CDao MFC classes and a Access 97 DB - Database Discussion Boards

Re: Microsoft SQL 2 tables alias GROUP BY questions

Edbert P3-Feb-04 11:50

3-Feb-04 11:50

I'm not sure whether this may help, but it doesn't hurt to try:

1. Index your tables, especially A.Number, B.Number, B.Time. I don't think indexing A.New_Name will help if you're using wildcard. Indexing your tables will help the performance when you're doing a search.

2. Do not write your SQL statements in the VBScript. Write them as Views (queries) in the SQL Database.
Views are precompiled (correct me if I'm wrong) and runs faster than a statement passed through VBScript.
You can then call the views from your VBScript (e.g. 'SELECT * FROM qryAB').

3. I'm guessing the reason the query is taking too long is the wildcard used. You may want to create a query just to join tables A and B (SELECT A.New_Name, A.Old_Name, B.Value, B.Time FROM A INNER JOIN B ON A.Number = B.Number WHERE B.Time BETWEEN '2003-12-01' AND '2004-01-30'). If this query doesn't take a long time then the wildcard is the cause.
After that, you can split the next View into two to get the final result, i.e.
"SELECT * FROM firstQuery WHERE New_Name LIKE '%mw%'"
"SELECT * FROM secondQuery WHERE New_Name LIKE '%mw'"
If you need the results as one then you don't need to split the Views. But if you're fine with it, there are ways to split the query so you can work on smaller portions of the data.

I hope it helps Smile | :)

Reading in an XML File into a DataSet

Member 64401630-Jan-04 7:55

Member 644016

30-Jan-04 7:55

I am trying to read an XML document into a .NET DataSet object. I used the XSD.EXE utility to generate a schema file for this XML document. This schema has re-ocurring elements that I would like to load as records into my DataSet. However, I only want to load certain child elements as fields into the DataSet record.

Is there a way I can modify this schema file such that I can use the DataSet.ReadXml method to parse this XML file and create the proper table rows??

Thanks

Re: Reading in an XML File into a DataSet

Mazdak31-Jan-04 5:36

Mazdak

31-Jan-04 5:36

I don't know if this is good way or not, DataSet.ReadXML() accept stream of XML or XML Text, with XML classes such as XMLReader or XPath classes for XML first load your proper nodes into XMLReader or String and then pass this parameter into DataSet.ReadXML().

Mazy

No sig. available now.

ADO.NET,C#, and MS:Access

Nick Jacobs30-Jan-04 5:38

Nick Jacobs

30-Jan-04 5:38

Hi Gang,
Here's what I've got,
I've got information stored in one table/database that I'd like to move to another table/database.

Sounds pretty simple doesn't it?

Well, here's where the pickle comes into play.

The tables could potentially be dynamic. So I can't used a predefined DataSet to do the moves, so I have to recreate the datasets each time. That part isn't even necessarily the problem.

The problem I'm running into is:

What happens when my tables have memo fields in them?
Some of our tables have Ole embedded (Not my choice, but I have to live with it) in them that also need to be mapped.

I'm trying to do this all with ado.net using C#.

If I can't I'll probably just end up writing it in VB6. I know we can do it in there, we have. But then again, that's using DAO, not ADO.Net.

Anybody have any suggestions?

Thanks,
Nick

Env: Studio 2003, MS:Access 2000 Databases, WinXP Pro+Patched

This are my own opinions. You know the rest.....

CDao MFC classes and a Access 97 DB

Phil.Benson29-Jan-04 21:46

Phil.Benson

29-Jan-04 21:46

Hi, I´m using the CDao classes from the MFC to manipulate and store data in an Access 97 db, using the Jet 3.5. engine. I was wondering if there are any good books that someone could suggest for reading. Any thing I´ve found sofar is all to to with VB. Even the various help files use VB examples. (I´m using Dev Studio 97 VC++ 5.0).

Normal opererations like deleteing,adding,modifying,
querydef´s and the like is quite simple, but creating relationshiups between tables is quite annoying, and the msdn does not help that much.

Thanks in advance

Phil

bum... and I thought I´d got rid of all the bugs Frown | :(

Re: CDao MFC classes and a Access 97 DB

Steve S10-Feb-04 6:27

Steve S

10-Feb-04 6:27

Phil

Most of the samples you'll find will be VB. Many of the Office related technologies are promoted (at least, their automation models are!) through use of VB, VBA or VBS. In the case of JET this isn't exactly a surprise, given the use of VB as the scripting language.

However, I assume you've already got the DAO SDK (I believe it's on the VC++ CD). You could try getting hold of the JET Programmers Guide, which is (or was) an MS Press book.

Another thing to do is get hold of the VB->VC++ automation conversion tool that was on the MSDN site some time ago. It doesn't do 100% conversion of course, but usually enough to get you started on the right road.

Steve S

Re: CDao MFC classes and a Access 97 DB

Phil.Benson10-Feb-04 20:27

Phil.Benson

10-Feb-04 20:27

Thanks Steve, I had almost given up getting a reply Frown | :(

I´ll look into what you have suggested, and thanks once again for your time.

Phil

bum... and I thought I´d got rid of all the bugs Frown | :(

Re: CDao MFC classes and a Access 97 DB

Phil.Benson11-Feb-04 1:40

Phil.Benson

11-Feb-04 1:40

Hi Steve, me again....

I´ve managed to find the book you mentioned, I´ll have to see if I can order it.

As for the automation conversion tool you mentioned, I have not been able to find it, do you still have a link to it?

Thanks in advance

Phil

bum... and I thought I´d got rid of all the bugs Frown | :(

C++ and Database

vin29-Jan-04 19:07

vin

29-Jan-04 19:07

Hi,

Is there any article available here which gives me an idea of connecting a c++ application to the database. I want to write a console database application. I don't want to use the MFC classes.

Kindly help.

regards,

vini

Re: C++ and Database

basementman30-Jan-04 10:30

basementman

30-Jan-04 10:30

Then you can use ODBC directly. Or, better yet, get or write your own wrapper classes around ODBC.

I have used my own set of ODBC wrapper classes since ODBC came out. Never have to think about it again...

onwards and upwards...

calculate datacolumn

Member 57546929-Jan-04 18:12

Member 575469

29-Jan-04 18:12

i insert one calculate datacolumn into datatable,and set the column expression, and set databinding to datagrid . when i set related column value form datagrid and update database via adosqladapter, it prompt me that i can't modify the calculate datacolumn' readonly value to be false ,however ,i never changed the column' readonly property value .why? who can help me ?

Re: calculate datacolumn

-Dr_X-30-Jan-04 17:31

-Dr_X-

30-Jan-04 17:31

I had problems as well updating a join table dataset. What I did is simple check the .HasChanges (I think) property and update each table individually in a transaction.

try
  'Begin a transaction
  '......
  If ds.Table(0).HasChanges then
    'Update table(0)
  end if
  if ds.Table(1).HasChanges then
    'Update table(0)
  end if
  'commit the transaction
  '.....
catch ex
  'Rollback the transaction
  '.....
finally
  'set any variable to nothing
  '.....
end try

Hope this is helpful.
Michael

Problem of ADO Recordset

Member 55036929-Jan-04 17:37

Member 550369

29-Jan-04 17:37

halo...everyone.l use the ado connect to the database to find out some records.And just first record display on the page but the results show in the database are over 20. and when l wrote out the record count, the answer is -1.please help me

The codes as below:
Dim objConn,objRs,strSQL

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open "DSN=myName;Driver={Microsoft Paradox Driver (*.db)}"

Set objRs = Server.CreateObject("ADODB.Recordset")

strSQL = "Select * from (mytable) WHERE condition='" & Request.Cookies("UserID") & "'"

objRs.Open strSQL,objConn,adOpenKeySet,adLockOptimistic,adCmdText

if not objRs.EOF then
Do until objRs.EOF
Response.write.......
objRs.MoveNext
Loop
end if

Thank you at advanced!

Using List Control with ADO

girl_lash29-Jan-04 16:59

girl_lash

29-Jan-04 16:59

How can i use List Control with ADO?
Please teach me...

Retrieving autonumber when inserting data in a table

Anonymous29-Jan-04 9:10

Anonymous

29-Jan-04 9:10

I have a table in an access database. This table has an ID field that is set to be Autonumber and it is the tables primary key.
When I insert new data I would like to know what number the new row got. Is there a nice clean simple way to do this without having to search that table for the data just entered?

Re: Retrieving autonumber when inserting data in a table

Guillermo Rivero29-Jan-04 9:12

Guillermo Rivero

29-Jan-04 9:12

Yes.

SELECT @@IDENTITY

Free your mind...

Re: Retrieving autonumber when inserting data in a table

Anonymous30-Jan-04 23:53

Anonymous

30-Jan-04 23:53

Just how do you use it? Is that the complete SQL command I need or do I need to place it in some larger SQL command or something?

Re: Retrieving autonumber when inserting data in a table

-Dr_X-31-Jan-04 3:59

-Dr_X-

31-Jan-04 3:59

Immediately after you insert statement call the Select @@Identity or preferably Select @ID = @@Identity where @ID is an output parameter.

create proc xyz
( @Name varchar(50),
@ID int output
)
AS

Insert into ....
....

Select @ID = @@Identity

GO

Michael

Re: Retrieving autonumber when inserting data in a table

Guillermo Rivero31-Jan-04 4:09

Guillermo Rivero

31-Jan-04 4:09

Can't do that on Access...

Free your mind...

Re: Retrieving autonumber when inserting data in a table

Guillermo Rivero31-Jan-04 4:03

Guillermo Rivero

31-Jan-04 4:03

OleDbCommand vcommand = new OleDbCommand();
vcommand.CommandText = "SELECT @@IDENTITY";
vcommand.Connection = urOleDbConnection;
int vvalue = (int)vcommand.ExecuteScalar();

VB.NET

Dim vCommand as new OleDbCommand()
vcommand.CommandText = "SELECT @@IDENTITY";
vCommand.Connection = urOleDbConnection
Dim vInt as Integer = CType(vCommand.ExecuteScalar(),Integer)

Free your mind...

Re: Retrieving autonumber when inserting data in a table

Anonymous31-Jan-04 10:18

Anonymous

31-Jan-04 10:18

Now I got it working.

Thanks all!

ADO

monrobot1329-Jan-04 8:50

monrobot13

29-Jan-04 8:50

I'm writing an MFC app and would like to use ADO for the database access. The problem is how? Is there headers I need to include? Are there MFC classes that use ADO? Help is very appreciated.

Thanks.

- monrobot13

Re: ADO

RChin30-Jan-04 4:05

RChin

30-Jan-04 4:05

Have a look at the ADO wrapper classes
here[^].

This one[^] is my current favourite class.

I Dream of Absolute Zero

Re: ADO

monrobot1330-Jan-04 9:19

monrobot13

30-Jan-04 9:19

Thanks for pointing me in the right direction.

- monrobot13

SP that support data Paging

Aryo Handono29-Jan-04 3:32

Aryo Handono

29-Jan-04 3:32

Hello all,
I want to create some store procedure that retieve data from a table, that storeprcedure should take 2 parameters PageNumber and RowInAPage

so if I use "SomeStoreProcedure 1,100", it will display data from record 1 - 100.
And If I use "SomeStoreProcedure 3,100", it will display data from record 301 - 400.

any idea ?

"Courage choose who will follow, Fate choose who will lead" - Lord Gunner, Septerra Core
"Press any key to continue, where's the ANY key ?" - Homer Simpsons
Drinking gives me amazing powers of insight. I can solve all the worlds problems when drunk, but can never remember the solutions in the morning. - Michael P Butler to Paul Watson on 12/08/03

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