How To Send Parameters From SqlDataSource?

May 8, 2008

I'm try to use a SqlDataSource control for binding data (stored procedure) to a GridView control. But I only get the error message below. What am I doing wrong?

 
 ----------- Error message ----------------------
Procedure or function 'StatisticCustomers' expects parameter '@From', which
was not supplied.

 
------------- Customer.aspx -------------------- 
            From <asp:TextBox id="txtDateFrom" CssClass="datebox" runat="server"></asp:TextBox>
            To <asp:TextBox id="txtDateTo" CssClass="datebox" runat="server"></asp:TextBox>
            Min: <asp:TextBox id="txtMinimumOrders" CssClass="" runat="server"></asp:TextBox>
 
     <asp:GridView ID="grdvwCustomers" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" runat="server" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" />
            <asp:BoundField DataField="Column1" HeaderText="Column1" ReadOnly="True" SortExpression="Column1" />
            <asp:BoundField DataField="Items" HeaderText="Items" ReadOnly="True" SortExpression="Items" />
            <asp:BoundField DataField="Column2" HeaderText="Column2" ReadOnly="True" SortExpression="Column2" />
        </Columns>
    </asp:GridView>
    
  
    <asp:SqlDataSource ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="StatisticCustomers" SelectCommandType="StoredProcedure" runat="server">
        <SelectParameters>
            <asp:ControlParameter ControlID="txtDateFrom" Name="dtFrom" PropertyName="Text" Type="DateTime" />
            <asp:ControlParameter ControlID="txtDateTo" Name="dtTo" PropertyName="Text" Type="DateTime" />
            <asp:ControlParameter ControlID="txtMinimumOrders" Name="iMinimumOrders" PropertyName="Text" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

 
 
ALTER PROCEDURE [dbo].[StatisticCustomers]
    (@From datetime,
    @To datetime,
    @MinimumOrders int )
AS
    SELECT
        Orders.UserId, 'name', COUNT(*) AS Items, SUM(OrderDetail.Subtotal)
    FROM
        Orders INNER JOIN OrderDetail ON Orders.OrderId = OrderDetail.OrderId
    WHERE
        DateCreated > @From AND DateCreated < @To
    GROUP BY
        Orders.UserId
    ORDER BY
        Items DESC
 
Regards, Sigurd 

View 3 Replies


ADVERTISEMENT

SqlDataSource Parameters

Apr 4, 2007

Is
it possible to specify different parameters for the
select/update/delete stored procedures used by a sqldatasource? When I
'configure the datasource' it lists the different stored procedures for
each command, but when it comes to specifying the parameters it only
lets me do so for the select command. Is there another way to do this
for the other commands?

I have tried doing it manually in the aspx file (using the
DeleteParemeters etc), but I don't know how to reference a specific
cell in the selected row of the gridview for the controlparameter). Any
thoughts?

View 4 Replies View Related

Parameters Of SqlDataSource

Apr 17, 2007

Hello I need help withsetting parameters for SqlDataSource
I have a simple program. I want display date from  database on MS SQLSERVER from the table USERS only for  current sing on user select by his login.
 I save into this  variable login current user:   string @LOGIN = Context.User.Identity.Name;
I have already done with this way without SqlDataSource:
string login = Context.User.Identity.Name;
SqlConnection conn1 = new SqlConnection("server=CR\SQLEXPRESS;database=myData;integrated security=SSPI");
conn1.Open();
SqlCommand cmd1 = new SqlCommand(" SELECT  IN_OUT.TIME_START,  IN_OUT.TIME_END, FROM  IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN", conn1);
cmd1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50);
cmd1.Parameters["@LOGIN"].Value = login;
1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50);
cmd1.Parameters["@LOGIN"].Value = login;
Now I don't know how to do with SqlDataSource, what I have to set in SqlDataSource1 yet
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>" SelectCommand="SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN">
</asp:SqlDataSource>

View 1 Replies View Related

Parameters.Add With SqlDataSource

Dec 2, 2007

Does anybody knows how to use parameters.add with SqlDataSource?

View 4 Replies View Related

How To Value Parameters For Sqldatasource

Dec 9, 2007

I set up a sqldatasource based on a stored procedure which takes one parameter.  The sqldatasrouce wizard generates the following code for the parameter below.  The question is how do I value the DeptID parameter on the load of the form.  I tried the following code in the load of the page, but get a null reference error:Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID")     <asp:SqlDataSource ID="SqlDataSource1" runat="server"             ConnectionString="<%$ ConnectionStrings:FDConn %>"             SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure">            <SelectParameters>                <asp:Parameter Name="DeptID" Type="Int32"  />            </SelectParameters>        </asp:SqlDataSource>        <radG:RadGrid ID="RadGrid1" runat="server">        </radG:RadGrid>

View 3 Replies View Related

SQLDataSource: How To Use Parameters

Dec 22, 2005

I need to do some select & update from VB code behind a web page. Using VS2005, ASP.NET.
To me, the most logical approach would be to use a SqlDataSource. I can select the connection string, predefine select, insert and update queries and call the select(), and other commands from this control.
I need to use parameters in the queries but I cannot connect parameters straight to controls, I need to do it from VB. But nowhere can I find how to set the parameter values from code. I can find all kind of examples from VS2003 or using SqlCommand, but it should be possible from this control as well, but the help documentation is very poor in this respect.
Please provide me with some example.
Kind regards
 

View 5 Replies View Related

Reporting Services :: Send A Report To Various Email ID Based On Parameters?

Jun 9, 2015

I need to send a report to various email id based on parameters.

I have a report which has a dropdown which shows the list of parameters. I need to send report to 3 different email ids with different parameters. How to do that?

View 6 Replies View Related

SqlDataSource Setting Of Parameters?

Aug 29, 2007

Hi,How do I set the parameters of an SqlDataSource programatically?I have tried the following...dsDraftBudgetPI.SelectParameters.Add(new Parameter("@person_id", TypeCode.Int32, pID.ToString()));But that didn't work.  I already have the parameter defined at design time so I don't need to create one I just want to set its value and then bind it to a ListBox to display the result.Thanks,Scott 

View 3 Replies View Related

Problems With SqlDataSource Parameters

Nov 29, 2007

Hello!
 I am pulling my hair out over this latest problem. I'm trying to do a simple SQL SELECT statement by taking values from textboxes and inserting them into the WHERE part of the statement. I'm using MySQL and have to use ? prefix for all inserts, that I have working by the way. Now for some reason I cannot get the values from the text boxes to replace the prefixes! Here is my code:
 
 private void submitLogin(object source, EventArgs e) {
if (isPassword(password.Text.ToString()) && isEmail(username.Text.ToString()))
{
ValidateCustomer.SelectParameters["c_email"].DefaultValue = "fish";
ValidateCustomer.SelectParameters["c_password"].DefaultValue = "cakes";

test_label.Text = ValidateCustomer.SelectCommand.ToString();
}
else {
test_label.Text = "Username or Password Incorrect";
}
}

 <asp:SqlDataSource runat="server"
ID="ValidateCustomer"
ProviderName="System.Data.Odbc"
ConnectionString ="<%$ connectionStrings:MySQLDatabase %>"
SelectCommand ="SELECT * FROM customer_details WHERE (Customer_Email = ? AND Customer_Password = ?)">
<selectparameters>
<asp:parameter name="c_email" type="String" />
<asp:parameter name="c_password" type="String" />
</selectparameters>
</asp:SqlDataSource> 

 
I have omitted obvious code. The test label, when updated, shows the SQL statement but with the ?. I also use controlparameters that took the values directly from the textboxes but again this did not work. I'm really stuck with this one. Your help is much needed!
 
Cheers
Dan

View 7 Replies View Related

Output Parameters From An SqlDataSource

Feb 21, 2008

Hi all,I have a stored procedure which sets an output variable. How could I access this after calling an SqlDataSource.Select()? ThanksChris 

View 9 Replies View Related

SQLDataSource Parameters Question

Apr 10, 2006

Can you access the values within sqldatasource parameters
programmatically?  I have tried to reference them in the following
ways without any luck.



-SqlDataSource_Contacts.UpdateParameters("original_user_id")


-SqlDataSource_Contacts.UpdateParameters("original_user_id").ToString



-SqlDataSource_Contacts.UpdateParameters("original_user_id").DefaultValue



-SqlDataSource_Contacts.UpdateParameters("original_user_id").DefaultValue.ToString


-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id")


-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").ToString



-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").DefaultValue



-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").DefaultValue.ToString

View 7 Replies View Related

Programmatically Specify SqlDataSource Parameters

Apr 27, 2006

I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted.
What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])
Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?
Thank you very much for your help.

View 3 Replies View Related

SQLDataSource Update Using Parameters Not Working

Jul 10, 2006

I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast.  And then firing off the update command that contains that parameter from a button.  But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes.  All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list.  But it doesn't even change anything.  It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc.  It just won't change the data from the page when I try to.
 
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Any help is much appreciated.

View 4 Replies View Related

Using One SqlDataSource For Many DropDownLists, But Ability To Set Parameters?

Jan 3, 2007

If I have a lot of dropdowns of similar type but only differ in one field, is there a way to set parameters so that the dropdownlist can specify what the parameter values are?
 For example dropdowns: expenseaccount, incomeaccount, assetaccount
SqlDataSource:
SelectCommand="select accountid,accountname from accounts, accounttypest where accounts.accounttypeid=acounttypes.accounttypeid and accounttypes.accounttypename=? order by accountname"
<SelectParameters>
<asp:Parameter Name="accountTypeName" Type="string" />
</SelectParameters>
 -------
I would like expenseaccount to be able to specify "EXPENSE", incomeaccount to specify "INCOME", etc...
 Or is sqldatasource reusage bad practice?
(damn vs2k5 colors)

View 7 Replies View Related

Insert Data With Parameters And Sqldatasource

Apr 24, 2007

I have a sqldatasource in a wizard. When I click the finishbutton in the wizard I want to insert some values (from textboxes, dropdownlists in my wizard) into a mysql-database. For this I use parameters, I add some values in the design mode and some in codebehind. The parameters in codebehind are added perfectly but the other parameters in designmode (name, email) just inserts null. Why?  My datasource: <asp:SqlDataSource
ID="dsInsert"
InsertCommand="INSERT INTO tbltest (name, email, city) VALUES (?name, ?email, ?city)"
runat="server"
ConnectionString="<%$ ConnectionStrings:conn %>"
ProviderName="MySql.Data.MySqlClient">
<InsertParameters>
<asp:FormParameter Name="?name" ConvertEmptyStringToNull="true" Type="string" FormField="name" />
<asp:FormParameter Name="?email" ConvertEmptyStringToNull="true" Type="string" FormField="email" />
</InsertParameters>
</asp:SqlDataSource>   The codebehind:         protected void finishButtonClick(object sender, EventArgs e)        {            dsInsert.InsertParameters.Add("?city", TypeCode.Int32, city.SelectedValue);            dsInsert.Insert();        }

View 3 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 Pass Parameters From Code-behind

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

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

Setting Command Parameters For SQLDataSource

Dec 26, 2007

I have an SQLDataSource. The SQL is
 SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records.
e.Command.Parameters("@Printed1").Value = Nothing           'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False
What am I doing wrong???
Thanks
Craig

View 10 Replies View Related

Dynamically Adding New Parameters To SQLDataSource

Dec 28, 2007

CancelSelectOnNullParameterHi,Suppose I have a SQLDataSource with a
SelectCommand="SELECT * FROM STOCKS WHERE USER=@USER ORDER BY STOCK_NAME"
and @USER is set as a session parameter. No problem till this part, but in some postbacks, I wish to add a new WHERE criteria to my SQL, so I do:DSSQL.SelectParameters.Add("@STOCK_NAME", "ABC123");
DSSQL.SelectCommand = DSgvStoklar.SelectCommand.Replace(" ORDER BY ", " AND STOCK_NAME LIKE @STOCK_NAME+'%' ORDER BY ");
gridViewDS.DataBind();
and wish that my SelectCommand is modified and a new parameter is added.
The SQL is modified, however -I guess- new parameter is not added, so I get "Must declare the scalar variable "@STOCK_NAME" error.
How can I solve, any ideas?

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

Setting SqlDatasource Parameters From TextBox

Feb 17, 2008

I have a sqlDatasource with 3 parameters based on the input of 3 text boxes on the page. The datasource returns sales details for a company based on a from and to date. I am attempting to set the value of the 3 parameters in the Selecting event of the datasource control but I'm not getting any data back. If I set the values literally then I get data back. Also when I step through the code I can see the 3 parameters getting their values from the textboxes and the drop down list as they should. This is driving me insane as I'm new to .net and just can't see what is stopping me retrieving the data when using the form fields to set the datasources parameters. Below is the aspx and the code behind for the page. Thanks in advance for any help. aspx..... <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="LabelSales.aspx.cs" Inherits="LabelSales" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">        <asp:SqlDataSource ID="SqlDataSourceSales" runat="server" EnableCaching="false"        ConnectionString="<%$ ConnectionStrings:streetwisedigitalConnectionString %>"        SelectCommand="DL_GET_SALES_BY_LABEL" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSourceSales_Selecting" >        <SelectParameters>            <asp:Parameter Name="fromDate" />            <asp:Parameter Name="toDate" />            <asp:Parameter Name="label" />        </SelectParameters>    </asp:SqlDataSource>        <asp:SqlDataSource ID="SqlDataSourceLabels" runat="server"         SelectCommand="select label_id, label_name from dl_label order by label_name asc"        ConnectionString="<%$ConnectionStrings:streetwisedigitalConnectionString%>">    </asp:SqlDataSource>    <div>        <table>            <tr>                <td>Start Date:&nbsp;</td>                <td>                    <asp:TextBox ID="FromDate" Runat="server" Width="70"/>                    <asp:Button ID="btnFrom" Runat="server" Text="..." UseSubmitBehavior="false" />                </td>            </tr>            <tr>                <td>End Date:&nbsp;</td>                <td>                    <asp:TextBox id="ToDate" Runat="server" Width="70" />                    <asp:Button ID="btnTo" Runat="server" Text="..." UseSubmitBehavior="false" />                </td>            </tr>            <tr>                <td>Label:&nbsp;</td>                <td>                    <asp:DropDownList ID="LabelList" Runat="server"                     DataSourceID="SqlDataSourceLabels"                     DataTextField="label_name" DataValueField="label_name">                    </asp:DropDownList>                </td>            </tr>            <tr>                <td colspan="2" align="right">                    <asp:Button ID="Button1" OnClick="SubmitButton_OnClick" Text="View Sales" runat="server" />                </td>            </tr>        </table>        <p>            <asp:GridView ID="GridViewSales" runat="server"                 DataSourceID="SqlDataSourceSales"                 ShowFooter="True"                 AllowSorting="True"                 AutoGenerateColumns="False"                 OnRowDataBound="GridViewSales_RowDataBound" EmptyDataText="No data to display.">                <Columns>                    <asp:BoundField DataField="cat_no" HeaderText="Cat No" />                    <asp:BoundField DataField="artist" HeaderText="Artist" />                    <asp:BoundField DataField="title" HeaderText="Title" />                    <asp:BoundField DataField="remix" HeaderText="Remix" />                    <asp:BoundField DataField="qty" HeaderText="Sold" />                    <asp:boundfield datafield="commission"                        HtmlEncode="False"                         dataformatstring="{0:F2}"                          headertext="Commission">                        <ItemStyle HorizontalAlign="Right" />                    </asp:boundfield>                </Columns>            </asp:GridView>        </p>    </div></asp:Content>code behind...using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class LabelSales : System.Web.UI.Page{    int totalSold;    decimal totalCommssion;    protected void GridViewSales_RowDataBound(object sender, GridViewRowEventArgs e)    {        // add column totals to gridview        if (e.Row.RowType == DataControlRowType.DataRow)        {            totalSold += (int)DataBinder.Eval(e.Row.DataItem, "qty");            totalCommssion += (decimal)DataBinder.Eval(e.Row.DataItem, "commission");        }        // display the totals        else if (e.Row.RowType == DataControlRowType.Footer)        {            e.Row.Cells[0].Text = "<b>Total</b>";            e.Row.Cells[4].Text = totalSold.ToString();            e.Row.Cells[5].Text = totalCommssion.ToString("f2");        }    }        protected void Page_Load(object sender, EventArgs e)    {                   }    protected void SqlDataSourceSales_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {        // *** This method does not work ***        e.Command.Parameters[0].Value = FromDate.Text;        e.Command.Parameters[1].Value = ToDate.Text;        e.Command.Parameters[2].Value = LabelList.Text;        // *** This method works! ***        e.Command.Parameters[0].Value = "02/01/2007";        e.Command.Parameters[1].Value = "02/01/2008";        e.Command.Parameters[2].Value = "Fat!";    }    protected void SubmitButton_OnClick(object sender, EventArgs e)    {        SqlDataSourceSales.Select(DataSourceSelectArguments.Empty);    }}  Many thanks  Simon 

View 3 Replies View Related

How Do I Pass VB Variables To The Asp Parameters Of An SqlDataSource?

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

Need To Increment Numeric Parameters With SqlDataSource- Help!

May 15, 2008

hi,
 i am using a sqldatasource with a formview that has three stored procedures associated with it. when i update a record in my formview, i need to increase two parameters that are displayed as labels in the formview by their value plus one.
i can't figure out how to increase the value in the stored procedure, or using the codebehind, or with some script in the html. any solution works for me. any suggestions?
if i can figure out how to increment one of my fields, i can use the same logic for the other. below is an example of what i am trying to do.
in my formview i have three fields: Count, Rate, Price. Count is a label, Rate and Price are editable textboxes. once i commit the update for the values of Rate and Price, i need the value of Count to increase by 1, and then have that value updated in the data base along with the new values for Rate and Price.
is there a way to do this in the codebehind, or in my stored procedure?
i am using Asp.Net 2.0, vs2005, and sql server.
any help is greatly appreciated. thank you.

View 2 Replies View Related

SQLDatasource, Stored Procs && Parameters

Mar 1, 2006

   Hi,
I'm having an issue trying to send parameters to a SQLDatasource object.   I"m creating it in code (and I need it to stay there)    I have determined that the stored procs called with no parameters work fine.   I start having problems when I have any parameters.    Interestingly, it doesn't crash.  It just fails to run (the onselected event never fires). 
dsUser is a protected SQLDatasource property
repMain is a repeater on the aspx sheet
lstUser is a stored proc with one varchar(10) parameter called cSort
Here is my current code that will result in no called to StatementCompleted and no data.  Pull cSort parameter from the proc and it works just fine.  Obviously I'm setting it wrong.
string cSelect = "dbo.lstUser";
dsUser = new SqlDataSource();
dsUser .ConnectionString = GetConStr();
dsUser .SelectCommand = cSelect;

dsUser .SelectParameters.Add( "cSort", TypeCode.String, "" );

dsUser .Selected += StatementCompleted;
repMain.DataSource = dsUser ;
 
TIA,
Nick Hustak

View 6 Replies View Related

UniqueIdentifier Fields As Parameters For SQLDataSource

Mar 7, 2006

Whn I use a UniqueIdentifier field as a parameter for SQLDataSource the parameter type defaults as an object. I just want to use the string value of the field.How do I do this?Thanks.

View 7 Replies View Related

SQLDatasource && Schema Refreshing (with Parameters)

Apr 9, 2006

Hi,
I'm having serious issues trying to refresh a schema in a SQLDatasource.  It is hooked to a stored procedure that takes two varchar(39) parameters.   The default parameters in this case are '%'. Note I am working in the designer.
If I set it up as a stored proc, I can't even get the 'test query' to run in the builder.
If I set it up as a select statement, ala 'exec <procname> @p1, @p2', the 'test query' will run.
In neither case will hitting refresh schema work.  It returns 'Invalid length parameter passed to the substring function'
The stored proc is nothing special, simply returning a select based upon the parameters.
Thanks,
Nick H

View 2 Replies View Related

Using SqlDatASource Programmatically - Output Parameters

May 13, 2006

I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues.
The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key:
ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY()
This works fine when run from Sql Server Management Studio and @NewID has the correct value.
My data access code is roughly as follows:
dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try   Dim rv As Integer = dsrc.Insert()   newid = parms("NewID")   Return newidCatch ex As Exception   Return -1End Try
The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value.
Has anyone tried to do this and can tell me what I am doing wrong?
Jon
 

View 8 Replies View Related

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

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

Sqldatasource Using Session Data In Select Parameters

Jan 12, 2007

I'm having trouble using session data in my select parameters.  If I manually plug a value into the selectcommand or create a hard coded value using a parameter it works, but if I try to use the session data the query pulls no results.  I know the session data is set because I print the value at the top of the page, but for some reason it's not getting passed to the sessionparameter??? 
 This is the datasource code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:flautoconnstr %>"
SelectCommand="SELECT * FROM [tblVehicles] WHERE ([profileid] = @profileid)">
<SelectParameters>
<asp:SessionParameter Name="profileid" SessionField="profileid" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
 
This is the line I use to set the session data.
Session["profileid"] = myQuote.Profileid;

View 1 Replies View Related

How To Use Comma Separated Lists With Parameters In Sqldatasource?

Feb 24, 2007

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report.  The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables.  The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results.  They are then displayed using the gridview control.  It actually works!
Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this?  I'm not very experienced with asp nor sql server and have been struggling with this new challenge.  I really could use pointers as to whether there's an easy way to handle this.  In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically.  I get the impression that with all this ASP.NET fanciness, there has to be a better way?
 If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express. 
 Thanks in advance for any replies, they would be GREATLY appreciated :)

View 1 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related







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