ADO.NET connection problem - Database Discussion Boards

Mike Dimmick29-Jul-03 2:44

29-Jul-03 2:44

Perhaps DAO isn't passing the query into SQL Server as-is?

I suggest using ODBC to connect to the database, i.e. use the CRecordset and CDatabase classes rather than CDaoRecordset.

Re: Problems with LIKE in MFC(vs6)

jag_typ2-Aug-03 0:18

jag_typ

2-Aug-03 0:18

I think it's much easier working with CDao classes...

And the way the program is ment to be used, i can't create System-DSN's on all computers (to be able to use ODBC) I would like to connect directly to the SQL server...

One strange thing I've noticed is that if I use very simple SQL queries such as SELECT * FROM scala_A_SL010100 or SELECT * FROM scala_A_SL010100 WHERE FNCustomerName LIKE 'Besam AB'.. Or something else without the % it works fine..

Iv'e also noticed that it doesn't work then i use joins.. but as I

SELECT DISTINCT scala_A_OR200100.FNSalesmanNo AS 'Salesman', scala_A_SYCD0100.FNCurrencyShortName AS 'Currency', scala_A_OR120100.FNOrderNumber AS 'Ordernumber', scala_A_OR120100.FNLineNumber AS 'LineNumber', scala_A_OR120100.FNStockCode AS 'StockCode', scala_A_OR120100.FNDescripLine1 AS 'DescLine1', scala_A_OR120100.FNDescripLine2 AS 'DescLine2', scala_A_OR120100.FNUnitPrice AS 'UnitPrice', scala_A_OR120100.FNQty AS 'Qty', scala_A_OR120100.FNDelDate AS 'DelDate', scala_A_OR120100.FNOrderDate AS 'OrderDate' <br />
FROM scala_A_OR200100 right outer join scala_A_OR120100 <br />
ON scala_A_OR120100.FNOrderNumber = scala_A_OR200100.FNOrderNo <br />
right outer join scala_A_SYCD0100 <br />
ON scala_A_OR120100.FNUnitCode = scala_A_SYCD0100.FNCurrencyCode <br />
WHERE scala_A_OR120100.FNCustCodeDel = '400002' <br />
ORDER BY scala_A_OR120100.FNOrderNumber Desc, <br />
scala_A_OR120100.FNLineNumber asc"

But it works perfectley in the Query Analyser...

If I take away the last join it seems to work without any errors, but I naturally don't recieve all data I need..

I wonder if he DaoClass can't cope with a little more advanced SQL queries.. Or if it may be something i've done wrong.. And if there may be some way to fix my problem??

(ps. been on a vaccation a few days, therefore I havn't replied until now ds.)

Getting Child Items;

Bo Hunter27-Jul-03 20:32

Bo Hunter

27-Jul-03 20:32

Well I have come to a stop on this one;
I have a Nodes table with 4 columns ( ID, ParentID, Name, IsRoot )
I have sample data entered in
ID=====ParentID=====Name=======IsRoot
1 =====0 ==========Title1======True
2 =====0 ==========Title2======True
3 =====0 ==========Title3======True
4 =====1 ==========Child1======False
5 =====2 ==========Child2======False
6 =====5 ==========Child3======False

Now when I do a search and include sub categories
I need all the sub categories IDs. Now if I pass
ID 2 for a search then I need to get 5 and 6; I have 2 already
as it was passed in to the function; So it will look
something like Select Whatever From Nodes Where
( ParentID = 2 OR ParentID = 5 OR ParentID = 6 ).
The reason this is so hard for me is I am not filling
a dataset with all the data because it will get rather large
and take a long time to load all the data. So I am
populating the nodes as the user selects them. This
is the only method I like.

Thank You
Bo Hunter

Re: Getting Child Items;

Not Active28-Jul-03 2:34

Not Active

28-Jul-03 2:34

Personally I would use two tables

Parent Table (id, name)
Children Table( id, parent_id, name)

Re: Getting Child Items;

ZoogieZork28-Jul-03 8:33

ZoogieZork

28-Jul-03 8:33

I'm not sure I understand your question...

Are you trying to build a query in which given a node ID, you get the node's children, the children's children, etc.?

- Mike

Re: Getting Child Items;

Bo Hunter28-Jul-03 10:35

Bo Hunter

28-Jul-03 10:35

I am not tring to build the query but the methods to do what you
are saying. So if I pass 2 to the function then I need to be able
to get the children of that node and then get the children for these
nodes and keep doing it until there is no more results. I had this
done using a recursive function on the TreeNodes in the treeview.
I added these ID and ParentID properties to the treenode derived class
but forgot that if the node has not been loaded then it does not return
all the chilren. Calling a function recursively using a DataReader does
not work to well Smile | :)

Thank You
Bo Hunter

Re: Getting Child Items;

Alexander Kojevnikov28-Jul-03 23:37

Alexander Kojevnikov

28-Jul-03 23:37

I would maintain another table with all sub-children for each node. This table could be updated automatically in your primary table triggers. If you have many requests for all sub-children, and not so many updates, this technique makes sense.

You can also have a look at this[^] article and its comments.

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

ADO.NET connection problem

sacoskun27-Jul-03 1:11

sacoskun

27-Jul-03 1:11

Hi all,
I tried to connect a database(SQL) by both VS 2003 interface tools and normal typing.

When I make the connection in local Web project it doesn't work and gives an error like this;

Server Error in '/Logon' Application.
----------------------------------------------------
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Nevertheless, when I use the same .aspx page in normal web site (not default site) it works without any error.

I have been using Windows 2000 and it was both working but when I installed Windows 2003 Enterprise Edition, it started to give that error(below). So, most probably this error acquires because of Windows 2003 settings is there anyone who knows how to solve this problem?

Kind Regards,
Arda Coskun

-
When in doubt, push a pawn!
-

Re: ADO.NET connection problem

Mike Dimmick29-Jul-03 2:54

Mike Dimmick

29-Jul-03 2:54

Under Windows 2000, your ASP.NET worker process runs as the ASPNET user. You probably added the ASPNET user to your database to make this work.

On Windows Server 2003, ASP.NET applications now use the standard worker process w3wp.exe, which runs using the NETWORK SERVICE credentials.

You need to change your worker process credentials for this application in IIS configuration so that it uses a local or domain account as appropriate. Ensure that this account can log in to SQL Server (use the Logins feature under Security in Enterprise Manager).

Alternatively, you could use SQL Server Mixed Mode and not use a trusted connection, but I wouldn't advise this.

Re: ADO.NET connection problem

sacoskun29-Jul-03 5:23

sacoskun

29-Jul-03 5:23

Thank you Mike.

-
When in doubt, push a pawn!
-

Bulk Insert - DataTable to DataTable

sburton@amersham25-Jul-03 12:41

sburton@amersham

25-Jul-03 12:41

I am currently researching an approach to bulk copy datarows from one datatable to another without iterating through each row adding them one at a time such as:

// The _dtGrid1.Rows is very large ( 20,000 rows per TIFF image loaded ).

foreach( DataRow row in _dtGrid1.Rows ) {

GenDataSet.grid2Row newRow = _dtGrid2.Newgrid2Row();

newRow.DISPENSE_FILE_ID = row[_dtGrid1.DISPENSE_FILE_IDColumn];
newRow.CUSTOMER_PROBE_NAME = row[_dtGrid1.CUSTOMER_PROBE_NAMEColumn]

_dtGrid2.Addgrid2Row( newRow );

}

This copy need to be able to execute extremely fast. I am going to look into creating my own provider but... Maybe memories move of some sort... Can somebody point me in the right direction or shed a little light on the subject.

Thanks

Stephen Burton

Re: Bulk Insert - DataTable to DataTable

Rein Hillmann26-Jul-03 20:56

Rein Hillmann

26-Jul-03 20:56

Why not just use BCP.exe to do this?

Re: Bulk Insert - DataTable to DataTable

sburton@amersham28-Jul-03 6:13

sburton@amersham

28-Jul-03 6:13

I’m not persisting data otherwise this would be an option. I calculate data from TIFF images and need to append the results to an ADO.NET typed datasets. There can be any given number of TIFF images so I need a way to calculate on them indivisially (within there on dataset) then merge selected data into one dataset.

I am not using a database. I read data only from the TIFF image, but it is important that the data is not persisted to disk.

Re: Bulk Insert - DataTable to DataTable

sburton30-Jul-03 8:35

sburton

30-Jul-03 8:35

I was able to achive the functionality I need by using the Merge method on the dataset.

GenDataSet _dsGeneratedData = null;
GenDataSet.MyTableDataTable _dtGenTable = null;

_dsGeneratedData = new GenDataSet();
_dsGeneratedData.ReadXml( @"..\..\performanceData.xml" ); // read test data.
_dtGenTable = (GenDataSet.MyTableDataTable)_dsGeneratedData.MyTable;

GenDataSet _dsMergedData = new GenDataSet();

_dsMergedData.Merge( _dtGenTable, true, MissingSchemaAction.Ignore );
_dsMergedData.Merge( _dtGenTable, true, MissingSchemaAction.Ignore );
_dsMergedData.Merge( _dtGenTable, true, MissingSchemaAction.Ignore );
_dsMergedData.Merge( _dtGenTable, true, MissingSchemaAction.Ignore );

thanks.

Mail

sardinka25-Jul-03 5:49

sardinka

25-Jul-03 5:49

I have following sp: How do I need to modify this sp in order to handle attachements?
CREATE PROCEDURE CDO_SendMail(
@From varchar(255) = 's@yahoo.com',
@To varchar(255),
@Cc varchar(255) = '',
@Bcc varchar(255) = '',
@Subject varchar(255),
@MessageFormat int = 0, -- default to HTML, 1 = text, 0 = html
@Message varchar(8000),
@Priority int = 2 -- default to high, 1 = normal, 0 = low

)
AS

DECLARE @CDO int, @OLEResult int, @Out int

--Create CDONTS.NewMail object
EXECUTE @OLEResult = sp_OACreate 'CDONTS.NewMail', @CDO OUT
IF @OLEResult <> 0 PRINT 'CDONTS.NewMail'

--Set CDONTS.NewMail properties
execute @OLEResult = sp_OASetProperty @CDO, 'From', @From
execute @OLEResult = sp_OASetProperty @CDO, 'To', @To
execute @OLEResult = sp_OASetProperty @CDO, 'Cc', @Cc
execute @OLEResult = sp_OASetProperty @CDO, 'Bcc', @Bcc
execute @OLEResult = sp_OASetProperty @CDO, 'Subject', @Subject
execute @OLEResult = sp_OASetProperty @CDO, 'BodyFormat', @MessageFormat
execute @OLEResult = sp_OASetProperty @CDO, 'Body', @Message
execute @OLEResult = sp_OASetProperty @CDO, 'Importance', @Priority

--Call Send method of the object
execute @OLEResult = sp_OAMethod @CDO, 'Send', Null
IF @OLEResult <> 0 PRINT 'Send'

--Destroy CDO
EXECUTE @OLEResult = sp_OADestroy @CDO

return @OLEResult
GO

Re: Mail

William O'Malley25-Jul-03 8:53

William O'Malley

25-Jul-03 8:53

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_newmail_object_cdonts_library_.asp?frame=true

that should help

Will

Re: Mail

sardinka30-Jul-03 3:26

sardinka

30-Jul-03 3:26

here is my sp. For some reason it won't attache file. Any idea why?
CREATE PROCEDURE CDO_SendMail_test(
@From varchar(255) = 's@yahoo.com',
@To varchar(255),
@Cc varchar(255) = '',
@Bcc varchar(255) = '',
@Subject varchar(255),
@MessageFormat int = 0, -- default to HTML, 1 = text, 0 = html
@Message varchar(8000),
@Priority int = 2,
@AttachFile varchar(8000) -- default to high, 1 = normal, 0 = low

)
AS

DECLARE @CDO int, @OLEResult int, @Out int

--Create CDONTS.NewMail object
EXECUTE @OLEResult = sp_OACreate 'CDONTS.NewMail', @CDO OUT
IF @OLEResult <> 0 PRINT 'CDONTS.NewMail'

--Set CDONTS.NewMail properties
execute @OLEResult = sp_OASetProperty @CDO, 'From', @From
execute @OLEResult = sp_OASetProperty @CDO, 'To', @To
execute @OLEResult = sp_OASetProperty @CDO, 'Cc', @Cc
execute @OLEResult = sp_OASetProperty @CDO, 'Bcc', @Bcc
execute @OLEResult = sp_OASetProperty @CDO, 'Subject', @Subject
execute @OLEResult = sp_OASetProperty @CDO, 'BodyFormat', @MessageFormat
execute @OLEResult = sp_OASetProperty @CDO, 'Body', @Message
execute @OLEResult = sp_OASetProperty @CDO, 'Importance', @Priority
execute @OLEResult = sp_OAMethod @CDO, 'AttachFile', @AttachFile--'' [, ''] [, ]

--Call Send method of the object
execute @OLEResult = sp_OAMethod @CDO, 'Send', Null
IF @OLEResult <> 0 PRINT 'Send'

--Destroy CDO
EXECUTE @OLEResult = sp_OADestroy @CDO

return @OLEResult

GO

exec CDO_SendMail_test
@From = 'yyr@yahoo.com',
@To = 'tt@chickering.com',
@Cc = '',
@Bcc = '',
@Subject = 'test',
@MessageFormat = 0,
@Message = '',
@AttachFile ='\\bsar\test.txt'

Wildcard SELECT in MySQL?

MKlucher25-Jul-03 5:11

MKlucher

25-Jul-03 5:11

I have a search page in ASP that has three fields users can search under but can also leave blank if they want to. My problem is I want to have a SELECT statment that can do wildcards but I am not sure if thats possible or if I just cant get it to work. For instance here is an example of what I would want.

SELECT * FROM rbcheckout WHERE TitleName='*' AND RSNumber ='*' AND Platform ='Playstation 2';

While a valid query it doesn't work cause it should bring up every record that has Platform as Playstation 2. Doning some searching on the net I saw that % was also a wildcard so it would be like

SELECT * FROM rbcheckout WHERE TitleName='%' AND RSNumber ='%' AND Platform ='Playstation 2';

But that doesnt work either. Anyone know if I am going about this wrong or what?

Re: Wildcard SELECT in MySQL?

ZoogieZork25-Jul-03 6:54

ZoogieZork

25-Jul-03 6:54

You need to use the LIKE operator when doing wildcard expressions:

SELECT *
FROM rbcheckout
WHERE TitleName LIKE '%' AND
    RSNumber LIKE '%' AND
    Platform = 'Playstation 2';

- Mike

Re: Wildcard SELECT in MySQL?

MKlucher25-Jul-03 7:03

MKlucher

25-Jul-03 7:03

Thanks!

Re: Wildcard SELECT in MySQL?

dabuskol3-Aug-03 19:44

dabuskol

3-Aug-03 19:44

I might suggest to remove the columns in your whre with the wildcard "%". instead, only the where clause with criteria as "Station 2" will be there. Try it and it will give you the same result.

/vb;PUser

Yukon !

Nino_124-Jul-03 20:01

Nino_1

24-Jul-03 20:01

http://www.fawcette.com/dotnetmag/2003_06/magazine/columns/sqlconnection/[^]

Tony

SQL server timeout expired

Imtiaz Murtaza24-Jul-03 18:59

Imtiaz Murtaza

24-Jul-03 18:59

Timeout expired

This is an error my application is getting at any sporadic time. My application is written in VC++ and uses ADO to make a permanent connection to SQL server. The application usually gets this error after two or three days but sometimes it may get this error just after two or three hours. Our tables are very simple. We use stored procedures and each stored procedure hardly takes 20 milliseconds to execute. But after some sporadic time when application tries to execute a stored procedure, it gets "timeout expired" error.

Any suggestion ??

Imtiaz

Re: SQL server timeout expired

Mike Dimmick29-Jul-03 3:05

Mike Dimmick

29-Jul-03 3:05

This could be a lock timeout. Lock timeouts might occur if you begin an explicit transaction but never commit it or roll it back. The default transaction isolation level is READ COMMITTED, where different sessions can only see data committed by other sessions, not data that hasn't yet been committed.

If you have a situation where connection A has updated a row but not committed the change, and connection B tries to read it, B will block waiting for A to commit before it can read. If you have a situation where B has a recordset open and hasn't read all of it, and A's update would affect that recordset, A will block until B closes its recordset. This assumes server-side cursors - with client-side cursors and forward-only, read-only recordsets, SQL Server just throws the whole resultset at B.

ADO normally uses connection pooling, so there's rarely much cost to closing and reopening a connection if you've used it recently. If it isn't used, ADO will close the connection fully, saving server resources.

Check your transactions and your connection logic.

procedure

aquaman22-Jul-03 16:46

aquaman

22-Jul-03 16:46

Hello,
I am trying to write a procedure that does the following. I currently have the user enter data in a textbox that populates a cbo. When they select data in the cbo it populates a listbox. I want the user to be able to take the items checked in a listbox and rename them. The data originated from what the user put in(batchno). So I want to be able to take the selected items in the textbox and rename a new "batchno" but leave the ones that were not selected. Thank you for your help.
Michael

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