Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied

Procedure Or Function 'selectFromView' Expects Parameter '@Year', Which Was Not Supplied. ???
 hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView

@Year as varchar(10),
@Country as varchar(10),
@Family as varchar(10),
@Manu as varchar(10),
@Status as varchar(10),
@Type as varchar(10),
@Operator as varchar(10)


AS
SELECT * FROM ViewofAll
WHERE
ProductionYear = @Year and
CountryName = @Country and
Family = @Family and
Manufacturer = @Manu and
Status = @Status and
Type = @Type and
OperatorName = @Operator

RETURN and below how i call the procedure :   Dim connection As SqlConnection
connection = New SqlConnection()
connection.ConnectionString = "Data Source=BLACKIRIS3SQLEXPRESS;Initial Catalog=Aircrafts;Integrated Security=True"
connection.Open()
Dim command As SqlCommand
command = New SqlCommand("selectFromView", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Year", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Country", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Family", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Manu", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input 'Stored prcedure parameters
command.Parameters.Add(New SqlParameter("@Status", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Type", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Operator", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Item("@Year").Value = myArray(0)
command.Parameters.Item("@Country").Value = myArray(1)
command.Parameters.Item("@Family").Value = myArray(2)
command.Parameters.Item("@Manu").Value = myArray(3)
command.Parameters.Item("@Status").Value = myArray(4)
command.Parameters.Item("@Type").Value = myArray(5)
command.Parameters.Item("@Operator").Value = myArray(5)
DSGrid.SelectCommand = command.CommandText With DSGrid
.DataBind()
End With  i'm not really familiar with stored procedures ...any one can help me plz ?  thanx in advance .  

View Replies !

---

Procedure Or Function 'SubscribeToNewsletters' Expects Parameter '@emailAddress', Which Was Not Supplied.
Hi i have been trying to insert some values selected in a checkbox list into the database, however it is giving me the following error "Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied. "public void Page_Load(object sender, EventArgs e) -- on page load the checkbox list is populated through stored procedure (stream_NewsletterTypes)
{
try
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn); command.CommandType = CommandType.StoredProcedure;
command.Connection.Open();SqlDataReader datareader = command.ExecuteReader();
AlertList.DataSource = datareader;AlertList.DataTextField = "newsletterName";
AlertList.DataBind();
command.Connection.Close();
command.Connection.Dispose();
}catch (Exception ex)
{throw new Exception("Exception. " + ex.Message);
}
}
 protected void Btn_Subscribe_Click(object sender, EventArgs e) when the button is clicked, the values should be sent to the database
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn);command.CommandType = CommandType.StoredProcedure;for (int i = 0; i < AlertList.Items.Count; i++)
{if (AlertList.Items[i].Selected)
{
command.Parameters.AddWithValue("firstName", txtFirstName.Text);command.Parameters.AddWithValue("surname", txtSurname.Text);
command.Parameters.AddWithValue("emailAddress", txtEmail.Text);command.Parameters.AddWithValue("newsletterID", AlertList.Items[i].Value);
}
}
try
{
conn.Open();
command.ExecuteNonQuery();
}catch (Exception ex)
{throw new Exception("Exception adding account. " + ex.Message);
}
finally
{
conn.Close();
}
}
 
And my stored procedure;ALTER PROCEDURE [dbo].[SubscribeToNewsletters]
@emailAddress VARCHAR(250),
@firstName VARCHAR(250),
@surname VARCHAR(250),
@newsletterID INT,@userID INT OUTPUT
AS
INSERT INTO ThinkUsers (emailAddress, firstName, surname)
VALUES
(@emailAddress, @firstName, @surname)
Select @userID = @@Identity -- this is the ID created in the TheUserTable
-- when you make that first insert, the database
-- will generate the next ID value in that table
INSERT INTO SubscribedNewsletters (userID, newsletterID)
VALUES
(@userID, @newsletterID)

View Replies !

---

Procedure Or Function 'retrieve_product' Expects Parameter '@productPrice', Which Was Not Supplied.
Hi i am getting this error whenever i try to execute a stored procedure, below is my stored procedure;
 ALTER PROCEDURE [dbo].[retrieve_product]
-- Add the parameters for the stored procedure here
@productPrice decimal (10,2),
@productInfoURL varchar (255),
@categoryName varchar (255),@companyName varchar (255),
@subCategoryName varchar (255),
@companyWebsiteURL varchar (255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Categories.categoryName, SubCategories.subCategoryName,Companies.companyName, Products.productPrice,
Products.productInfoURL FROM Categories
INNER JOIN Products ON Categories.categoryID = Products.categoryID
INNER JOIN Companies ON Products.companyID = Companies.companyID
INNER JOIN SubCategories ON Categories.categoryID = SubCategories.categoryID AND Products.subcategoryID = SubCategories.subCategoryID
END
 

View Replies !

---

Procedure Or Function 'syl_EmailQueueInsert' Expects Parameter '@EmailCC', Which Was Not Supplied.
We have a email table in the database (SQL Server 2005). When we try to execute the Insert Stored Proc in the database to add a record and leave the EmailCC and EmailBCC fields blank we are getting the following error: Msg 201, Level 16, State 4, Procedure syl_EmailQueueInsert, Line 0Procedure or Function 'syl_EmailQueueInsert' expects parameter '@EmailCC', which was not supplied. The EmailCC and EmailBCC are set to allow nulls. Shouldn’t the system automatically insert nulls into those colums if no value is supplied??? What am I doing wrong? Newbie

View Replies !

---

Procedure Or Function 'insertProjectDetails' Expects Parameter '@ProjectTitle', Which Was Not Supplied.?
Running [dbo].[insertProjectDetails] ( @ProjectTitle = <DEFAULT>, @ProjectPeriod = <DEFAULT>, @Client = <DEFAULT>, @Position = <DEFAULT>, @ProjectDescription = <DEFAULT>, @Responsiblities = <DEFAULT>, @OtherInformation = <DEFAULT> ).

Procedure or Function 'insertProjectDetails' expects parameter '@ProjectTitle', which was not supplied.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[insertProjectDetails].

Can any body send solution for this error?

 

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied
 Hi everyone, can you please take a look at this code and tell me where I'm wrong.I am getting the error Procedure 'usp_Insert_Attribution' expects parameter '@At_Code', which was not supplied <asp:SqlDataSource
id="ds_Attribution"
runat="server"
SelectCommand="usp_Select_Attribution"
SelectCommandType="StoredProcedure"
InsertCommand="usp_Insert_Attribution"
InsertCommandType="StoredProcedure"
DeleteCommand="usp_Delete_Attribution"
DeleteCommandType="StoredProcedure"
UpdateCommand="usp_Update_Attribution"
UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="Identity" Direction="Output" Type="Int32"/>
</InsertParameters>
<DeleteParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource>   <InsertItemTemplate>
<table>
<tr>
<td>Attribution Code</td>
<td>
<asp:Textbox ID="txtAt_Code" runat="server" Text='<%# Eval("At_Code") %>' CssClass="textbox" />
<asp:RequiredFieldValidator ID="rfvAt_Code" runat="server" ControlToValidate="txtAt_Code"
Display="Static" Text="*" ErrorMessage="Attribution Code must be entered." />
<asp:CustomValidator ID="cvAt_Code" runat="server" Text="*" ErrorMessage="Attribution Code already exists."
Display="Static" />
</td>
</tr>
<tr>
<td>Attribution Description</td>
<td>
<asp:Textbox ID="txtAt_Description" runat="server" CssClass="textBox_Multiline" maxlength="250" TextMode="MultiLine" Text='<%# Bind("At_Description") %>'></asp:Textbox>
</td>
</tr>
</table>

</table>
<asp:Panel ID="pnlAttributionOptions_I" runat="server" GroupingText="Options" CssClass="panel">
<table width="100%">
<tr>
<td><asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel" CssClass="button" CausesValidation="false"></asp:LinkButton>
</td><td><asp:LinkButton ID="lnkInsert" runat="server" Text="Save" CommandName="Insert" CssClass="button"></asp:LinkButton>
</td></tr></table>
</asp:Panel>

</InsertItemTemplate>  CREATE PROCEDURE dbo.usp_Insert_Attribution
(
@At_Code varchar(10),
@At_Description varchar(250),
@At_SystemDate datetime,
@Identity int OUT
)
AS
SET NOCOUNT OFF;
INSERT INTO Attribution (At_Code, At_Description, At_SystemDate)
VALUES (@At_Code,@At_Description, @At_SystemDate);

SELECT * FROM Attribution WHERE (At_ID = SCOPE_IDENTITY())
SET @Identity = SCOPE_IDENTITY()
GO
  

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied
Here is my code. Please help me evaluate? Why do i get thiis error. What am i doing wrong: System.Data.SqlClient.SqlException: Procedure 'SearchHRMS' expects parameter
'@SearchString', which was not supplied 1 If Not IsPostBack Then
2 '2 - declare variables
3 Dim conn As SqlConnection
4 Dim cmd As SqlCommand
5
6 '3 - create connection
7 conn = New SqlConnection
8 conn.ConnectionString = ConfigurationManager.ConnectionStrings("HRMSConnectionString").ConnectionString
9 conn.Open()
10
11 '4 - create command
12 cmd = New SqlCommand
13 cmd.CommandType = CommandType.StoredProcedure
14 cmd.CommandText = "SearchHRMS"
15 '4.2 -Declare a parameter
16 Dim param As SqlParameter
17 param = New SqlParameter("@SearchString", SqlDbType.VarChar, 20)
18 param.Direction = ParameterDirection.Input
19 param.Value = "john"
20
21 '5-Link command to connection
22 cmd.Connection = conn
23
24 'EmployeeListDataList.DataSource = cmd.ExecuteReader()
25 'EmployeeListDataList.DataBind()
26 'GridView1.DataSource = cmd.ExecuteReader()
27 'GridView1.DataBind()
28 conn.Close()
29 End If
  

View Replies !

---

Procedure Expects Parameter Not Supplied
Hi - Im working with a sign up page in which I'd like to include the date that the member signed up. I keep getting this error and I was wondering if anyone could help. The error is:

Procedure 'spAddCustomer' expects parameter '@MemberSince', which was not supplied.

I have the date in a label on the form and then I am passing to the database with:

cmdAddCustomer.Parameters.Add("@MemberSince", today_date.text).

Can anyone help with what I am doing wrong? Thanks!

View Replies !

---

Procedure 'x' Expects Parameter '@y' Which Was Not Supplied.
I'm trying to update a member's record via stored procedure. The procedure is thus:

*********************************
CREATE PROCEDURE upd_MemberProfile(
@MemberID VarChar(10),
@FirstName VarChar(20),
@LastName VarChar(20),
@Address VarChar(50),
@City VarChar(40),
@StateID Char(2),
@ZipCode Char(5),
@Email VarChar(30),
@PayPalSubscrID VarChar(22)
) AS

UPDATE Members
SET FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, StateID = @StateID, ZipCode = @ZipCode, Email = @Email, PayPalSubscrID = @PayPalSubscrID
WHERE MemberID = @MemberID
GO
*****************************

The call from the Webpage is like so:

*****************************
...
Dim Subscr_id As String = "123ABC-HDTV"

Try
cmd.CommandText = dbo() & "upd_MemberProfile"
cmd.CommandType = CommandType.StoredProcedure

With cmd.Parameters
.Add("@MemberID", MemberID)
.Add("@FirstName", FirstName)
.Add("@LastName", LastName)
.Add("@Address", Address)
.Add("@City", City)
.Add("@StateID", StateID)
.Add("@ZipCode", ZipCode)
.Add("@Email", Payer_email)
.Add("@PayPalSubscrID", Subscr_id)
End With


If cnn.State.Open Then
cnn.Close()
End If

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

Catch ex As Exception

'Notify Admin of the Problem
MailUsTheOrder("ERROR on upd_MemberProfile: " & ex.Message)

'Close the SQL Connection
If cnn.State.Open Then
cnn.Close()
End If
End Try
******************************

This was working two weeks ago. I have changed NOTHING and now I get the error:

Procedure 'upd_MemberProfile' expects parameter '@PayPalSubscrID', which was not supplied.

Any Ideas?

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied
 
why is this? I posted this to the Windows forms group but this may be a problem with my spro?
here is the;
1.  sproc in SQL Server Express  (on Vista)
2.  the code ( vb.net VS2008 Beta 2)
3.  the error
http://www.hazzsoftwaresolutions.net/sprocParam_ex.htm
 
Thank you for any help.
Greg

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied...?
I have this SP definition and below that the code to execute it from .Net. But I'm getting this error: Procedure 'GetNewId' expects parameter '@Key', which was not supplied.

Saw an solution on some site which said to set the value of the output param to NULL before calling it, but that didn't do anything.

Anyone have any ideas why this may be happening?? Thanks in advance.

CREATE PROC GetNewId(@TableName varchar(32), @Key bigint OUTPUT) AS
Declare @NextKey bigint
BEGIN TRAN

SELECT @NextKey = NewID from ID where TableName = @TableName
IF (SELECT @NextKey)IS NULL
  BEGIN
     INSERT INTO ID(NewID, TableName) VALUES(0, @TableName)
  END

  UPDATE ID
  SET NewID = NewID + 1 Where TableName = @TableName
 
  SELECT @NextKey = NewID from ID where TableName = @TableName
 
  SET @Key=@NextKey

COMMIT TRAN

  Return @Key

GO

---------------------------------------------------------------------------------------------
_SQLCommand.CommandText = theCommStr;
_SQLCommand.CommandType = CommandType.StoredProcedure;

SqlParameter aInputTblPrm = new SqlParameter("@TableName", "Jack");
aInputTblPrm.Direction = ParameterDirection.Input;
        
SqlParameter aReturnKeyPrm = new SqlParameter("@Key", SqlDbType.BigInt);
aReturnKeyPrm.Direction = ParameterDirection.InputOutput;        

_SQLCommand.Parameters.Add(aInputTblPrm);
_SQLCommand.Parameters.Add(aReturnKeyPrm);

_SQLCommand.ExecuteNonQuery();

View Replies !

---

Procedure X Expects Parameter '@dealer', Which Was Not Supplied.
Hi
In my code I do this
Dim ds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString(), "usp_SearchCar", dealer, vehicleType, bodyType, make, model, engineType, year, minPrice, maxPrice, transmission)
But I get the error (in the subject)
I check and the value of dealer is an empty string but that's ok right?
ThanksP
 

View Replies !

---

Error - Procedure Expects Parameter, Which Was Not Supplied
Hi,

I created a stored procedure in the sql server. I try to insert a record from the aspx page. But I keep getting this error,
"procedure expects parameter <@firstname>, which was not supplied". This is what I am doing.

cmd.CommandText = "proc_insertuser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@firstname", SqlDbType.VarChar, 50);
cmd.Parameters["@firstname"].Value = txtFirstName.Text
....other parameters

I checked the parameter names, it matches correctly. The parameter does get added to the collection. I checked it using cmd.Parameters.Contains("@firstname") and the value is also correct. Using query analyzer I executed the procedure, I am able to insert a record.

Can anyone tell me what could be the problem.
TAI

View Replies !

---

Procedure 'JaiDeleteUser' Expects Parameter '@user_name', Which Was Not Supplied
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server"><title>Untitled Page</title> </head>
<body>
 
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
 
<div>
 
<br />
<asp:Label ID="lblUserInfo" runat="server" Text="Label"></asp:Label><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:JaiConnectionString %>" DeleteCommand="JaiDeleteUser" DeleteCommandType="StoredProcedure" SelectCommand="SELECT users.user_name, users.user_pass, user_roles.role_name, users.VendorId FROM users INNER JOIN user_roles ON users.user_name = user_roles.user_name"
UpdateCommand="JaiUpdateUser" UpdateCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource><asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /><asp:BoundField DataField="user_name" HeaderText="user_name"
SortExpression="user_name" /><asp:BoundField DataField="user_pass" HeaderText="user_pass"
SortExpression="user_pass" /><asp:BoundField DataField="role_name" HeaderText="role_name"
SortExpression="role_name" /><asp:BoundField DataField="VendorId" HeaderText="VendorId"
SortExpression="VendorId" />
</Columns>
</asp:GridView>
<br />
<br />
</div></form> </body>
</html>
 
 
<<<<<<<<<<<<<Stored Procedure>>>>>>>>>>>>>>>>
CREATE PROCEDURE [dbo].[JaiDeleteUser] @user_name varchar (25), @user_pass varchar (25), @role_name Varchar (15), @VendorId intAS
beginDelete from user_roles where user_name =@user_nameDELETE FROM [users] WHERE user_name =@user_nameendGO
I am getting the error
Procedure 'JaiDeleteUser' expects parameter '@user_name', which was not supplied
whenever I try to delete a record. While Updating works with no problem. Please help.

View Replies !

---

SQL_Error Procedure 'DelectHostName' Expects Parameter '@Host', Which Was Not Supplied.
How do I supply the parameter @Host?  Below is the part of my code that has the call to the stored procedure 
public void DeleteHostName()
{ // start of the method
using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))            {
                cn.Open();                SqlCommand cmd = new SqlCommand("hnDeleteHost", cn);                cmd.CommandType = CommandType.StoredProcedure;                cmd.Parameters.Add(new SqlParameter("@pHostID", SqlDbType.VarChar, 50, "HostNameID"));                cmd.Parameters.Add(new SqlParameter("@pRet", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, " ", DataRowVersion.Default, null));                cmd.UpdatedRowSource = UpdateRowSource.None;                cmd.ExecuteNonQuery();            }

View Replies !

---

Error--Procedure 'SP_Insert_NewPipeLine' Expects Parameter '@CompanyId', Which Was Not Supplied.
Can any body help me in solving this problem.
First I use to get Error which reads "Object Must Implement Iconvertible"
After using the overloaded Sp.paramerers.add() function It started giving this problem.
I am giving the sample code.
ConObject = new SqlConnection(ConString);
ConObject.Open();
string SpString ="dbo.SP_Insert_NewPipeLine";
SqlCommand CmdObject = new SqlCommand(SpString,ConObject);
CmdObject.CommandType = CommandType.StoredProcedure;
CmdObject.Parameters.Add("@RequestTypeId",SqlDbType.Int,4,"0");
//CmdObject.Parameters["@RequestTypeId"].Value= 0;
CmdObject.Parameters.Add("@OnBehalfOf",SqlDbType.Int,4,"21");//Onbehalf of Id
//CmdObject.Parameters["@OnBehalfOf"].Value = 21;

CmdObject.Parameters.Add("@SubmittedBy",SqlDbType.Int,4,ddlSalesRep.SelectedValue.ToString());//Submitted by Id
//CmdObject.Parameters["@SubmittedBy"].Value = ddlSalesRep.SelectedItem.Value;
CmdObject.Parameters.Add("@CompanyId",SqlDbType.Int,4,ddlCustomer.SelectedItem.Value.ToString());//Company_Id
//CmdObject.Parameters["@CompanyId"].Value = ddlCustomer.SelectedItem.Value;
CmdObject.ExecuteScalar();

View Replies !

---

Please Help, Procedure 'sp_displaylabels' Expects Parameter '@mod_id', Which Was Not Supplied In Asp.net Form
Procedure 'sp_displaylabels' expects parameter '@mod_id', which was not supplied.

I get the error at the SQLAdptr1.Fill(DS): Procedure 'sp_displaylabels' expects parameter '@mod_id', which was not supplied.

But i am passing the @mod_id value in the string Valmodid.


Private Sub BindData()
Dim DS As New DataSet
Dim SQLCmd1 As New SqlCommand
Dim moduleId As New SqlParameter
Dim Valmodid As String

Valmodid = Request.QueryString("modid")

moduleId = SQLCmd1.Parameters.Add("@mod_id", Valmodid)

SQLCmd1 = New SqlCommand("sp_displaylabels", MyConnection)

Dim SQLAdptr1 As SqlDataAdapter = New SqlDataAdapter(SQLCmd1)

SQLAdptr1.Fill(DS)

MyDataGrid.DataSource = DS
MyDataGrid.DataBind()
End Sub

'''My Stored Procedure code:
CREATE PROCEDURE [dbo].[sp_displaylabels]
(
@mod_id nvarchar(10)
)
AS
SELECT *
FROM tbl_labels
where module_id=@mod_id ORDER BY id ASC
GO

View Replies !

---

Error: Procedure Or Function &"Roles_delete&" Expects Parameter &"@Role_name&", Which Not Supplied...
Hello guy...i have a problem with my web application...i'm using SqlDataSource and GridView....i'm using store procedure that i've created in my mssql server...the problem is when i'm trying to delete current row...this error alert and said
Error

"Procedure or Function "Roles_delete" expects parameter "@Role_name", which not supplied"
Im new user in asp.net....how i can passing my selected value to this parameter??? This is my code...please help me guy...
Asp.net code
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>"
DeleteCommand="Roles_delete" DeleteCommandType="StoredProcedure" SelectCommand="Roles_select"
SelectCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="Role_name" Type="String" />
</DeleteParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="Role_application_id"
DataSourceID="SqlDataSource1" ForeColor="Black" GridLines="Vertical">
<FooterStyle BackColor="#CCCC99" />
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="Role_application_id" HeaderText="Role_application_id"
ReadOnly="True" SortExpression="Role_application_id" />
<asp:BoundField DataField="Role_name" HeaderText="Role_name" SortExpression="Role_name" />
<asp:BoundField DataField="Role_description" HeaderText="Role_description" SortExpression="Role_description" />
</Columns>
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
 
Store Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Roles_delete]
@Role_name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Role
WHERE Role_name=@Role_name
END

 Error

Procedure or Function "Roles_delete" expects parameter "@Role_name", which not supplied"
Please help me guy to settle my problem...

View Replies !

---

Exception Details: System.Data.SqlClient.SqlException: Procedure 'spInsertBillingQ' Expects Parameter '@bankname', Which Was Not Supplied.
Hello Coders,
 I need help fixing a problem in my code that I do not understand. I basically have a procedure that is supposed to take all user information and then insert it to the DB or update the Database based on user entry.
The call to the method is this
Dim bankname As String = ""
If (objBill.BillTransaction(Session("conString"), Session("whattobill"), PROC_CURR, Session("lISN"), 0, Session("fISN"), Request.ServerVariables("REMOTE_ADDR"), "scott@mycleanstart.com", Session("fAmount"), Session("first_name"), Session("last_name"), Session("city"), Session("state"), Session("phone"), Session("address1"), Session("address2"), Session("postal_code"), Session("email_address"), Session("fDesc"), Session("ssn"), Session("nameoncard"), Session("PAN"), Session("cvv"), Session("cardexpirationdate"), bankname, Session("txtroutingnumber"), Session("txtaccountnumber"), Session("txtchecknumber"), Session("bqISN"), _
returnedbqISN, AuthCode, OrderNumber, DeclineCode, TermCode, ErrorMessage, authenticationValue, authenticationTransactionID, str_Centinal_ECI, "signup", PAResStatus, SignatureVerification, paypalSubAgreeID, notificationLocation, strErrorNo, strErrorDesc, strTransactionID, strStatus, strStatusCode, strReasonCode)) Then
 
 And then below is the actual method itself........
NOTE: The code below is contained in a DLL
 
 
 
 
 1 public bool BillTransaction(string strDBInstance, string ccORach, string whichProcessor,Int32 lISN, Int32 mISN,
2 Int32 fISN, string ip,string merchant_email,string total_amount,
3 string firstname, string lastname, string city,string state, string phone, string address1,string address2, string zip, string customer_email,
4 string product_desc, string socialsecuritynum, string nameoncard, string creditcardnumber, Int32 cardverifynum, DateTime cardexpiredate,
5 string bankname, string routingnumber, string accountnumber, string checknumber, Int32 bqISN,
6 ref Int32 returnedbqISN, ref string AuthCode, ref string OrderNumber, ref string DeclineCode,
7 ref string TermCode, ref string ErrorMessage,
8 string authenticationValue,string authenticationTransactionID,string eci,string transactiontype,
9 string PAResStatus, string SignatureVerification, string paypalSubAgreeID, string notificationLocation,
10 ref string strErrorNo, ref string strErrorDesc, ref string strTransactionId, ref string strStatus,
11 ref string strStatusCode, ref string strReasonCode)
12
13 {
14
15 if (ccORach.Trim().Length <= 0)
16 {
17 ErrorMessage = "CC or Check?";
18 return false;
19 }
20
21 if (whichProcessor.Trim().Length <= 0)
22 {
23 ErrorMessage = "Blank processor";
24 return false;
25 }
26
27 decimal grand_total = Convert.ToDecimal(decimal.Parse(total_amount).ToString("N2"));
28 string ProcessorResponse="";
29 Database db = DatabaseFactory.CreateDatabase(strDBInstance);
30 DBCommandWrapper dbCmdWrapper = null;
31 bool retBilling = true;
32 string sql = "";
33 string bqAction = "SBILL";
34 string strFirstName = "";
35 string strLastName = "";
36 string[] cardname = nameoncard.Split(new char[] {' '});
37 strFirstName = cardname[0];
38 for (int i=1; i <= cardname.GetUpperBound(0); i++)
39 {
40 strLastName = strLastName + ' ' + cardname[i].Trim();
41 }
42
43 MCS_Encryption.Encryption mcscrypt = new MCS_Encryption.Encryption();
44 //card number
45 string enc_cardnumber = mcscrypt.Encrypt(creditcardnumber);
46 //account number
47 string enc_accountnumber = mcscrypt.Encrypt(accountnumber);
48
49 if (bqISN > 0)
50 {
51 //if bqISN is present, do UPDATE instead
52 sql = "spUpdateBillingQ";
53 dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);
54 dbCmdWrapper.AddInParameter("@bqISN",DbType.Int32,bqISN);
55 dbCmdWrapper.AddInParameter("@email",DbType.String,customer_email);
56 dbCmdWrapper.AddInParameter("@bankname",DbType.String,bankname);
57 dbCmdWrapper.AddInParameter("@routingnumber",DbType.String,routingnumber);
58 dbCmdWrapper.AddInParameter("@accountnumber",DbType.String,enc_accountnumber);
59 dbCmdWrapper.AddInParameter("@checknumber",DbType.String,checknumber);
60 dbCmdWrapper.AddInParameter("@nameoncard",DbType.String,nameoncard);
61 dbCmdWrapper.AddInParameter("@cardnumber",DbType.String,enc_cardnumber);
62 dbCmdWrapper.AddInParameter("@cardverifynum",DbType.Int32,cardverifynum);
63 dbCmdWrapper.AddInParameter("@cardexpirationdate",DbType.DateTime,cardexpiredate);
64 dbCmdWrapper.AddInParameter("@billaddress1",DbType.String,address1);
65 dbCmdWrapper.AddInParameter("@billaddress2",DbType.String,address2);
66 dbCmdWrapper.AddInParameter("@billcity",DbType.String,city);
67 dbCmdWrapper.AddInParameter("@billstate",DbType.String,state);
68 dbCmdWrapper.AddInParameter("@billzip",DbType.String,zip);
69 dbCmdWrapper.AddInParameter("@billphone",DbType.String, phone);
70 dbCmdWrapper.AddInParameter("@ip",DbType.String, ip);
71 dbCmdWrapper.AddInParameter("@fISN",DbType.Int32, fISN);
72 dbCmdWrapper.AddInParameter("@price",DbType.Currency, grand_total);
73 dbCmdWrapper.AddInParameter("@description",DbType.String, product_desc);
74 dbCmdWrapper.AddInParameter("@bqaction",DbType.String, bqAction);
75 dbCmdWrapper.AddInParameter("@creditamount",DbType.Currency, 0);
76 dbCmdWrapper.AddInParameter("@processor",DbType.String, whichProcessor);
77 db.ExecuteNonQuery(dbCmdWrapper) ;
78
79 returnedbqISN = bqISN;
80 }
81 else
82 {
83 //insert into billingQ or update if bqISN is passed
84 sql = "spInsertBillingQ";
85 dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);
86 dbCmdWrapper.AddInParameter("@lISN",DbType.Int32,lISN);
87 dbCmdWrapper.AddInParameter("@mISN",DbType.Int32,mISN);
88 dbCmdWrapper.AddInParameter("@email",DbType.String,customer_email);
89 dbCmdWrapper.AddInParameter("@bankname",DbType.String,bankname);
90 dbCmdWrapper.AddInParameter("@routingnumber",DbType.String,routingnumber);
91 dbCmdWrapper.AddInParameter("@accountnumber",DbType.String,enc_accountnumber);
92 dbCmdWrapper.AddInParameter("@checknumber",DbType.String,checknumber);
93 dbCmdWrapper.AddInParameter("@nameoncard",DbType.String,nameoncard);
94 dbCmdWrapper.AddInParameter("@cardnumber",DbType.String,enc_cardnumber);
95 dbCmdWrapper.AddInParameter("@cardverifynum",DbType.Int32,cardverifynum);
96 dbCmdWrapper.AddInParameter("@cardexpirationdate",DbType.DateTime,cardexpiredate);
97 dbCmdWrapper.AddInParameter("@billaddress1",DbType.String,address1);
98 dbCmdWrapper.AddInParameter("@billaddress2",DbType.String,address2);
99 dbCmdWrapper.AddInParameter("@billcity",DbType.String,city);
100 dbCmdWrapper.AddInParameter("@billstate",DbType.String,state);
101 dbCmdWrapper.AddInParameter("@billzip",DbType.String,zip);
102 dbCmdWrapper.AddInParameter("@billphone",DbType.String, phone);
103 dbCmdWrapper.AddInParameter("@ip",DbType.String, ip);
104 dbCmdWrapper.AddInParameter("@fISN",DbType.Int32, fISN);
105 dbCmdWrapper.AddInParameter("@price",DbType.Currency, grand_total);
106 dbCmdWrapper.AddInParameter("@description",DbType.String, product_desc);
107 dbCmdWrapper.AddInParameter("@bqaction",DbType.String, bqAction);
108 dbCmdWrapper.AddInParameter("@creditamount",DbType.Currency, 0);
109 dbCmdWrapper.AddInParameter("@processor",DbType.String, whichProcessor);
110 returnedbqISN = 0;
111 returnedbqISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;
112
113 }
114
115
116
117 //bill credit card or ACH
118 switch (ccORach.Trim())
119 {
120 case "CC":
121 retBilling = BillCreditCard(whichProcessor,ip,merchant_email,grand_total, strFirstName.Trim(), strLastName.Trim(),
122 city,state, phone, address1,address2, zip, customer_email,
123 product_desc, socialsecuritynum,creditcardnumber, cardverifynum, cardexpiredate,returnedbqISN,
124 ref AuthCode, ref OrderNumber, ref DeclineCode, ref TermCode, ref ErrorMessage, ref ProcessorResponse,
125 authenticationValue,authenticationTransactionID, eci, transactiontype, PAResStatus, SignatureVerification);
126 break;
127 case "ACH":
128 retBilling = BillACH(whichProcessor, ip,merchant_email, grand_total, firstname, lastname,
129 city,state, phone, address1,address2, zip, customer_email,
130 product_desc, socialsecuritynum,bankname, routingnumber, accountnumber, checknumber, returnedbqISN,
131 ref AuthCode, ref OrderNumber, ref DeclineCode, ref TermCode, ref ErrorMessage, ref ProcessorResponse);
132 break;
133 case "PAYPAL":
134 retBilling = BillPayPal(ip,merchant_email,grand_total,firstname,lastname,
135 city,state,phone,address1,address2,zip,customer_email,
136 product_desc,socialsecuritynum,returnedbqISN, paypalSubAgreeID, notificationLocation,
137 ref strErrorNo, ref strErrorDesc, ref strTransactionId, ref strStatus,
138 ref strStatusCode, ref strReasonCode);
139 break;
140 }
141
142 //insert into billingDetail
143 char bqRecurring = 'N';
144 sql = "spInsertBillingDetail2";
145 dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);
146
147 dbCmdWrapper.AddInParameter("@mISN",DbType.Int32,mISN);
148 dbCmdWrapper.AddInParameter("@bqISN",DbType.Int32,returnedbqISN);
149 dbCmdWrapper.AddInParameter("@bdamountcollected",DbType.Currency,grand_total);
150 dbCmdWrapper.AddInParameter("@bdtransactioncode",DbType.String,AuthCode);
151 dbCmdWrapper.AddInParameter("@bdstatus",DbType.Boolean,retBilling);
152 dbCmdWrapper.AddInParameter("@bdauthcode",DbType.String,AuthCode);
153 dbCmdWrapper.AddInParameter("@bdordernumber",DbType.String,OrderNumber);
154 dbCmdWrapper.AddInParameter("@bdtracecode",DbType.String,returnedbqISN);
155 dbCmdWrapper.AddInParameter("@email",DbType.String,customer_email);
156 dbCmdWrapper.AddInParameter("@bankname",DbType.String,bankname);
157 dbCmdWrapper.AddInParameter("@routingnumber",DbType.String,routingnumber);
158 dbCmdWrapper.AddInParameter("@accountnumber",DbType.String,enc_accountnumber);
159 dbCmdWrapper.AddInParameter("@checknumber",DbType.String,checknumber);
160 dbCmdWrapper.AddInParameter("@nameoncard",DbType.String,nameoncard);
161 dbCmdWrapper.AddInParameter("@cardnumber",DbType.String,enc_cardnumber);
162 dbCmdWrapper.AddInParameter("@cardverifynum",DbType.Int32,cardverifynum);
163 dbCmdWrapper.AddInParameter("@cardexpirationdate",DbType.DateTime,cardexpiredate);
164 dbCmdWrapper.AddInParameter("@billaddress1",DbType.String,address1);
165 dbCmdWrapper.AddInParameter("@billaddress2",DbType.String,address2);
166 dbCmdWrapper.AddInParameter("@billcity",DbType.String,city);
167 dbCmdWrapper.AddInParameter("@billstate",DbType.String,state);
168 dbCmdWrapper.AddInParameter("@billzip",DbType.String,zip);
169 dbCmdWrapper.AddInParameter("@billphone",DbType.String, phone);
170 dbCmdWrapper.AddInParameter("@ip",DbType.String, ip);
171 dbCmdWrapper.AddInParameter("@bqrecurring",DbType.String, bqRecurring);
172 dbCmdWrapper.AddInParameter("@Message",DbType.String, ErrorMessage);
173 dbCmdWrapper.AddInParameter("@ProcessorResponse",DbType.String, ProcessorResponse);
174 dbCmdWrapper.AddInParameter("@processor",DbType.String, whichProcessor);
175
176 dbCmdWrapper.AddInParameter("@strErrorNo",DbType.String, strErrorNo);
177 dbCmdWrapper.AddInParameter("@strErrorDesc",DbType.String, strErrorDesc);
178 dbCmdWrapper.AddInParameter("@strTransactionId",DbType.String, strTransactionId);
179 dbCmdWrapper.AddInParameter("@strStatus",DbType.String, strStatus);
180 dbCmdWrapper.AddInParameter("@strStatusCode",DbType.String, strStatusCode);
181 dbCmdWrapper.AddInParameter("@strReasonCode",DbType.String, strReasonCode);
182
183
184 Int32 returnedbdISN = 0;
185 returnedbdISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;
186 if (retBilling)
187 {
188 if (UpdateBillingQStatuses(strDBInstance,returnedbqISN,'S'))
189 {
190 return true;
191 }
192 }
193 else
194 {
195 if (UpdateBillingQStatuses(strDBInstance,returnedbqISN,'F'))
196 {
197 return false;
198 }
199 }
200 return false;
201
202 }

 
 
 

View Replies !

---

I Am Getting This Message &"System.Data.SqlClient.SqlException: Xp_sendmail: Procedure Expects Parameter @user, Which Was Not Supplied.&"
I have looked all over my code and can not find anywhere that I am referencing the xp_sendmail procedure! Here is all the code<code>With sqlCmdUpdateParticipants
.Parameters("@ClassID").Value = ddlClass.SelectedItem.Value
.Parameters("@Person").Value = tbName.Text()
End With
cnCapMaster.Open()
sqlCmdUpdateParticipants.ExecuteNonQuery()
cnCapMaster.Close()</code>I am just getting a couple values and and inserting them into the database.  the insert works then I get darn error message.  This code worked at one time but it has been about 2 years sense I worked on it so who knows what might have happened sense then.Thanks,Bryan

View Replies !

---

Stored Procedures, Error Says &"expects Parameter @Lname, Which Was Not Supplied.
this error comes up when I run a form to supmit information to the DB. .
 
"Procedure or function 'aspnet_Users_CreateUser' expects parameter '@Lname', which was not supplied"
I have it declared in the procedure. so why is it telling me it was not supplied.
Here is what I have:
 
ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
    @ApplicationId   uniqueidentifier,
    @UserName   nvarchar(256),
    @IsUserAnonymous  bit,
    @LastActivityDate DATETIME,
  
    @Fname varchar(50) OUTPUT,
    @Lname  nvarchar(50) OUTPUT,
    @Address nvarchar(50) OUTPUT,
    @City  varchar(50) OUTPUT,
    @State  nchar(2) OUTPUT,
    @Zip  nchar(5) OUTPUT,
@RefNum  nchar(10) OUTPUT,@UserId  uniqueidentifier OUTPUTASBEGIN     IF( @UserId IS NULL )        SELECT @UserId = NEWID()    ELSE    BEGIN        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users                    WHERE @UserId = UserId ) )            RETURN -1    ENDINSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate, Fname, Lname, Address, City, State, Zip, RefNum)    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate, @Fname, @Lname, @Address, @City, @State, @Zip, @RefNum)     RETURN 0END
Thanks in advance

View Replies !

---

Parameter Doesn't Get Supplied To Stored Procedure
I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@startWeek and @endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!

View Replies !

---

Stored Procedure Parameter Supplied But Error Messages Says It's Not
I keep getting this error on one of my pages when trying to do an update to the table via a stored procedure:
*System.Data.SqlClient.SqlException: Procedure or function 'sp_u_contractor_references' expects parameter '@p_contractorrfid', which was not supplied
 I have verified that I am adding this parameter and that the spelling is correct. I have also made sure that the value is not NULL.
 I'm confused as to what else the problem could be. Any ideas?

View Replies !

---

Receive &&"Procedure Or Function Expects Parameter...&&" Error Unexpectedly
Maybe I am missing something completely obvious but as far as I can tell I aren't!
 
I am trying to simply pass a parameter to a stored proc to return results. Easy? So I thought so too...
 
This is my stored procedure below (in it's ALTER PROCEDURE form):
 

ALTER PROCEDURE [dbo].[spd_Tester]

-- Add the parameters for the stored procedure here

@Check int

AS

BEGIN

SELECT * FROM tblPerson

WHERE PersonID = @Check

END

 
Now I know this is a SQL Server forum but I figure anyone who reads this probably does some application programming as well, so here is the code that calls this.  It's in VBA using Access 2003.  I figure the error is being returned from SQL Server Express so that's why I have come to this forum instead of an Access forum! I could be wrong though...
 
Here is the code that calls this:
 
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
   
    Set cmd = New ADODB.Command
   
    'Run sql command
    With cmd
        .ActiveConnection = CurrentProject.Connection

        .CommandText = "dbo.spd_Tester"
        .Parameters.Append .CreateParameter("@Check", adInteger, adParamInput, , Me.txtFirstName)

        
        'Execute, return recordset
        Set rs = .Execute
       
    End With
 
Now I figure this should work.  However, I get the error "Procedure or function 'spd_Tester' expects parameter '@Check', which was not supplied." when I execute the procedure with the number 1 in the txtFirstName text box.
 
Can someone tell me what I am doing wrong?? The tblPerson table has data in it, if I run the select from a query window, specifying a value for @Check, it works just fine.
 
Thanks in advance!

View Replies !

---

Expects Parameter Which Was Not Supplied....need Help....
I wanted to add new record into two different tables. By using one web form...
Not so sure I coded it correctly. And I got this error trying to insert new record

" Procedure 'sp_insert' expects parameter '@mid', which was not supplied. "

Isn't it i have include @mid in my code ...??


<STORED PROCEDURES>
sp_insert

(
@mid char(10),
@status char(10),
@name char(20),
@dj char(20),
@rank char(20),
@nric char(14),
@dob char(20),
@age char(10),
@add char(80),
@school char(50),
@hp char(10),
@mp char(10),
@email char(30),
@pgname char(20),
@relationship char(20),
@contact char(10)

)
AS
insert into Table1(mid,status,name,date_joined,rank,nric,dob,age,address,school,house_phone,Mobile_phone,email,[P/G_name],[P/G_relationship],[P/G_contacts])
values (@mid,@status,@name,@dj,@rank,@nric,@dob,@age,@add,@school,@hp,@mp,@email,@pgname,@relationship,@contact)

insert into Table2 (name) values (@name)

RETURN

<WEB FORM>
mycommand = New SqlCommand("sp_insert", mycon)
mycommand.CommandType = CommandType.StoredProcedure

Dim midpar As New SqlParameter("@mid", SqlDbType.Char, 10)
midpar.Direction = ParameterDirection.Input
midpar.Value = Tb1.Text

Dim statuspar As New SqlParameter("@status", SqlDbType.Char, 10)
statuspar.Direction = ParameterDirection.Input
statuspar.Value = ddl1.SelectedItem.Text

---------
---------
---------

View Replies !

---

Sp_tblSharedUser_INS' Expects Parameter '@Ids1', Which Was Not Supplied
I've spent many days analyzing this error (sp_tblSharedUser_INS' expects parameter '@Ids1', which was not supplied) and trying to figure it out. If anyone out there can help me, I will be forever grateful.
STORED PROCEDURE
ALTER PROCEDURE sp_tblSharedUser_INS
(@MyId [VarChar](25),
@CreatedBy int,
@DependantIds [VarChar](255),
@Ids1 varchar(255) output
)
AS

Declare @Ids as varchar(255)
declare @separator_position as int
declare @array_value as varchar(20)
declare @Count as int
declare @MaxValue as int
set @Ids=@DependantIds + ','
set @Ids1='0'
set @MaxValue=5
while patindex('%' + ',' + '%' , @Ids) <> 0
begin
select @separator_position = patindex('%' + ',' + '%' , @Ids)
select @array_value = left(@Ids, @separator_position - 1)

select @Count=Count(SharedUserId) from tblSharedUser where DependantIds LIKE ('%,' + @array_value + ',%')



if( @Count< @MaxValue)
begin
set @Ids1 =@Ids1 + ',' + @array_value

end


select @Ids = stuff(@Ids, 1, @separator_position, '')
end
IF NOT @Ids1='0' or @Ids1='0,0'
begin
if not exists(select MyId from tblSharedUser where MyId=@MyId and CreatedBy=@CreatedBy and DependantIds=@DependantIds )
begin

INSERT INTO tblSharedUser
([CreatedBy],
[DependantIds],
[MyId]
)
VALUES(
@CreatedBy,
@Ids1,
@MyId)
RETURN @@identity
end
else
begin
return 0
end
end

else
begin
return -1
end
CODE
insertCommand = New SqlCommand("sp_tblSharedUser_INS", New SqlConnection(Config.cnnstr))
insertCommand.CommandType = CommandType.StoredProcedure
With insertCommand.Parameters
.Add(New SqlParameter(MYID_PARAM, SqlDbType.VarChar))
.Item(MYID_PARAM).SourceColumn = dsSharedUser.MYID_FIELD
.Add(New SqlParameter(CREATEDBY_PARAM, SqlDbType.BigInt))
.Item(CREATEDBY_PARAM).SourceColumn = dsSharedUser.CREATEDBY_FIELD
.Add(New SqlParameter(DEPENDANTIDS_PARAM, SqlDbType.VarChar))
.Item(DEPENDANTIDS_PARAM).SourceColumn = dsSharedUser.DEPENDANTIDS_FIELD
.Add(New SqlParameter("@lngReturn", SqlDbType.Int))
.Item("@lngReturn").Direction = ParameterDirection.ReturnValue
.Add(New SqlParameter("@Ids1", SqlDbType.VarChar, 255))
.Item("@Ids1").Direction = ParameterDirection.Output
End With
dsCommand.InsertCommand = insertCommand
dsCommand.Update(dsSharedUser, dsSharedUser.SHAREDUSER_TABLE)
lngReturn = CType(dsCommand.InsertCommand.Parameters.Item("@lngReturn").Value, Int32)
Ids = CStr(dsCommand.InsertCommand.Parameters.Item("@Ids1").Value)

View Replies !

---

'sp_procedure_params_rowset' Expects Parameter '@procedure_name', Which Was Not Supplied.
Hi,
I am facing a new problem with asp.net. I am not clear whether this problem is by asp.net or by sql server.
Actually the error is in the following line of sqlhelper class - data application block.SqlCommandBuilder.DeriveParameters(cmd);
Procedure 'sp_procedure_params_rowset' expects parameter '@procedure_name', which was not supplied.
But the above line is executing only when I bind the dataset to datalist.
 I think asp.net is internally calling this stored procedure while using this statement.
DListMaster.DataBind();

If I command the above line I am not getting any error. And I am not getting this error in all the systems. 
Please any one help me. Any help could be appreciated.

View Replies !

---

SqlException - Error Expects Parameter HostID; Which Was Not Supplied
Why am I getting this error, what does it mean and how can I fix it. Here is my stored proceduce and method to call it.
ALTER PROCEDURE dbo.hnDeleteHost(@pHostID varchar(50), @pRet int out)AS
SELECT Host FROM HostName WHERE HostNameID = @pHostID
if (@@ROWCOUNT > 0) begin DELETE FROM HostName WHERE HostNameID = @pHostID SET @pRet = @@ROWCOUNTendelse begin SET @pRet = -1end
 And the method
using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))            {
                cn.Open();                SqlCommand cmd = new SqlCommand("hnDeleteHost", cn);                cmd.CommandType = CommandType.StoredProcedure;                cmd.Parameters.Add(new SqlParameter("@pHostID", SqlDbType.VarChar, 50, "HostNameID"));                cmd.UpdatedRowSource = UpdateRowSource.None;                cmd.ExecuteNonQuery();            }

View Replies !

---

Prepared Statement ......... Expects Parameter @CORP_NAME, Which Was Not Supplied
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied
I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me...
 private void Submit1_ServerClick(object sender, System.EventArgs e)
{

erkaner = Page.Session.Contents["CORP_ID"].ToString();
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox32.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox27.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TC_NO", System.Data.SqlDbType.VarChar, 50, TextBox28.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ID", System.Data.SqlDbType.Int, 4, erkaner));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_EMAIL", System.Data.SqlDbType.VarChar, 50, TextBox29.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TEL", System.Data.SqlDbType.VarChar, 50, TextBox30.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_DUTY", System.Data.SqlDbType.VarChar, 150, TextBox31.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FOUND_YEAR", System.Data.SqlDbType.VarChar, 50, TextBox33.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COUNTRY", System.Data.SqlDbType.VarChar, 50, DropDownListUlke.SelectedValue.ToString()));
 if (DropDownListUlke.SelectedIndex == 148)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, DropdownlistSehir.SelectedValue.ToString()));else if(DropDownListUlke.SelectedIndex != 0 && DropDownListUlke.SelectedIndex != 148)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, TextBox1.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SECTOR", System.Data.SqlDbType.VarChar, 150, RadioButtonList6.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMP_NUM", System.Data.SqlDbType.VarChar, 50, RadioButtonList1.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ACT", System.Data.SqlDbType.VarChar, 150, DropDownList3.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PC_NUM", System.Data.SqlDbType.VarChar, 50, TextBox35.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_USER_NUM", System.Data.SqlDbType.VarChar, 50, "INT_USER_NUM"));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_LAN", System.Data.SqlDbType.VarChar, 50, RadioButtonList3.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_SERVER", System.Data.SqlDbType.VarChar, 50, RadioButtonList4.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ECOM_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox37.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OT_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox38.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_CON_TYPE", System.Data.SqlDbType.VarChar, 50, RadioButtonList5.SelectedValue.ToString()));
 
 if (CheckBox1.Checked)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "Ticari Uygulamalar (B2B,B2C,e-iş,e-ihracat,…)"));else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, ""));
 
if (CheckBox2.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "Yönetişim Uygulamaları (Muhasebe, stok, satış, kalite, raporlama,denetim...)"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox3.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "ERP Uygulamalari(Unity, SAP, JDE, BAAN, QAD vb.)"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox4.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "Müşteri İlişkileri Yönetimi (CRM) Uygulamaları"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox5.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "Bilgisayar Destekli Tasarım / Bilgisayar Destekli Üretim (CAD/CAM) Uygulamaları"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox6.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "Veri Ambarı ve Veri Madenciliği Uygulamaları "));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox7.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, TextBox36.Text));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, ""));
sqlUpdateCommand1.CommandText = "UPDATE dbo.CORP_TBL SET REP_NAME = @REP_NAME, CORP_NAME = @CORP_NAME, REP_TC_NO =" +
" @REP_TC_NO, REP_EMAIL = @REP_EMAIL, REP_TEL = @REP_TEL, REP_DUTY = @REP_DUTY, F" +
"OUND_YEAR = @FOUND_YEAR, COUNTRY = @COUNTRY, CITY = @CITY, SECTOR = @SECTOR, EMP" +
"_NUM = @EMP_NUM, CORP_ACT = @CORP_ACT, PC_NUM = @PC_NUM, INT_USER_NUM = @INT_USE" +
"R_NUM, HAVE_LAN = @HAVE_LAN, HAVE_SERVER = @HAVE_SERVER, ECOM_SER_NUM = @ECOM_SE" +
"R_NUM, OT_SER_NUM = @OT_SER_NUM, INT_CON_TYPE = @INT_CON_TYPE, APPLICATION1 = @A" +
"PPLICATION1, APPLICATION2 = @APPLICATION2, APPLICATION3 = @APPLICATION3, APPLICA" +
"TION4 = @APPLICATION4, APPLICATION5 = @APPLICATION5, APPLICATION6 = @APPLICATION" +
"6, APPLICATION7 = @APPLICATION7 WHERE CORP_ID = @CORP_ID";
sqlUpdateCommand1.Connection = sqlConnection1;
sqlUpdateCommand1.Connection.Open();
sqlUpdateCommand1.ExecuteNonQuery(); //This line gives the errorstring mySqlQuery = "UPDATE CORP_TBL SET FLAG = 1 WHERE CORP_ID=" + Page.Session.Contents["CORP_ID"].ToString();SqlCommand myCommand = new SqlCommand(mySqlQuery, sqlConnection1);
myCommand.ExecuteNonQuery();
Page.Response.Redirect("main.aspx");
}

View Replies !

---

Procedure Expects Parameter Error When Adding To DB
Hi,I'm trying to create a page where a user can search the database according to some criteria and get back the result in the form of a GridView. Also, the user has the option of saving the criteria to another table in the database by assigning it a name so that it can be retrieved easily in the future.I have the search and display part working, however, saving the criteria to the database is giving problems for some reason.Given below is my stored procedure to add the info to the db.  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToReport]
(@ReportName varchar(100), @ProjID varchar(300), @DeptCode varchar(20), @ProjType varchar(20), @ProjectTitle varchar(300),
@ProjectManagerID int, @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)

AS
SET NOCOUNT ON
DECLARE @Dept varchar(50)
DECLARE @err int

BEGIN TRANSACTION

IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
IF @DeptCode IS NOT NULL
BEGIN
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
END
SET @err = @@ERROR


INSERT INTO dbo.tbl_Report (ReportName, ProjID, DeptCode, ProjType, ProjectTitle, ProjectManagerID, DateRequested, DueDate, ProjectStatusID)
VALUES (@ReportName, @ProjID, @Dept, @ProjType, @ProjectTitle, @ProjectManagerID, @DateRequested, @DueDate, @ProjectStatusID);
IF @err<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @err
END
END
COMMIT TRANSACTION Given below is the relevant codebehind.  This is how the values are initialized:   Dim newManager As New ListItem
newManager.Text = "Choose a Manager"
newManager.Value = 0
projectManagerDDL.Items.Add(newManager)

Dim newDept As New ListItem
newDept.Text = "Choose a Department"
newDept.Value = ""
deptCodeDDL.Items.Add(newDept)

Dim newID As New ListItem
newID.Text = "Choose a Project"
newID.Value = ""
projIDDDL.Items.Add(newID)

Dim newStatus As New ListItem
newStatus.Text = "Choose a Status"
newStatus.Value = 0
projectStatusDDL.Items.Add(newStatus)

Dim newDateRequestedMonth As New ListItem
newDateRequestedMonth.Text = "Month"
newDateRequestedMonth.Value = 0
dateRequestedMonthDDL.Items.Add(newDateRequestedMonth)

Dim newDateRequestedDay As New ListItem
newDateRequestedDay.Text = "Day"
newDateRequestedDay.Value = 0
dateRequestedDayDDL.Items.Add(newDateRequestedDay)

Dim newDateRequestedYear As New ListItem
newDateRequestedYear.Text = "Year"
newDateRequestedYear.Value = 0
dateRequestedYearDDL.Items.Add(newDateRequestedYear)

Dim newDueDateMonth As New ListItem
newDueDateMonth.Text = "Month"
newDueDateMonth.Value = 0
dueDateMonthDDL.Items.Add(newDueDateMonth)

Dim newDueDateDay As New ListItem
newDueDateDay.Text = "Day"
newDueDateDay.Value = 0
dueDateDayDDL.Items.Add(newDueDateDay)

Dim newDueDateYear As New ListItem
newDueDateYear.Text = "Year"
newDueDateYear.Value = 0
dueDateYearDDL.Items.Add(newDueDateYear) This is the submit code:  Protected Sub saveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connString As String
Dim con As SqlConnection


Try
connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

con = New SqlConnection(connString)

Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con

If (([String].IsNullOrEmpty(reportNameTextBox.Text) = False) Or reportNameTextBox.Text <> "Enter Report Name") Then
cmd.Parameters.Add("ReportName", SqlDbType.VarChar, 300).Value = reportNameTextBox.Text
End If

If ([String].IsNullOrEmpty(projIDDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("ProjID", SqlDbType.VarChar, 30).Value = projIDDDL.SelectedItem.Value
End If

If ([String].IsNullOrEmpty(deptCodeDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("DeptCode", SqlDbType.VarChar, 20).Value = deptCodeDDL.SelectedItem.Value
End If

If (typeRBL.SelectedItem.Value <> "All") Then
cmd.Parameters.Add("ProjType", SqlDbType.VarChar, 20).Value = typeRBL.SelectedItem.Value
End If

If ([String].IsNullOrEmpty(projectTitleTextBox.Text)) = False Then
cmd.Parameters.Add("ProjectTitle", SqlDbType.VarChar, 300).Value = projectTitleTextBox.Text
End If

If CInt(projectManagerDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectManagerID", SqlDbType.Int).Value = CInt(projectManagerDDL.SelectedItem.Value)
End If

If (dateRequestedDayDDL.SelectedItem.Value = 0 Or dateRequestedMonthDDL.SelectedItem.Value = 0 Or dateRequestedYearDDL.SelectedItem.Value = 0) Then
Dim dateRequested As New DateTime
dateRequested = Nothing
Else
Dim dateRequested As New DateTime(dateRequestedYearDDL.SelectedValue, dateRequestedMonthDDL.SelectedValue, dateRequestedDayDDL.SelectedValue)
If (dateRequested) <> Nothing Then
cmd.Parameters.Add("DateRequested", SqlDbType.DateTime).Value = dateRequested
End If
End If


If (dueDateDayDDL.SelectedItem.Value = 0 Or dueDateMonthDDL.SelectedItem.Value = 0 Or dueDateYearDDL.SelectedItem.Value = 0) Then
Dim dueDate As New DateTime
dueDate = Nothing
Else
Dim dueDate As New DateTime(dueDateYearDDL.SelectedValue, dueDateMonthDDL.SelectedValue, dueDateDayDDL.SelectedValue)
If (dueDate) <> Nothing Then
cmd.Parameters.Add("DueDate", SqlDbType.DateTime).Value = dueDate
End If
End If

If (projectStatusDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectStatusID", SqlDbType.Int).Value = CInt(projectStatusDDL.SelectedItem.Value)
End If

cmd.CommandText = "dbo.AddToReport"
cmd.CommandType = CommandType.StoredProcedure

Try
con.Open()
cmd.ExecuteNonQuery()
Response.Write("Report Saved")
Catch ex As Exception
Response.Write(ex)
Finally
con.Close()
con.Dispose()

End Try



Catch ex As ApplicationException
Response.Write("Could not load the database")
End Try

End Sub   The only absolute requirement when saving to the table is the ReportName. All the other criteria can be NULL. If I don't select and values and try to save the values, I get an error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@ProjID', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError...  etc If I choose the ProjID (thus giving it a value), I get the following error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@DeptCode', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc  and so forth. I'm guessing it's a problem of NULLs somewhere, but I'm not sure. Thanks. 

View Replies !

---

Error: Procedure Expectes Parameter '###', Which Was Not Supplied
I created the SP ( just called' SP_TEST') and when I tried to preview the report, I got the following error message:
 
"Procedure or fucntion 'SP_TEST' expects parameter @SelectWeek, which was not supplied.
 
This error message appears when you don't supply a value for a parameter expected by a stored procedure.
However, I set up the report parameter for Select Week and default week as well.
Here is what i've done for dataset,Select Week and parameter:
 



Code Snippet
SELECT DISTINCT  Week , 
( SELECT DISTINCT   Week  FROM DIM_Date
  WHERE    full_date = CONVERT(VARCHAR(20),GETDATE(),112) ) AS defaultWeek
FROM  DIM_Date
WHERE   fiscal_year_number IS NOT NULL and  @year = fiscal_year_number
ORDER BY  Week
 
 



Any comments and advice would be greatly appreciated.
Thanks in advance.

View Replies !

---

Using A Function To Pass A Parameter To A Stored Procedure
In the snippet below,  ExecuteSqlString is a stored procedure that accepts one parameter.  SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass.  I can't seem to find the correct syntax to get it to work.  The way it is show below, the error comes back about incorrect syntax near ')' .  Is this doable? 
<asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"
DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>"
SelectCommandType=StoredProcedure
SelectCommand="ExecuteSqlString">
<selectparameters>
<asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/>
</selectparameters>
</asp:SqlDataSource>

View Replies !

---

System.Data.SqlClient.SqlException: Procedure Expects Parameter
I have a stored procedure to validate the data from the table
CREATE PROCEDURE [dbo].[proc_Validate_BLDG]
@BLDGData varchar(50),@output int OUTAS
BeginIf Exists (Select DISTINCT  GBC FROM ServerName.Database.dbo.Table where GBC = @BLDGData) begin set @output = 1  end
Else begin set @output = 0  end
return @output
endGO
Also, I have a function in my asp.net pages to call the stored procedure
Public Function BLDGLookup() As Integer
'Create a connectionDim objConn As SqlConnection
objConn = GetConnection()
'Create a command object for the query Dim objCommand As New SqlCommand("proc_Validate_BLDG", objConn)
objCommand.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter = Nothing
Dim output As IntegerobjCommand.Parameters.AddWithValue("@BLDGData", strBLDGTextBox)param = objCommand.Parameters.Add("@output", SqlDbType.Int)
param.Direction = ParameterDirection.Output
Try
objCommand.ExecuteNonQuery()output = Int32.Parse(objCommand.Parameters("@output").Value.ToString())
Catch ex As ExceptionDim ex2 As Exception = ex
Dim errorMessage As String = String.EmptyWhile Not (ex2 Is Nothing)
errorMessage += ex2.ToString()
ex2 = ex2.InnerException
End While
Response.Write(errorMessage)
Finally
End TryReturn output
objConn.Close()
End Function
 However, when i put in code to call this function and return a value I am getting thsi error:
System.Data.SqlClient.SqlException: Procedure 'proc_Validate_BLDG' expects parameter '@BLDGData', which was not supplied.

View Replies !

---

ERROR: Procedure Expects Parameter '@statement' Of Type 'ntext/nchar/nvarchar'.
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */
DECLARE @X VARCHAR(10)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Num_Members SMALLINT
SELECT @X = 'x.dbo.v_NumberofMembers'
DECLARE @SQLString AS VARCHAR(500)

SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB'
SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT'


EXECUTE sp_executesql <-LINE 11
@SQLString,
@ParmDefinition,
@DB = @X,
@Num_MembersOUT = @Num_Members OUTPUT


Just Need Help On This Error
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly

View Replies !

---

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View Replies !

---

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
 Hi everybody,   I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End


oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">&quot;@CatId&quot;</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); &lt;&lt;--- Error occured here


 

View Replies !

---

The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub

View Replies !

---

Calling A Stored Procedure Or Function From Another Stored Procedure
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 Replies !

---

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !

---

Stored Procedure With User!UserID As Parameter, As Report Parameter?
I had thought that this was possible but I can't seem to figure out the syntax.  Essentially I have a report where one of the parameters is populated by a stored procedure.

Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter.  However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure.  Is it? Thanks

View Replies !

---

Function Vs Stored Procedure
I know this is a stupid question (actually, maybe its not..?)

They seem to be identical in some ways, but not available to the outside world. what are some differences?

View Replies !

---

When To Use A Function And When To Use A Stored Procedure.
Hi,

I thought that procedures do not return a value: well they only return a 1 or 0 depending on whether the procedure executed correctly. Functions are what you're supposed to use when you want to get a result set of some sort: ie a table or a scalar value.

Apparently that is not the case becuase you can return values from procedures. I've attempted to find out what the differences are between the two and when it is appropriate to use a procedure and when it is appropriate to use a function but I'm still unsure. Can someone please tell me the difference.

Thanks.

Przemek

View Replies !

---

Function From Stored Procedure
Hi Everyone,

i want to create a function in Stored procedure and then call this function
that returns true or false from a stored procedure .
is this possible?

please help me if you know something that can help me.

thanks

Tvin

View Replies !

---

Stored Procedure Not Getting A Parameter
I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure.
When I run the update from the app the error is that the procedure is not getting the @Phrase parameter.
 Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]

@paymentID int,
@PaymentDate datetime,
@TenderTypeID int,
@PaymentAmount money,
@CreditCardType int,
@ExpirationDate datetime,
@ccNumber nvarchar(50),
@Phrase nvarchar(25)

AS
BEGIN
SET NOCOUNT ON;
SET @Phrase = (SELECT Phrase FROM tblSpecialStuff)

UPDATE tblReceipts SET

PaymentDate=@PaymentDate,
TenderTypeID=@TenderTypeID,
AmountPaid=@PaymentAmount,
CreditCardType=@CreditCardType,
ExpirationDate=@ExpirationDate,
ccNumber = (CASE
WHEN @CreditCardType > 0 THEN
EncryptByPassPhrase(@Phrase,@ccNumber)
ELSE @ccNumber
END)
END 

View Replies !

---

Parameter For Stored Procedure
HiI'm trying to alter my stored procedure to take a parameter for theDatabase Name, but as usual the syntax is killing me.Thanks for any helpDennis'--------------------------------------------------------------------------*--------------------------------Before - This Works without a paramater'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE [MyClientDatabase] ON PRIMARY( NAME = N'MyClientDatabase',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'MyClientDatabase_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--------------------------------------------------------------------------*--------------------------------After - This Doesn't work with a parameter'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE @ClientDBName ON PRIMARY( NAME = N@ClientDBName,FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'@ClientDBName' + '_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASMsg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4Incorrect syntax near '@ClientDBName'.

View Replies !

---

Stored Procedure Parameter
In the Create portion of a stored procedure, does the line in bold mean
that if the parameter @RegoDate is not provided, then use the current datetime?

CREATE PROCEDURE spGetRegoDetail
@Owner nvarchar(20),
@RegoDate datetime = getdate
AS ...

Thanks in advance,
G11DB

View Replies !

---

Stored Procedure Parameter
Hi,

I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ).

I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...)

I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones )

I'm really not sure as to how to accomplish this... Any ideas?

thanks!

View Replies !

---

Parameter To Stored Procedure
Hello!

I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I don´t know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this

SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... )

How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work)

CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar)
AS
BEGIN
SELECT Customer
FROM Customer
WHERE CustomerId IN (@sCustomerId)
END

/Fredrik

View Replies !

---

Using A Parameter In A Stored Procedure
hi,

i need to run a stored procedure from an asp page that will import data into a temporary table.

i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'.

I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>'

this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter.

can anyone help please?

TIA

View Replies !

---

Stored Procedure With A Parameter
Hi all,
 
I'm trying to create a SP with a parameter for server.

 
 
CREATE PROCEDURE dbo.sp_Testing
@server         ???????

 

AS

SELECT * from @server

GO

 
Does anyone know how to do what i'm trying here..???
I don't know the type of the parameter...is it just simply server? or is there another specific name for it???
thanks,

View Replies !

---

Calling A Function From A Stored Procedure
Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions.  My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing.  Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool.  The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor).  So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT *
FROM UDFmanufacturer_SearchName('mol')  SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
 I'm stuck.  I haven't been able to find anything that shows me where I'm going wrong.  Any thoughts or suggestions are appreciated. Thanks,Jay

View Replies !

---