MSDE Connection String - Database Discussion Boards

John Kuhn22-Feb-04 6:05

22-Feb-04 6:05

I think I see a possible issue here.

If a member has a visit between 4/1/2003 and 6/30/2003 and the same member has a visit between 1/1/2003 and 3/31/2003, is that member included or excluded?

Also, after I wrote this last night, I realized that the same query might be feasible with an INNER JOIN...

SELECT * FROM tbl_Customer 
  INNER JOIN Qry_Child ON tblCustomer.l_Mem_id = Qry_Child.l_Mem_id 
     AND Qry_Child.l_age BETWEEN 0 AND 5
  INNER JOIN tbl_Visit ON tblCustomer.l_Mem_id = tbl_Visit.l_Mem_id 
     AND (tbl_Visit.dt_Date BETWEEN #4/1/2003# AND #6/30/2003#)
     AND NOT (tbl_Visit.dt_Date BETWEEN #1/1/2003# AND #3/31/2003#)

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.

Re: Problems building Query

xfitr222-Feb-04 6:57

xfitr2

22-Feb-04 6:57

They are excluded. They are only counted once a year. I tried to run your query and it returned no results. I tried playing with it a little and inner joining the Qry_Child and running subqueries on the tbl_Visit (IN and NOT IN) but only recieved errors.

Also, could you possibly explain the difference of when to join tables and when to use a subquery. I am still having trouble understanding the logic. Thanks.

Re: Problems building Query

John Kuhn22-Feb-04 7:54

John Kuhn

22-Feb-04 7:54

Yeah, I suppose joins are best applied when you want to include fields from table on both sides of the join statement, but they can be applied to form the basis for including or excluding rows. Since it was late last night, the idea of INNER JOINS might be flawed... I always seem to try several things until I get the one thing that works consistenly.

* thinking out loud *

SELECT table1.*, table2.* FROM table1 RIGHT JOIN table2 ON table1.key = table2.fkey WHERE ...

-- all rows from table2 and matching rows from table1

SELECT table1.*, table2.* FROM table1 LEFT JOIN table2 ON table1.key = table2.fkey WHERE ...

-- all rows from table1 and matching rows from table2

SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.key = table2.fkey WHERE ...

-- only matching rows from table1 and table2

SELECT table1.*, table2.* FROM table1 FULL JOIN table2 ON table1.key = table2.fkey WHERE ...

-- all rows from table1 and table2

Ok, so that's the difference, using IN or EXISTS simply forms the basis for inclusion or exclusion of rows from the table specified in the FROM clause, whereas using a join does as described above.

So, it seems like your example should work consistently, unless the underlying data are changing so often that the results change accordingly. If you like, you can send me some or all of the data and I can try it out.

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.

Re: Problems building Query

xfitr222-Feb-04 8:25

xfitr2

22-Feb-04 8:25

I ran the query a few more times today and it seemed to work fine without the small discrepancies. I can't send any of the data b/c some of the data falls under HIPAA. Again, thank you for all your help.

-Garrett

Triggers in windows application ^_^!!!

solostar***19-Feb-04 20:15

solostar***

19-Feb-04 20:15

hello

I have created a windows application with visual c++ 6.0 which connects to a database.
The application executes SQL queries and I want to set some triggers or the insert, update and delete queries.
However, Where should i place my triggers?

sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=s",sDriver,sFile);
TRY
{
CDatabase ComboDatabase;
// Open the database
ComboDatabase.Ope(NULL,false,false,sDsn);

// Build the SQL statement
SqlCommand = "INSERT INTO BandSetting "
"VALUES('" + str25 + "'" ",'" + str1 + "'" ",'" + str2 + "'"
",'" + str3 + "'" ",'" + str4 + "'" ",'"+ str5 + "'" ",'"+ str6 + "'" ",'" + str7 + "'" ",'" + str8 + "'" ",'"+ str9 + "'" ",'" + str10 + "'" ",'" + str11 + "'" ",'"+ str12 + "'" ",'" + str13 + "'" ",'" + str14 + "'" ",'" + str15 + "'" ",'" + str16 + "'" ",'" + str17 + "'" ",'"+ str18 + "'" ",'" + str19 + "'" ",'" + str20 + "'" ",'"+ str21 + "'" ",'" + str22 + "'" ",'" + str23 + "'" ", + str24 + "'" ")" ;

ComboDatabase.ExecuteSQL(SqlCommand);
// Close the database
ComboDatabase.Close();
}

where do i put the trigger???
hope you guys can help me out on this! thanks (^_^!!)

Re: Triggers in windows application ^_^!!!

Mazdak20-Feb-04 1:11

Mazdak

20-Feb-04 1:11

solostar*** wrote:
where do i put the trigger???

Trigger does not run from your application , you have to create it in your SQLServer(don't know if access suport triggers) , then it will call automaticlly.

Mazy

You're face to face,
With the man who sold the world - David Bowie

SQL / PHP Problem

LOSTTWARE.com19-Feb-04 18:18

LOSTTWARE.com

19-Feb-04 18:18

I don't know if I should put this under php or sql, but it is a little of both. I have secure info that I do not want published to a file on the server at any time even a few seconds. I just want to take it strait out of the data base and have the person click a link, to this php file below and when they go to it it prompts them to download. I want the sql info in the file. I think I have most of the code right, but it isn't quite working. As of now, it is downloading a blank file.

<br />
function show_file_information($filename)<br />
{<br />
@readfile($filename);<br />
}<br />
<br />
if(!class_exists(MySQL))<br />
{<br />
require '../home/MySQL.php';<br />
require '../home/conf_global.php';<br />
$mysql = new MySQL();<br />
$mysql->connect($INFO['sql_user'],$INFO['sql_pass'],$INFO['sql_database'],$INFO['sql_host']);<br />
}<br />
<br />
$news_result = $mysql->query("SELECT * FROM newsletters WHERE id='1'");<br />
$news = mysql_fetch_assoc($news_result);<br />
<br />
$filename = $news['add_id'];<br />
<br />
header("Pragma: public");<br />
header("Expires: 0");<br />
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); <br />
header("Content-Type: application/force-download");<br />
header("Content-Type: application/octet-stream");<br />
header("Content-Type: application/download");<br />
header("Content-Disposition: attachment; filename=".basename(emaillist).".txt");<br />
header("Content-Transfer-Encoding: binary");<br />
header("Content-Length: ".strlen($filename));<br />
//@readfile("$filename"); <br />
<br />
<br />
?> <br />

LOSTTWARE.com
My site allows for people to develop private programming teams, with password protected forums for each team.

MSDE Connection String

monrobot1319-Feb-04 15:40

monrobot13

19-Feb-04 15:40

I'm trying to connect to an MSDE database that is running on my local machine and I've tried the following connection strings:

Provider=[SQL Server]; Server=(local); Trusted_Connection=no; Database=users; Uid=me; Pwd=123456;

When I use that one I get an error that "Provider cannot be found. It may not be installed properly." I did install a SQL Server prodvider though the ODBC control panel, but I don't know if that was the right thing to do (this is my first time trying to program for a database). The other connection string I tried was:

Provider=MSDASQL; Server=(local); Trusted_Connection=no; Database=users; Uid=me; Pwd=123456;

The error I get on that string is "[Microsoft][ODBC Driver Manager]Data source name not found an no default driver specified." Could someone point out what I'm doing wrong here. Thanks in advance for the help. I'm using the ADO classes written by Carlos Antollini (http://www.codeproject.com/database/caaadoclass1.asp[^]) to try to connect.

- monrobot13

Re: MSDE Connection String

Mike Dimmick20-Feb-04 0:15

Mike Dimmick

20-Feb-04 0:15

Provider=SQLOLEDB.1

Problem in Joining Two Tables

Touseef Afzal19-Feb-04 10:23

Touseef Afzal

19-Feb-04 10:23

Hi Gurus:

i m new to database programing , so i m in problem even at first step ,

i have two tables (using MS Access as DBMS)

Purchase_Detail

OrderId | ProductID | QtyPurchased
1====== | == 1 ======== | == 20
1====== | == 2 ======== | == 20

And

Purchase_Return

OrderId == | == ProductID == | == QtyReturn
1 ======= | ====== 1 ====== | ==== 5

now i want to get the result from Product_Detail table as
using query

OrderId == | == ProductID == | == QtyPurchased
1 ======== | ===== 1 ======= | == 15
1 ======== | ===== 2 ======= | == 20

i m using the following query

SELECT Purchase_Detail.*
FROM Purchase_Detail LEFT JOIN Purchase_Return ON [Purchase_Detail].[pdOrderID] = [Purchase_Return].[prOrderID] AND
(Purchase_Detail.pdProductID = Purchase_Return.prProductID) AND (Purchase_Detail.pdOrderID = Purchase_Return.prOrderID)
WHERE (((Purchase_Detail.pdOrderID = '4')AND(Purchase_Return.prOrderID) Is Null));

the result of which is

OrderID == | == ProductID == | == QtyPurchased
1 ======== | ==== 2 ======== | ==== 20

missing the product ID 1 which has remaing 15 items in the stock....

can any one help me..

Thanks in advance.....

Seefou

Re: Problem in Joining Two Tables

Edbert P19-Feb-04 12:43

Edbert P

19-Feb-04 12:43

Try this:

SELECT Purchase_Detail.OrderId, Purchase_Detail.ProductId, Purchase_Detail.QtyPurchased - ISNULL(Purchase_Return.QtyReturn, 0)<br />
FROM Purchase_Detail <br />
LEFT JOIN Purchase_Return ON Purchase_Detail.OrderId = Purchase_Return.OrderId <br />
AND Purchase_Detail.ProductID = Purchase_Return.ProductID

Basically this is returning everything from Purchase_Detail as it is a left join, but the QtyPurchased has been deducted from Purchase_Return.QtyReturn (if there is any, otherwise it is deducted by 0).

I hope it works Wink | ;)

Edbert P.
Sydney, Australia.

Re: Problem in Joining Two Tables

Touseef Afzal20-Feb-04 10:31

Touseef Afzal

20-Feb-04 10:31

Hi:
The problem remains same and i m getting the message that wrong number of arguments in the Function IsNull

can any one help me ,,,.........

Thanks

"Winner's don't do different things , they do things differently "

Re: Problem in Joining Two Tables

Edbert P22-Feb-04 17:41

Edbert P

22-Feb-04 17:41

Sorry..I forgot that you're using MSAccess.
You have to change the IsNull function into IIF(IsNull(Purchase_Return.QtyReturn), 0, Purchase_Return.QtyReturn)

So from

- ISNULL(Purchase_Return.QtyReturn, 0)

to

- IIF(IsNull(Purchase_Return.QtyReturn), 0, Purchase_Return.QtyReturn)

Edbert P.
Sydney, Australia.

Timeouts in C#

19-Feb-04 5:49

I am writing a program in C# to access a SQL Server database using the ODBC ADO.NET classes. I am having a problem with it timeing out before the dataset is returned. I have tried to increase the connect timeout (60) and the command timeout (300 to as much as 48000) but I am still getting the timeouts. In fact, it seems that it is ignoring the timeout I am setting. Is there a way to disable the command timeout and just have it process until finished.

NOTE: The command consistently returns in less than 45 seconds and I am getting partial data, depending on the network traffic, in the dataset upon return from the call.

NOTE: I don't know if this helps any but I am also converting reports, originally written in MS XL to Crystal Reports. The XL version of the reports has the same timeout problem but the Crystal Reports version does not, and I think this is due to the fact that the Crystal Reports version runs considerably faster.

Additional info: yesterday, the database had just over 1 million records in it. It returned the requested number of records, but was accompanied by a timeout. today, there are over 2 million records and it times out after the same amount of time, but no records are returned, when it should have returned the same records as yesterday.

Re: Timeouts in C#

Tom Larsen19-Feb-04 10:44

Tom Larsen

19-Feb-04 10:44

I've personally seen problems like this crop up. There are actually many layers of timeout not just between the client and the DB.

The general problem is that between one of the layers a timeout is being reached. You've found the most obvious one in the client connection. But there is one from the DB too. Depending if you are using a transport like HTTP that has a timeout too. And so on and so on.

By your description it seems that your query succeeded but ran out of time trying to return data to the client. The time taken to run the query and complete dumping the data to your client is passing some timeout. A time out which you might not be able to change.

What I would do is "by hand" run the query. Plug it into Query Analyzer and see how long it takes for it to complete on the server side. If the query is too long or returns way to much data you might want to consider a different approach. Instead of batching the entire query waiting minutes for it to return the result you may want to think of a small selection set or using cursors.

ps. Are you catching the exception? What does it report?

Re: Timeouts in C#

Kwai Cheng Kane19-Feb-04 12:45

Kwai Cheng Kane

19-Feb-04 12:45

Thanks. I finally found the solution. It turns out that it was something simple, but I wouldn't have known it without digging through the endless dbConnect values in the debugger. In essence, I was doing the following:

dbCommand = new OdbcCommand(Query, dbConnect);
dbCommand.CommandTimeout = 300;
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbAdapter.Fill(dbData,"Play");

What I found, after going through the 'dbCommand' parameters in debug, is that the

'dbAdapter.SelectCommand = dbCommand'

RESETS THE TIMEOUT VALUE BACK TO THE DEFAULT!!! This is why it would ignore any timeout values I set. AAAARRRRGGGGHHHH!!!! So, you MUST set the timeout AFTER calling that line, preferrably just before the "Fill()" call. When I did this...

dbCommand = new OdbcCommand(Query, dbConnect);
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbCommand.CommandTimeout = 300;
dbAdapter.Fill(dbData,"Play");

it worked fine. No timeout whatsoever. GEEEEZZZZEEE!! It even worked great setting a timeout of 90, but 60 timed out. Microsoft should not reset values like this. Putting initial values should be left up to constructors and explicitly called initializers ONLY!!!

I have noticed other people on this board complaining of the same problem. They were probably doing the very same thing as I was.

need sql query?

murali_utr18-Feb-04 18:36

murali_utr

18-Feb-04 18:36

Hi,
table containing two fields one is code and another is name. i need to fetch repeated names only.(more than one occurance of name and code is not same). any one help to me. thanks in advance.

Have A Nice Day!
Murali.M

Re: need sql query?

stefan b18-Feb-04 18:54

stefan b

18-Feb-04 18:54

SELECT name
FROM table
GROUP BY name
HAVING COUNT(name)>1

Cheers,

_____________
stefan bornuz

Re: need sql query?

murali_utr18-Feb-04 19:01

murali_utr

18-Feb-04 19:01

hi stefan,
thanks for ur help.

Have A Nice Day!
Murali.M

Simple Dataset Question...

Verdant12318-Feb-04 16:45

Verdant123

18-Feb-04 16:45

in C# /w asp.net using SQL... (which forum, lol)

i have a dataset that i bind to a html datagrid, is there anyway i can manipulate the data in between the grab from the database, and the posting into the table?

i just want to do some simple replaces... to replace some strings with images, etc... things i can't do with simple formatting... this is NOT for posting back to the db...

Re: Simple Dataset Question...

Gerald Leslie Jones19-Feb-04 18:59

Gerald Leslie Jones

19-Feb-04 18:59

I feel your question is not clear enough to tell us your requirement. Can you explain it bit more detail?

If I understand your requirement correctly, I feel you can use template columns in which you can have the images.

Re: Simple Dataset Question...

Verdant12319-Feb-04 19:06

Verdant123

19-Feb-04 19:06

the data in the database in the columns i want to "interject" is signed integers.... i want negetive integers to have a upwards arrow (html img tag) replace the negative sign, and positive integers to have a downwards facing arrow in front of the number

Re: Simple Dataset Question...

Bill Dean20-Feb-04 5:07

Bill Dean

20-Feb-04 5:07

Hi,

Sure can! Your easiest solution is probably to add a column to your DataTable and set an Expression on that column. Here's code snippet:
<code><pre>
DataColumn DC=new DataColumn("strDisplay");
DS.Tables[0].Columns.Add (DC);
DS.Tables[0].Columns["strDisplay"].Expression = "IIF(iIndex<0,'your downarrow html' + -1*iIndex,'your up arrow html'+iIndex)";
</pre></code>

Check out the VS.NET help for DataColumn.Expression

I am assuming 1) that you only have one DataTable in your DataSet 2) that the column of intergers on which you wish to operate is named "iIndex".

Then you just display the content of the new column, "strDisplay", in your DataGrid.

Hope this helps,
Bill

Re: Simple Dataset Question...

Verdant12322-Feb-04 3:01

Verdant123

22-Feb-04 3:01

thanks!
works great!

Parameters of PutElement()

girl_lash18-Feb-04 16:26

girl_lash

18-Feb-04 16:26

how can i pass a null value to PutElement()?

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