|
please help by sending the correct syntax for this procedure i tried to set an exit condition in many ways but in vain
|
|
|
|
|
Your code:
CREATE PROCEDURE SelectRecipient
AS
DECLARE @mail nvarchar(4000)
Select Mail FROM Member
select Mail from Member
exec @mail = SelectRecipient
select @mail
GO
I'm not sure what you are trying to do. It looks like you only want one value, but you are not passing in any parameters so there is nothing to filter the result set.
Suggested change:
CREATE PROCEDURE SelectRecipient
AS
SELECT Mail from Member
GO
From C#
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandTypeStoredProcedure;
cmd.CommandText = "SelectRecipient";
cmd.Connection = myConnection;
SqlReader reader = cmd.ExecuteReader()
while(reader.Read())
{
string mail = reader.GetString(0);
}
The above will retrieve all mail in the table. (Which I don't think is what you want - but it is the best I can help you with given the information you have supplied)
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
|
|
|
|
|
Dear Colin, really i appreciate ur effort with me ,this solution in fact exactly is what i want to do , my fault which i deiscovered lately is i'm trying to send multiple values with one parameter,u r code works with me very well now, with all my thanks, bless u
karim kamal ( developers are dime a dozen but good developers are weighted by GOLD)
|
|
|
|
|
This what you are trying to do
CREATE PROCEDURE SelectRecipient
@Recipient varchar(40)
AS
DECLARE @mail nvarchar(4000)
SET @mail = ( Select Mail FROM Member
Where Member = @Recipient )
select @mail
GO
run this as
exec SelectRecipient 'fkerrigan'
will produce
frank@nospam.com
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
Yes you are quite correct (as always). But I was trying to stick to the orginal code and theme.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
About 5 months ago when I was designing a database for a client, a co-worker wrote a number of CLR stored procedures that augment functionality of my database. The database was delivered to the client, and we have since found out that one of the stored procedures has a habit of throwing a DivideByZero exception in ceratin cases. I have fixed the code and redeployed it locally, but I'm having problems understanding how I get it to the client when I don't have direct deployment access to their SQL server. Do I need to copy a .dll and manually move it to the client, or is there a way I need to set things up(even if only temporarily) so that I can deploy the new CLR code to the remove server?
Thanks in advance.
-- modified at 14:14 Wednesday 5th July, 2006
Forgot to mention, I've generated scripts using these instructions: http://msdn2.microsoft.com/en-us/library/ms345099.aspx[^], but they refuse to let me drop and then add the CLR's (as I do with other items). I'm using all of the defaults in the wizard, is there something I can check to remove the constraint check briefly so I can remove something and add a new version in its place?
Alternately, can I alter the statement that is being generated?
ALTER ASSEMBLY [Assembly Name]
ADD FILE FROM (huge hex code)
AS N'filename.cs'
Should the Add be modify?
|
|
|
|
|
Figured out my issue. Since I had a number of stored procedures and functions, I needed to perform a massive drop and add (drop sp, drop fun, drop assem, add assem, add fun, add sp), but that appears to have successfully transfered them over. The msdn link above is really helpful for generating those files, but I think they probably need to include a paragraph or so about dealing with dependencies.
|
|
|
|
|
Hi,
I have a table like this:
Param Cat Payee Amt1 Amt2
AS x1 y1 23 34
AS x1 y2 43 53
BJ x1 y1 53 33
I want to select row 1 and row 3.
For just row1, I write,
“SELECT * FROM Tab1 WHERE Param=’AS’ AND Cat=’x1’ AND Payee=’y1’”
But how do write the sql if I want both the 1st row and the 3rd row. They both differ in only the Param name.
Please can you tell me.
Thanks,
Tara
Fortitudine Vincimus!
|
|
|
|
|
|
try
SELECT * from Table1 where Payee = 'y1'
or
SELECT Top 2 * from Table1 ORDER BY Amt2
if depends on what you want to filter by.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
I have to calculate the difference between the amounts in the 1st row and that in the 3rd row.
I understand what a primary key is, but my database does not require it, or so I think!!
Nevermind. I did it differently. Got the 1st row, collected the amounts in an array, then got the 2nd row and did the calculation.
If there is a better approach to doing the calculation, I would be glad to know.
Thanks,
Tara
Fortitudine Vincimus!
|
|
|
|
|
TheinstruTara wrote: I have to calculate the difference between the amounts in the 1st row and that in the 3rd row.
First a wee bit about databases. They deal with sets of information, using set based queries. In a set there is no concept of order, although many database systems can impose a concept of order by using the ORDER BY clause in SQL. So, as far as the datbase system is concerned there is no 1st and 3rd row.
TheinstruTara wrote: I understand what a primary key is, but my database does not require it, or so I think!!
If you want to refer to a specific row you need a primary key. If you understand what a primary key is you should know that is will always uniquely refer to a row.
You mention a calculation, but you never mentioned this before. If you are looking for a better way to do a calculation then you need to let us know what the calculation is in order for us to help you with that.
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
-- modified at 15:30 Wednesday 5th July, 2006
|
|
|
|
|
Thankyou so much for your help.
I am coding a program in vc++ and I use MS Access as the database. We need to know a little bit to sql to interact with the database. I just mentioned 1st row and 3 row as examples.
In my program I do not know which row it actually is. Therefore, even if there is a primary key assosiated with a row, its of now use to my program.
I was trying something like this:
SELECT * FROM Tab1 WHERE param='AS' AND param='BJ' AND cat='x1' AND payee='y1'
But the sql was not accepted.
Fortitudine Vincimus!
|
|
|
|
|
TheinstruTara wrote: WHERE param='AS' AND param='BJ'
That says that the column called param must be equal to 'AS' and 'BJ' at the same time. This is, naturally, impossible.
I'm guessing what you actually wanted to do was:
SELECT * FROM Tab1 WHERE param IN('AS', 'BJ') AND cat='x1' AND payee='y1'
If MS access cannot handle the IN() operator then it is logically equivalent to
(param = 'AS' OR param = 'BJ') (note the brackets are also important)
TheinstruTara wrote: In my program I do not know which row it actually is
You must know something about the rows you want, you are putting stuff in the WHERE clause - Unfortunately it means nothing to me, so it is difficult for me to make meaningful suggestions.
And, you still haven't mentioned anything about the calculation you are doing.
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
|
|
|
|
|
Dose'nt matter. It's OK. Thank you.
Fortitudine Vincimus!
|
|
|
|
|
I am Having DropDownList,GridView1 and GridView2 in my page.
If i select one value in the DropDownList that related data has to display in the GridView.
Please help me out. Very Urgent.
Thanks
Kalpana
|
|
|
|
|
This should not be posted in the SQL forum !
basically you need a SelectedItemChange event on your DropDownList that runs a SQL query and then binds the data to to your gridview.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Frank Kerrigan wrote: This should not be posted in the SQL forum !
This could arguably be deemed an ADO.NET question as well as a ASP.NET question. Either forum is correct, imo.
Jerry
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
|
I'm being kind and looking at it as implied.
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
1. What does this have to do with SQL (the subject of this forum)
2. The people here volunteer their abilities in their spare time, for free, for the benefit of others. Saying your problem is "Very Urgent" is not going to get you a faster response. I would suggest that it does, in fact, turn off some people because they would rather help someone who appears to appreciate the scenario in which the questions are being asked. i.e. a community forum.
If you want urgent help with your problem then I would suggest you call Microsoft Support who will help for a small fee.
|
|
|
|
|
This could arguably be deemed an ADO.NET question as well as an ASP.NET question. Either forum is correct, imo.
Jerry
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
When I update data,I hope the datetime change together.so,I use the Now() function,but throw the Excetption.look at following:
@ID
@name,
@sex,
@age,
"Update mytable set name=@name,sex=@sex,age=@age,DateTime=now() where ID=@ID"
so,how to modify?who can help me?
thank you!
KSCsoft
|
|
|
|
|
you can always can have the value of "date" field filled with the current date/time by setting the field's Default value to =Now() in Access table design mode.
What is the exception you get?
|
|
|
|