DataSource Controls :: Reterive Data From Two Tables By Using This Stored Procedure?
Jan 22, 2010
I'm using below stored procedure to reterive data and i'm displaying it in datalist... Now i want to reterive data from two tables by using this stored procedure...collegedetails is my table name... I want data from coursedetails also.
CREATE PROCEDURE [dbo].[SearchInstitute]
@startRowIndex int,
@Category varchar(500),
@inst_name varchar(500),
@course_name varchar(500),
@city varchar(500),
@pageSize int,
@totalCount int output
AS
BEGIN
SET NOCOUNT ON;
SET @totalCount = 0
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM
( SELECT distinct *, ROW_NUMBER() OVER (ORDER BY ArticleList.clgid DESC) as RowNum
FROM (
Select distinct collegedetails.*
FROM collegedetails
WHERE category like '%' +@Category+ '%' OR inst_name like '%' +@inst_name+ '%' Or city like '%' +@city+ '%'
) as ArticleList
) as ArticleList1
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY clgId DESC
SELECT distinct @totalCount = Count(clgId) FROM collegedetails WHERE category like '%' +@Category+ '%' OR inst_name like '%' +@inst_name+ '%' Or city like '%' +@city+ '%'
END
END
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
i have a details table which include detailid, custname, address, classification , pincode, telephone, remarks and classification table contain classification id & classification.i want to insert detailid & corresponding classificationid to a new table detail_classification. How to write stored procedure for that?
I have a DAL for accessing data from sql server in my asp.net 3.5 application.
[code]....
Here I am passing parameter and connection string. I want to create a common method which i can use for anywhere. I don't want to specify parameter datatype. sometime i have 3 or 4 parameter.
The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.
On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.
I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.
Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?
I am bulding a dvd catalog and have a table called DVD which holds all my details about each dvd except Genre, Director and Actor as I want more than 1 Genre, Director, Actor per DVD so put these in seperate tables to avoid repeating the data and used a joining tabe to join the tables so the tables I have are
DVD, Genre, Director, Actor, DVDGenre, DVDDirector and DVDActor
I have now written a stored procedure to pull the info about 1 DVD out of all the tables to display on the webpage I have now got the info I want out of the tables but as the data is now in 1 table I have repeating data is there a way to stop the data repeating the stored procedure i have written is
I'm looking for a steer on how to go about running stored procs efficiently.
I have a current requirement to populate a control, so I have all the specific data i.e the parameter value I need to pass and the control I want to populate, plus generic data such as SqlConnection etc.
I'd like to be able to call, say, "MyStoredProc". MyStoredProc would contain all the necessary information to run the proc, but then pass onto a method, say, GetSpData, which contained all the generic stuff about running stored procs.
Then when setting up MyStoredProc2 I could do without repeating a lot of the original function
Im I barking up the wrong tree or is there an efficient way of managing this kind of situation.
i have a grid bound to sqldatasource control which gets its data from stored procedure. in page load event i code a dropdownlist where i take the selectedvalue parameter for the parameter query. only when a second dependant dropdownlist is selected (coded in dropdownlist1 selected event)is data returned ?
More specifically, i'm trying to create a stored procedure that displays data from a table if the date column in the table matches a specific day of the week. (IE. Monday, Tuesday, ect...)
I am building a WCF Web Service that has a Business Logic layer (that implements all validation logic and other business rules) and a Data Access Layer that makes calls to Stored Procedures in my database.
I would like to be able to validate the length of data passed against that of the length of the parameters in the Stored Procedures without having to explicitly define the parameter lengths in my WCF Web Service.
Eg:
Person Object Person.Name = "John Dhoe" Person.Age = 37 SQL Stored Procedure SavePerson FullName Varchar(15) Age Int
In my BLL or DAL, I would like to be able to do something like :
I am trying to write a function that can be called to run a stored procedure. I pass the stored procedure name, followed by as many parameters as I need to run the procedure. I am able to do this by using the params keyword, so my function looks something like this;
[Code]....
How can I determine what the data type of the parameter is? Maybe I need to alter the string[] part, above?
I want to update two tables using stored procedures but my data not updating to data base
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace INV_DN { public partial class Form1 : Form { SqlConnection con; SqlDataAdapter da1; SqlCommand cmd; // SqlCommandBuilder cb; DataSet ds; public Form1()
I'm a novice in .NET programming. I was trying to figure a way to make a MULTI TABLE search via a stored procedure for my website. I've written a SP to select records from one table, which isn't working for some unknown reasons:
[Code]....
Whatever I search for, the datagrid is displaying all records from my table.
i have a stored procedure with this values and i need just to make a button that will send something to "Equip" column,how to do it? write the values on my webpage and make a button to exec the procedu
I created a database on SQL Server 2005 developer edition with MS SQL Server Mangement Studio Express. Now I created code that accessess a stored procedure named 'pS_TableData'. However I have one problem the code raises an exception "Could not find stored procedure 'pS_TableData 'Letters''."the code looks like this:
[Code]....
Note I am using (for now) the same connection that I used to create my database so in essence my web site is the administrator. I tried a simple SELECT from a table with the same connection and it raised no exceptions!When I run EXECUTE ps_TableData 'Letters' in SSMS it has no . I also tried EXECUTE ps_TableData 'Letters' as the command paramated for SqlCommand but it had the same effect