Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter

Mar 28, 2007

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.

 

Code:

.

.

<td style="width: 3px">

<asp:TextBox ID="txtName" runat="server"></asp:TextBox>

</td>

.



<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"

AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"

Width="533px">

<Columns>

<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"

SortExpression="LogId" />

<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />

<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />

<asp:TemplateField>

<ItemTemplate>

<span>

<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>

</ItemTemplate>

</asp:TemplateField>

 

</Columns>

<HeaderStyle BackColor="#608FC8" />

<AlternatingRowStyle BackColor="#FFFFC0" />

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"

SelectCommand="spViewLog" SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

Stored Procedure:

ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )

 

AS

SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')

RETURN

 

I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.

 Any guidance would be much appreciated.

Thank you

Lee

 

 

View 2 Replies


ADVERTISEMENT

Passing Parameters To Sqldatasource Stored Procedure

Aug 22, 2006

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.

View 4 Replies View Related

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.StoredProcedureGridView1.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 

View 2 Replies View Related

Passing Parameter To Stored Procedure Call Within SqlDataSource

Aug 3, 2007

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>   

View 5 Replies View Related

Passing Parameters Into Stored Procedure

Apr 11, 2007

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>
 

View 1 Replies View Related

Passing Parameters To A Stored Procedure

Mar 8, 2004

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.

Thanks in advance.

View 9 Replies View Related

Passing Parameters In A Stored Procedure

Sep 19, 2013

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

[code]....

View 11 Replies View Related

Passing Parameters To A Stored Procedure

Nov 22, 2006

Hey people,

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?

Thanks to you all!

View 1 Replies View Related

Stored Procedure Parameters In SQLDataSource

Jan 19, 2008

I want to know how to set parameters for Update Stored Procedure in SQLDataSource
 

View 5 Replies View Related

Running A Stored Procedure Without Passing Parameters

Feb 14, 2007

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. 

View 6 Replies View Related

Passing Two Or More Parameters To An Update Stored Procedure From VB6 Using ADO

May 26, 1999

I am trying to pass two parameters to a stored procedure using the code below:

Dim cmd As New Command
Dim rs As New Recordset
Dim prm As New Parameter
Dim ttt As New Parameter

cmd.ActiveConnection = cnn1
cmd.CommandText = "{call usp_PlcwithinUID_upt(?,?)}"
cmd.CommandType = adCmdStoredProc

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.

Please help.

Paul

View 1 Replies View Related

Passing SQL Clauses As Parameters To A Stored Procedure

Feb 26, 2004

Hi,

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.

--Tom.

View 2 Replies View Related

Passing Different Number Of Parameters To A Stored Procedure

Jan 22, 2007

Hi to all,

How can I Pass different number of parameters to a Stored Procedure?

In my Requirement,

Some times i want to pass 2 parameters only,

In some cases i want to pass 6 parameters.

How can i do this?

Please give me a solution.

Thanx in advance...

View 1 Replies View Related

Passing Parameters To MySQL From A Stored Procedure

Dec 22, 2007



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
')

View 2 Replies View Related

SqlDataSource And Optional Stored Procedure Parameters

Oct 12, 2006

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

View 1 Replies View Related

Passing In Variable Number Of Parameters To A Stored Procedure

Jul 9, 2006

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.

Any other ways to do this? Thanks

View 7 Replies View Related

Stored Procedure - Passing Date Parameters Failed

Aug 4, 2015

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:

cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)

where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.

The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.

View 5 Replies View Related

Passing Parameters To A Stored Procedure In Visual Basic

Jan 5, 2007

Hi peeps,

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.

Both tables joined on personid

Author: Iain Blackwood

Modification log: Change

Description Date Changed by

Created proc 02/01/07 Iain Blackwood

*/

ALTER PROCEDURE usp_display_all_users

(

@searchStr nvarchar(128) =''

)

AS

SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment

FROM dbo.pswds INNER JOIN

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

DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

Catch ex As Exception


' Display suitable error message

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.

Thanx Flakkie

View 6 Replies View Related

SqlDataSource With Stored Procedure With Parameters Returns No Data

Mar 21, 2007

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>&nbsp;            <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox>&nbsp;            <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3  />            &nbsp;&nbsp;            <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>

View 7 Replies View Related

Passing Parameters Read From An Ini File To A Stored Procedure Within DTS Package

Sep 24, 2002

Hi

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.

How can I do that? Any idea?

Thanks

mipo

View 1 Replies View Related

Dyanamically Passing Input Parameters To Stored Procedure By Using SSIS

May 14, 2008

Hi,

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

Uniq_Id, emp_ass_id, start_date end_date status_id





1099

1001

1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM 1








1100

1001

1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM 1








1101

1001

1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM 1








1102

1001

1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM 1








1103

1001

1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM 1






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.

Thanks in advance.

View 9 Replies View Related

Problem Passing Variables As Parameters To Extended Stored Procedure

Jun 20, 2007

Hello all,



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)



srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '';



srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '';



srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '';



sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);





Calling the XP with literal values:



EXEC xp_mytest 'one','two','three'



Output:

Params received by xp: one, two, three





Calling XP via a stored procedure:



create procedure sp_mytest

(

@myvar1 nvarchar(200),

@myvar2 nvarchar(50),

@myvar3 nvarchar(50)

)

as BEGIN

PRINT @myvar1

PRINT @myvar2

PRINT @myvar3

EXEC xp_mytest @myvar1, @myvar2, @myvar3

END



EXEC sp_mytest 'one','two','three'



Output:

one

two

three

Params received by xp: o,t,t





Any insight or assistance is greatly appreciated!!!

View 1 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

SQL 2012 :: SSIS Passing Parameters To Stored Procedure That Changes Based On The Data Being Passed?

Jun 23, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

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

Sample Data:

SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8

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.

View 6 Replies View Related

Can A ControlParameter Be Used To Supply A Parameter For A Stored Procedure?

Apr 13, 2006

The code below is an attempt at using the value from a dropdownlist to feed into stored procedure outlined in the Select command. I have seen examples where the control parameter is used with a select command but nothing where the parameter has to be fed into a stored procedure.
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString=".."SelectCommand="procCAGetCustomerKPIs" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter Name="iCustomerGroupId" ControlID="CustomerFilterList" PropertyName="SelectedValue" /></SelectParameters></asp:SqlDataSource>
 
cheers-jim.

View 2 Replies View Related

Passing Parameters For SqlDataSource From Code Behind

May 25, 2007

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  

View 8 Replies View Related

SQLDataSource Parameters Passing Null

Aug 23, 2007

 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>

View 3 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Passing '% Variable %' To SqlDataSource Through E.Command.Parameters

Feb 23, 2008

 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! 

View 3 Replies View Related

How To Add ControlParameter To SqlDataSource At Runtime?

Dec 8, 2006

Hi!
My question is exactly the subject.
My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)1 Dim SQLDS As SqlDataSource = New SqlDataSource()
2
3 SQLDS.ID = "CustomerDataSource"
4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString
5 SQLDS.SelectCommand = "select customerid,companyname,contactname,country from customers"
6 SQLDS.InsertCommand = "insert into customers(customerid,companyname,contactname,country) values(@customerid,@companyname,@contactname,@country)"
7 SQLDS.UpdateCommand = "update customers set companyname=@companyname,contactname=@contactname,country=@country where customerid=@customerid"
8 SQLDS.DeleteCommand = "delete from customers where customerid=@customerid"
9
10 SQLDS.UpdateParameters.Add(New Parameter("companyname"))
11 SQLDS.UpdateParameters.Add(New Parameter("contactname"))
12 SQLDS.UpdateParameters.Add(New Parameter("country"))
13 SQLDS.UpdateParameters.Add(New Parameter("customerid"))
14
15 Page.Controls.Add(SQLDS)
16
17 If Not Page.IsPostBack Then
18 GridView1.DataKeyNames = New String() {"customerid"}
19 GridView1.DataSourceID = SQLDS.ID
20
21 ' ... and so on
The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.How can I synchronize the DetailsView?I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this: 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server"
2 ConnectionString="<%$ ConnectionStrings:Pubs %>"
3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @state">
4 <SelectParameters>
5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
6 </SelectParameters>
7 </asp:SqlDataSource>
8
 Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?Thanks!

View 3 Replies View Related

SqlDataSource+ControlParameter+Textbox

Jan 15, 2008

Hi!
I have a page (a search page) with sqldatasource, gridview and set of textboxes. The sqldatasource is using stored procedure with parameters. Using the visual wizard i'm associating the parameters with apropriate textboxes controls. It all works, like a charm.
The problem arises when I input some dangerous code in any textbox , like ' or % .
I'm having exception about unenclosed strings, generally info about the possibility of sql injection.
I thought that using ControlParameters, any Parameter in fact is sqlinjection safe, but apparently it isn't.
Does anyone know the right way of achieving my goal? It's very urgent.

View 7 Replies View Related

Passing Parameters To A Procedure

May 30, 2008



How does we pass dynamic number of parameters to a procedure?
Eg.

DECALRE @IDs varchar(50);

SET @IDs = '1,2,3'

SELECT Description FROM MyTable WHERE ID IN (@IDs )

Here ID is int. When I tested this, it shows an error 'Cannot covert '1,2,3' to int'. No errors while it is one number ie '1'

How I can manage this type of problem in a stored procedrue that receive the @IDs as parameters?

View 1 Replies View Related

Passing Parameters To Stored Procedures

Feb 17, 2005

Hello,

I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.

I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.

Thanks for your help, Gary

Here is the web code:


Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)

cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))

cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue

conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()


Here is the stored procedure:



CREATE PROCEDURE s_ItemLookUp

@Field AS VARCHAR(50),
@Value AS VARCHAR(50)

AS


SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved