SqlParameters Not Working With Stored Procedures

Dec 31, 2006

Anyone can help me why is this not working?

My function is

public void updateSupplierInformation(string FirstLetter, string Vendor, string SegmentTeam, string VendorRank, string ScrubOwner)

{

SqlCommand myCommand = new SqlCommand("sp_LAST_UpdateSupplier", myConnection);

 

myCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();

SqlParameter myParam;

myParam = myCommand.Parameters.Add(new SqlParameter("@FirstLetterS", SqlDbType.VarChar, 1));

myParam.Direction = ParameterDirection.Input;

myParam.Value = FirstLetter;

myParam = myCommand.Parameters.Add(new SqlParameter("@VendorS", SqlDbType.VarChar, 30));

myParam.Direction = ParameterDirection.Input;

myParam.Value = Vendor;

myParam = myCommand.Parameters.Add(new SqlParameter("@SegTeamS", SqlDbType.VarChar, 30));

myParam.Direction = ParameterDirection.Input;

myParam.Value = SegmentTeam;

myParam = myCommand.Parameters.Add(new SqlParameter("@VendorRankS", SqlDbType.VarChar, 30));

myParam.Direction = ParameterDirection.Input;

myParam.Value = VendorRank;

myParam = myCommand.Parameters.Add(new SqlParameter("@ScrubOwnerS", SqlDbType.VarChar, 30));

myParam.Direction = ParameterDirection.Input;

myParam.Value = ScrubOwner;

 

int i =myCommand.ExecuteNonQuery();

 

myConnection.Close();

}

My Stored procedure is:

ALTER PROCEDURE sp_LAST_UpdateSupplier

@FirstLetterS varchar(1),

@VendorS varchar(30),

@SegTeamS varchar(30),

@VendorRankS varchar(30),

@ScrubOwnerS varchar(30)

AS

UPDATE [gntm_user].LAST_Supplier_Ownership

SET First_Letter =@FirstLetterS,Seg_Team =@SegTeamS,Vendor_Rank =@VendorRankS,

Scrub_Owner =@ScrubOwnerS

WHERE Vendor=@VendorS

 

RETURN

 

When I debug on any of the myParam value it displays "@FirstLetterS,@VendorS" etc. not the value which is being passed in the function which is FirstLetter,Vendor.

Can anyone help me on this please? Thanks in advance

View 2 Replies


ADVERTISEMENT

Stored Procedures And SqlParameters

Mar 22, 2006

Hi:I'm trying to pass a parameter to the store procedure below:CREATE PROC get_cat(@lang varchar(2))ASif(@lang="es")    SELECT [cod_cat], [nombre]     FROM [myuser].[Tbl_cat]if(@lang="en")    SELECT [cod_cat], [nombre_en]     FROM [myuser].[Tbl_cat]GOand on the .cs Codebehind file I'm using using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;namespace links_us{public class faqs : System.Web.UI.Page    {        protected System.Data.SqlClient.SqlConnection sqlConnFaqs;        protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapFaqs;        protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;        protected System.Data.SqlClient.SqlParameter Params;        protected System.Web.UI.WebControls.Repeater RepeaterFaq;        protected links_us.DataSetFaq dataSetFaq;            private void Page_Load(object sender, System.EventArgs e)        {            // Put user code to initialize the page here                                            Params = new System.Data.SqlClient.SqlParameter("@lang", SqlDbType.VarChar, 2);            Params.Value = "en";            Params.Direction = ParameterDirection.Output;            sqlDataAdapFaqs.Fill(dataSetFaq, "get_cat");            RepeaterFaq.DataBind();        } ........I´m getting this error:System.Data.SqlClient.SqlException: Procedure 'get_cat' expects parameter '@lang', which was not supplied.What´s wrong?Thanks in advance

View 2 Replies View Related

Working Woth Stored Procedures

May 6, 2007

Hi,
I am working on VS 2005 and I am using the grid view with Stored procedure the case is ..I want the grid to view all the supplier records by default .. but if the user specified a supplier name and/or type the grid should onlyview the relevant records..
What i did is .. I have created a text box (to enter the supplier name) and a drop down list (to choose the type)
** The problem is the grid does not appear unless I choose and fill the text box and drop down list and it only works with the type notwith the name ((does not match both parameters))
Can you please help I am stuck !!--------------------------------
My procedure is as following:
ALTER PROCEDURE p_test
@SupName nvarchar(200)='',@TypeID int= 0
AS
declare @SQL_Text varchar(8000)set @SQL_text = 'select * from dbo.v_supplier' +' where 1 = 1'
if rtrim(ltrim(@SupName)) <> ''set @SQL_Text = @sql_text + ' and name = '''+ @SupName +''''
if @TypeID <> ''set @SQL_Text = @sql_text + ' and type_id = '+ CONVERT(varchar, @TypeID)
if CONVERT(varchar, @TypeID)= '0'set @SQL_Text = @sql_text
BEGIN
exec(@sql_text)
END
----------------------------------------
My grid parameters is as following:
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSourceSup"></asp:GridView>
<asp:SqlDataSource ID="SqlDataSourceSup" runat="server" ConnectionString="<%$ ConnectionStrings:con %>" SelectCommand="p_test" SelectCommandType="StoredProcedure">
<SelectParameters><asp:ControlParameter ControlID="txtSup" Name="SupName" PropertyName="Text" Type="String" DefaultValue="" /><asp:ControlParameter ControlID="drpType" Name="TypeID" PropertyName="SelectedValue" DefaultValue = 0 Type="Int32" /></SelectParameters>
</asp:SqlDataSource>

View 4 Replies View Related

Update And Delete Stored Procedures Not Working

Feb 24, 2006

I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.
CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO
<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>
    Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs)        Dim connStr As SqlConnection        Dim cmdUpdateCartItem As SqlCommand        Dim UpdateCartItem        Dim strCartItemID As String        Dim txtQuantity As TextBox        strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex)        txtQuantity = e.Item.FindControl("txtQuantity")        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))        cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr)        cmdUpdateCartItem.CommandType = CommandType.StoredProcedure        cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID)        cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text)        connStr.Open()        cmdUpdateCartItem.ExecuteNonQuery()        connStr.Close()        dlstShoppingCart.EditItemIndex = -1        BindDataList()    End Sub
____________________________________________________________
CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO
<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>
    Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs)        Dim connStr As SqlConnection        Dim cmdDeleteCartItem As SqlCommand        Dim DeleteCartItem        Dim strCartItemID        strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex)        connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))        cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr)        cmdDeleteCartItem.CommandType = CommandType.StoredProcedure        cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID)        connStr.Open()        cmdDeleteCartItem.ExecuteNonQuery()        connStr.Close()        dlstShoppingCart.EditItemIndex = -1        BindDataList()    End Sub

View 2 Replies View Related

Debugging Stored Procedures With Visual Studio.net Has Stopped Working

Mar 17, 2004

Hi, I used to be able to debug stored procedures via Visual Studio.net 2003. However, this has stopped working. It does not produce an error just simply doesn't work anymore i.e. the breakpoints are by-passed.
I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated.
I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.

Thanks in advance
Lee

View 2 Replies View Related

Transfer SQL Server Objects Task Not Working With Stored Procedures

Sep 17, 2007



I'm unable to copy my Stored Procedures from one database to another. I'm using mixed mode authentication. I have set CopyAllStoredProcedures to True, DropObjectsFirst to True and CopySchema to True.


Nothing gets copied. I have followed many web sites that say Transfer SQL Server Objects Task is broken. Is this true and I should give up?

Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume is the update for SSIS? yes ?

Thanks for any help

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "CREATE PROCEDURE [dbo].[del_Admin_RemoveContractorFromContract] @ContractID int, @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID " failed with the following error: "There is already an object named 'del_Admin_RemoveContractorFromContract' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

View 4 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Looping Through Sqlparameters

Apr 11, 2008

lets say i have a stored procedure (for insert command) which i am calling in my code to execute.  The user provided data is being stored in a array. My class takes the stored procedure name and also takes parameters name and types.  Is there any way to loop through the parameters,  (various columns in the table which is of diffrent data type ie varchar, int, etc).  How to implement it?

View 1 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

SQL Server Fields And Sqlparameters

Nov 7, 2005

I am setting up a database and I would like to insert fields using sqlparameters into the database.  What I need to know is the relation between sqlparameter size and db field 'length' field.IE:  In the database does the 'length' field indicate bits? (which means that you have to convert parameter to bytes?)and what does the 8 refer to below?<CODE>Dim Param2 As New SqlClient.SqlParameter("@flTest", SqlDbType.VarChar)Param2.Size = 8</CODE>Thanks,Joe

View 1 Replies View Related

SqlParameters From Dynamically Loaded Assembly Won't Work!

May 13, 2004

Hi there!

I was trying to dynamically load a custom WebControl and display it into my WebFrom.

WebControl is loaded by:

System.Reflection.Assembly assembly = System.Reflection.Assembly.Load("myAssemblyName");
Control module = (Control)Activator.CreateInstance( assembly.GetType("myClassName") );
ModulePlaceHolder.Controls.Add(module);

This is working fine. Assembly is loaded and WebControl is created.

But as my Custom WebControl tries to access SQL Server database, i get a "Must declare the variable '@id'" while executing an ExecuteScalar() from a SqlCommand.

Code in my WebControl was perferctly functioning before converting it from a UserControl (.ascx) to Custom WebControl. Parameter '@id' is correctly declared in my query. If i don't use SqlParameters, everithing works fine...

Any ideas??

Thank you so much!!!

Bye!

View 2 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

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

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

Help Stored Procedure Working But Not Doing Anything

Jul 31, 2006

Help Stored procedure working but not doing anything New Post
Quote    Reply
Please i need some help.

I am calling a stored procedure from  asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.

if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors


This is the code from my control
System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};
               
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
               
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);
               
cn.Open();              

               
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();

               
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);
      


------------------------THis is the code for my storedprocedure-------------

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@periodStart datetime,
@periodEnd datetime,
@addedby varchar(40)

AS

begin transaction generatePayList

DECLARE @pensioner_id int, @dateadded datetime,
 @amountpaid float,
@currentMonthlypension float,@actionType varchar(50),
@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,
@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,
@reportSummary varchar(500)

set @NoOfLoadedPensioners =0

set @NoOfDeadPensioners=0
set @NoOfEnrolledPensioners=0
set @DeactivatedPensioners=0
set @actionType ="PayList Generation"

DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on  p.pensionerid=py.pensionerId

where p.isActive = 1


OPEN paylist_cursor

FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension

WHILE @@FETCH_STATUS = 0
BEGIN

set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1
if(@isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@pensioner_id
set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners
set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)
set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners
end

   -- Get the next author.
   FETCH NEXT FROM paylist_cursor
   INTO  @pensioner_id,@isAlive,@isActive,@currentMonthlypension
END

CLOSE paylist_cursor
DEALLOCATE paylist_cursor

set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')

 if (@@ERROR <> 0)               
          BEGIN

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')

ROLLBACK TRANSACTION  generatePayList

          END

commit Transaction generatePayList
GO

View 5 Replies View Related

Stored Proc Not Working, HELP PLEASE!

May 9, 2005

I'm pretty new when it comes to Stored Procs, and for some reason, the CurAge isn't coming up with an actual numeric value. Does anyone see what I am doing wrong?

CREATE PROCEDURE sp_GetTargetProducts
@hmid int,
@wtid int
AS
DECLARE @CurAge INT
if exists(
SELECT LastName, FirstName, HMID, DATEDIFF (YY , BirthDate, GetDate()) As CurAge
FROM Members
WHERE hmid = @hmid
)
BEGIN
SELECT DISTINCT Product, ProductAdvance, AgeBottom, AgeTop, ProductName, ProductDesc, ProductPrice, ProductPicture
FROM Products
WHERE wtid = @wtid
AND AgeBottom <= @CurAge
AND AgeTop >= @CurAge
GROUP BY Product, ProductAdvance, AgeBottom, AgeTop, ProductName, ProductDesc, ProductPrice, ProductPicture
return(1)
END
else
return(0)
GO

View 6 Replies View Related

Set Identity Not Working For Stored Procedure

Jan 31, 2008

Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
@publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null
ASBEGINSET NOCOUNT ON
IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
 

View 5 Replies View Related

Stored Prosedure Dont Working

Apr 25, 2008

i have 2 server, Database Server and Application Server.
i create a stored prosedure in Database SERVER (sql 2005)
i call this stored prosedure from my application but , my application dont see my stored prosedure..
what is a problem ? this is working my local computer and database.
my stored prosedure : sp_MyStoredProsedure
application :
...........Using myConnection As New SqlConnection(Config.ConnectionString)Dim myCommand As SqlCommand = New SqlCommand("sp_MyStoredProsedure", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
......
i think Ado.net permission problem but i dont know what is problem
thx.

View 6 Replies View Related

Update Stored Proc Not Working

Jan 12, 2005

I'm trying to run a UPDATE stored proc to allow my users to update records that are in a datagrid. What the update proc looks like is as follows:

Proc sp_UpdateRecords
@fname nvarchar(30), @lname nvarchar(30), @address1 nvarchar(50), @address2 nvarchar(50), @CITY nvarchar(33), @ST nvarchar(10), @ZIP_OUT nvarchar(5), @ZIP4_OUT nvarchar(4), @home_phone nvarchar(22), @autonumber int
AS
UPDATE NCOA20040603 SET fname=@fname, lname=@lname, address1=@address1, address2=@address2, CITY=@CITY, ST=@ST, ZIP_OUT=@ZIP_OUT, ZIP4_OUT=@ZIP4_OUT, home_phone=@home_phone
WHERE autonumber=@autonumber

The message I'm getting is as follows:

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index


What could be the problem

Any ideas are appriciated -- Thanks in advance
RB

View 5 Replies View Related

Working With Images In Stored Procs

Jan 17, 2005

What is the best way to store images in sql server and then retrieve them with stored proc's using vb.net/asp.net? There seems to be a lot of different opinions about just saving the img tag, the path, etc.

I only need to store 15 icons....

appreciate any help I can get.

Thanks

soggy coder seattle

View 4 Replies View Related

Stored Procedure - Insert Not Working

Jun 23, 2005

Having a little trouble not seeing why this insert is not happening.... --snip--  DECLARE c_studId CURSOR FOR  SELECT studentId  FROM students FOR READ ONLY   OPEN c_studId  FETCH NEXT FROM c_studId INTO @studentId  IF( @@FETCH_STATUS = 0 )  BEGIN   SET @studRec = 'Found'  END CLOSE c_studId DEALLOCATE c_studId
 BEGIN TRAN IF (@studRec <> 'Found')  BEGIN  INSERT INTO students  (studentId)  VALUES  (@studentId)    END  Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath

View 6 Replies View Related

Simple Stored Procedure Not Working

Jul 14, 2005

I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login

 what are mine doing wrong ?

Please help


CREATE PROCEDURE DBAuthenticate

(
  @username Varchar( 100 ),
  @password Varchar( 100 )
)
As

DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )

SELECT
  @ID = IdentityCol,
  @actualPassword = password
  FROM CandidatesAccount
  WHERE username = @username and Activate = 1

IF @ID IS NOT NULL
  IF @password = @actualPassword
    RETURN @ID
  ELSE
    RETURN - 2
ELSE
  RETURN - 1
GO

View 5 Replies View Related

Stored Procedure Stops Working

Jun 1, 1999

I have a stored procedure which does a simple select joining 3 tables.

This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.

After re-compiling the procedure, it worked fine as before.

Does anyone know of any reason why a procedure would need recompiling like this?

We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?

View 3 Replies View Related

Why Isnt My Stored Procedure Working

Oct 24, 2007

Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table











USE [dw_data]
GO
/****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[usp_address]
(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')

CREATE TABLE [dw_build].[dbo].[address] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[other_inv_link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out_of_district] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packed_address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_ignore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_valid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pct_of_res] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real_end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

END

IF @TableType is NULL OR @TableType = 2

BEGIN

CREATE TABLE [dw_build].[dbo].[address_cl] (
[_id] [int] NULL,
[address_1] [varchar](40) NULL,
[address_2] [varchar](40) NULL,
[category] [varchar](7) NULL,
[category_userno] [int] NULL,
[county] [varchar](40) NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[end_date] [datetime] NULL,
[forename] [varchar](40) NULL,
[notes] [varchar](max) NULL,
[other_inv_link] [int] NULL,
[out_of_district] [bit] NOT NULL,
[packed_address] [varchar](220) NULL,
[paf_ignore] [bit] NOT NULL,
[paf_valid] [bit] NOT NULL,
[patient] [int] NULL,
[patient_date] [datetime] NULL,
[pct_of_res] [int] NULL,
[postcode] [varchar](40) NULL,
[real_end_date] [datetime] NULL,
[relationship] [varchar](7) NULL,
[relationship_userno] [int] NULL,
[surname] [varchar](40) NULL,
[telephone] [varchar](40) NULL,
[title] [varchar](40) NULL,
[town] [varchar](40) NULL,
[updated] [datetime] NULL,
[updator] [int] NULL
) ON [PRIMARY]

END

View 13 Replies View Related







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