Stored Procedure And ExecuteReader

Jul 5, 2005

I am not seeing why this is not executing the reader, it just goes right by it when stepping through the code...

command.CommandType = CommandType.StoredProcedure;

// course

command.Parameters.Add( "@courseId", courseId );

// Parameter: LessonName

SqlParameter sLessonName = command.Parameters.Add( "@lessonName", SqlDbType.VarChar );

sLessonName.Size = 256;

sLessonName.Direction = ParameterDirection.Output;

// error code

SqlParameter pErrCode = command.Parameters.Add( "@errCode", SqlDbType.Int );

pErrCode.Direction = ParameterDirection.Output;

// execute the stored procedure

SqlDataReader spResults;

conn.Open();

spResults = command.ExecuteReader();

while( spResults.Read() ) // It never steps into the while statement like the reader is completed

{

RetrieveObjId objNames = new RetrieveObjId( spResults.GetString( 0 ));

searchResults.Add( objNames );

}

spResults.Close();And the stored procedure is.....CREATE  PROCEDURE dbo.retrieveLessonNames @courseId  VARCHAR(20), @lessonName  VARCHAR(256) OUTPUT, @errCode         INT               OUTPUT  ASBEGIN

 SELECT @lessonName = objName       FROM   objStructure WHERE  courseId = @courseId

 SET @errCode = 0  RETURN @errCode HANDLE_APPERR: SET @errCode = 1  RETURNHANDLE_DBERR: SET @errCode = -1  RETURNENDGOSuggestions?Thanks all,Zath

View 3 Replies


ADVERTISEMENT

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Need Help With ExecuteReader() Command

Jan 18, 2008

HI I am having problem with my Execute Reader. I am trying to insert values from 2 different tables into another table.    SqlCommand comm2; SqlDataReader reader2; /* Grabs the stuff out of the database */
comm2 = new SqlCommand("SELECT HiraganaCharacter,HiraganaImage FROM Hiragana", getConnection()); /* opens the database */
comm2.Connection.Open();
/* starts the reader */
reader2 = comm2.ExecuteReader();
/* goes through the first array list */
for (int i = 0; i < checkedLetters.Count; i++) { /* find the data by using the array list value as a where clause */ comm2.CommandText = "SELECT HiraganaCharacter,HiraganaImage FROM Hiragana WHERE HiraganaCharacter ='"
+ checkedLetters[i] + "'"; /* reads through the data */ reader2.Read(); /* puts the ID- this id was set somewhere else */ CommQuickLinksItems.Parameters["@QuickLinkID"].Value = QuickLinkId; CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["HiraganaCharacter"].ToString(); CommQuickLinksItems.Parameters["@CharacterImagePath"].Value = reader2["HiraganaImage"].ToString(); CommQuickLinksItems.ExecuteNonQuery(); } for (int j = 0; j < checkedLettersKata.Count; j++) { comm2.CommandText = "SELECT KatakanaCharacter,KatakanaImage FROM Katakana WHERE KatakanaCharacter ='"
+ checkedLettersKata[j] + "'"; reader2.Read(); CommQuickLinksItems.Parameters["@QuickLinkID"].Value = QuickLinkId; /* line it dies on */ CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["KatakanaCharacter"].ToString(); CommQuickLinksItems.Parameters["@CharacterImagePath"].Value = reader2["KatakanaImage"].ToString(); CommQuickLinksItems.ExecuteNonQuery(); } CommQuickLinksItems.Connection.Dispose(); CommQuickLinksItems.Dispose(); comm2.Connection.Dispose(); comm2.Dispose(); My first question is there a better way to setup a SqlCommand to just get the connection and wait on the Command object text? Right now I am doing comm2 = new SqlCommand("SELECT HiraganaCharacter,HiraganaImage FROM Hiragana", getConnection());Which is kinda pointless since in the for loop I change the command to something different right away. At the same time though I don't really want to make a new SqlCommand object in the for loop since then everytime it goes through the loop it would then re grab the connection what I find pointless tooNow the problem How I have it right now it does not grab the right stuff. The first for loop works great and everything gets inserted. The next loop does not work It seems like it it trying to take the data from the first for loop and insert that stuff again since I get this error System.IndexOutOfRangeException was unhandled by user code Message="KatakanaCharacter" Source="System.Data" StackTrace: at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name) at Practice.QuickLinks() in g:WebsiteJapanesePractice.aspx.cs:line 385 at Practice.btnQuickLink_Click(Object sender, EventArgs e) in g:WebsiteJapanesePractice.aspx.cs:line 411 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException: Basically what I did was for the first loop I chose 2 items and for the 2nd loop I chose 3 items. When it died on this line  CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["KatakanaCharacter"].ToString();The value was "i" but that was one of the values I choose for the first for loop. It should have been either u,e,o. So I am not sure what I am doing wrong. I thought as long as I change the Command text I would not need to do anything else but it seems like I am missing something.  

View 5 Replies View Related

Using ExecuteReader() And GetString()

Mar 3, 2008

Hi.
I'm trying to read data from a database. This is my code:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
connection.Open();SqlCommand cmd = new SqlCommand(sql, connection);
myReader = cmd.ExecuteReader();if (myReader.Read())
{
name1TextBox.Text = myReader.GetString(1);
addr1TextBox.Text = myReader.GetString(2);
code1TextBox.Text = myReader.GetString(5);
tel1TextBox.Text = myReader.GetString(6);
fax1TextBox.Text = myReader.GetString(7);
:
:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The above code works fine until one of the GetString calls trys to return NULL (in this case myReader.GetString(5)).
In other words, this code will run through about 30 rows of data until it runs in to a NULL entry for one of the columns.
At that stage it's too late. I'm not allowed call GetString( ) on a NULL value.
Is there anyway I can test the column entry before calling GetString( ).
Regards (& thanks in advance)
Garrett

View 4 Replies View Related

Number Of Row Returned By ExecuteReader

Nov 14, 2007

when I execute the line:
reader = comm.ExecuteReader();
Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned.
For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form.
Here is my code snippet with further explanation of what I am trying to do:int Inumber = 0;foreach (string item in menuHeaders)
{string title = menuHeaders[Inumber];
sp.Value = menuHeaders[Inumber];
Inumber++;
conn.Open();reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
//Get the culture property of the thread.CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
//Create TextInfo object.TextInfo textInfo = cultureInfo.TextInfo;
// WHAT I AM TRYING TO DO....... Here I would like to wrap this with an if statement, if  Records returned by the reader are 0, skip while loop and header display
// If one, then display in singular and if 2 add an s to the title. Convert to title case and display.content.Text += "<H3>" + textInfo.ToTitleCase(title) + "</H3>";while (reader.Read())
{
content.Text += "<a href='" + reader["website"] + "'>" + reader["f_name"] + reader["l_name"] + "</a>"+ ", " +reader["organization"]+"<br />";
}
//Close the connection.
reader.Close();
conn.Close();
}

View 1 Replies View Related

Getting Data From An ExecuteReader Variable

Jan 28, 2008

Hi all,I have a script which I am running to get the minimum date from a database table.I've connected to the database and run the sql but when I try to get the result i get an error saying "No data exists for the row/column."This is the code I have for it at the moment.1 Dim mySql As String = "SELECT MIN(LOSS_DATE) AS minDate FROM dbo_CLAIMS"
2 Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|NexusHolding.mdb;Persist Security Info=True"
3 Dim dbCon As New OleDbConnection(connectionString)4
5 dbCon.Open()6
7 Dim dbComm As New OleDbCommand(mySql, dbCon)8 Dim dbRead = dbComm.ExecuteReader()9 Dim minDate As String = dbRead.GetValue(0)10
11 Response.Write(minDate)Thanks in advance for any help. 

View 3 Replies View Related

Catch Raiserror From ExecuteReader

Feb 27, 2004

Hi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?

Here's the sp:

CREATE PROCEDURE dbo.rel_test
AS
select 1
raiserror ('My error.', 11, 2)
return
GO

Here's the ASP.Net page:

<% @Page Language="VB" debug="True" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Function RunSP(ByVal strSP As String) As SqlDataReader
Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

o_conn.Open

Dim cmd As New SqlCommand(strSP, o_conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rdr.Close()
cmd.Dispose()

Response.Write(o_conn.State)


End Function

Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}" & _
"on line {4} of procedure {5} on server {6}:{7}", _
err.Source, err.Class, err.State, err.Number, err.LineNumber, _
err.Procedure, err.Server, err.Message))
Next
End Sub

Sub Page_Load(sender as Object, e as EventArgs)
RunSP("rel_test")
End Sub
</script>

View 2 Replies View Related

Intermittent ExecuteReader Problems

Oct 26, 2006

I have VS 2005 and SQL CE 3.0. I sometimes get the a FileNotFoundException when I first use ExecuteReader. I believe this is because a dll has not been copied across because if I restart the emulator I can get it to work again.

Do I need to add a cab file/dll to my project to stop this happening?

Thanks

Emmet

View 1 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Problems With : SqlDataReader = Command.ExecuteReader()

Apr 11, 2007

I am currently tryinh to have this variable declared :   Dim SQLLecteur As SqlDataReader = Command.ExecuteReader()And receiving the following error : 'ExecuteReader' is not member of 'String'.1. The ExecuteReader was not present in the list following the Command.2. The variable is declared from a : Public Shared Sub3. This sub is located in a code library referenced in the web.config as a namespace : <add namespace="PAX20070409" />4. If used directly in the .vb file within this sub : Protected Sub btnConnection_Click, I am not receiving any errors about the Dim.It is pretty clear why the code is not working, but I have not been able to find a way to fix the problem. I am currently trying to find a way to make the Dim work from within my code library. If you have any idea on how this could be achieve, it would be greatly apreciated.Thank you :)RV3 

View 4 Replies View Related

ExecuteReader: Connection Property Has Not Been Initialized.

Apr 23, 2008

I'm writing my first vb.net app.  Have a default page that uses a persons network login to query a database to get all their  timekeeper id, firstname, last name, etc.  But I keep getting this error.  (My code is below)  What am I missing??? 
ExecuteReader: Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.Source Error:



Line 21: conn.Open()
Line 22:
Line 23: reader = comm.ExecuteReader()
Line 24: If reader.Read() Then
Line 25: EmployeesLabel.Text = reader.Item("tkinit") 
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxxConnectionString").ConnectionStringcomm = New SqlCommand("Select top 1 tkinit, tklast, tkfirst +' '+ tklast as fullname from txxx WHERE login = @login)", conn)comm.Parameters.Add("@Login", Data.SqlDbType.VarChar)comm.Parameters("@Login").Value = Me.User.Identity.Name.Substring(User.Identity.Name.IndexOf("") + 1)conn = New SqlConnection(connectionString)conn.Open()reader = comm.ExecuteReader()If reader.Read() ThenEmployeesLabel.Text = reader.Item("tkinit")FirstLastName.Text = reader.Item("fullname")End Ifreader.Close()conn.Close()End Sub</script>

View 1 Replies View Related

ExecuteReader: Connection Property Has Not Been Initialized.

Mar 11, 2005

I have a web form that is generating an error and I can't seem to figure out why for the life of me. Below is the code:


Private Sub VerifyNoDuplicateEmail()
Dim conn As SqlConnection
Dim sql As String
Dim cmd As SqlCommand
Dim id As Guid
sql = "Select UserID from SDCUsers where email='{0}'"
sql = String.Format(sql, txtEmail.Text)
cmd = New SqlCommand(sql, conn)
conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString"))
conn.Open()
Try
'The first this we need to do here is query the database and verify
'that no one has registed with this particular e-mail address
id = cmd.ExecuteScalar()
Response.Write(id.ToString & "<BR>")
Catch
Response.Write(sql & "<BR>")
Response.Write("An error has occurred: " & Err.Description)
Finally
If Not id.ToString Is Nothing Then
'The e-mail address is already registered.
Response.Write("Your e-mail address has already been registered with this site.<BR>")
conn.Close()
_NoDuplicates = False
Else
'It's safe to add the user to the database
conn.Close()
_NoDuplicates = True
End If
End Try
End Sub

Web.Config
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Example: <add key="settingName" value="settingValue"/> -->
<add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" />
</appSettings>


Can anyone show me the error of my ways?

Thanks,
Stephen

View 4 Replies View Related

Command Doesnt Support Executereader

Nov 26, 2006

Code:

connstr = "my connction info"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connstr

Set objCmd=Server.CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objconn

objCmd.CommandText = "my stored procedure"
objCmd.CommandType = 4

objcmd.parameters.append objcmd.createparameter("@itemcheckduplicate", 200, 1, 30, itemadd)
SqlDataReader forduplicate = objcmd.ExecuteReader()

my procedure :

@itemcheckduplicate varchar(30)

as
select sw_item_itemid
from sw_item
where sw_item_item like @itemcheckduplicate



why my command doesnt support ExecuteReader ?

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'ExecuteReader'

View 2 Replies View Related

ExecuteReader: CommandText Propert Has Not Been Initialized

Oct 2, 2007



Hello All,

I receive the following error when attempting to preview or run a simple report:

ExecuteReader: CommandText propert has not been initialized


I have searched the help website and other forums, but I have had no success.

I am new to Reporting Services and this is one of the first reports I have created. All of my reports have this error.

Any ideas?

Thanks in advance.

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

ExecuteReader Error A Parameter Is Missing [Paremeter Ordinal = 1]

Sep 24, 2007

Hello and thanks in advance.

This error was received executing a SqlCeCommand method of ExecuteReader();

I have only found 3 hits in Google about this error and the time it was asked in a MS forum it was not answered. I need an answer, please.

bombar...see code below


SqlCeDataAdapter adp = null;

adp = new SqlCeDataAdapter();

SqlCeCommand cmd;

cmd = _Con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into InventoryCounts(DateTime,WarehouseID,PartID,LotID,UserName,Count) VALUES(?,?,?,?,?,?)";

adp.InsertCommand = cmd;

cmd = _Con.CreateCommand();

cmd.Parameters.Clear();

SqlCeParameter p1 = cmd.Parameters.Add("DateTime",SqlDbType.DateTime);

SqlCeParameter p2 = cmd.Parameters.Add("WarehouseID",SqlDbType.NChar,20);

SqlCeParameter p3 = cmd.Parameters.Add("PartID", SqlDbType.NChar,20);

SqlCeParameter p4 = cmd.Parameters.Add("LotID", SqlDbType.NChar,20);

SqlCeParameter p5 = cmd.Parameters.Add("UserName", SqlDbType.NChar,20);

SqlCeParameter p6 = cmd.Parameters.Add("Count", SqlDbType.Int);

p1.Value = Convert.ToString(DateTime.Now);

p2.Value = "Warehouse1";

p3.Value = txtBPartIDShow.Text;

p4.Value = txtBLotIDEntry.Text;

p5.Value = "JHB";

p6.Value = Convert.ToInt32(txtBCountEntry.Text);



cmd = _Con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into InventoryCounts(DateTime,WarehouseID,PartID,LotID,UserName,Count) VALUES(?,?,?,?,?,?)";

cmd.Prepare();

cmd.ExecuteReader();

View 4 Replies View Related

System.InvalidOperationException: ExecuteReader: Connection Property Has Not Been Initialized.

Jan 29, 2008

Hi,

I have written a CLR Function in C#. The function works as expected except that I am trying to read data some data during the function call and get the following error:


Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_SLARemaining":

System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.

System.InvalidOperationException:

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader()

at SLARemaining.SupportTimes.addSupportHours()

at SLARemaining.CalculateTimeRemaining.GetTimeRemaining(String openDate, Decimal SLADuration)

at UserDefinedFunctions.fn_SLARemaining(SqlDateTime DateFrom, SqlDateTime DateTo, SqlInt32 PriorityTime, SqlDecimal AdjustmentTime, SqlDecimal Status)

.


The main code for the function is this:


using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString fn_SLARemaining(SqlDateTime @DateFrom, SqlDateTime @DateTo, SqlInt32 @PriorityTime, SqlDecimal @AdjustmentTime, SqlDecimal @Status)

{

SLARemaining.CalculateTimeRemaining remaining;

remaining = new SLARemaining.CalculateTimeRemaining();

int duration = Convert.ToInt32(PriorityTime.ToString());

if (!DateFrom.IsNull)

{

string date = DateFrom.Value.ToShortDateString() + " " + DateFrom.Value.ToShortTimeString();

SqlString result = remaining.GetTimeRemaining(date, duration);

return result;

}

else

{

return null;

}

}

};


The function calls the following method from another class:


public string[] addSupportHours()

{

string[] supportedHours = new string[28];

SqlDataReader dr;



SqlCommand cmd = new SqlCommand();

cmd.CommandText = "xxxxxxx"; //<-- commented out for this post

using (SqlConnection cn = new SqlConnection("context connection=true;"))

{

cn.Open();


dr = cmd.ExecuteReader();



while (dr.Read())

{
//do some stuff
}

cn.Close();

cn.Dispose();
}

The error message claims that the connection has not been initialized - can't work out why? Any help will be appreciated!

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related

Calling Stored Procedure Fromanother Stored Procedure

Oct 10, 2006

Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames

View 16 Replies View Related

Use Resultset Returned From A Stored Procedure In Another Stored Procedure

Nov 15, 2006

I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
 Thanks,
Kevin

View 3 Replies View Related

SQL Stored Procedure Issue - Search Stored Procedure

May 18, 2007

This is the Stored Procedure below -> 
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/2/2007 4:52:19 PM ******/
 
CREATE  PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
 
IF (@i_WildCardFlag=0)BEGIN
 SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch  INNER JOIN   BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND  ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
 if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
 if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch  INNER JOIN  BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND  ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
 if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
 if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
 
 
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
 
Please help me regarding the same.
 
Thanks in advance.
 
Sandeep Kumar
 

View 2 Replies View Related

Sql Count Using Stored Procedure Withing Stored Procedure

Apr 29, 2008

I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks

View 10 Replies View Related

Exec Twp Stored Procedure In A Main Stored Procedure

Apr 29, 2004

Hi there

i have a stored procedure like this:

CREATE PROCEDURE SP_Main

AS
SET NOCOUNT ON

BEGIN TRANSACTION

exec SP_Sub_One output

exec SP_Sub_Two output


IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran

return
GO


now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.

now i can put the "begin transation" in the two sub stored procedure's. The problem is

what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"

now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data

how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.

Thanks in advance

Mahmoud Manasrah

View 1 Replies View Related

Calling A Stored Procedure Or Function From Another Stored Procedure

Mar 2, 2007

Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



SET NOCOUNT ON



DECLARE @TTL int

SET @TTL = dbo.TTLValue()



DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)

END

CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.

View 6 Replies View Related

Execute Stored Procedure From Stored Procedure With Parameters

May 16, 2008

Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.
Thank you,
Dave

View 6 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related







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