I'm kind of new at doing something like this. I'm trying to pull data into a results that exists in one of two databases. It works fine with query analyzer
but gives me the error "has no parameters and arguments were supplied" when I try to convert it to a stored procedure.
The procedure is as follows, any help would be appreciated.
CREATE PROCEDURE sp_getInvoiceNoTest AS
declare @InVoiceNo as VarChar(30)
delete From Invoice_NBR
Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)
select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.Acuity_App.dbo.tarInvoice as a
inner join
Colucw17.Acuity_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.Acuity_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.Acuity_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.Acuity_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@InvoiceNo'
--Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)
select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.CSM_App.dbo.tarInvoice as a
inner join
Colucw17.CSM_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.CSM_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.CSM_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.CSM_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@InvoiceNo'
GO
I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in. I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file Function GetCategoryName(ByVal ID As Integer) As String sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3 sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function ERROR AS FOLLOWS argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
Help I have not got much more hair to loose Thanks Steve
I would like to set up cascading report parameters but need to supply the list of values in my dataset query as they are not being pulled from a table or stored procedure.
I would like to set up something like
select 'LS' Source Union select 'RH' Source Union select 'UK' Source
as the value list for my first parameter, then, I would like the second parameter to default to '1' if the first parameter has 'RH' or 'UK' selected but default to blank if the first parameter has 'LS' selected. I can get all to default to blank or 1 but can not seem to make it dependent.
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
Hi,I'm trying to store values from a session to sql server table. I created an sql stored procedure to pass the values into the database. For some reason when I'm running the website and trying to store value from session to database an error is being displayed with the following message "Procedure or function OrderFromCart has too many arguments specified". OrderFromCart is the name of the stored procedure. Any Ideas??ThanksInter FC
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:
I have created a stored procedure that will back up a database to a file then restore the database into a new db. The stored procedure accepts 2 parameters that determine the name of the old DB to be backed up and the name of the new DB to be created. The only problem now is that when you specify the name of a database that already eixists, the restore fails. I want to add an if statement that would check to see if the new db supplied exists, and if it does drop it before running the restore.
I thought the following would work but it does not
Code Block declare @DB nvarchar (50) set @DB = 'LDRPSsmap2k'
IF EXISTS (SELECT name FROM sys.databases WHERE name = @db) Drop Database @db
I am running a SQL2K on a Server2K box. I have linked servers to SQL2005 Express on WinXP. I am trying to get data off of the SQL2005 DB to the central DB on SQL2K. This has been running for about a year. I am now getting an error message as follows:
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time. OLE DB error trace [Non-interface error: Column 'TestVarChar' (compile-time ordinal 2) of object '"WKS"."DBo"."testtable"' was reported to have changed. The exact nature of the change is unknown].
When I run the following Queries on the SQL2K box: SELECT * from WKSA400.WKS.DBo.testtable
or
SELECT * from OPENQUERY(WKSA400, 'SELECT * from WKS.DBO.testtable')
I did notice that if my testtable has only numeric fields, it works fine. It only has a problem with alphanumeric fields.
If I run 'exec sp_tables_ex WKSA400' on the SQL2K, it works fine. I get a listing of all the tables on the remote server. This tells me that my security is fine. It seems to be a conversion from the 2005 to 2000 SQL. If it had not been working for over a year, the dent in the wall the size of my head would be much smaller.
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
In Oracle we can use the arguments for the Cursor, it's very useful. Not sure how to do it in MS SQL Server 7.0.
For example, in SQL server we use the following cursor in a stored procedure,
select @intID =10 DECLARE cur_test CURSOR FOR select object_id, object_name from objects where object_id = @intID
OPEN .... FETCH .... ....
How about I need to change the @intID for the cursor based on logical condition in the code after a while?? Am I supposed to close the cursor cur_test and re-declare it?? Is there any way to make the code neat and short?
Any idea/help would be greatly appreciated! Dana Jian
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)
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.
I am try to build a Stored procedure to insert into a table whose name is contain in variable @tname, other field are just variable to supply value to table fields,i have implement simple type of SP for "SELECT" AND "CREATE" ,but this procedure does not work, please help me
CREATE proc insert_mess @tname nvarchar(50), @mass nvarchar(250), @to_id int, @form_id int as exec ('insert into ['+ @tname +'] ( [message],to_id,form_id) values ('+ @mass +',' +@to_id + ','+@form_id +') ')
I have multiple sites trying to communicate with a SQL Server 2012 Express database at another remote site. At one site I am unable to connect to the remote server. If I try to use my program I get this message:
System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The token supplied to the function is invalid)
If I try to connect using SSMS I get the same error.I have been unable to find any reference to this message on the internet.
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.
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.
Im getting this error when looping through a gridview. Im trying to save each row into the database. The weird thing is that it saves the first row, but errors out on the second.If I only add one item to the gridview, it works. The second row is screwing it up somehow. Any ideas? ------------------------------------------------------------------------------------------------------------------------------------------------------------------Some of my code to get an idea of how im doing it:(This is for a shopping cart. It displays the products in the shopping cart. If the order is approved by the CC processing company, each product entry is saved in the DB. The gridview is being populated by my ShoppingCart Profile Object.) SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["rdevie"].ConnectionString); SqlCommand objCmd = new SqlCommand("InsertOrderDetails", objConn); objCmd.CommandType = CommandType.StoredProcedure;
Hi,I have a simple stored procedure that basically SELECT some data from database. I am calling this stored procedure using a sqldatasource. The first time I try it, it works perfectly fine. But I get the error message "Procedure or function has too many arguments specified" the second time I try to search for thing. This is the code that I have that called the stored procedure: Session("SearchItem") = txtSearch.Text.Trim SqlDataSource1.SelectCommand = "_MyStoredProcedure" SqlDataSource1.SelectParameters.Add("SearchItem", Session("SearchItem")) SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure Gridview1.DataSourceID = "SqlDataSource1" Gridview1.DataBind() The following is the code in the stored procedure: CREATE PROCEDURE [dbo].[_MyStoredProcedure] ( @SearchItem nvarchar(255) = NULL)ASSELECT Field1, Field2, Field3 FROM Registration WHERE((@SearchItem IS NULL) OR (Field1 LIKE '%' + @SearchItem + '%') OR (Field2 LIKE '%' + @SearchItem + '%'))GO Please help.Stan
I am trying to delete from a SQL table using two arguments, I currently use the following on the aspx page: <ItemTemplate><asp:LinkButton ID="cmdDeleteJob" runat="server" CommandName="CancelJob" Text="Delete" CssClass="CommandButton" CommandArgument='<%# Bind("Type_of_service") %>' OnClientClick="return confirm('Are you sure you want to delete this item?');"></asp:LinkButton></ItemTemplate> and then I use the following on the aspx.vb page: If e.CommandName = "CancelJob" Then Dim typeService As Integer = CType(e.CommandArgument, Integer) Dim typeDesc As String = "" Dim wsDelete As New page1 wsDelete.deleteAgencySvcs(typeService) gvTech.DataBind() I need to be able to pick up two CommandArguments.
Hi, I'm a bit stumped as to how to do this.I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field. E.g. Say I have two results in my string[], fred and bob.How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??"; IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to: for(int a = 0; a < userArray.Length; a++){ conn.Open(); SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close()} That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set. Any help is greatly appreciated.
"Procedure or function sptblTrgFACalculator_Insert_v2 has too many arguments specified"
This error was produced when i tried to insert 12 arguments into my table.Is there a limit to the number of arguments you can pass into your stored procedure?
I have two tables: Person (member_no, name) and Participant (participant, member_no) member_no is the primary key in Person.The stored procedure is:CREATE PROCEDURE dbo.InsertRecs@member_no char(10),@name char(10),@participant char(10)ASbegininsert into person (member_no, name)values (@member_no, @name)select @member_no = @@Identityinsert into participant (participant, member_no)values (@participant, @member_no)endGOWhen I run this via the DetailsView control and try to insert a record I always get "Procedure or function InsertRecs has too many arguments specified"If I remove the second table from the stored procedure it works fine.Does anyone have any ideas?
Hello All, I am having a very strange problem which I cannot figure out. I am using the ASP.NET 2.0 SqlDataSource control to insert a news item into a SQL Express database. When inserting the a message I receive the following error: Procedure or function has too many arguments specified However according to me every thing adds up. Can anyone please help me figure out what is wrong? Here is the code: SqlDataSource: <asp:SqlDataSource ID="sqlNews" runat="server" ConnectionString="<%$ ConnectionStrings:FlevoOptiekDB %>" SelectCommandType="StoredProcedure" SelectCommand="dbo.usp_News_ByID" InsertCommandType="StoredProcedure" InsertCommand="dbo.usp_News_Insert" UpdateCommandType="StoredProcedure" UpdateCommand="dbo.usp_News_Update" DeleteCommandType="StoredProcedure" DeleteCommand="dbo.usp_News_Delete" OldValuesParameterFormatString="{0}"> <SelectParameters> <asp:QueryStringParameter Name="p_iNewsID" QueryStringField="news_ID" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="p_dtmPublished" Type="DateTime" /> <asp:Parameter Name="p_sTitle" /> <asp:Parameter Name="p_sDescription" /> <asp:Parameter Name="p_sStaticUrl" /> <asp:Parameter Name="p_iPhotoID" /> <asp:Parameter Name="p_iNewsID" /> <asp:Parameter Name="p_iAlbumID" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="p_dtmPublished" Type="DateTime" /> <asp:Parameter Name="p_sTitle" /> <asp:Parameter Name="p_sDescription" /> <asp:Parameter Name="p_sStaticUrl" /> <asp:Parameter Name="p_iPhotoID" /> <asp:Parameter Name="p_iAlbumID" /> </InsertParameters> <DeleteParameters> <asp:QueryStringParameter Name="p_iNewsID" QueryStringField="news_ID" /> </DeleteParameters> </asp:SqlDataSource>Stored procedure:ALTER PROCEDURE dbo.usp_News_Insert( @p_sTitle VARCHAR(256), @p_sDescription TEXT, @p_sStaticUrl VARCHAR(256), @p_dtmPublished DATETIME, @p_iPhotoID INT, @p_iAlbumID INT)AS /* SET NOCOUNT ON */ INSERT INTO dbo.tbNews(news_Title, news_Description, news_StaticUrl, news_Published, photo_ID, album_ID) VALUES(@p_sTitle, @p_sDescription, @p_sStaticUrl, @p_dtmPublished, @p_iPhotoID, @p_iAlbumID) RETURN Thanks, Maurits