Procedure Or Function 'selectFromView' Expects Parameter '@Year', Which Was Not Supplied. ???
May 21, 2008
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 ?
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
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
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)
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...
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; }
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help!
When i open any reports getting the below error message.An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
(rsReportServerDatabaseError)Procedure or function 'CreateSession' expects parameter '@SiteZone', which was not supplied.
I have a report that uses a stored procedure as a dataset.The stored procedure accepts four parameters, which I have defined in the report. works fine in query builder and SSMS.
When I run the report I get an error stating a parameter is missing. I tried deleting parameters and recreating not luck.
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
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:
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
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
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
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.
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.
<<<<<<<<<<<<<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.
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();
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(); }
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
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
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
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. "
) 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
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)
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) {
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(); }
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
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.
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:
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
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
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
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.
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.
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.
