Database

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!

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_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

stored procedure Output parameter value is Null

17-Jul-06 22:28

Simple answer - NO

unless as mentioned by someone else you are creating an MS access project to hook up to a sql server, if so its just a SQL server stored proc

But if its a normal access database you should be able to write your own scalar functions (single return value), but you can not write functions that will return a recordset. unless the consumer of the recordset is another vba function...

hope that makes sense, I've not had my coffee yet!

Mr ra ra

For_IT14-Jul-06 12:26

For_IT

14-Jul-06 12:26

Hi
I am using following stored procedure
-----------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;

Select dbo.cp_product.product_type, dbo.cp_genmask.genmask_code
from dbo.cp_genmask inner join dbo.cp_product
on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type
where dbo.cp_product.product_id=@product_id

END
--------------------------------
when I execute the above code using

exec GetUserControlTemplateCode 1,'x'

I get correct values for x i.e. output parameter.

My code behind to call stored procedure is as follows

---------------------------------
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sConnectionString2"].ConnectionString);
SqlCommand Com = con.CreateCommand();
Com.CommandType = CommandType.StoredProcedure;
Com.CommandText = "GetUserControlTemplateCode";

//Create parameter object to provide input
SqlParameter parInput = Com.Parameters.Add("@product_id", SqlDbType.Int);
parInput.Direction = ParameterDirection.Input;
parInput.Value = nProductID;

// Create parameter to hold output
SqlParameter parOutput = Com.Parameters.Add("@genmask_code", SqlDbType.NVarChar,300);
parOutput.Direction = ParameterDirection.Output;

//Open the connection
con.Open();
//Execute command
try
{
Com.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}

string sTemplateCode = Convert.ToString(parOutput.Value);
sTemplateCode = sTemplateCode.Replace("ProductID", nProductID.ToString());
Control myControl = ParseControl(sTemplateCode);
PlaceHolderForProducts.Controls.Add(myControl);
con.Close();
}
-----------------------------------

when I execute the code, i do not get any exception, but the value of output parameter is null.

Can someone please help me to understand whats missing here?

Thanks.

Re: stored procedure Output parameter value is Null

nguyenvhn14-Jul-06 16:24

nguyenvhn

14-Jul-06 16:24

I did not see the statement setting value for @genmask_code so infact, it have no return value. Also I think exec GetUserControlTemplateCode 1,'x' will not work because of lacking OUTPUT modifier. It should be:

<br />
exec GetUserControlTemplateCode 1, @x OUTPUT<br />
print @x<br />

Re: stored procedure Output parameter value is Null

Colin Angus Mackay14-Jul-06 21:53