|
Hi,
I have 2 tables tbl_Member and tbl_Community, they are related by primary/foreign keys.
I access them using SPs. So I created TableAdapters for SPs which return rows from these tables (only). It generated typed MemberRow and ComunityRow for me.
Now I have created SP which joins these 2 tables and returns me all columns from tbl_Member and one from tbl_Community.
I cannot add this SP as a query to MemberTableAdapter which I use to get records from tbl_Membe because this new field (from tbl_Community) does not fit into the existing XML schema.
How can I use this SP ? I would probably need more queries which return columns from joined tables.
How do you "typify" such queries ? It seems like the same problem would have arised if I created custom business objects and not used typed DataSets, I could have Member and Community objects but not something in between.
Thanks a lot,
Slava
|
|
|
|
|
Hi,
How to find the number of databases in Oracle database?
Regards,
Uma
|
|
|
|
|
What are you trying to do?
|
|
|
|
|
simply i created the following stored procedure and it gives me the following error:
Server: Msg 217, Level 16, State 1, Procedure SelectRecipient, Line 6
Maximum stored procedure, function, trigger, or view nesting level exceeded
the code is te following:
CREATE PROCEDURE SelectRecipient
AS
DECLARE @mail nvarchar(4000)
Select Mail FROM Member
select Mail from Member
exec @mail = SelectRecipient
select @mail
GO
please i need to know how to resolve the problem and how to retrieve the @mail value in my C# application
karim kamal
|
|
|
|
|
|
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”
|
|
|
|
|