Database Discussion Boards

Jeff Monheiser2-Mar-04 3:42

2-Mar-04 3:42

I'm writing a data acquisition program that will be acquiring data and storing it within multiple tables of a DataSet. I'd like to export this data set directly to a new excel file if possible. I'd like each table of the data set to be a new worksheet within excel. My customer doesn't know databases so they are requiring that I use excel. Any help or advice would be great.

Jeff Confused | :confused:

Re: Export a DataSet to a NEW Excel File

John Kuhn2-Mar-04 12:43

John Kuhn

2-Mar-04 12:43

How simple (or how complex) should the resulting workbook be? Also, perhaps obviously, there are a number of ways someone can transform SQL data into Excel -- using the DTS Wizard, exporting to Excel using a remote view in Access, or transforming a DataSet into Excel using the Excel object model. Which method is simpler for your users? In the case that you want to generate Excel directly from C# or VB.NET, you walk through the rows and columns in your dataset and create corresponding rows and columns in Excel. The method I often use is to store metadata about the format of the spreadsheet in other tables, then use a transformation process to go from one to the other.

What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.

DataSet object : not disposed

mxcad2-Mar-04 3:36

mxcad

2-Mar-04 3:36

Hello all!
DataSet object is never disposed! I derived my own class from DatSet, added destructors to it then, and the problem is I NEVER get destructor called!
Is it right? I never get any of created objects of that class to be collected by GC.

Is it right? Or I miss something?

Thanks in advance.

Regards,
alexx.

Re: DataSet object : not disposed

Mike Dimmick2-Mar-04 6:30

Mike Dimmick

2-Mar-04 6:30

Yes, you didn't wait for a garbage collection to happen that required your finalizer to be called.

The GC presently runs finalizers in a queue using a dedicated finalizer thread. An object is added to this queue (called the freachable queue by the CLR people and some of the documentation) when the garbage collector collects the object (the memory actually survives the garbage collection; the memory itself will only be freed on the next collection of that generation after the finalizer runs). If memory pressure isn't high enough, the GC won't reclaim the memory and won't run the finalizer.

Finalizers in .NET languages do not work in the same way as C++ destructors. If you need to dispose of resources as soon as the object goes out of scope, implement IDisposable and use a using block (in C#) or call Dispose directly.

Stability. What an interesting concept. -- Chris Maunder

DataSet object : not disposed

The Bug2-Mar-04 3:36

The Bug

2-Mar-04 3:36

those xmlbuilder generated objects..

Anonymous1-Mar-04 23:32

Anonymous

1-Mar-04 23:32

Has anyone ever had luck type-casting them from standard classes before? ie. a "UserDataTable" from DataTable?

Even with matching constraints I get errors. Shouldn't it be possible?

Re: those xmlbuilder generated objects..

Mike Dimmick3-Mar-04 2:17

Mike Dimmick

3-Mar-04 2:17

If you've got a DataTable that's been returned from some general procedure which doesn't know about your derived classes, you can't cast it to a derived class - because it isn't one. The object's actual type is carried with it.

You should be able to write the data to an XML format using DataSet.WriteXml and read it back using a typed dataset's ReadXml method.

Stability. What an interesting concept. -- Chris Maunder

use modem dialup to retrieve data from SQL 2000 Server...

DengJW1-Mar-04 17:23

DengJW

1-Mar-04 17:23

I am programming a program to use modem dialup to retrieve data from SQL 2000 Server...

What would be a good solution for the connection betweeb a client PC and the server running SQL Server 2000?

JW

DJ

Problem while accessing SQL server 2k remotely?

DengJW1-Mar-04 15:49

DengJW

1-Mar-04 15:49

I have an SQL Server 2k (personal edition) running on Windows 2000 Pro for testing.

I tried to connect from other PC running SQL server to this databse. However,
from SQL Query Analyzer on the local PC, I could not find the database once I clicked on the ... which is right to the SQL Sever: text box.

The connection between the PC and the Database machine is modem dialup. From either side, I can ping to each other without any problem.

Moreover, once I connected my PC to the intranet, I can access/login to any SQl server database which is within the intranet environment.

ANy help are greatly appreciated.

JW

DJ

Re: Problem while accessing SQL server 2k remotely?

Mike Dimmick3-Mar-04 2:31

Mike Dimmick

3-Mar-04 2:31

The box that appears when you click the '...' button is filled using broadcasts to the local subnet, on UDP port 1434, or by using NetBIOS broadcasts. It's unlikely that this will work over a dial-up connection, because routers do not forward broadcast messages.

You'll need to specify the server explicitly. This may not work using names for the same reason as above, so you may need to use an IP address.

Stability. What an interesting concept. -- Chris Maunder

How to read encrypted data from SQL Server...

OHSAH1-Mar-04 10:12

OHSAH

1-Mar-04 10:12

Hi all,

I'm encrypting some private information into a SQL but now I got
an issue where my users want to be able to perform searches on
these encrypted columns. I could limite them to exact searches but
for sure they will complain about that so I will have to provide
search where I can use the LIKE operator.

The encryption happens before I store the data into the database.

Any ideas?

Thanks in advance.

Alberto

OHSAH

Dynamic names in SQL Server

theJazzyBrain1-Mar-04 6:20

theJazzyBrain

1-Mar-04 6:20

Hello all,

I am developing an ASP application using SQL Server as my RDBMS. I need to create a new ulogin and user and assign the user to a specific database. I have done that and works ok. Now I need to create a few tables and views that only the new user can access and I go:

<br />
CREATE TABLE @myNewUser.[MY_TABLE_NAME] (...etc...etc...)<br />

Obviously this doesnt work. Can anyone tell me how to do this?

Thank you

theJazzyBrain

Excelence is not an act, but a habbit

Aristotle

Re: Dynamic names in SQL Server

ian mariano1-Mar-04 11:57

ian mariano

1-Mar-04 11:57

In a stored procedure where you pass in @newUserName and @tableName to the procedure:

<code>declare @stmt varchar(1024)
 
set @stmt = 'CREATE TABLE [' + @newUserName + '].[' + @tableName + '] ...'
 
exec @stmt

Ian Mariano - http://www.ian-space.com/

"We are all wave equations in the information matrix of the universe" - me

Re: Dynamic names in SQL Server

theJazzyBrain1-Mar-04 21:11

theJazzyBrain

1-Mar-04 21:11

thanx...

theJazzyBrain

Excelence is not an act, but a habbit

Aristotle

Can I keep a connection open while the session exists?

iluha29-Feb-04 3:42

iluha

29-Feb-04 3:42

I'm developing a shopping cart using vb6. (ActiveX dll)

For db connectivity I'm using ADO 2.7

The database (free tables) in use for now will be FOXPRO (I know not the best choice)
At some point the databases will be converted to MSSQL.

My question is:
When the cCustomer class is created, can I open the connection to the database and closed it when class is destroyed (on session end)?
Or
Should I open and close connection as needed?

It just seem better to just do it ones, but I'm not sure on how much extra load it will be on the server.
There might be up to 500 people on the server at any given time...

Please let me know what you think

Thank you
Iluha

Smile | :)

Re: Can I keep a connection open while the session exists?

Spanky329-Feb-04 7:27

Spanky3

29-Feb-04 7:27

To do this project personally.

I would use a MS access DB (crap but suited)

Create your pages and then store the products buy within an Array which begins size one and then dynamically grows as you insert more items to the basket.

To dynamically increase the size of the array while maintaing the content of the array use "Redim"

Also when the person moves page to page store the array within a session Smile | :)

Atul

Re: Can I keep a connection open while the session exists?

ian mariano29-Feb-04 21:36

ian mariano

29-Feb-04 21:36

Holding a connection open to a database as you're thinking doesn't scale well.

Use a combination of "just in time" connections, caching (ASP.NET caching is pretty nifty,) and a session shopping cart using an ArrayList of order items should do the trick.

Ian Mariano - http://www.ian-space.com/

"We are all wave equations in the information matrix of the universe" - me

Re: Can I keep a connection open while the session exists?

gpa20001-Mar-04 19:57

gpa2000

1-Mar-04 19:57

I had a DBA once who stored the UserId created by MSSQL in a table. After a while, usually after lunch, strange effects occured. People started to see other peoples data. After a while we figured out that when the connection is dormant for a while the connection drops and a new one is created when needed. MSSQL does this all by itself.
So i dont think you can get a connection all to yourself, you share it with others. Perhaps there is a way to keep the connection alive but why would you want this.

HTH, Grtz,
Guus

select top, limit 2, 3?

Anonymous28-Feb-04 20:13

Anonymous

28-Feb-04 20:13

hi,
i am using ms access.
how can i select only 2nd record in the records returned?
"select top 2 .... " can only get 1st, 2nd record
"select * limit 2,3" can only use in mysql.

any method to get the 2nd record in the records returned?

pls help!

thanks,
jim

Re: select top, limit 2, 3?

Colin Angus Mackay29-Feb-04 1:57

Colin Angus Mackay

29-Feb-04 1:57

If you use an ORDER BY in your query, you could turn your original query with the TOP 2 into a subquery and select it again but reversing the direction of the ORRDER BY and setting TOP 1 on the outer query.

EuroCPian Spring 2004 Get Together[^]
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Update Customise Object to Database

Member 435691127-Feb-04 10:26

Member 4356911

27-Feb-04 10:26

I populate my object with data from the database into arraylist. Later I perform add data using the ilist components to the object. The problem now is how the changes in the object will take effect on the database? Do I need to perform insert command to database and add into object at the same time.Please HELP!!!! Cry | :((

Re: Update Customise Object to Database

Mazdak28-Feb-04 4:55

Mazdak

28-Feb-04 4:55

What language are you using? IF you using .NET platform why don't you use DataSet instead?

Mazy

"A bank is a place that will lend you money if you can prove that you don't need it." - Bob Hope

get minor errors via OLEDB

misha_grewal27-Feb-04 2:47

misha_grewal

27-Feb-04 2:47

How do I get minor errors in OLE DB Provider. Like if I get E_FAIL, how wd I get the minor error associated with it?

Re: get minor errors via OLEDB

Prakash Nadar1-Mar-04 3:12

Prakash Nadar

1-Mar-04 3:12

you have everything in HRESULT i dont thing there is anything related to minor code.
you have the following things.
HRESULT_CODE
HRESULT_FACILITY
HRESULT_FROM_NT
HRESULT_FROM_WIN32
HRESULT_SEVERITY

pick your choice now.

P.R.A.K.A.S.H

Re: get minor errors via OLEDB

Steve S1-Mar-04 6:52

Steve S

1-Mar-04 6:52

No, you have a result in HRESULT; generally this is E_FAIL. However, mostly, you can then use IErrorInfo with the i/f that failed to get more useful information. I'm fairly sure, for instance, that in the ATLDBCLI.H shipped with VC6.0 there's a function that you can call to rip the info out and dump it to a trace window (OutputDebugString or ATLTRACE). You could check that function and see how to do it yourself.

Steve S

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