Pass Datasource Name As Input Parameter
Feb 23, 2007 hi,
how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?
Thanks,
shanthi
ADVERTISEMENT
SQLCMD - How Do I Pass Parameter At Dos Command Line To Input File?
Mar 23, 2007I am not sure if this has been asked before but I couldn't find any thread talking about this.
Let's say we have a parameter in the .sql input file called @Start_Date, how can we pass the value of a particular date, for example, "02-28-2007" to @Start_Date via SQLCMD? is it possible?
I'm trying to skip the need to write a simple windows application...if things can be achieved via dos command line, that will keep everything simple!
thanks! :)
Can't Remember How To Pass Int Value To Datasource
Sep 22, 2007I have know this error but have completely forgotten how to take care of it. How do I pass the integer values to the SQL statement?
ERROR: Syntax error converting the nvarchar value 'Label' to a column of data type int.
WHERE (LinkInfo.L_State = @State) AND (LinkInfo.CG_ID > @CatIDLow) AND (LinkInfo.CG_ID > @CatIDHigh)
<SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text" /> <asp:ControlParameter ControlID="Label2" DefaultValue="0" Name="CatIDLow" PropertyName="Text" /> <asp:ControlParameter ControlID="Label3" DefaultValue="1899" Name="CatIDHigh" PropertyName="Text" /></SelectParameters>
CODE BEHIND
public partial class TopandBottomSelect : System.Web.UI.Page{ int CG_IDlower; int CG_IDupper; protected void Page_Load(object sender, EventArgs e) { } protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e) { if (RadioButtonList1.SelectedIndex == 0) { CG_IDlower = 0; CG_IDupper = 1900; Label1.Text = Convert.ToString(CG_IDlower); Label2.Text = Convert.ToString(CG_IDupper); } else { CG_IDlower = 1899; CG_IDupper = 3000; Label1.Text = Convert.ToString(CG_IDlower); Label2.Text = Convert.ToString(CG_IDupper); } GridView1.DataBind(); }}
Thank you
How To Pass Pass The Parameter In SQL Command In SSIS Package
Jul 31, 2006Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
How To Pass In SOAP Header For XML Datasource
Apr 3, 2006Hi,
I have created a simple webservice with a soap header and am trying to
access it using an xml datasource in reporting services. The query
works but I have no idea how to pass in a value for the UID on the host
header. Any help? what XML would I add for the header?
This is what I have no that allows me to pass in parameters through the editor.
<Query>
<Method Namespace="http://tempuri.org/" Name="HelloPerson2"/>
<SoapAction>http://tempuri.org/HelloPerson2</SoapAction>
</Query>
Here is the code for the webservice:
/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService {
public WebService () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[Serializable]
public class PersonHeader : SoapHeader
{
private int _uid;
public int UID
{
get { return _uid; }
set { _uid = value; }
}
}
public PersonHeader userCredentials;
[WebMethod]
[SoapHeader("userCredentials")]
public Person HelloPerson2(string firsName, string lastName)
{
Person person = new Person();
//person.FirstName = "John";
//person.LastName = "Smith";
person.FirstName = firsName;
person.LastName = lastName;
person.UID = userCredentials.UID;
return person;
}
[Serializable]
public class Person
{
public Person()
{
}
private string _firstName;
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
private string _lastName;
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
private int _uid=1000;
public int UID
{
get { return _uid; }
set { _uid = value; }
}
}
}
How To Pass A Long Parameters To The Datasource?
Apr 5, 2007I defined the following stored Procedure as the datasource
CREATE PROCEDURE p1(@cond String)
AS
BEGIN
SELECT Id,Name from table1 Where @cond
END
@cond is a long sql , for example
Id in (select sunId from table2 where .......)
my question is
how can I pass the parameters( @cond ) to RS2005?
thanks!
OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Apr 17, 2008Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
How To Pass A Input Value To A Script Using Osql
Dec 2, 2004I want to run a sql script that creates some views using "osql". The views in turn refer to user defined functions which need a two-part name reference(MyUser.MyFunction). Is there a way to do this without hardcoding the value of the "MyUser" in the script?
Example..
I want to run a script called myscript.sql using OSQL. The file myscript.sql looks like this..
CREATE view V1
AS
SELECT *, UserOwner1.MyFunction1(5)
FROM MyTable
go
CREATE view V11111
AS
SELECT *, UserOwner1.ScalarFunction11111(7)
FROM MyTable
go
-----
I do not want to hardcode the username (UserOwner1) since it varies from database to database. So when I run this script from command prompt using "osql" I want the ability to supply the value for the username (UserOwner1) which owns the user defined function. How to do this?
This problem would go away if SQLServer 2000 was not so adamant about requiring two part reference to a user defined function.
Thanks!
Need To Pass User Input Through To SQL Query
May 20, 2008Hello Everybody,
I am sure this has been asked a thousand times before in a thousand different ways, but here goes...
I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.
Thanks in advance for your time and patience,
Rocky Mountain Tech
My Output Parameter Is Being Treated As An Input Parameter...why
Sep 25, 2006I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
2 DataSet From Xml DataSource, And Parameter ...
Apr 5, 2007Hi !!!
I have a problem, I don't know how to do...
I have 2 dataset from 2 differents XML dataSource...
I want the result of one dataSet to be a parameter for the second !
I try to add a parameter in param tab, with expression, I choose DataSet and
=Sum(Fields!ID.Value, "DataSetID")
but when I execute the query, the prompt for the param appear, but the value of id was <NULL>...
Someone have ideas to resolve this problem ?
thank you for your help !
Setting Formview Datasource Parameter
May 23, 2007I am trying to use the code below to set a formview datasource parameter in the page_load section of a user control (ascx file): public void Page_Load(object sender, EventArgs e){ formview_datasource.SelectParameters.Add("@department_id", "e62bbc7d623f44a68e101cba90e839s3");} However I am getting the following error: Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@department_id'.
So it would seem that page_load in my user control isn't being called? I'm not sure why or how to work around it. Has anyone else experienced anything like this or can give me some pointers on where I am going wrong?
ThanksBrad
Multivalue Parameter Against Oracle 9 Datasource?
Jan 22, 2007Everything I have read says that this is possible in Oracle 9 and greater versions, but I can't get it to work...
Here is my query:
select * from employee where employee_id in ('111','222')
This works fine in the data tab until I replace the values with a parameter. I get the pop-up to enter the parameter values but no matter how I enter them, I don't get any rows returned. I've tried entering them as '111','222' and as 111,222 and changing the parameter type from a string to an int, but nothing seems to work.
I am running this on SQL Server RS 2005 and my Oracle driver (Oracle in OraHome92) is version 9.02.
Sorting Parameter Listbox On Analysis Services Datasource
Feb 26, 2007Hi all,
How must I change the mdx that is generated for the available values for a user parameter in order to get the content sorted?
Regards,
Henk
BTW the exact mdx query is given below (and the label field of the parameter is set to 'ParameterCaption'), but I would already appreciate an example of a simple mdx.
MEMBER [Measures].[ParameterCaption] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[Nummer en Naam] AS '[Organisatie].[Kosten Nummer].CURRENTMEMBER.MEMBER_CAPTION +": "+ [Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'
--MEMBER [Measures].[ParameterValue] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption],
[Measures].[Nummer en Naam]
, [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS
, filter ([Organisatie].[Level 5 naam].MEMBERS,[Measures]) ON 1 ,
[Organisatie].[Kosten Nummer] on 2
FROM ( SELECT ( STRTOSET(@OrganisatieLevelnaam, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurPeriode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurJaarNummerLang, CONSTRAINED) ) ON COLUMNS FROM [FMR DWH Afgenomen Dienst])))
Sorting According To Input Parameter
Jun 1, 2007Hi, I need to do the following task, which is described by pseudo-code
SELECT * FROM Customers
SORT BY @SortExpression
How can I do something like it (sorting according to input parameter)
Thanks for any idea
Need To Get Input In Parameter From End User
May 24, 2007Hi,
I am trying to make a simple stored procedure which I want to take input on every run from end user:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @sku varchar(20)
insert into skua (sku,SumOfQtyNum)
(select sku,sumofqtynum from sku where @sku = sku.sku)
it is working but not asking parameter values
(I want to use passthrough query after that in Access)
thanks
AA
Clearing Parameter Input
Sep 21, 2007Hi everyone,
I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.
Thanks,
Roy
How Can I Pass A Parameter To Use In An IN
Oct 31, 2007I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.
Dim MySqlParamSelected As New SqlParameter("@Selected", SqlDbType.Int) Cmd.Parameters.Add(MySqlParamSelected) MySqlParamSelected.Value = Session("intSelected")
WHERE tblSelected.Selected_ID IN (@Selected)
It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.
Here is the error:
Msg 119, Level 15, State 1, Line 4Must pass parameter number 7 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Does anyone know how I can correctly pass multiple numbers to use in my IN?
Pass Parameter To SP
Jan 29, 2008How do i pass a parameter to a stored proc using this command:
object SqlHelper.ExecuteScalar (SqlConnection connection, string spName, params object[] parameterValues)
let's say that my parameter name is @ID and the value is IDval ..
thanx
How Do I Pass A Parameter To A DTS ?
Nov 7, 2007HiWe still use SqlServer 2000 and DTSs.My question is: Can I pass a parameter to a DTS and if yes then how ?We would like to plan our screen like this.The user will input a parameter on the screen/form and then the DTS willbe activated using the value inputed.We do have how to work around it if parameters can't be passed but itwould make things a lot easier and straightforward if we could.Thanks !David Greenberg
View 2 Replies View RelatedCannot Pass Parameter Via URL
May 22, 2008I see a lot of posts asking for help with this type of problem but cannot find an answer that works. After 24 hours of trying, using examples from the Microsoft documentation, it's been utterly impossible to pass a parameter to a report via URL.
To make sure I'm not fighting more than one problem at the same time, I've stepped away from the production report I was trying to produce and created a simple report with a parameter named "p1". The only field on this test report is a textbox that displays the value of the parameter "p1". If I set the report up so that the user is queried for a value for "p1", the report works as expected -- the parameter is echoed back on the report.
The same thing can't be done via the URL. If I follow the docs and tack something like &rs:Command=Render&p1=hello onto the URL, I'm prompted for a value and/or told 'p1 is missing a value', depending on the visibility and default options I set for the parameter in the report definition.
I've tried every possible combination of settings I can think of for a parameter in the report definition, and every single one fails. Is this a task that's beyond RS abilities?
How To Pass Mdx Parameter
Oct 26, 2007Hi,
Any body can help me on passing parameters in to an MDX query?
sample :
SELECT NON EMPTY { [Measures].[Gross Profit], [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Employee].[Employee Department].[Employee].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] where [Employee].[Department Name].[engineering] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
instead of engineering i want to pass a parameter Department.
I created another dataset for it
WITH MEMBER [Measures].[ParameterCaption] AS '[Employee].[Department Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Employee].[Department Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Employee].[Department Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Employee].[Department Name].ALLMEMBERS ON ROWS FROM [Adventure Works]
Parameter is populating perfectly but how i edit my MDX to accept that parameter ?
Thanks in Advance...
How To Pass Parameter
Sep 5, 2007Hi Friends,
I have two reports, one master report & one child report. If I click a patricular value in Territory column, It should take me to the child report where only rows which matches with the territory should be displayed. Since I'm new to SSRS, Kindly some guide me.
Please note that, in the child report I should not get any dropdown boxes for selection.
Thanks in advance
Naveen
Help With Incorrect Syntax (input Parameter)
Nov 14, 2006Hi
Help with syntax, I get the error in the line: myDA.Fill(ds, "t1")
Function GetProductsOnDepartmentPromotionPaging(ByVal departmentId As String)
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myDA As New SqlClient.SqlDataAdapter _
("MM_SP_GetProductsOnDepartmentPromotion", myConnection)
' Add an input parameter and supply a value for it
myDA.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4)
myDA.SelectCommand.Parameters("@DepartmentID").Value = departmentId
Dim ds As New DataSet
Dim pageds As New PagedDataSource
myDA.Fill(ds, "t1")
pageds.DataSource = ds.Tables("t1").DefaultView
pageds.AllowPaging = True
pageds.PageSize = 4
Dim curpage As Integer
If Not IsNothing(Request.QueryString("Page")) Then
curpage = Convert.ToInt32(Request.QueryString("Page"))
Else
curpage = 1
End If
pageds.CurrentPageIndex = curpage - 1
lblCurrpage.Text = "Page: " + curpage.ToString()
If Not pageds.IsFirstPage Then
lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage - 1)
End If
If Not pageds.IsLastPage Then
lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage + 1)
End If
list.DataSource = pageds
list.DataBind()
End Function
Best Regards
Primillo
C# Command Timestamp Input Parameter
Mar 27, 2007I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? ThanksRyan
View 2 Replies View RelatedFunction That Take A Table As Input Parameter
Feb 26, 2008hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance
How To Use A Cookie As An Input Parameter In Sqldatasource?
Apr 8, 2008i created a cookie as follows
HttpCookie myCookie = new HttpCookie("Portal");
myCookie["EMail_ID"] = Email_ID;myCookie["Role"] = Role_ID.Value.ToString();myCookie.Expires = DateTime.Now.AddMinutes(1);
Response.Cookies.Add(myCookie);
Now i have to take the"EMail_ID" as input parameter in sqldatasourse.
<asp:SqlDataSource ID="SqlDS_DC_List" runat="server" ConnectionString="<%$ ConnectionStrings:EnR_Portal_ConnectionString %>"
SelectCommand="proc_DC_List" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:CookieParameter CookieName=" " Name="Email_ID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
how can i specify Email ID as cookie name?
Input Parameter To Store Procedure
Jan 26, 2004Hi,
I am using a ListBox where a user can choose multiple lines.
The index of the selected items are then used in a stored procedure.
I wan´t to use the ID´s in this statement:
SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)
But how can I do this?
If I pass them as a string, then I can´t use them as above.
Can I separate the string '1,2,4,9' so I can use them in the statement above?
Or can I send the values as a array to the stored procedure?
Regards!
Jonas
Stored Procedure Input Parameter (asp.net 2.0)
Jun 13, 2006This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Code:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"
cmd.Insert()
Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)
AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)
Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
ERROR:
Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
Input Parameter In MS SQL 2005 Report
Sep 27, 2007I am designing a report which need to take parameter input by user when the report is open. Can anyone please tell me how to do this? Using MS SQL 2005 report. Thanks.
View 2 Replies View RelatedUpdating A Column With Input Parameter?
May 19, 2014Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)
[code].....
Set Input Parameter To Zero If Null Or Blank
Jan 8, 2015I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero
View 1 Replies View RelatedInput Parameter Doesn't Show.
Oct 10, 2006when i pass a input parameter, the SP doesn't recognize it.
like
exec SP 00004
it will print:
select * from table where idname = 4
i want it to print
select * from table where idname = '00004'
how can i do that?