Fields Not Displayed Binding Dataset To Stored Procedure
May 7, 2008
Hi everyone
I am using Stored procedure :
ALTER PROCEDURE [dbo].[ReportChart]@Num int,@patID char(16) ASbegin if @Num=2 Begin select * from table1 where patientid=@patID End
else If @Num=1 Begin select * from table2 where patientid=@patID End end
While using the above stored procedure, when i bind it with Dataset. The fields corresponding to table1 are displayed in the Fields Tab of the Dataset whereas the Fields corresponding to table2 are not displayed.
Please help me out .
Thanks In Advance
Regards
Navdeep
View 20 Replies
ADVERTISEMENT
Jul 23, 2005
I have a strange problem with an OleDB call to a stored procedure thatreturns a rowset.Only the first time I execute the query, after I restart SqlServer, myprogram crashes becausethe rowset is empty. All next calls (after restarting my program, ofcourse) run successfully.The context is:1) if I do not bind the output rowset, my program doesn't crash2) If I run the call without the binding and then I run the call withthe binding, it doesn't crash3) It is not the first query I do in my session4) the call crashes only if in the SP there are some inserts: it is awell known problem with OleDB, but in manyother cases I have fixed it setting NOCOUNT to ON
View 2 Replies
View Related
Mar 12, 2008
Hi all,
I have some problem about bind field from stored procedure to report.
Now I have dataset that come form stored procedure. In Dataset Dialog, "Fields" tab, I add Fieldname like fieldname in stored procedure. And "Parameters" tab, I add parameters those are neccessary for my stored procedure.
I test on data tab view. When I click "!" to run, then there is input dialog parameter.
I filled it, then the result is ok.
But when I build project, I noticed warning message....
[rsMissingFieldInDataSet] The data set €˜MyDataSet€™ contains a definition for the Field €˜MyField€™. This field is missing from the returned result set from the data source. c:.....visual studio 2005projects
eport project1
eport project1Report7.rdl 0 0
[rsErrorReadingDataSetField] The data set €˜MyDataSet€™ contains a definition for the Field €˜MyField€™. The data extension returned an error during reading the field. There is no data for the field at position 6. c:.....visual studio 2005projects
eport project1
eport project1Report7.rdl 0 0
After I build my project, I run the report, each textbox that binded field, display empty value.
What happen with my report? And how can I solve this problem?
Thank you very much.
View 1 Replies
View Related
Nov 22, 2007
Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string. Any ideas? ============================== CREATE PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List ( @websiteID AS int = 0, @SelectedDateFrom AS dateTime, @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO============================== <Columns> <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn> <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn> <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn> <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn> <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn> <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn> </Columns> ============================
View 1 Replies
View Related
Oct 11, 2004
Hi all;
How can I show the Name & Age of the selected student's ID in the appropriate TextBox(s) (txtName & txtAge)?
BTW:
My "ShowStudent" stored procedure:
ALTER PROCEDURE ShowStudent
@ID int
AS
SELECT ID, Name, Age FROM Student WHERE ID=@ID
RETURN
And this is the code for "Show" button:
' Which SP? Connection?
Dim cmdSelect As New SqlCommand("ShowStudent", con)
cmdSelect.CommandType = CommandType.StoredProcedure
'-----[ Spacifay SP parameters ]-----
'ID
cmdSelect.Parameters.Add("@ID", SqlDbType.Int, 4)
cmdSelect.Parameters("@ID").Value = CType(txtID.Text, Integer)
'Open Connection
con.Open()
'-----[ Execute the select command ]-----
cmdSelect.ExecuteReader()
'----------------------------------------
'Close connection
con.Close()
'========================================================
End Sub
So, do I have to use an OUTPUT parameter in the stored procedure? If Yes, How to get its (the parameter) value and bind it to the appropriate TextBox?
Hope my question is clear!!
Thanks in advanced!
View 6 Replies
View Related
Mar 6, 2007
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset
rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset
Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset
B) Build a “pager� of some sort using the values from the first resultset
View 3 Replies
View Related
May 22, 2008
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
ASPX.CS file
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = null;
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
da.Fill(ds);
IFDdl.DataSource = ds;
IFDdl.DataTextField= ds.Tables[0].Columns[0].ToString();
IFDdl.DataValueField = ds.Tables[0].Columns[0].ToString();
IFDdl.DataBind();
}
protected void IncAddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = null;
try
{
con = DataBaseConnection.GetConnection();
SqlCommand cmd = new SqlCommand("AddIncome", con);
cmd.CommandType = CommandType.StoredProcedure;
//SqlCommand cmd = new SqlCommand("",con);
//cmd.CommandText = "insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)";
cmd.Parameters.Add("PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
cmd.Parameters.Add("@PA_IFName", SqlDbType.VarChar, 10).Value = IFDdl.SelectedValue;
cmd.Parameters.Add("@PA_IDesc", SqlDbType.VarChar, 50).Value = IFDescTB.Text;
cmd.Parameters.Add("@PA_IDate", SqlDbType.DateTime).Value = Convert.ToDateTime(IFDateTB.Text);
cmd.Parameters.Add("@PA_IAmt", SqlDbType.Money).Value = Convert.ToDecimal(IFAmtTB.Text);
cmd.ExecuteNonQuery();
IFLabelMsg.Text = "Income Added Successfully!";
} // end of try
catch (Exception ex)
{
IFLabelMsg.Text = "Error : " + ex.Message;
}
finally
{
con.Close();
}
}
Stored Procedure
ALTER PROCEDURE dbo.AddIncome (@PA_UID int,@PA_IFName varchar(10),@PA_IDesc varchar(50),@PA_IDate datetime,@PA_IAmt money)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/*SET NOCOUNT ON*/
insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)
ASPX File
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AddIncome.aspx.cs" Inherits="AddIncome" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>
Add Income </h2>
<br />
<table>
<tr>
<td>
Select Income Field</td>
<td>
<asp:DropDownList ID="IFDdl" runat="server" Width="247px" >
</asp:DropDownList>
<a href="addincomefield.aspx">Add Income Field</a>
</td>
</tr>
<tr>
<td>
Enter Income Amount
</td>
<td>
<asp:TextBox ID="IFAmtTB" runat="server" Width="96px"></asp:TextBox>
Date <asp:TextBox ID="IFDateTB" runat="server" Width="93px"></asp:TextBox>(MM/DD/YY)</td>
</tr>
<tr>
<td>
Enter Income Description
</td>
<td>
<asp:TextBox ID="IFDescTB" runat="server" Width="239px"></asp:TextBox></td>
</tr>
</table>
<br />
<asp:Button ID="IncAddBtn" runat="server" Text="Add Income" OnClick="IncAddBtn_Click" /><br />
<br />
<asp:Label ID="IFLabelMsg" runat="server"></asp:Label>
</asp:Content>
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
View 3 Replies
View Related
Oct 26, 2007
Ive run into a situation where some code im using will not function the same when between these two cases. So, this has me wondering what is going on behind the scenes. What is the sequence of events that are occurring that would possibly be messing things up.Here is the sample codeIts a gridview that is using a sqldatasource control. The code works fine, but if you want to bind the grid to a dataset and call databind yourself, things dont work as expected and the other features that the code performs just isnt happening, at least not for me.
View 1 Replies
View Related
Jan 16, 2006
Hello and thank you for taking a moment to read this message. I am simply trying to use a stored procedure to set up a dataset. For some reason when I try to fill the dataset with the data adapter I get the following error:
Compiler Error Message: BC30638: Array bounds cannot appear in type specifiers.Line 86: ' Create the Data AdapterLine 87: Dim objadapter As SQLDataAdapter(mycommand2, myconnection2)
my code looks as follows for the dataset:<script runat="server">Sub ListSongs()
' Dimension Variables in order to get songs Dim myConnection2 as SQLConnection Dim myCommand2 as SQLCommand Dim intID4 As Integer
'retrieve albumn ID for track listings
intID4 = Int32.Parse (Request.QueryString("id"))
' Create Instance of Connection
myConnection2 = New SqlConnection( "Server=localhost;uid=jazz***;pwd=**secret**;database=Beatles" ) myConnection2.Open()
'Create Command object Dim mycommand2 AS New SQLCommand( "usp_Retrieve song_",objCon) mycommand2.CommandType = CommandType.StoredProcedure mycommand2.Parameters.Add("@ID", intID4)
' Create the Data Adapter (this is where my code fails, not really sure what to do) Dim objadapter As SQLDataAdapter(mycommand2, myconnection2)
'Use the Fill() method to create and populate a datatable object into a dataset. Table will be called dtsongs Dim objdataset As DataSet() objadapter.Fill(objdataset, "dtsongs")
'Bind the datatable object called dtsongs to our Datagrid:
dgrdSongs.Datasource = objdataset.Tables("dtsongs") dgrdsongs.DataBind()</script><html><head> <title>Albumn Details</title></head><body style="FONT: 10pt verdana" bgcolor="#fce9ca"><center> <asp:DataGrid id="dgrdSongs" Runat="Server" ></ asp:DataGrid> </center></body></html>
Any help or advice would be greatly appreciated. Thank You - Jason
View 4 Replies
View Related
Jun 26, 2006
I am trying to make a dataset to use with a report. I need to get the data out of a stored procedure. I am using a temporaty table in the stored procedure, and the dataset doesnt recognize any of the colums that are output.
View 1 Replies
View Related
Nov 2, 2006
I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
View 5 Replies
View Related
May 25, 2007
Hi all,
Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere.
Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this.
This is what I have so far:-
1 SqlDataSource dataSrc2 = new SqlDataSource();2 dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3 dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4 dataSrc2.InsertCommand = "reportData";5 6 dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7 8 int rowsAffected;9 10 11 try12 {13 rowsAffected = dataSrc2.Insert();14 }
As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data.
Any help is greatly appreciated.
Slainte,
Sean
View 2 Replies
View Related
Oct 23, 2007
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
View 1 Replies
View Related
Mar 11, 2008
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts?
Thanks in advance,
N
View 6 Replies
View Related
Mar 24, 2008
I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem.
Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to.
Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e)
{GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C");
}
View 1 Replies
View Related
Jul 21, 2005
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms
@email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT
SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read.....
Dim errCode As Integer
Dim conn = dbconn.GetConnection()
Dim sqlAdpt As New SqlDataAdapter
Dim DS As New DataSet
Dim command As SqlCommand = New SqlCommand("Comms", conn)
command.Parameters.Add("@email", Trim(sEmail))
command.CommandType = CommandType.StoredProcedure
command.Connection = conn
sqlAdpt.SelectCommand = command
Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int)
pNum.Direction = ParameterDirection.Output
Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar)
pUserEmail.Size = 256
pUserEmail.Direction = ParameterDirection.Output
sqlAdpt.Fill(DS)
Return DS
Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way....
Dim DScomm As New DataSet
DScomm = getPts.getBabComm(sEmail)
dgBabComm.DataSource = DScomm.Tables(0)
And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
View 7 Replies
View Related
Apr 8, 2006
Hi, Can anyone please help me solve this problem.
My functions works well with this stored procedure:
CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO
But when I applied the same function to the following stored procedure
CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO
I received this message
Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error:
Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter();
Line 34: sqlDA.SelectCommand = sqlComm;
Line 35: sqlDA.Fill(dataSet);
Line 36:
Line 37: return dataSet;
The function I am using is returning a DataSet as below:
public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue)
{
DataSet dataSet = new DataSet();
SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString());
SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect);
sqlComm.CommandType = CommandType.StoredProcedure;
for (int n=0; n<paramName.Count; n++)
{
sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n]));
}
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = sqlComm;
sqlDA.Fill(dataSet);
return dataSet;
}
If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure?
Thanks.
View 1 Replies
View Related
Feb 6, 2007
Hello everyone,
I have a great deal of experience in Intrebase Stored Procedures, and there I had the FOR SELECT statement to loop through a recordset, and return the records I wish (or to make any other calculations in the loop).
I'm new in MS SQL Stored Procedures, and I try to achieve the same if possible. Below is a Stored Procedure written for MS SQL, which returns me a calculated field for every record from a table, but it places different values in the calculated field. Everything is working fine, except that I receive back as many datasets as many records I have in the Guests table. I would like to get back the same info, but in one dataset:
ALTER PROCEDURE dbo.GetVal AS
Declare @fname varchar(50)
Declare @lname varchar(50)
Declare @grname varchar(100)
Declare @isgroup int
Declare @id int
Declare @ListName varchar(200)
DECLARE guests_cursor CURSOR FOR
SELECT id, fname, lname, grname, b_isgroup FROM guests
OPEN guests_cursor
-- Perform the first fetch.
FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS =0
BEGIN
if (@isgroup=1)
Select @grname+'('+@lname+', '+@fname+')' as ListName
else
Select @lname+', '+@fname as ListName
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
END
CLOSE guests_cursor
DEALLOCATE guests_cursor
GO
can somebody help me please. Thanks in advance
View 1 Replies
View Related
Sep 26, 2014
ALTER PROCEDURE [dbo].[getFavoriteList]
as
begin
SET NOCOUNT ON
select manufacturer_name from dbo.Favorite_list
end
execute getFavoriteList
It reruns infinite data and finally i got message as
Msg 217, Level 16, State 1, Procedure getFavoriteList, Line 15
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I am trying to return the dataset from stored procedure.
View 1 Replies
View Related
May 4, 2006
Hi all,
I'm writing a CLR stored procedure that just execute a query using 2 parameters.
SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet?
How can I obtain a SqlDataReader from a DataSet?
Dim command As New SqlCommand(.......).....Dim ds As New DataSet()Dim adapter As New SqlDataAdapter(command)adapter.Fill(ds, "MyTable")... 'manipulating the ds.Tables("MyTable")
At this moment I have to send the table...but
ds.Tables("MyTable").CreateDataReader()
just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(...
Help me please!
View 7 Replies
View Related
Sep 26, 2004
I have a big SQL Stored Procedure which works with a cursor inside of it. During the procedure the data is inserted into a table and at the end is a SELECT statement from that table. The problem is that when i create a dataset with that stored procedure and i run it in the Data tab i get the correct select, but in the Fields section of the Report I don't get the fields from the last SELECT, but the fields from the cursor. Am I doing something wrong or is this a bug and how can i fix it.
Thanks!
View 3 Replies
View Related
Aug 20, 2007
Is it possible to use an Oracle Stored Procedure for an RDLC report. There are posts I've read that deal with RDL reports that use the data tab and command type of "Stored Procedure", but I don't have that installed. I just create a new dataset that the report uses. I can do reports just fine with SQL statements, but I want to be able to call a stored procedure...
Thanks
View 1 Replies
View Related
Sep 6, 2007
Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?
Thanks.
Brad
View 1 Replies
View Related
Oct 31, 2006
Hi all,
I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.
Thanks and have a nice day to all.
View 1 Replies
View Related
Jan 29, 2008
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
Thank you all very much for the information.
View 1 Replies
View Related
Aug 29, 2007
Hi all
I have a procedure where I am inserting some elements into #Table and then finally get the datset I need.
Now when I am using this procedure as dataset to my report, it throws up the following error:
Invalid object Name "#TEMP2".
The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1
Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?
I am not sure should I posting it here or in T-SQL Forum.
View 17 Replies
View Related
Jul 10, 2007
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
In the dataset, I typed
=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")
I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.
Thanks.
View 4 Replies
View Related
Jun 21, 2007
I have a stored procedure that works in my dataset editor, but when i try to run the report, only the "amount" field shows up. Everything else is blank. why is this happening. Here is the stored procedure.
USE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_TE_MKT_DETAIL_EXPENSE] Script Date: 06/21/2007 09:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_TE_MKT_DETAIL_EXPENSE]
(@Territory varchar(10) = Null)
AS
BEGIN
IF @Territory IS Null
BEGIN
SELECT
[Item_Description]+' '+'('+[Item_No]+')' Entry_Description
,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime, 'SALESAMP') * -1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL * -1 ) Amount
,-1*[Item_Ledger_Invoiced_Qty] Quantity
,Customer_Name
,'' External_Doc_no
,[Item_Ledger_Sales_Responsible] SR_Code
,[Item_Ledger_Mars_Period_Code] ThePeriod
, Budget_Reporting_Group.Budget_Reporting_Group_Description
, Budget_Type.Budget_Type_Code, Budget_Type.Budget_Type_Description
, Budget_Reporting.Budget_Forecast_Period, Salesperson_Purchaser.SalesPerson_Purchaser_Code
, Salesperson_Purchaser.SalesPerson_Purchaser_Description
, CASE WHEN Budget_Reporting_Group.Budget_Reporting_Group_Id = 1 THEN Budget_Reporting_Amount ELSE - 1 * Budget_Reporting_Amount END AS Amount
, Salesperson_Purchaser.Territory_Code
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
, Budget_Reporting_Group.Budget_Reporting_Group_Id
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting AS Budget_Reporting
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group AS Budget_Reporting_Group
ON Budget_Reporting_Group.Budget_Reporting_Group_Id = Budget_Reporting.Budget_Reporting_Group_Id
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Type AS Budget_Type
ON Budget_Reporting.Budget_Type_Code = Budget_Type.Budget_Type_Code
INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON Budget_Reporting.SalesPerson_Purchaser_Code = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]
ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Budget_Reporting.Budget_Year = 2007
AND Budget_Type.Budget_Type_Code in ('T&E', 'MKT')
END
IF @Territory IS NOT Null
BEGIN
SELECT Budget_Reporting_Group.Budget_Reporting_Group_Description
, Budget_Type.Budget_Type_Code, Budget_Type.Budget_Type_Description
, Budget_Reporting.Budget_Forecast_Period, Salesperson_Purchaser.SalesPerson_Purchaser_Code
, Salesperson_Purchaser.SalesPerson_Purchaser_Description
, CASE WHEN Budget_Reporting_Group.Budget_Reporting_Group_Id = 1 THEN Budget_Reporting_Amount ELSE - 1 * Budget_Reporting_Amount END AS Amount
, Salesperson_Purchaser.Territory_Code
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
, Budget_Reporting_Group.Budget_Reporting_Group_Id
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting AS Budget_Reporting
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group AS Budget_Reporting_Group
ON Budget_Reporting_Group.Budget_Reporting_Group_Id = Budget_Reporting.Budget_Reporting_Group_Id
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Type AS Budget_Type
ON Budget_Reporting.Budget_Type_Code = Budget_Type.Budget_Type_Code
INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON Budget_Reporting.SalesPerson_Purchaser_Code = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Territory.Code = @Territory
AND Budget_Reporting.Budget_Year = 2007
AND Budget_Type.Budget_Type_Code in ('T&E', 'MKT')
END
END
View 1 Replies
View Related
Dec 13, 2007
Hi, i have a dataset that has its data from a stored procedure. it returning data as one result set, so no fields. But i need fields to put them in a table in my report. How would i be able to access stored procedure fields? Please it's urgent...
View 2 Replies
View Related
Apr 27, 2004
Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.
This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.
Region FullName SHARP Year Ann Goal YTD Goal YTDActual
Region1 Doe10, John X 2003 20400 5100 0 Select
Region1 Doe10, John X 2003 0 0 3987 Select
Region1 Doe11, John X 2003 29645 7411.25 0 Select
Region1 Doe11, John X 2003 0 0 5377 Select
Here's my stored procedure:
CREATE PROCEDURE spFilterRegion
@RIDsent As Integer,
@StatusSent As Integer,
@SelectedRegion As NVARCHAR (50) Output
AS
SELECT Region.CountryID,
Employee.RegionID,
Employee.StatusID,
Employee.SHARP,
CASE
When Employee.SHARP = 1 THEN "X"
ELSE ""
END AS SHARPresult,
Employee.LastName,
Employee.FirstName,
Employee.LastName + ', ' + FirstName AS FullName,
Employee.EmployeeID,
Region.RegionName,
ProducerRevenue.RevenueTypeID,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS AnnGoal,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE())
ELSE 0
END AS YTDGoal,
CASE
When ProducerRevenue.RevenueTypeID = 2 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS Actual,
ProducerRevenue.YearID
FROM Employee
LEFT OUTER JOIN ProducerRevenue
ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND
ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND
ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND
ProducerRevenue.StatusID = 1 AND
ProducerRevenue.RevenueTypeID <= 2
LEFT OUTER JOIN Region
ON Employee.RegionID = Region.RegionID
WHERE Employee.StatusID = @StatusSent AND
Employee.RegionID = @RIDsent AND
Employee.RoleID = 1
GROUP BY Region.CountryID,
Employee.RegionID,
Region.RegionName,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
ORDER BY Region.CountryID,
Employee.RegionID,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
View 1 Replies
View Related
Jun 3, 2015
We are facing an issue while executing a stored procedure which uses a table of current database with INNER JOIN a table of another database in same instance.
Per our requirement, we are inserting select statement output in table variable. Then applying business logic and finally showing the data from table variable.
This scenario is working exactly fine in Dev environment. But when we deployed the code in quality environment. Stored procedure does not returning OUTPUT/ (No column names) from table variable.
During initial investigation, we found that collation of these two databases are different but we added DATABASE_DEFAULT collation in the JOIN.
View 14 Replies
View Related
Aug 17, 2015
We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..
In sql 2014 can we archive the same..sample format currently we are getting the client is like this is
Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.
View 6 Replies
View Related
