|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
|
Hi, i used the wrong word but you know what i mean, thanks for the link.
|
|
|
|
|
Multiple Active Result Sets (MARS). Probable you need ADO.Net 2.0.
|
|
|
|
|
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/
|
|
|
|
|
|
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....
|
|
|
|
|
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
|
|
|
|
|
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
bye
-- modified at 9:58 Sunday 16th July, 2006
|
|
|
|
|
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?
|
|
|
|
|
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/[^]
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 does not support Access project with SQL 2005 Server.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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 />
|
|
|
|