Database

Re: Why can't use 2 DataReader with same connection

16-Jul-06 23:00

Heinz_ wrote:
How can i do multiple queries from the same connection

Just open a second identical connection to run the second command.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Heinz_17-Jul-06 9:26

Re: Why can't use 2 DataReader with same connection

17-Jul-06 9:26

Hi, i just did it but that second connection is what i have been trying to avoid. I remember that with ADODB in VB6 i was able to do multiple command and reads from the same connection.

Thanx, it works this way anyway.

Colin Angus Mackay17-Jul-06 9:32

17-Jul-06 9:32

Heinz_ wrote:
i just did it but that second connection is what i have been trying to avoid.

Why?

Heinz_ wrote:
I remember that with ADODB in VB6 i was able to do multiple command and reads from the same connection.

ADO.NET is not ADODB.

To clarify

Ennis Ray Lynch, Jr.17-Jul-06 9:37

Ennis Ray Lynch, Jr.

17-Jul-06 9:37

If you have a connection you can execute multiple commands on it. Also, keep in mind, that idenctical connection strings will use the connection pool.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: To clarify

Colin Angus Mackay17-Jul-06 11:01

Re: To clarify [modified]

17-Jul-06 11:01

Ennis Ray Lynch, Jr. wrote:
If you have a connection you can execute multiple commands on it

But not at the same time, which is what the OP wanted to do.

Ennis Ray Lynch, Jr. wrote:
Also, keep in mind, that idenctical connection strings will use the connection pool.

Absolutely!

Heinz_18-Jul-06 8:25

18-Jul-06 8:25

Understood.

I was trying to use only one connection to gain performance but now i replaced the second connection with a subquery in a command of the original connection, check this tasty and parameter rich query:

"select SUM(t1.total - COALESCE(t2sum,0)) from ventas as t1 left join (select venta, SUM(cantidad_pagada) as t2sum from pagos_ventas where fecha_pago='" + CDC.Global.SetDBdate(hoy) + "' and metodo_pago<>1 and metodo_pago<>7 group by venta) as t2 on t2.venta=t1.id where t1.nula=0 and t1.pagada=0 and t1.fecha='" + CDC.Global.SetDBdate(hoy) + "'"

Bye.

-- modified at 15:29 Tuesday 18th July, 2006

Re: To clarify

Colin Angus Mackay18-Jul-06 12:31

18-Jul-06 12:31

I don't see any parameters in your query - What I do see is lots of SQL Injection that is ripe for attack.

A parameter is something like this:

SELECT * FROM Table WHERE something = @Parameter

@Parameter is the parameter.

You might like to read about SQL Injection Attacks, what they are, what damage they can do, and most importantly, how to prevent them: http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^]

Re: To clarify

Heinz_18-Jul-06 13:59

Re: Why can't use 2 DataReader with same connection

18-Jul-06 13:59

Hi, i used the wrong word but you know what i mean, thanks for the link.

sampathkumarg19-Jul-06 8:34

sampathkumarg

19-Jul-06 8:34

Multiple Active Result Sets (MARS). Probable you need ADO.Net 2.0.

Save Changes

slSoftware16-Jul-06 21:25

slSoftware

16-Jul-06 21:25

Hi everyone!

I need to display the save changes message box in my windows form if user try to close the form without saving data.so can anyone help me to do this pls/ Smile | :)

Re: Save Changes

Colin Angus Mackay16-Jul-06 22:58

SQL Server Management Studio Express and Locks

16-Jul-06 22:58

This is off topic for this forum - please ask in the correct forum (probably for the technology you are using)

Kuira16-Jul-06 14:07

Kuira

16-Jul-06 14:07

I am not sure, but I am trying to work this out but if I do a CREATE SCRIPT SELECT on a TABLE and execute it using SQL Server Management Studio Express, will it lock access to it for transactional or for update purposes? Like a read commted? or am I going off track here as I am not completely sure about SQL stuff.... Confused | :confused:

Re: SQL Server Management Studio Express and Locks

Frank Kerrigan18-Jul-06 1:59

Frank Kerrigan

18-Jul-06 1:59

Have you installed service pack 1 for SQL Server 2005.

This seems to make the front end stable.

Look where you want to go not where you don't want to crash.

Bikers Bible

corrupted database [modified]

illusionFinder16-Jul-06 3:55

illusionFinder

16-Jul-06 3:55

hi guys ;
I am having a problem, my Access2000 Database is corrupted it won't even open it gives me a message that it cannot find the object named 'DataBases', I can't import from it or even compact it, I tried to look for a software to repaire they are all expensive and I can't afford them, can anyone help me or tell me a way to repare it

thanks in advance Confused | :confused:

bye

-- modified at 9:58 Sunday 16th July, 2006

Re: corrupted database

Paul Conrad17-Jul-06 8:08

Paul Conrad

17-Jul-06 8:08

illusionFinder wrote:
gives me a message that it cannot find the object named 'DataBases'

Sounds like you're up a creek without a paddle. If you are missing any of the internal tables/objects, the database is pretty much dust. Do you have a backup of the database somewheres?

Re: corrupted database

ra ra ra ra17-Jul-06 22:42

Return 0 when no rows [modified]

17-Jul-06 22:42

revert to most recent backup, or prepare to empty your wallet..

or try going through MS details on how to troubleshoot it.

How to troubleshoot and repair a damaged Jet 4.0 database in Access 2000:

http://support.microsoft.com/kb/209137/[^]

Heinz_15-Jul-06 13:49

Re: Return 0 when no rows

15-Jul-06 13:49

Hi, how can i return a 0 if my query returns no results?

I'm trying to do this because i have a subquery inside a query but if the subquery returns no values then the main query also returns nothing.
I'm using MySQL 5.

Thanks in advance.

-- modified at 20:24 Saturday 15th July, 2006

ra ra ra ra16-Jul-06 23:26

Re: Return 0 when no rows [modified]

16-Jul-06 23:26

sounds like you are using an inner join between your queries? try a left or right join.

or post you sql and you may get a better response

Heinz_17-Jul-06 9:30

Re: Return 0 when no rows

17-Jul-06 9:30

Hi, you're right, there's a inner join inside my query, check it out, hope you understand the query and the problem:

select SUM((t1.valor + t1.total) - t2sum) from consultas as t1 inner join (select consulta, SUM(cantidad_pagada) as t2sum from pagos_consultas where fecha_pago='2006-07-05' group by consulta) as t2 on t2.consulta=t1.id where t1.nula=0 and t1.pagada=0 and t1.fecha='2006-07-05'

I had no idea about left and right join so i went to http://www.w3schools.com/sql/sql_join.asp and took a read, the left join is the one for me! but this will return NULL in the second table when there're no matches and if you look at my query in "(t1.valor + t1.total) - t2sum" i do a " - " operation and i cant reduce a number with a null value or can i? i must replace that null for a 0.

Thanx man.

-- modified at 15:42 Monday 17th July, 2006

Ennis Ray Lynch, Jr.17-Jul-06 12:42

Ennis Ray Lynch, Jr.

17-Jul-06 12:42

isnull( field, newValue) works in SQL Server and NVL in Oracle.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Return 0 when no rows [modified]

ra ra ra ra17-Jul-06 22:22

Re: Return 0 when no rows [modified]

17-Jul-06 22:22

you can use also use the coalesce() function to remove the Null value.

eg.

replace - t2sum

with

- coalesce(t2sum,0)

is a nice little function is coalesce as you can list as many variables as you want, it works through from left to right until it finds a non null value and uses that.

sounds like its the same as the isNull() function mentioned in previous post.

its a SQL server only function though!

-- modified at 4:23 Tuesday 18th July, 2006

Heinz_18-Jul-06 8:27

can we create stored procedures in MS Access database

18-Jul-06 8:27

Thanx man. You really did it! thanx.
I was reading mysql 5 manual and there's a ISNULL(expression) function but it returns 1 if the expression is null and 0 if not null (the inverse of what i need), but, thank god there's also a IS NOT NULL wich return 1 when not null and 0 when null, so i have now many ways to acomplish my objective.

[ADDED] Reading about COALESCE() in mysql 5, and it works in a different manner than the normal ISNULL for MS SQL SERVER and others...COALESCE does not replace any null values nor it has a fixed return value, COALESCE() in mysql takes a list of values as a parameter and returns the first Not NULL value and returns NULL if any of the values in the list are not null, ie:

mysql> SELECT COALESCE(NULL,NULL,1,'hello','world',25);<br />
        -> 1<br />
mysql> SELECT COALESCE(NULL,NULL,NULL);<br />
        -> NULL

So, all i have to do in my case is: COALESCE(mycolumnvalue,0) and if the query returns mycolumnvalue null then it will return 0.

Bye,

-- modified at 14:52 Tuesday 18th July, 2006

ChennaiBabu14-Jul-06 21:15

ChennaiBabu

14-Jul-06 21:15

hi all,

greetings.I have a doubt regarding the creation and execution of stored procedures.Can any one tell me how to create a stored procedure in MS Access database and how to execute it?

Thanks in advance.

Babu

Re: can we create stored procedures in MS Access database

mysorian15-Jul-06 4:43

mysorian

15-Jul-06 4:43

You need to create an Access Project. You may refer to my article on this at:
http://www.angelfire.com/vt2/hodentek/[^]

This being said, Access 2003 Cool | :cool:

does not support Access project with SQL 2005 Server.

Re: can we create stored procedures in MS Access database

ra ra ra ra17-Jul-06 22:28