Query With Multiple Arguments?
Dec 11, 2007
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.
View 4 Replies
ADVERTISEMENT
Mar 25, 2007
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
View 5 Replies
View Related
Jan 9, 2008
I get an error like 'Query failed for the datatset Reportsource.Procedure of function has too many arguments specified.'
What could be the reason for this error??
please help me
View 1 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 Replies
View Related
Mar 17, 2006
Howdy folks. I have a SELECT question for you. Or maybe a WHERE question. That I am not sure is part of the problem. My application is ASP.NET 2.0, and I’d like to avoid getting into stored procedures right now, if I could.
I am trying to summarize the order status for each customer in the database (SQL Server 2005, by the way). I want to provide two counts: the number of open jobs per customer, and the number of rush jobs per customer. Something like this:
CustID CustName JobOpen JobRush
----------------------------------------------------------------
22 John Deere 47 3
24 MCP 32 7
37 BlueON 6 0
In my noobness, I developed this:
SELECT CustID, MAX(CustName) AS CustName,
COUNT (*) AS JobOpen
FROM Customers
INNER JOIN Jobs ON JobCustID = CustID
WHERE (JobDone = 0)
GROUP BY CustID
As you can see, it finds from the Jobs table all jobs that are not done (JobDone is a T/F field), joins the Jobs and Customers tables, groups by CustID, and counts the totals in each CustID group. It works great in outputting the first three columns that I am looking for.
But I cannot come up with a simple way to add JobRush (also a T/F field), because it needs a different WHERE clause than the one JobOpen uses. It would need
WHERE (JobRush = 1)
So maybe my question should be: how do I use multiple WHERE clauses, each with its own COUNT?
I did mess around with Common Table Expressions, and managed to build two CTEs (one with JobOpen results and the other with JobRush results) that I joined together. It worked in Studio Manager, but my ASP.NET page didn’t like it. I guess that means I could learn stored procedures, but wow I’d love to just have a nice complete SELECT command for my page.
Thanks for reading all this. Any input is greatly appreciated.
Matt
View 3 Replies
View Related
Oct 6, 2015
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
View 3 Replies
View Related
Mar 3, 2006
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
View 11 Replies
View Related
Apr 6, 2006
My stored procedure: ALTER PROCEDURE [dbo].[ShortUpdateSurvey]
@s_survey_id int,
@s_survey_num varchar(10),
@s_job_address varchar(30),
@s_job_city varchar(20),
@s_client_name varchar(50),
@s_date_ordered datetime,
@s_date_required datetime,
@s_amount money
AS
SET NOCOUNT OFF;
UPDATE [survey_master]
SET
[survey_num] = @s_survey_num,
[job_address] = @s_job_address,
[job_city] = @s_job_city,
[client_name] = @s_client_name,
[date_ordered] = @s_date_ordered,
[date_required] = @s_date_required,
[amount] = @s_amount
WHERE [survey_id] = @s_survey_id My ASP code:
<asp:BoundField DataField="survey_id" HeaderText="Survey ID" SortExpression="survey_id" />
<asp:BoundField DataField="survey_num" HeaderText="Survey Num." SortExpression="survey_num" />
<asp:BoundField DataField="job_address" HeaderText="Address" SortExpression="job_address" />
<asp:BoundField DataField="job_city" HeaderText="City" SortExpression="job_city" />
<asp:BoundField DataField="client_name" HeaderText="Client" SortExpression="client_name" ReadOnly="True" />
<asp:BoundField DataField="date_ordered" HeaderText="Ordered" SortExpression="date_ordered" DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="date_required" HeaderText="Needed" SortExpression="date_required" DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="amount" HeaderText="Amount" SortExpression="amount" />
...
SelectCommand="ShortAddressSearch"
SelectCommandType="StoredProcedure"
UpdateCommand="ShortUpdateSurvey"
UpdateCommandType="StoredProcedure">
...
<UpdateParameters>
<asp:Parameter Name="s_survey_id" Type="Int32" />
<asp:Parameter Name="s_survey_num" Type="String" />
<asp:Parameter Name="s_job_address" Type="String" />
<asp:Parameter Name="s_job_city" Type="String" />
<asp:Parameter Name="s_client_name" Type="String" />
<asp:Parameter Name="s_date_ordered" Type="DateTime" />
<asp:Parameter Name="s_date_required" Type="DateTime" />
<asp:Parameter Name="s_amount" Type="Decimal" />
...
and once again, the error message:
"Procedure or function ShortUpdateSurvey has too many arguments specified."
TIA
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Sep 13, 2006
Can someone help me with this? For the life of me, I cannot figure out why I recieve this error: Procedure or function usp_ContactInfo_Update has too many arguments specified.Here is my code: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="usp_ContactInfo_Select" SelectCommandType="StoredProcedure" UpdateCommand="usp_ContactInfo_Update"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:Parameter Name="Division" Type="String" /><asp:Parameter Name="Address" Type="String" /><asp:Parameter Name="City" Type="String" /><asp:Parameter Name="State" Type="String" /><asp:Parameter Name="Zip" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="TollFree" Type="String" /><asp:Parameter Name="Fax" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Res" Type="Boolean" /><asp:Parameter Name="Res_Rec" Type="Boolean" /><asp:Parameter Name="Com" Type="Boolean" /><asp:Parameter Name="Com_Rec" Type="Boolean" /><asp:Parameter Name="Temp" Type="Boolean" /><asp:Parameter Name="Temp_Rec" Type="Boolean" /><asp:Parameter Name="Port" Type="Boolean" /><asp:Parameter Name="Land" Type="Boolean" /><asp:Parameter Name="Drop" Type="Boolean" /></UpdateParameters><SelectParameters><asp:ProfileParameter Name="DivID" PropertyName="Division" Type="String" /></SelectParameters></asp:SqlDataSource>Here is my Stored Proc:ALTER PROCEDURE dbo.usp_ContactInfo_Update @Division varchar(1),@Address varchar(50),@City varchar(50),@State varchar(2),@Zip varchar(10),@Phone varchar(20),@TollFree varchar(20),@Fax varchar(20),@Email varchar(50),@Res bit,@Res_Rec bit,@Com bit,@Com_Rec bit,@Temp bit,@Temp_Rec bit,@Port bit,@Land bit,@Drop bit/*(@parameter1 int = 5,@parameter2 datatype OUTPUT)*/AS/* SET NOCOUNT ON */ UPDATE tblDivisionSET Division = @Division, Address = @Address, City = @City, State = @State, Zip = @Zip, Phone = @Phone, TollFree = @TollFree, Fax = @Fax, Email = @Email,Residential = @Res, Residential_Recycling = @Res_Rec, Commercial = @Com, Commercial_Recycling = @Com_Rec, Temp = @Temp, Temp_Recycling = @Temp_Rec, Portable_Restrooms = @Port, Landscaping = @Land, Drop_Off_Center = @DropWHERE (Division = @Division) RETURNAny Help is greatly appreciated!
View 2 Replies
View Related
May 26, 2008
i have written procedure like belowALTER PROCEDURE GetDetails
(@vpid int,
@vpname varchar(30) OUTPUT,
@vprice int OUTPUT
)
ASselect @vpname=productname,@vprice=price from fastfood where prod_id=@vpid
RETURN
and codeSqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;SqlCommand cmd = new SqlCommand("GetDetails",con);cmd.CommandType = CommandType.StoredProcedure;
con.Open();
Hashtable items=new Hashtable();items = (Hashtable)Session["basketdata"];string name,price;
foreach(int s in items.Keys)
{
SqlParameter p1 = new SqlParameter("@vpid", SqlDbType.Int, 10);p1.Direction = ParameterDirection.Input;
p1.Value = s;
cmd.Parameters.Add(p1);
//cmd.Parameters.Add("@vpid", SqlDbType.Int, 10).Value=s;cmd.Parameters.Add("@vpname", SqlDbType.Char, 30).Direction=ParameterDirection.Output; cmd.Parameters.Add("@vprice", SqlDbType.Int, 10).Direction=ParameterDirection.Output;
cmd.ExecuteNonQuery();
name = cmd.Parameters["@vpname"].Value.ToString();price = cmd.Parameters["@vprice"].Value.ToString();
%>
}
showing too many arguments to procedure
View 2 Replies
View Related
Jun 15, 2000
Hi,
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
View 1 Replies
View Related
Feb 2, 2007
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;
objConn.Open();
foreach (GridViewRow row in gvOrderDetails.Rows)
{
objCmd.Parameters.Add("@idOrder", SqlDbType.Int).Value = orderNum;
objCmd.Parameters.Add("@fldName", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblName")).Text;
objCmd.Parameters.Add("@fldUnitPrice", SqlDbType.Money).Value = ((Label)row.FindControl("lblUnitPrice")).Text;
objCmd.Parameters.Add("@fldQuantity", SqlDbType.Int).Value = Convert.ToInt32(((Label)row.FindControl("lblQuantity")).Text);
if (!String.IsNullOrEmpty(((Label)row.FindControl("lblSku")).Text))
objCmd.Parameters.Add("@fldSku", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblSku")).Text;
else
objCmd.Parameters.Add("@fldSku", SqlDbType.NVarChar).Value = DBNull.Value;
objCmd.Parameters.Add("@fldSize", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblSize")).Text;
objCmd.Parameters.Add("@fldLogo", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblLogo")).Text;
objCmd.Parameters.Add("@fldPlacement", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblPlacement")).Text;
objCmd.Parameters.Add("@fldColor", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblColor")).Text;
objCmd.ExecuteNonQuery();
}
objCmd.Dispose();
objConn.Close();
objConn.Dispose();
View 5 Replies
View Related
Oct 3, 2007
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
View 6 Replies
View Related
Nov 7, 2007
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.
View 4 Replies
View Related
Jul 20, 2004
"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?
View 3 Replies
View Related
Jan 19, 2006
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?
View 9 Replies
View Related
Feb 18, 2006
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
View 3 Replies
View Related
Mar 7, 2006
I have tried to introduce information in webform with sp and she leaves the following error
View 1 Replies
View Related
Jul 26, 2004
Greetings everyone.
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.
Cheers,
Lawrence
View 1 Replies
View Related
Dec 31, 2005
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
View 2 Replies
View Related
Aug 31, 2007
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
View 4 Replies
View Related
Oct 13, 2007
Hi,
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?
View 2 Replies
View Related
May 25, 2008
Hi all,
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).
SqlCommand cmd = new SqlCommand();
SqlParameter[] param = new SqlParameter[2];
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertaUnaFoto";
cmd.Connection = conn;
param[0] = new SqlParameter("@ID_Inmueble", SqlDbType.BigInt);
param[0].Value = ID_Inmueble;
param[1] = new SqlParameter("@Foto", SqlDbType.Image);
param[1].Value = imagen;
cmd.Parameters.AddRange(param);
SqlDataAdapter da = new SqlDataAdapter(conexion.GetSqlCommand());
da.Fill(ds); //here I got the error
The stored Procedure
T
Code Snippet
ALTER PROCEDURE dbo.InsertaUnaFoto
(@ID_Inmueble bigint,
@Foto Image)
AS
INSERT into Fotos (ID_Inmueble, Foto) values (@ID_Inmueble, @Foto)
Many Thanks,
View 3 Replies
View Related
Mar 3, 2008
Hello,
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)
VALUES (@mbrFirstName, @mbrLastName, @mbrStreetAddress, @mbrPostalAddress, @mbrTelephoneHome, @mbrTelephoneJob, @mbrEmail)
declare @cgpID int
set @cgpID = (select cgpID from currentgroup where cgpActivityGroupID = @cgpActivityGroupID)
INSERT INTO member_currentgroup
(mbrID, cgpID, mcgPaymentMethod)
values
(@@identity, @cgpID, @mcgPaymentMethod)
View 8 Replies
View Related
Jun 9, 2008
Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
View 1 Replies
View Related
Mar 22, 2007
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()
..................
Thanks in Advance
View 2 Replies
View Related
Sep 19, 2007
I'm Deleting my record in the database using stored procedure and I'm always getting this error :Procedure or function DeleteSender has too many arguments specified. same as I insert record... here's my stored proc for delete:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[DeleteSender]@RecNo int ASDELETE FROM [Sender] WHERE [RecNo] = @RecNo for insert:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InsertSender](@SenderID nvarchar(50), @SenderLastName nvarchar(50), @SenderFirstName nvarchar(50), @SenderAddress nvarchar(50), @SenderCity nvarchar(50), @SenderState nvarchar(25), @SenderZip nvarchar(10), @SenderTel nvarchar(25), @FS int, @TB int, @Status nvarchar(10), @EndBalance money)ASINSERT INTO [Sender] ([SenderID], [SenderLastName], [SenderFirstName], [SenderAddress], [SenderCity], [SenderState], [SenderZip], [SenderTel], [FS], [TB], [Status], [EndBalance]) VALUES (@SenderID, @SenderLastName, @SenderFirstName, @SenderAddress, @SenderCity, @SenderState, @SenderZip, @SenderTel, @FS, @TB, @Status, @EndBalance) aspx:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %><%@ Register src="FormControl.ascx" TagName="FormControl" TagPrefix="uc1" %><%@ Register Assembly="RadGrid.Net2" Namespace="Telerik.WebControls" TagPrefix="radG" %><%@ Register Assembly="RadComboBox.Net2" Namespace="Telerik.WebControls" TagPrefix="radC" %><%@ Register Assembly="RadInput.Net2" Namespace="Telerik.WebControls" TagPrefix="radI" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Sender</title> <link href="sender.css" rel="stylesheet" type="text/css" /></head><body> <form id="form1" runat="server"> <div class="wrapper"> <div class="banner"> <radG:RadGrid ID="rgSender" runat="server" GridLines="None" DataSourceID="sqlDataSource1" EnableAJAX="True" ShowStatusBar="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True" AllowAutomaticDeletes="True" Skin="3D" AutoGenerateColumns="False"> <MasterTableView CommandItemDisplay="Top" DataKeyNames="RecNo" DataSourceID="sqlDataSource1"> <ExpandCollapseColumn Visible="False"> <HeaderStyle Width="19px" /> </ExpandCollapseColumn> <RowIndicatorColumn Visible="False"> <HeaderStyle Width="20px" /> </RowIndicatorColumn> <EditFormSettings EditFormType="Template"> <FormTemplate> <table border="0"> <tr> <td> <asp:Label ID="Label1" runat="server" Text="Record No.:" Visible='<%# rgSender.EditIndexes.Count > 0 %>'></asp:Label></td> <td> <radI:RadTextBox ID="rtbRecNo" runat="server" Text='<%# Bind("RecNo") %>' Visible='<%# rgSender.EditIndexes.Count > 0 %>'> </radI:RadTextBox></td> <td> <asp:Label ID="Label8" runat="server" Text="Zip Code:"></asp:Label></td> <td> <radI:RadTextBox ID="rtbZip" runat="server" Text='<%# Bind("SenderZip") %>'> </radI:RadTextBox></td> </tr> <tr> <td> <asp:Label ID="Label2" runat="server" Text="Sender ID:"></asp:Label></td> <td><radI:RadTextBox ID="rtbSenderID" runat="server" Text='<%# Bind("SenderID") %>'> </radI:RadTextBox></td> <td> <asp:Label ID="Label9" runat="server" Text="Telephone #:"></asp:Label></td> <td><radI:RadTextBox ID="rtbTel" runat="server" Text='<%# Bind("SenderTel") %>'> </radI:RadTextBox></td> </tr> <tr> <td><asp:Label ID="Label3" runat="server" Text="First Name:"></asp:Label></td> <td><radI:RadTextBox ID="rtbFName" runat="server" Text='<%# Bind("SenderFirstName") %>'> </radI:RadTextBox></td> <td> <asp:Label ID="Label10" runat="server" Text="Total Free Sent:"></asp:Label></td> <td><radI:RadTextBox ID="rtbFS" runat="server" Text='<%# Bind("FS") %>'> </radI:RadTextBox></td> </tr> <tr> <td><asp:Label ID="Label4" runat="server" Text="Last Name:"></asp:Label></td> <td><radI:RadTextBox ID="rtbLName" runat="server" Text='<%# Bind("SenderLastName") %>'> </radI:RadTextBox></td> <td><asp:Label ID="Label11" runat="server" Text="Total Box Sent:"></asp:Label></td> <td> <radI:RadTextBox ID="rtbBS" runat="server" Text='<%# Bind("TB") %>'> </radI:RadTextBox></td> </tr> <tr> <td> <asp:Label ID="Label5" runat="server" Text="Address:"></asp:Label></td> <td><radI:RadTextBox ID="rtbAddress" runat="server" Text='<%# Bind("SenderAddress") %>'> </radI:RadTextBox></td> <td><asp:Label ID="Label12" runat="server" Text="Status:"></asp:Label></td> <td><radI:RadTextBox ID="rtbStatus" runat="server" Text='<%# Bind("Status") %>'> </radI:RadTextBox></td> </tr> <tr> <td><asp:Label ID="Label6" runat="server" Text="City:"> </asp:Label></td> <td> <radI:RadTextBox ID="rtbCity" runat="server" Text='<%# Bind("SenderCity") %>'> </radI:RadTextBox></td> <td><asp:Label ID="Label13" runat="server" Text="End Balance:"></asp:Label></td> <td><radI:RadTextBox ID="rtbEndBalance" runat="server" Text='<%# Bind("EndBalance") %>'> </radI:RadTextBox></td> </tr> <tr> <td> <asp:Label ID="Label7" runat="server" Text="State:"></asp:Label></td> <td><radI:RadTextBox ID="rtbState" runat="server" Text='<%# Bind("SenderState") %>'> </radI:RadTextBox> </td> <td> <asp:Button ID="btnUpdate" Text='<%# IIf(CType(Container,GridItem).OwnerTableView.IsItemInserted,"Insert","Update") %>' runat="server" CommandName='<%# IIf(CType(Container,GridItem).OwnerTableView.IsItemInserted,"PerformInsert","Update") %>'></asp:Button> <asp:Button ID="btnCancel" Text="Cancel" runat="server" CausesValidation="False" CommandName="Cancel"></asp:Button></td> </tr> </table> </FormTemplate> </EditFormSettings> <Columns> <radG:GridEditCommandColumn ButtonType="ImageButton"/> <radG:GridTemplateColumn HeaderText="Sender ID" UniqueName="TCSenderID"> <ItemTemplate> <asp:Label ID="lblSenderID" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "SenderID") %>'></asp:Label> </ItemTemplate> </radG:GridTemplateColumn> <radG:GridTemplateColumn HeaderText="First Name" UniqueName="TCSenderFName"> <ItemTemplate> <asp:Label ID="lblSenderFName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "SenderFirstName") %>'></asp:Label> </ItemTemplate> </radG:GridTemplateColumn> <radG:GridTemplateColumn HeaderText="Last Name" UniqueName="TCSenderLName"> <ItemTemplate> <asp:Label ID="lblSenderLName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "SenderLastName") %>'></asp:Label> </ItemTemplate> </radG:GridTemplateColumn> <radG:GridButtonColumn UniqueName="ButtonColumn" ConfirmText="Are you sure?" Text="Delete" CommandName="Delete" ButtonType="ImageButton" ImageUrl="RadControls/Grid/Skins/Default/Delete.gif" /> </Columns> </MasterTableView> </radG:RadGrid></div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConString_TO_Cargo %>" SelectCommandType="StoredProcedure" SelectCommand="sp_SelectSender" DeleteCommandType="StoredProcedure" DeleteCommand="DeleteSender" InserCommandType="StoredProcedure" InsertCommand="InsertSender" OldValuesParameterFormatString="original_{0}" UpdateCommand="UPDATE [Sender] SET [SenderID] = @SenderID, [SenderLastName] = @SenderLastName, [SenderFirstName] = @SenderFirstName, [SenderAddress] = @SenderAddress, [SenderCity] = @SenderCity, [SenderState] = @SenderState, [SenderZip] = @SenderZip, [SenderTel] = @SenderTel, [FS] = @FS, [TB] = @TB, [Status] = @Status, [EndBalance] = @EndBalance WHERE [RecNo] = @original_RecNo AND [SenderID] = @original_SenderID AND [SenderLastName] = @original_SenderLastName AND [SenderFirstName] = @original_SenderFirstName AND [SenderAddress] = @original_SenderAddress AND [SenderCity] = @original_SenderCity AND [SenderState] = @original_SenderState AND [SenderZip] = @original_SenderZip AND [SenderTel] = @original_SenderTel AND [FS] = @original_FS AND [TB] = @original_TB AND [Status] = @original_Status AND [EndBalance] = @original_EndBalance"> <DeleteParameters> <asp:Parameter Name="RecNo" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="SenderID" Type="String" /> <asp:Parameter Name="SenderLastName" Type="String" /> <asp:Parameter Name="SenderFirstName" Type="String" /> <asp:Parameter Name="SenderAddress" Type="String" /> <asp:Parameter Name="SenderCity" Type="String" /> <asp:Parameter Name="SenderState" Type="String" /> <asp:Parameter Name="SenderZip" Type="String" /> <asp:Parameter Name="SenderTel" Type="String" /> <asp:Parameter Name="FS" Type="Int32" /> <asp:Parameter Name="TB" Type="Int32" /> <asp:Parameter Name="Status" Type="String" /> <asp:Parameter Name="EndBalance" Type="Decimal" /> <asp:Parameter Name="original_RecNo" Type="Int32" /> <asp:Parameter Name="original_SenderID" Type="String" /> <asp:Parameter Name="original_SenderLastName" Type="String" /> <asp:Parameter Name="original_SenderFirstName" Type="String" /> <asp:Parameter Name="original_SenderAddress" Type="String" /> <asp:Parameter Name="original_SenderCity" Type="String" /> <asp:Parameter Name="original_SenderState" Type="String" /> <asp:Parameter Name="original_SenderZip" Type="String" /> <asp:Parameter Name="original_SenderTel" Type="String" /> <asp:Parameter Name="original_FS" Type="Int32" /> <asp:Parameter Name="original_TB" Type="Int32" /> <asp:Parameter Name="original_Status" Type="String" /> <asp:Parameter Name="original_EndBalance" Type="Decimal" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="SenderID" Type="String" /> <asp:Parameter Name="SenderLastName" Type="String" /> <asp:Parameter Name="SenderFirstName" Type="String" /> <asp:Parameter Name="SenderAddress" Type="String" /> <asp:Parameter Name="SenderCity" Type="String" /> <asp:Parameter Name="SenderState" Type="String" /> <asp:Parameter Name="SenderZip" Type="String" /> <asp:Parameter Name="SenderTel" Type="String" /> <asp:Parameter Name="FS" Type="Int32" /> <asp:Parameter Name="TB" Type="Int32" /> <asp:Parameter Name="Status" Type="String" /> <asp:Parameter Name="EndBalance" Type="Decimal" /> </InsertParameters> </asp:SqlDataSource> <br /> <radG:RadGrid ID="rgSenderDetails" runat="server" AutoGenerateColumns="False" GridLines="None"> <MasterTableView> <ExpandCollapseColumn Visible="False"> <HeaderStyle Width="19px" /> </ExpandCollapseColumn> <RowIndicatorColumn Visible="False"> <HeaderStyle Width="20px" /> </RowIndicatorColumn> </MasterTableView> </radG:RadGrid><br /> </div> </form></body></html> hope you can help me guys/// stings...
View 1 Replies
View Related
Sep 26, 2007
Hi all I having suffering from a strange behavior when trying to update. I receive the following error :
Here is my code: Procedure or function SavePhotoHotelLang has too many arguments specified.
<asp:SqlDataSource ID="SqlDataSourceHotelPhotoList" runat="server" ConnectionString="<%$ ConnectionStrings:smileDBConnectionString %>"SelectCommand="SELECT phl.PhotoHotelLangId, ph.smallfilepath, ph.photorank, pht.Type, phl.name, phl.description, ph.PhotoHotelId FROM datPhotoHotel ph
INNER JOIN datPhotoHotelType pht ON ph.PhotoHotelTypeId = pht.PhotoHotelTypeId
LEFT OUTER JOIN datPhotoHotelLang phl on ph.PhotoHotelId = phl.PhotoHotelId WHERE HotelId = @HotelId and ( LanguageId = @languageId or LanguageId is null)"
UpdateCommand="SavePhotoHotelLang" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="HotelId" QueryStringField="HotelId" Type="int32" />
<asp:ControlParameter Name="languageId" ControlID="ddlLanguageForGV" PropertyName="SelectedValue" Type="int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="name"/>
<asp:Parameter Name="Description" />
<asp:ControlParameter Name="languageId" ControlID="ddlLanguageForGV" PropertyName="SelectedValue"/>
<asp:Parameter Name="PhotoHotelLangId" />
<asp:Parameter Name="PhotoHotelId" />
</UpdateParameters>
</asp:SqlDataSource>
<Grids:BulkEditGridView
ID="GvHotelPhotoList" runat="server"
DataSourceID="SqlDataSourceHotelPhotoList" AutoGenerateColumns="false" SaveButtonID="saveBtn"
DataKeyNames="PhotoHotelLangId, PhotoHotelId">
<Columns>
<asp:TemplateField>
<itemtemplate>
<asp:Image id="smallimage" runat="server" ImageUrl='<%# Eval("smallFilePath") %>'></asp:Image>
</itemtemplate>
<Edititemtemplate>
<asp:Image id="smallimageedit" runat="server" ImageUrl='<%# Eval("smallFilePath") %>'></asp:Image>
</Edititemtemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name"/>
<asp:BoundField DataField="Description" FooterText="Description"/>
<asp:BoundField DataField="photorank" HeaderText="Rank" ReadOnly="true"/>
<asp:BoundField DataField="Type" FooterText="Type" ReadOnly="true"/>
</Columns></Grids:BulkEditGridView>
">'>'>
alter procedure SavePhotoHotelLang @name varchar(100), @Description varchar(500), @languageId int, @PhotoHotelLangId int, @PhotoHotelId intasbegin
if ( @PhotoHotelLangId is null ) begin
insert into dbo.datPhotoHotelLang ( name, Description, languageId ) values (@name, @Description, @languageId, @PhotoHotelId) end else begin
update dbo.datPhotoHotelLang set name = @name, Description = @Description where PhotoHotelLangId = @PhotoHotelLangId
endend
View 1 Replies
View Related
Dec 6, 2007
Hi,
I keep on getting the same error for my stored procedure:System.Data.SqlClient.SqlException: Procedure or function StoredProcedure1 has too many arguments specified.The procedure itself looks like this:@imie varchar(255), @nazwisko varchar(255), @email varchar(255), @username varchar(255), @password varchar(255), @id_roli Int, @PESEL varchar(11), @ulica text, @numer_domu int, @numer_mieszkania int, @kod_pocztowy varchar(7), @miasto text, @telefon text, @id_kandydat int, @id_kierunku int, @id_stan int ASBeginSet nocount on Insert into Uzytkownik (imie, nazwisko, email, username, password, id_roli, PESEL, ulica, numer_domu, numer_mieszkania, kod_pocztowy, miasto, telefon) values ( @imie, @nazwisko, @email, @username, @password, @id_roli, @PESEL, @ulica, @numer_domu, @numer_mieszkania, @kod_pocztowy, @miasto, @telefon) select @id_kandydat=@@IDENTITY Insert into Kandydat_na_Kierunek (id_kandydat, id_kierunku, id_stan) Values (@id_kandydat, @id_kierunku, @id_stan) SET NOCOUNT OFF ENDand the code that uses it: SqlConnection con; con = new SqlConnection("Data Source=ELF;Initial Catalog=logos;Integrated Security=True"); SqlCommand cmd = new SqlCommand("StoredProcedure1", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@imie", TextBox3.Text)); cmd.Parameters.Add(new SqlParameter("@nazwisko", TextBox4.Text)); cmd.Parameters.Add(new SqlParameter("@email", TextBox6.Text)); cmd.Parameters.Add(new SqlParameter("@username", Label2.Text)); cmd.Parameters.Add(new SqlParameter("@password", Label1.Text)); cmd.Parameters.Add(new SqlParameter("@id_roli", '1')); cmd.Parameters.Add(new SqlParameter("@PESEL", TextBox5.Text)); cmd.Parameters.Add(new SqlParameter("@ulica", TextBox8.Text)); cmd.Parameters.Add(new SqlParameter("@numer_domu", TextBox9.Text)); cmd.Parameters.Add(new SqlParameter("@numer_mieszkania", TextBox10.Text)); cmd.Parameters.Add(new SqlParameter("@kod_pocztowy", TextBox11.Text)); cmd.Parameters.Add(new SqlParameter("@miasto", TextBox12.Text)); cmd.Parameters.Add(new SqlParameter("@telefon", TextBox13.Text)); if (DropDownList1.SelectedValue == "1") { } else { cmd.Parameters.Add(new SqlParameter("@id_kierunku", DropDownList1.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@id_stan", '1')); } if (DropDownList2.SelectedValue == "1") { } else { cmd.Parameters.Add(new SqlParameter("@id_kierunku", DropDownList2.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@id_stan", '1')); } if (DropDownList3.SelectedValue == "1") { } else { cmd.Parameters.Add(new SqlParameter("@id_kierunku", DropDownList3.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@id_stan", '1')); } con.Open(); cmd.ExecuteNonQuery(); con.Close();Could somebody explain what is wrong with this? I dpn't really unserstand the meaning of error. Any help will be apreaciated.Regards,N.
View 7 Replies
View Related
Mar 3, 2008
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!
View 5 Replies
View Related