Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts

Monday, March 19, 2012

Assing Report Parameters

Can you evaluate and assign a new value to a parameter in an expression for a textbox?

Did you just curse?

I'm not even sure what you're asking. It's too general. What are you trying to do?

|||

Basically you want to assign a new value to the Parameter after the user selects his option. is that correct?

If yes, you will not be able to reassign this parameter but what you can do is

1. create another hidden parameter

2. Make sure that this is placed next tothe first parameter

3. Now, you can use the expression to assign the value to this hidden parameter based on what is selected in the first parameter.

Hope this answers your question...or another option is to take it back to SQL and then use the expression in SQL to reassign the values.

Sunday, March 11, 2012

Assigning multiple values to a parameter in stored procedure

Hi:
How do you write a SQL statement in a stored procedure so
that it will allow you to assign multiple values to a
parameter?
For instance in this example below, depending on what the
users select on the front-end application, the values
assign can be one customer id value or multiple customer
id values:
Create procedure dbo.SP_Test
As @.CustID varchar(3)
Select * from tblCust where customerid = @.CustID
Please help!This is probably not the best solution but it should work
create procedure sp_test
@.cust_id varchar(50)
as
set nocount on
exec ('select * from tblcust where customerid in (' + @.cust_id +')')
go
This procedure would be called as sp_test '50' for one cust_id or sp_test
'50, 55, 100' for several cust_id's

Thursday, March 8, 2012

Assign session variable value to update parameter

Hi, I'm trying to update a sqlserver database through vb.net in an asp.net 2.0 project. I'm using a sqldatasource and am trying to code an update parameter with a session variable.

code snippet:

<UpdateParameters><asp:ParameterName="hrs_credited"/>

<asp:ParameterName="updater_id"DefaultValue="<%$ Session("User_ID")%>"Type="Int32"/>

<asp:ParameterName="activity_id"/>

<asp:ParameterName="attendee_id"/>

</UpdateParameters>

The error message that I receive is:

Error 2 Literal content ('<asp:Parameter Name="updater_id" DefaultValue="" Type="Int32"/>') is not allowed within a 'System.Web.UI.WebControls.ParameterCollection'. C:\Development\CME\dataentry\attendance.aspx 29

Does anyone have an idea how to assign the session var value to the parameter?

Thanks!

There is a special parameter called a SessionParameter that does exactly that. Refer to this page for more information:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sessionparameter.aspx

Saturday, February 25, 2012

aspx vb sql parameter passing

Hello,
Can someone kindly point out what is wrong with the following code file. I'm trying to:
- fill a dropdown from a db on page load (this works!)
- when user selects from the list and hits a button, pass the dropdown value to a second query
- use the second query to make another call to the db and fill a data grid

In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed.

Thanks

PS comments about my coding approach are welcome - I'm new to aspx...


<%@. Language="VBScript" Debug="true"%>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.data.SqlClient" %>
<html>
<head>
<script language="vb" runat="server">
Dim dbConnection As SqlConnection
Dim ds_Teams,ds_Agents As DataSet
Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter

Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd"

Dim sql_select_teams = "" & _
"SELECT team_no, team_name " & _
"FROM dbo.team " & _
"WHERE (team_status = 'Curr')"

Dim sql_select_agents = "" & _
"SELECT last_name + ', ' + first_name AS name, agent_no " & _
"FROM dbo.users " & _
"WHERE (team_no = @.Team) AND (NOT (agent_no = '1029')) " & _
" AND (NOT (last_name IS NULL)) " & _
" AND (NOT (first_name IS NULL)) " & _
" AND (NOT (first_name = 'FirstName')) " & _
"ORDER BY last_name"

Dim teamList = "teamList"
Dim agentList = "agentList"

Sub Page_Load(Sender As Object, E As EventArgs)
if not (IsPostBack)
ds_Teams = new DataSet()
dbConnection = New SqlConnection(dbConn)
sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection)
sqlCmd_Teams.Fill(ds_Teams, teamList)
dbConnection.close()

dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView
dropdownlist_Teams.DataBind()
end if
End Sub

sub Get_Agents(Sender As Object, E As EventArgs)
ds_Agents = new DataSet()
dbConnection = New SqlConnection(dbConn)
sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection)

sqlCmd_Agents.SelectCommand.Parameters.Add(new SqlParameter("@.Team", SqlDbType.NVarChar,4))
sqlCmd_Agents.SelectCommand.Parameters("@.Team").Value = dropdownlist_Teams.DataValueField

sqlCmd_Agents.Fill(ds_Agents,agentList)

dbConnection.close()

datagrid_Agents.DataSource=ds_Agents.Tables(agentList).DefaultView
datagrid_Agents.DataBind()
end sub

</script>
</head>
<body>
<form runat="server">
<asp:DropDownList id="dropdownlist_Teams" runat="server"
DataTextField="team_name"
DataValueField="team_no">
</asp:DropDownList
<input type="submit" onserverclick="Get_Agents" value="Get Agents" runat="server"><br /
<ASP:DataGrid id="datagrid_Agents" runat="server"
Width="500"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</form>
</body>
</html>

(1) dim a variable as string and then assign the sql stmt to the variable.
xample :
dim str1 as string
str1="Select..."

(2) is the valuefield numeric or string type ? also you might want to move the assigning of the str stmt to the get_events event itself rather than declaring it as a global string.

hth