Fill DataSet Using Stored Procedure

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


ADVERTISEMENT

How Do I Get The OUTPUT Parameter Value From A Stored Procedure That I Am Using To Fill A Dataset?

May 10, 2007

View 6 Replies View Related

Stored Procedure To Fill Bridgetable Not Running

Oct 4, 2005

I have a table with information about a mobile account in one table, a table for mobile plans, and a table for planfeatures. Each mobile account is associated with a planid, and may be associated with any combination of the features associated with that plan. The plan features are stored in a bridgetable which contains 'invoicedate' (date stamped on the invoice in question), 'subaccountnumber' (the cell phone number), 'planid', and 'featureid'. I've tested the UpdateMobileFeature sp directly from the SQL Profiler--it works fine on it's own. I use a stored procedure to fill the mobile table (called from aspx page), and since I use three of the four columns written above for both the mobilesub and the mobilefeature tables, I tried just adding the parameter which holds the featureid's to the sp to update the mobilesub table. Then I call the UpdateMobileFeature sp from the updatemobile sp. (The code in mobilesub that is not calling UpdateMobileDetail works well). All seems to work fine--nothing crashes or anything, but nothing is being added to the mobilefeature table. Here is the code:CREATE procedure usp_updatemobile(     @InvoiceDate smalldatetime,    @SubaccountNumber varchar(50),    @PlanId int,   @FeatureList varchar(500) --added for UpdateMobileFeatures. In the form of a comma-seperated list, to imitate an array.  --***irrelevant parameters removed***--)
as
exec dbo.UpdateMobileFeature '@InvoiceDate','@SubaccountNumber','@PlanId','@FeatureList' --the apparently nonfunctional call
if exists (    //select for the mobile row in question )  Begin      //update row 
End
Else  Begin   //insert new row End
GOCREATE PROC dbo.UpdateMobileFeatures(  @InvoiceDate smalldatetime, @SubaccountNumber varchar(50),  @PlanID int, @FeatureList varchar(500))ASBEGIN SET NOCOUNT ON
 CREATE TABLE #TempList (  InvoiceDate smalldatetime,  SubaccountNumber varchar(50),  PlanID int,  FeatureID int     )
 DECLARE @FeatureID varchar(10), @Pos int  SET @FeatureList = LTRIM(RTRIM(@FeatureList))+ ',' SET @Pos = CHARINDEX(',', @FeatureList, 1)
 IF REPLACE(@FeatureList, ',', '') <> '' BEGIN  WHILE @Pos > 0  BEGIN   SET @FeatureID = LTRIM(RTRIM(LEFT(@FeatureList, @Pos - 1)))   IF @FeatureID <> ''   BEGIN    INSERT INTO #TempList (InvoiceDate, SubaccountNumber, PlanID, FeatureID)     VALUES (@InvoiceDate, @SubaccountNumber, @PlanID, CAST(@FeatureID AS int)) --Use Appropriate conversion   END   SET @FeatureList = RIGHT(@FeatureList, LEN(@FeatureList) - @Pos)   SET @Pos = CHARINDEX(',', @FeatureList, 1)
  END END 
 --SELECT o.FeatureID, CustomerID, EmployeeID, FeatureDate --FROM  dbo.Features AS o -- JOIN  -- #TempList t -- ON o.FeatureID = t.FeatureID
 
 Insert Into MobileFeatures Select InvoiceDate, SubaccountNumber, PlanID, FeatureID From #TempList  ENDGOHere is the method I used to call the first sp: (also with irrelevant stuff removed)public void saveCurrentMobile()           {
                      calculateTotals();
                      InvoiceDataSet dataset = InvoiceDataSet.GetInstance();
                      SqlConnection conn = (SqlConnection)Session["connection"];
                      SqlCommand cmdUpdateMobile;
                      cmdUpdateMobile = new SqlCommand("usp_updatemobile", conn);                      cmdUpdateMobile.CommandType = CommandType.StoredProcedure;
                      SqlParameter invoicedate = cmdUpdateMobile.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime);                      invoicedate.Value = DateTime.Parse(Request.Params["InvoiceDate"].ToString());
                      SqlParameter cellnumber = cmdUpdateMobile.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50);                      cellnumber.Value = txtCellNumber.Text.Trim();
                      SqlParameter plan = cmdUpdateMobile.Parameters.Add("@PlanId", SqlDbType.Int);                      plan.Value = planid;
                      SqlParameter featurelist = cmdUpdateMobile.Parameters.Add("@FeatureList", SqlDbType.VarChar, 500);                      featurelist.Value = planform.FeatureList;
                    
                 //Response.Write(isthirdparty.ToString());                 Response.Write(thirdpartycompany);
                      SqlParameter cycle = cmdUpdateMobile.Parameters.Add("@Cycle", SqlDbType.Int);                      cycle.Value = Convert.ToInt32(Request.Params["Cycle"]);
                      int returnvalue = runStoredProcedure(cmdUpdateMobile); //the sp is called within this method.
 
                      if (returnvalue != 0)                      {                          dataset.fillMobileTable();                         Response.Write("Mobile Subaccount Saved!");                      }           }
 

View 3 Replies View Related

Stored Procedure To Fill A Date Field

Oct 27, 1998

I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.

View 2 Replies View Related

Stored Procedure Go On And Fill It Automatic From Code Block

Dec 26, 2007


i explain
what i mean is if i have stored procedure with 7 conditions
for evry day in the week one condition
like this i have 7 code blocks for Sunday,Monday , .....Saturday







Code Block
CREATE PROC YourProc
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied
SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today

WHILE @StartDate <= @EndDate
BEGIN
SELECT CASE
WHEN DATENAME( dw,@StartDate)='Sunday' AND

empid IN (SELECT empid FROM v_un WHERE (shift =51 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Monday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =11 )
THEN 2
............
WHEN DATENAME( dw,@StartDate)='Saturday' AND
...
THEN ..
END
SET @StartDate=DATEADD(d,1,@StartDate)
END
GO








like this until the last day of the month

TNX

View 2 Replies View Related

How To Fill A Dataset?

May 11, 2006

Can a SqlDataSource be used to fill a DataSet? If so, will you show me how it's done?

View 2 Replies View Related

Unable To Fill Dataset

Apr 26, 2008

I'm trying without success to do a select command against the dataadapter and then fill a dataset. I'm using VS 2008, and I've set my project to the 2.0 framework. What is wrong is I get an empty dataset (The count on the dataset = 0. )with the code below. The database is connecting fine, so I assume it's something to do with the commandtext. The database is SQL CE 3.5.

Any ideas would be appreciated.





Code Snippet

Public Shared Function Init() As DataSet
'Todo: need to encrypt the password
Dim instance As New SqlCeConnection("Data Source=|DataDirectory|PBdata.sdf")
Dim selectcmd As SqlCeCommand = instance.CreateCommand
selectcmd.CommandText = "Select * From [dusers]"
Dim adp As New SqlCeDataAdapter(selectcmd)
Dim ds As New DataSet("dusers")
adp.Fill(ds)

Return ds

End Function

View 1 Replies View Related

DataSet Fill Using Cmd.Parameters - Legal?

Jan 2, 2007

A - Must Declare variable @ZipEntry - error generates when I run the code below.  If I hard code the "WHERE ZipCode = 72364" it works (display messed up but it works - don't know how to code in the line breaks yet).  But when I try to pass in the value of a TextBox called txtZipEntry on the page I get this error.  Am I not delcaring @ZipCode in the correct spot ?
Thank you,
public partial class ContractorResultsDisplay : System.Web.UI.Page{string ZipEntry;protected void Page_Load(object sender, EventArgs e){
ZipEntry = txtZipEntry.Text;SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);string sql = "Select [City], [StateCode], [AreaCode], [Latitude], [Longitude], [ZipCode] From [ZipCensus11202006] Where ZipCode > @ZipEntry";SqlCommand cmd = new SqlCommand(sql, con);cmd.Parameters.AddWithValue("@ZipEntry", ZipEntry);SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();da.Fill(ds, "ZipCodeRefs");StringBuilder htmlStr = new StringBuilder("");
foreach (DataRow dr in ds.Tables["ZipCodeRefs"].Rows){htmlStr.Append(dr["City"].ToString());}
ZipContent.Text = htmlStr.ToString();
}
}

View 1 Replies View Related

Dataset.fill Error In Sqlserver2000 + ASP.net 2.0

Jun 12, 2006

Hi,I am unable to load data in dataset via sqlconncetion object. Always i get error at ds.fill.Thanks in advance. PLS Help.Sudipta Datta www.geocities.com/dattasudipta 

View 1 Replies View Related

Fill A Dataset In Visual Studio 2005

Oct 13, 2006

this may seem like a real newbie question, but I got no clue how to solve this problem.  When I was working in Visual Studio 2003, whatever I wanted to fill a dataset with data, I was using a SqlDataAdapter that I created like this adapTest.Fill(dsTest); But now, I cant seem to find the sqldataadapter anywhere, I can just create (on the visual interface of one of my page of my aspx project) dataset, but cant find anywhere a adapter to put an SQL instruction into...maybe theres a new way to do this in Visual Studio 2005 that I dont know about.  Im not sure if I was clear enough, but what I want in the end is to be able to use my dataset like that dsTest.Tables.Rows[0]["a column"].ToString()by filling the dataset with data like I was able to do in VS2003.  Thanks for taking the time to read this.

View 3 Replies View Related

Fill DataSet Takes Forever, Query Db 7 Sec

Jan 16, 2007

Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.

View 2 Replies View Related

Counting Result Set From SQL Query Before Fill()ing A DataSet

Mar 6, 2007

I need to display something like "Results x-y of z."  The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set.  How can I get the right value for z?

View 5 Replies View Related

How To Fill Dataset With Parameterized SELECT Statement

Dec 17, 2007

 Hello,
I have been reading about how you shouldn't build dynamic SQL statements (see TextBox1.Text in line 3)  and should use parameters instead, but I haven't yet found how to create a SELECT statement with a parameter that fills a dataset. If anyone can show me the correct way of doing this I would appreciate it so I can add it to my code snippets for proper coding practices. Thanks in advance for any assistance.           
            string strConnectionString =   ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
            SqlConnection myConnection = new SqlConnection(strConnectionString);
 ->       string sqlSelect = "select * from customers where city = " + "'" + TextBox1.Text + "'";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, myConnection);
            DataSet ds = new DataSet();
            myConnection.Open();
            da.Fill(ds, "myDataset");
            myConnection.Close();
 
jcfrasco
                    

View 1 Replies View Related

Character String Query Doesn't Fill Dataset

Mar 15, 2007

I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end.  I have a strongly typed dataset in the application that calls a stored procedure for the select.  I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery)
This works fine in query analyzer.  The results return and display correctly.  However, when I run the application, the dataset does not get filled.  It is like the results do not output to the application.
If I change the query to be a normal select it works.  For example:
select * from tblEmample
That works fine.  What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?

View 1 Replies View Related

SQL Express Goes Out To Lunch In Visual Studio After Failing To Fill A DataSet

Aug 24, 2006

I have an off the wall situation that occurs when I try to fill a dataset that has already been filled with data. The issue with the DataSet in and of itself was a mistake I made and is not relevant to this forum.

What would appear to be relevant though, is the fact that after the exception was caught, I could not longer query data from the SQL Express database that was included in my project. In other words, when I tried to view the data in the IDE by selecting "Show Table Data," I received a message telling me that the query was running and eventually it timed out.

To fix the problem in my application, I obviously eliminated the bad code. But to be able to query the database again, I had to physically restart my computer (yes, I probably could have restarted the service, I know).

So my question is, has anyone run into this phenomenon before? If so, is there a way to prevent it other than the obvious, which is to clear the DataTables in my DataSet before filling them?

It's not that big a deal, but it is kind of a pain in the rear to have to restart everything because of a stupid coding mistake on the client side while I'm developing an app.

View 1 Replies View Related

Using A Stored Procedure With A Dataset

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

Dataset Stored Procedure Problem

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

Stored Procedure - Return DataSet

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

Populating A DataSet From A Stored Procedure

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

No Output From The Stored Procedure In The Dataset

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

Dataset With Existing Stored Procedure

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

Dataset Stored Procedure Return Value

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

Retrieve A Dataset From Stored Procedure

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

Return One Dataset Instead Of Many From A Stored Procedure

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

Stored Procedure Returning Dataset

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

From DataSet To SqlDataReader In A CLR Stored Procedure

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

Reporting Services Stored Procedure Dataset

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

Oracle Stored Procedure For RDLC Dataset

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

Combining A Stored Procedure And A Table In One Dataset?

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

Stored Procedure That Return Dataset(Select Query)

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

All Select Queries From Stored Procedure Not Appearing Under Dataset

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

#Table Not Found Error Using Stored Procedure For DataSet

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

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 View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved