Passing Multiple Parameters To Stored Procedure Using SqlDataSource
Oct 9, 2007
Hi,
I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:
SqlDataSource1.SelectCommand = "_Search"
SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)
SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)
SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
GridView1.DataSourceID = "SqlDataSource1"
GridView1.DataBind()
MsgBox(GridView1.Rows.Count)
It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?
Stan
Hi, I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine. Now, instead of the default value i want to pass the current logged in user name as a parameter. How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a stored procedure. Thanks, M.
Hello, I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related. The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated) When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
I have two variables in my code behind .cs, string Zip;int MenuCode; I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object? error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string" <asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
Hi, I'm fairly new to TSQL so I'll try to explain as best I can. I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :
CREATE PROCEDURE ExplodeDilutions @MyLSN AS VarChar(8)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.* FROM TSample WHERE (TSample.ISmpShortCode = @MyLsn)
The value I'm passing (@MyLSN) is 24/12359 (one LS Number)
The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:
CREATE PROCEDURE ExplodeDilutions @MyLSN AS VarChar(50)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.* FROM TSample WHERE (TSample.ISmpShortCode IN (@MyLsn))
The value I'm passing (@MyLSN) is '24/12359','24/12360'
This is where the problem begins because the stored procedure now doesn't return a recordset.
If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned. Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.
So if someone could point me in the right direction then that would be much appreiciated.
I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.
It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?
EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123' SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
I am trying to pass a parameter to a stored procedure based on a select statement. Basically it's something like this (in my head :P ):
foreach PersonID in (SELECT id FROM person) { MyStoredProcedure(PersonID); }
Multiple rows will be returned from the select statement.
The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?
HI all, I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks.
Set prm = New Parameter prm.Type = adInteger prm.Value = gsPlcCode cmd.Parameters.Append prm
Set ttt = New Parameter ttt.Type = adVarChar ttt.Value = "TEST" cmd.Parameters.Append ttt
'cmd(1) = gsPlcCode 'cmd(2) = "Test"
Set rs = cmd.Execute
It works when passing one but NOT two ?? Can anyone shed any light ?? I've also tried using the Create Parameter method but again one is fine but passing two parameters doesn't work ?
The stored procedure just performs an update on a table.
Basically I want to be able to update any fields in a table (specific record) when they have been changed by the user. I have placed boolean variables in the change events to detect any user edits. If any field(s) have been edited I then want to send each field within the current record as parameters to the update stored procedure.
Any clues as to how to get an update stored procedure to except multiple variables.
I have trawled through loads of documentation to no avail.
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
I am trying to pass in parameters from MSSQL Store procedure to a MySql database but it doesn't take the parameters? Any idea how to achieve this? The query works if I pass in the actual dates.
Create procedure [dbo].[MY_STORED_PROC]
@STime datetime, @ETime datetime
AS
select * into #MyTempTable from openQuery(MYSQL, ' SELECT a.* FROM mytable a where a.createdate between @STime and @ETime ')
Everyone,I have an application that pulls a list of employees into a web form. I use the same stored procedure to search for employees by various demographic characteristics, and have set all of these search parameters to be optional in the stored procedure.Everything works fine when I pull the entire list (without declaring any select parameters). However, on my Search page, I have declared a number of SelectParameter (ControlParameters, to be specific) objects and mapped each one to its respective control (text boxes and a checkbox). When I attempt a search, the SqlDataSource does not select any records unless I specify an argument for ALL of the search parameters. If I omit even a single search value, the page just posts back and does not pull any information from the database. I have verified with SQL Profiler that it is not attempting to hit the database.I have experimented with various declarations in the ControlParameter object, including the DefaultValue and ConvertEmptyStringToNull items. If I specify a non-empty string as a DefaultValue for all of the parameters, it will perform the query, but it does not query if I use an empty string as a default value - unless my user specifies a value in the form. Any ideas?ThanksTim
I am fairly new to MSSQL. Looking for a answer to a simple question.
I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.
The structure of this table is something like
PKID customerID email address .....
customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...
Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?
Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.
I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.
The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.
The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.
Here is a portion of the stored procedure:
alter proc uspSalesCommission ... ... @IncludeOrderDateAsCriterion int, @OrderDateRangeStart date, @OrderDateRangeEnd date as BEGIN ... ... WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and (@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)
In Excel VBA, the code passing the parameters are:
I need some help with passing parameters to a stored procedure from my visual basic code.
Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it.
I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code.
The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it.
The code for the procedure is listed below:
/*
Name: usp_display_all_users
Description: Displays activeuser, personid, comment from table: pswds
Userid and sort from table: people
Where the username is like the parameter supplied.
dbo.people ON dbo.pswds.personid = dbo.people.personid
WHERE (dbo.people.sort LIKE @searchStr + '%')
ORDER BY dbo.people.sort
The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox.
I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written.
Private Sub fillDataGrid()
' I NEED TO:
' 1: Fill the data set with all Accounts
' 2: Diplay the Data to the data grid
' delcare a new SQL connection
sqlCon = New SqlConnection(conStr)
' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE
Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment"
comStrPeople += " FROM pswds INNER JOIN"
comStrPeople += " people ON pswds.personid = people.personid"
comStrPeople += " ORDER BY sort"
' Display the command string: TEMPOARY
testlbl2.Text = comStrPeople
' Declare a new SQL data adapter
sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon)
Try
' Declare a new dataset
sqlDataSet = New DataSet
' fill the sql data adapter with data from dataset: called PeoplePswds
sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds")
' Fill the forms datagrid view with data from the Dataset table PeoplePswds
MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message)
End Try
End Sub
I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required.
I have a Gridview bound to a SQLDataSource that uses a Stored Procedure expecting 1 or 2 parameters. The parameters are bound to textbox controls. The SP looks up a person by name, the textboxes are Last Name, First Name. It will handle last name only (ie. pass @ln ='Jones' and @fn=null and you get all the people with last name=Jones. I tested the SP in Management Studio and it works as expected. When I enter a last name in the Last Name textbox and no First Name I get no results. If I enter a Last Name AND First Name I get results. I don't understand. Here's the HTML View of the page. The only code is to bind the Gridview when the Search button is pressed. <%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server" TabIndex=1></asp:TextBox> <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3 /> <hr /> </div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="EmpID" CellPadding="4" EnableSortingAndPagingCallbacks="True" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="EmpID" HeaderText="Emp ID" ReadOnly="True" SortExpression="EmpID" /> <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName" /> <asp:BoundField DataField="Nickname" HeaderText="Nickname" ReadOnly="True" SortExpression="Nickname" /> <asp:BoundField DataField="BGS2" HeaderText="BGS2" SortExpression="BGS2" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpbaseConnectionString %>" SelectCommand="GetByName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="fn" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> <asp:ControlParameter ControlID="TextBox1" Name="ln" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> </SelectParameters> </asp:SqlDataSource> </form> </body></html>
I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:
[DatabaseCleaner] ! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd> ! -- <N> must be a successive number starting from 1 ... TableToBeCleaned_1=Transactions,24,24 TableToBeCleaned_2=Payments,24,24 TableToBeCleaned_3=PresenceTickets,24,24
As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.
I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date, ex: if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert
the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.
I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:
C++ Extended Stored Procedure:
(Basically all this code is doing is retrieving the parameters and printing them back out)
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General Tab:
Result Set: None Connection Type: OLE DB SourceType: Direct Input IsQueryStoredProcedure: False (this is greyed out and cannot be changed) Bypass Prepare: True SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;
Parameter Mapping:
Variable Name Direction Data Type Prmtr Name Prmtr Size User: system_cd Input NVARCHAR 0 10 User: location_type_cd Input NVARCHAR 1 10
Variables:
location_type_cd - Data type - string; Value - Store (this is static) system_cd - Data type - string - ?????? The system code changes based on the system field for each record in the load table
STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:
Stored procedure variables: ALTER PROCEDURE [dbo].[sp_ml_location_load] (@system_cd nvarchar(10), @location_type_cd nvarchar(10)) AS BEGIN .....................
This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.
I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.
hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e) { try {foreach (ListItem item in authorsListBox4.Items) {if (item.Selected) { AddConfSqlDataSource.Insert(); } }saveStatusLabel.Text = "Save Successfull: The above publication has been saved"; }catch (Exception ex) {saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message; } }
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter. I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter? Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure" DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="locationStationId" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="locationStationId" Type="String" /> <asp:Parameter Name="locationType" Type="String" /> <asp:Parameter Name="locationName" /> <asp:Parameter Name="division" Type="String" /> </InsertParameters>
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.
Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.
Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?
Hi, I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample, CREATE PROCEDURE [dbo].[display_customer] @agentID varchar(20), @customer1 varchar(20), @customer2 varchar(20), ..... -- Here I do know how many customers for each agent AS SELECT name, city, state, zip FROM rep_customer WHERE agent = @agentID and (name = @customer1 or name = @customer2) Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure? Thanks a lot!
OK, 1st, I have looked at every article that has come back on a "Stored Procedures" Search on this site, and am more confused than when I started looking for my answer. This is what I need to do: I need to pass a search sentence to a stored procedure, have the stored procedure break up the space delimited string and then do a "like" and "contains" in the WHERE statement, on what was sent to the stored procedure, and then return the results to a gridview for the person to select which item best answers their search. I am just totally lost with using a stored procedure. I have done this in webmatrix when I coded it all into the aspx page, or into the codepage, but I have never done it with a stored procedure on the sql server, never sent a varible to a stored procedure... and am totlaly lost, or just do not understand how to do it. Any help would be great. Thanks in advance. D4D
