Pass A Parameter To A SqlDataSource
Feb 28, 2007
I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?
SubmitForm.ascx page:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string...... %>"
SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
SubmitForm.ascx.vb page:
Private ProgramID as string = "25"
Public ReadOnly Property ProgramID() As String
Get
Return _ProgramID
End Get
End Property
Thanks
Jason
View 2 Replies
ADVERTISEMENT
Feb 1, 2007
i have a sqldatasource on my asp.net page -- select * from table where id = @id
i want to set the @id in the backend and set the result to textbox1.text
how do i do this?
View 1 Replies
View Related
Jun 5, 2008
Either method is in the “ASPX� file
This is a DataSource for a “DetailsView� which has on top of “DeleteCommand� an “InsertCommand� a “SelectCommand� and an “UpdateCommand�. It is related to a GridView and the “@DonationRecID� comes from this GridView.
Method 1. Using an SQL Query – this works fine <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="DELETE FROM [Donations] WHERE [DonationRecID] = @DonationRecID">
Method 2. – using a stored procedure – this bombs because I have no clue as to how to pass “@DonationRecID� to the stored procedure "Donations_Delete". <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="Donations_Delete" DeleteCommandType="StoredProcedure"> How do I pass “@DonationRecID� to the "Donations_Delete" stored procedure?
Does anyone have an example of how I can do this in the “ASPX.CS� file instead.
View 3 Replies
View Related
Jul 31, 2006
Hi
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
View 1 Replies
View Related
Mar 13, 2007
Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code?
In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it?
I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything.
Thanks!
View 5 Replies
View Related
Jul 15, 2007
how do i pass the paramenters and storedprocedure to dataview from code-behind?
the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display." DataKeyNames="NewsId">
<Columns>
<asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True"
SortExpression="NewsId" />
<asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" />
<asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" />
<asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" />
<asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" />
</Columns>
</asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
View 3 Replies
View Related
Feb 29, 2008
I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_diplomaViewQualifications"
UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"
DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"
SelectCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="quantity"
/>
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="createdBy" DefaultValue="TEST"/>
</DeleteParameters>
<SelectParameters>
<asp:Parameter Name="masterKey"
/>
</SelectParameters>
</asp:SqlDataSource>
My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.
This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)
How do I pass this into the update and/or delete part
of the command field on the ASP page?
View 3 Replies
View Related
Feb 17, 2006
How to pass a null to SelectParameters in SqlDataSource?
The type of "CreateDate" is DateTime, the following code can be run correctly!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "2006-11-12";
now I hope to pass null value to the Parameter "CreateDate", but the following 3 section codes don't work!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = string.Empty;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue =null;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "";
View 4 Replies
View Related
Sep 23, 2007
I'm developing a web app using VS2005. I have a webpage with panel containing a gridview populated by a SQLdatasource. The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters. The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search. The gridview then becomes visible. My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows. 5 searchterms returns the proper results. Somehow, I need to be able to pass in null or empty values for unneeded parameters.
I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem. Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command
Dim x As Integer
pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''")
If Not filtertext Is Nothing Then
filtertext = filtertext.Trim
Else
Return
End IfDim arrayString() As String = filtertext.Split(" ")
Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1)
If Not arrayString(x) Is Nothing ThenSelect Case x
Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1)
Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3)
Case 4 : lblFTParm5.Text = arrayString(4)
End Select
End If
Next
pnlFullText.Visible = "True"
End Sub
Any ideas?
Thanks in advance.
View 2 Replies
View Related
Mar 15, 2008
I know i can accomplish this by setting a session variable on page load on the server side (Session["UserName"]= User.Identity.Name) and then use a session parameter, but I was wondering how to do it without using session variables, i was hoping the following code would work, but it doesn't. Thanks for any ideas.
" Name="UserName" Type="String" />
View 3 Replies
View Related
Feb 28, 2008
Hi,
I'm reasonably new to ASP.NET 2.0
I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete.
For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures).
I desperately need the syntax to:
a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need).
b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters).
c) syntax on how to set the values for these parameters (again...coming from form controls and session variables)
d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource.
If anybody has sample code or a link or two, I would be most appreciative.
Thank you in advance for any help!
View 5 Replies
View Related
Oct 31, 2007
I'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?
View 11 Replies
View Related
Jan 29, 2008
How 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
View 2 Replies
View Related
Nov 7, 2007
HiWe 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 Related
May 22, 2008
I 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?
View 1 Replies
View Related
Oct 26, 2007
Hi,
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...
View 6 Replies
View Related
Sep 5, 2007
Hi 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
View 2 Replies
View Related
Dec 11, 2006
How can I pass NULL to a parameter, if now entry is made in the textbox?
Dim KeywordParam As New SqlParameter("@Keyword", Me.KeyWordText.Text)
MyCommand.Parameters.Add(KeywordParam)
View 1 Replies
View Related
Jul 7, 2007
Hi, I have a SQL table with a column field type varchar(max). It stores large amounts of text (actually HTML). I am trying to write an insert statement but cant figure out how to pass varchar(max) value to a parameter. I am using VB. The value is being passed from a formview control label. I get the following error: "Conversion from string "@manualtext" to type 'Integer' is not valid." from this line: dbComm.Parameters.Add("manualtext", SqlDbType.NText, "@manualtext") Here is what I have so far: ' Get HTML text file from formview control label Dim t As Label = CType(Me.FormView2.FindControl("manualtextlabel"), Label) ' get record number from querystring Dim param = CType(Request.QueryString("record"), String) ' connect to DB Dim myconnection As SqlConnection myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("MYCMSConnectionString").ConnectionString ' SQL statement Dim strSQL As String = "INSERT INTO dataentry " & _ "(project, manualtext) VALUES (@project, @manaultext)" Dim dbComm As New SqlCommand(strSQL, myconnection) ' create and pass value to parameters dbComm.Parameters.Add("project", SqlDbType.NVarChar, 50, "@project") dbComm.Parameters("project").Value = param.ToString() dbComm.Parameters.Add("manualtext", SqlDbType.NText, "@manualtext") dbComm.Parameters("manualtext").Value = t.Text Try myconnection.Open() dbComm.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Response.End() Finally If myconnection.State = ConnectionState.Open Then myconnection.Close() End If End Try ' **************** Dim newloc = "viewdetail.aspx?record=" + param Response.Redirect(newloc) End Sub Thanks for the help.
View 4 Replies
View Related
Dec 7, 2007
Can a table be passed as parameter to a T-SQL procedure or function?One can define a temporary table within a procedure, and the system then manages its lifetime, keeps it separate from other instances executing the same procedure, etc: - CREATE PROCEDURE dbo.name (@Parameter1, @Parameter2) AS BEGIN DECLARE @name TABLE (column list) etcPerfect: but I need to be able to have another [recursive] procedure process this table. How do I make it available to the second procedure? If I write EXEC procedure @Namean error message "Must declare the variable '@Name'" is produced. Within the 2nd procedure, an attempt to name a table within the parameter list results in another error message, "Incorrect syntax near the keyword 'TABLE'". If I can't pass it as argument/parameter, then how do you process a temporary table in another procedure? Do I pass a cursor to it? Or is it quite impossible?Regards, Robert Barnes.
View 6 Replies
View Related
Apr 12, 2008
Hi Friends,
Is it possible to pass the name of table to the User Defined Function in SQL and use it inside the function.
Any help would be appreciated.
Fazal.
View 4 Replies
View Related
Sep 15, 2005
Hello,
Is there a way to pass a parameter to a trigger?
Thanks,
View 1 Replies
View Related
Dec 13, 2000
I have the following function that is run when a person
enters a value, "Cell", in an access form. I am trying to
execute the pass through query in SQL Server but i keep
getting the following message:
"The microsoft jet database engine cannot find the input
table or query 'CellDefs'. Make sure it exists and its name
is spelt correctly."
There is nothing wrong with the table name.
I cant understand what the problem is ?????
Can anyone tell me how to execute a query that passes
a parameter from access to Sql server.
Thanks,
Ruaidhri
Public Function GetData(Cell As String)
Dim dbsCurrent As Database
Dim qd1 As QueryDef
Dim SQL As String
Dim rs As Recordset
Dim qdf As QueryDef
Set dbsCurrent = CurrentDb
Set qd1 = dbsCurrent.CreateQueryDef("")
With qd1
.Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS"
SQL = ""
SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber"
SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON "
SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON "
SQL = SQL + "B.Finders_id = C.Finders_id "
SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' "
SQL = SQL + "ORDER BY C.FinderNumber"
.ReturnsRecords = False
.ODBCTimeout = 2400
dbsCurrent.Execute (SQL)
End With
dbsCurrent.Close
End Function
View 1 Replies
View Related
Sep 29, 2005
HI
I want to Run a Dynamic SQL in which i need to pass IN parameters.
DECLARE @QUERY varchar(100)
DECLARE @Paremeter varchar(10)
SET @Parameter = '1,2'
SET @QUERY='SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (' + @parameter + ')'
But this causes an error that cant convert varchar string to int. Please dont suggest to avoid Dynamic Sql. I have to use it.
View 1 Replies
View Related
Oct 18, 2004
The application developers came up with an interesting requirement that I’d like some help with.
They want to be able to somehow pass a parameter to the trigger that is fired when you do something to a table. The parameter would control what logic is executed within the trigger.
The two solutions that I proposed, but were found unacceptable by them were:
1.Have parameter as a column within the table itself.
2.Don’t use Update, Delete, Insert but instead use a stored procedure.
Thoughts are welcome.
Thanks.
View 7 Replies
View Related
Mar 2, 2006
I build a DTS package to get data from Oracle.
The data I want may be yesterday, may be two days ago or may be others. So I need a date parameter to tell the package how to get the data. But I do not know how to do it.
View 1 Replies
View Related
Mar 31, 2004
Is there is a way to pass parameters to pass parameters to sql script during command line exeution with ISQL or Osql
View 11 Replies
View Related
Jan 20, 2014
I have several tables which are all identical in columns and datatypes. I want to create a function which does some calculations on this tables.
Since I just want to create one function I Need to specify the table on which the function should work on. That means i want to pass the table Name as a Parameter to the function.
Is that possible?
View 6 Replies
View Related
Mar 27, 2008
I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
View 1 Replies
View Related
Jul 20, 2005
Hi Everyone,Is there an easy way that I could pass a filter (variable) unto a viewvia stored procedures? I would appreciate your suggestions.Benito
View 3 Replies
View Related
Jul 2, 2007
I have a value I need to show on every report so I want to create a report parameter. It appears the only way for a report to have a parameter is for it to be a variable in the SQL WHERE clause. This value is not in the database so can't be retrieved from SQL.
How can I pass this value to the report and display it in a textbox in the footer?
Thanks.
View 1 Replies
View Related
Jun 27, 2007
Hi,
I've developed a matrix report with two row groups. The second is visible or hidden depending on the toggle status of the first. I have set the navigation properties of the detail field to display a report of the underlying detail. I want to pass the current toggle state of the first row group to the details report as a boolean parameter. I this possible? I don't seem to have access to this property within the expression or code editors.
I'd be happy, as an alternative that implies the same thing, to pass the hidden / visible state of the second group.
Thanks,
Ian.
View 1 Replies
View Related
Jul 12, 2006
I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.
Is this possible?
View 3 Replies
View Related