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
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
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
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
A couple days ago, i posted a question on the Access boards.Here (http://forums.databasejournal.com/showthread.php?s=&threadid=36511)
Long story short, instead of filtering the results Locally, returned by query run locally on linked tables in an access front end, seems we should filter remotly. Seems easier and more secure.
We still want to hand out MS Access front ends. The results of queries run by users must be filtered based on authetication and values in the tables they query. There would be two ways to accomplish this.
1 - Stored procedures, apprearing as queries in Access.
2 - Views, appearing as tables.
Stored procedures are sure to work, but somewhat a hassle, sine we have to write code for each.
Views seem to be the simplest approach. we only need to build an SQL statement representing the new table. yet again, we have not done any constructive filtering. Is there a way to pass arguments to views just like stored procedures?
An exemple could be :
SELECT field1, field2, field3 FROM edu_details WHERE ID_department = @usr_dept AND ID_Division = $usr_div
usr_dept and usr_div being fields in the queried table(or course) AND auth information.
Hello. I am pretty new to SQL Stored Procedure, and ran into a problem. I am guessing there is a simple solution to this, as I am self-teaching myself this stuff.
I currently have a procedure that creates a new table, by reading in columns from a variety of Linked Servers (ODBC connection to to Sybase databases)
I will want to generalize a procedure by setting as arguments the ODBC connection name and the name of the new table that will be created ("CREATE TABLE") so I can pass these in.
As a test of this idea, I created this as a simple procedure, but I get a syntax error when I try to Check Syntax this. Do I have to do something else when I reference the string "argu1" to specify a new table name, or an OPEN QUERY Linked table name? Thanks and Happy New Year!
CREATE PROCEDURE gis.pr_PARCEL_TEST( @argu1 char(25)) AS CREATE TABLE @argu1 GO
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
we can use 'sp_executesql' to execute any statemens. I have made a search and people, seems, need the dynamic sql only to process some table/cloumn unknown in advance. My idea is that the dynamic SQL feature is ideal for passing blocks of code (aka delegates). Particularily, you may require to execute different procedures under some acquired locks. A procedure would acquire the locks, execute the code and release the locks. The problem is, however, that I cannot find the specification for the variable length parameters. It seems not feasible for SPs. Nevertheless, the 'sp_executesql itself does accept the variable number of parameters. How? Can we look at the defenition?
I have got this issue when I am trying to store an Image on a database through a stored procedure. I give you the code in C# and the stored procedure
Code Snippet
//I have got these two variables with the ID(foreign key) and the array of bytes of the Image (the values are OK) int ID_Inmueble byte[] imagen -- dimension 26246
SqlConnection conn = new SqlConnection(...) //The conexion works fine (I tested it).
When I try to call a stored procedure I get an SQL-exception reading "Procedure or function insert_member has too many arguments specified."
As far as I can see the number of parameters in the application match the number of arguments in the stored procedure. I supply 10 parameters in the application and 10 in the procedure. Is the location of the declaration of the argument cgpID a problem?
ALTER PROCEDURE [dbo].[insert_member]
(
@mbrFirstName nvarchar(15),
@mbrLastName nvarchar(15),
@mbrStreetAddress nvarchar(15),
@mbrPostalAddress nvarchar(15),
@mbrTelephoneHome nvarchar(15),
@mbrTelephoneJob nvarchar(15),
@mbrEmail nvarchar(15),
@mbrActivityGroupID int,
@mbrPaymentMethod int
)
AS
INSERT INTO dbo.member(mbrFirstName, mbrLastName, mbrStreetAddress, mbrPostalAddress, mbrTelephoneHome, mbrTelephoneJob, mbrEmail)
Hi While coding with ASP.NET 2.0 I came across this error "Procedure or function InsertUpdateArtist_Tracks has too many arguments specified." Its quite frustrating because I dont know what arguments are being referred to because I've clearly assigned the correct parameters with their correct values to this Stored procedure :InsertUpdateArtist_Tracks Please can some help me with this? is there something fundamentality wrong with my code (Below) or is this a know Microsoft stuff up? or by the way I'm using SQL Server 2005 and ASP.NET 2.0 Below is my sample code: .............. 'Insert or Update Artist_tracks table Response.Write("<br><b>Inserting or Updating Artist_tracks table</b>") AT_Title = SearchArtistsResults(i).title AT_Meta_ID = SearchArtistsResults(i).meta_id AT_AA_ID = AA_ID Dim ParamTitle As SqlParameter Dim ParamMeta As SqlParameter Dim ParamAA_ID As SqlParameter dbCommand.CommandText = "InsertUpdateArtist_Tracks" dbCommand.CommandType = Data.CommandType.StoredProcedure dbCommand.Connection = conn ParamTitle = dbCommand.Parameters.Add(New SqlParameter("@AT_Title", Data.SqlDbType.VarChar, 50)) ParamTitle.Value = AT_Title ParamTitle.Direction = Data.ParameterDirection.Input Response.Write("<br>Sent through this Title =" & AT_Title) ParamMeta = dbCommand.Parameters.Add(New SqlParameter("@AT_Meta_ID", Data.SqlDbType.Int)) ParamMeta.Value = AT_Meta_ID ParamMeta.Direction = Data.ParameterDirection.Input Response.Write("<br>Sent through this Meta_id =" & AT_Meta_ID) ParamAA_ID = dbCommand.Parameters.Add(New SqlParameter("@AT_AA_ID", Data.SqlDbType.Int)) ParamAA_ID.Value = AT_AA_ID ParamAA_ID.Direction = Data.ParameterDirection.Input Response.Write("<br>Sent through this AT_AA_ID =" & AT_AA_ID) conn.Open() dbCommand.ExecuteNonQuery() conn.Close() ..................
Hi All, I'm getting this annoying message when I try to call my Sp on an insert. I've executed the SP straight from SQL Server 2005 Express and it works fine. but I think I have a problem how I'm calling it. The SP------------------------------------------------------------------------- ALTER PROCEDURE dbo.spInsertNewProduct( @productName nvarchar(50), @productSummary text, @productDesc text, @productPhoto int, @productFeaturelist text, @productTestimonials text, @productFile1 int, @productFile1_Title nvarchar(50), @productFile2 int, @productFile1_Title nvarchar(50), @NewProductID int OUTPUT)AS -- inserts new product INSERT INTO [tbl_products] ([productName], [productSummary], [productDesc], [productPhoto], [productFeaturelist], [productTestimonials], [productFile1], [productFile1_Title], [productFile2], [productFile2_Title]) VALUES (@productName, @productSummary, @productDesc, @productPhoto, @productFeaturelist, @productTestimonials, @productFile1, @productFile1_Title, @productFile2, @productFile2_Title); -- Read the just-inserted productID into @NewProductID SET @NewProductID = SCOPE_IDENTITY(); ------------------------------------------------------------------------- The DataSource------------------------------------------------------------------------- <asp:SqlDataSource ID="dsproduct" runat="server" ConnectionString="<%$ ConnectionStrings:myConn %>" InsertCommand="spInsertNewProduct" InsertCommandType="StoredProcedure"> <InsertParameters> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productSummary" Type="String" /> <asp:Parameter Name="productDesc" Type="String" /> <asp:Parameter Name="productPhoto" Type="String" /> <asp:Parameter Name="productFeaturelist" Type="String" /> <asp:Parameter Name="productTestimonials" Type="String" /> <asp:Parameter Name="productFile1" Type="String" /> <asp:Parameter Name="productFile1_Title" Type="String" /> <asp:Parameter Name="productFile2" Type="String" /> <asp:Parameter Name="productFile2_Title" Type="String" /> <asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" /> </InsertParameters> </asp:SqlDataSource>------------------------------------------------------------------------- In code behind I grab params from front end dropdowns:------------------------------------------------------------------------- Sub InsertProduct(ByVal Src As Object, ByVal Args As DetailsViewInsertEventArgs) dsProduct.InsertParameters("productPhoto").DefaultValue = CType(dvEditProduct.FindControl("ddlProdImage"), DropDownList).SelectedValue dsProduct.InsertParameters("productFile1").DefaultValue = CType(dvEditProduct.FindControl("ddlProdFile1"), DropDownList).SelectedValue dsProduct.InsertParameters("productFile2").DefaultValue = CType(dvEditProduct.FindControl("ddlProdFile2"), DropDownList).SelectedValue lblStatus.Text = "Product has been added to the database" End Sub------------------------------------------------------------------------- I can't see how in the VB front end file how there can be too many params? Any help is greatly appreciated!
Thanks in advance, I am getting the "Procedure or function JobsDb_Development_Update has too many arguments specified." error. Below is the sp and the vwd generated sqldatasource, any suggestions? Thanks Nick
Hi,I cant see why this will not work, when i run the page and try and perform a function it clicks me out and underlines SQLCommand.ExecuteNonQuery() with this error Procedure or function sppaintinsert has too many arguments specified Code below: Imports System.IO Imports System.Data Imports System.Data.SqlClientPartial Class admin Inherits System.Web.UI.PageProtected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click Dim strFileName As String Dim strFilePath As String Dim strFolder As String Dim strPicTitle As StringDim SQLConnection As Data.SqlClient.SqlConnectionDim SQLCommand As Data.SqlClient.SqlCommand
strFolder = "C:Documents and SettingsRoss HintonMy DocumentsVisual Studio 2005WebSitesjessicawebsitePictures" strFileName = oFile.PostedFile.FileName strFileName = Path.GetFileName(strFileName) strPicTitle = TextBox1.Text If (Not Directory.Exists(strFolder)) Then Directory.CreateDirectory(strFolder) End If strFilePath = strFolder & strFileName If File.Exists(strFilePath) Then lblUploadResult.Text = strFileName & " already exists on the server!" ElseSQLConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|picturesdb.mdf;Integrated Security=True;User Instance=True")SQLCommand = New SqlCommand SQLCommand.CommandType = CommandType.StoredProcedure If DropDownList.SelectedItem.Value = 0 Then SQLCommand.CommandText = "paintingpictures" Else SQLCommand.CommandText = "spdrawinsert" End If SQLCommand.Connection = SQLConnection SQLConnection.Open() SQLCommand.Parameters.AddWithValue("@image", "Pictures" + strFileName) ' sp parameter nameSQLCommand.Parameters.AddWithValue("@Title", strPicTitle) SQLCommand.ExecuteNonQuery() oFile.PostedFile.SaveAs(strFilePath) lblUploadResult.Text = strFileName & " has been successfully uploaded." End If frmConfirmation.Visible = TrueEnd Sub End ClassALTER PROCEDURE paintingpictures @image VARCHAR(50) AS INSERT INTO pictable([image]) VALUES (@image)
Can anybody help me with this error? I am not even sure where to start looking.
i am looping through an arrayList that populates the SqlParameters. I open the database outside the loop, close it outside the loop and execute myCommand.ExecuteNonQuery(); inside the loop for every loop.
The error I am getting is:
System.Data.SqlClient.SqlException: Procedure or function ImportData has too many arguments specified.
I've found that I'm not the first one to get the error
"Procedure or function x has too many arguments specified"
while working with Stored Procedures (which is no surprise at all). But all suggested solutions didn't help, maybe this is because I misunderstood the whole concept. The situation is: On my page there is a FormView control including the EditItemTemplate. The database contains a Stored Procedure called UpdatePersonByID which is working fine as long as executed in Visual Web Developer. Here's the procedure code:
This is not exactly what it will finally have to do, of course the WHERE-clause later will contain an ID comparison. But since I tried to break down my code as much as possible I changed it to what you see right now. Here's the aspx-source (the red stuff is what I think is important):
And then once again a totally different question: Is there a way to post the highlighted aspx or vb code into this forum and keep the colors? i think I've seen that in some posts but didn't wanna do it manually.
I have the following stored procedure ALTER PROCEDURE dbo.TextEntered @searchString varchar(30) /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN doesn't work!!!! I would like to produce the following where s is @searchString: Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME Any ideas how I might acomplish this I have tried almost everything !
Does any know of a technique to filter out headers dashes and row count in an output file of an isql result? These switches seem to be available when running as isql/w, but not the command line. The -h-1 argument for no headers had no effect. Thanks in advance. Ron Hurley
