|
|
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 />
|
|
|
|
|
|
Colin Angus Mackay wrote: NOTE: You should only do this if you expect the SELECT statement to retrieve only one row.
You won't get an error if it does retrieve more than one row, but if it does, you'll simply get the value from the last row returned by the query. Unless you put an ORDER BY clause in, however, which row that is will be unpredictable.
If no rows are returned, the value of the variable will be unchanged.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for your replies,
I changed my stored procedure as follows to assign the value to output variable:
ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;
Select dbo.cp_product.product_type, @genmask_code=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 this I get following error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
where should I do data assignment operation?
|
|
|
|
|
I changed the code as follows
ALTER PROCEDURE [dbo].[GetUserControlTemplateCode]
@product_id int,
@genmask_code nvarchar(max) output
AS
BEGIN
SET NOCOUNT ON;
Declare @product_type int
Select b.product_type, @genmask_code=dbo.cp_genmask.genmask_code
from dbo.cp_genmask as a inner join dbo.cp_product as b
on a.genmask_product_type=b.product_type
where b.product_id=@product_id
END
I executed this code using using
exec GetUserControlTemplateCode 1, @genmask_code output
print @genmask_code
It gives me message as command executed successfully, but I can not see any data in output window. Again when I call this procedure from code behind code it shows null value...
Please suggest where I am wrong.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
