.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

sqldatasource parameters from code

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :ASP.Net
 

Hi I'm trying to pass default value to a sql parameter which is

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    SelectCommand="SELECT * FROM [Activ] WHERE ([DayNum] = @DayNum)">
    <SelectParameters>
        <asp:Parameter Name="DayNum" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>
and the Page_Load goes like this
Int32  MyDay;
            MyDay = (Int32)DateTime.Today.DayOfWeek;
            MyDay = MyDay + 1;
            SqlDataSource1.SelectParameters["DayNum"].DefaultValue = MyDay;
Intellisense says that I can´t convert int into string underlining red MyDay, I get it but, if I use ToString
then the SQL statement won't work since DayNum is int.
 
How can I work this out? Thank you


 




View Complete Post


More Related Resource Links

Update Parameters with SQLDataSource and VB code behind

  

I have been going around the houses for several days now. I have an sql datasource that uses a stored Proc  to uodate.  I set the updateParameters values from vbcode behind

        Me.SqlAdministration.UpdateParameters("Licence").DefaultValue = Me.TxtLicence.ToString
        Me.SqlAdministration.UpdateParameters("CompanyName").DefaultValue = CType(FormView1.FindControl("CompanyNameTextBox"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("Status").DefaultValue = CType(FormView1.FindControl("status"), DropDownList).SelectedValue.ToString
        Me.SqlAdministration.UpdateParameters("EmailAccounts").DefaultValue = CType(FormView1.FindControl("txtEmailAccounts"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("EmailGeneral").DefaultValue = CType(FormView1.FindControl("txtEmailGeneral"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("TelAccounts").DefaultValue = CType(FormView1.FindControl("txtTelAccounts"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("TelGeneral").DefaultValue = CType(FormView1.FindControl("txtTelGeneral"), TextBox).Text
        Me.SqlAdministration.UpdateParameters("CompanyAddress").DefaultValue = CType(FormView1.Find

SqlDataSource and Parameters query

  

Hi everyone,

I'm on the edge wondering is it possible ? I have got 2 questions. Please help me out.

1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like

SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.

Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).

2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query

(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).

If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.  I am stucked and desparately looking for help. Thanks all.


Mapping SqlDataSource Parameters to Formview with Multiple DataKeys

  
Hi I have:a FormView that is linked to SqlDataSource1a Repeater control (inside the formview) that is linked to SqlDataSource2Thus representing a simple "1 to many" or "parent/child" relationship in the database!SqlDataSource1 is simple in that it just retrieves a straight set of records from a table and they are displayed back in the FormView (nothing special happening there). SqlDataSource2 has parameters that  filter the contents of the repeater based on the record that is shown in the Formview. This would normally be simple enough to set up by setting the parameter to the formview.selectedvalue property.HOWEVER - THE PROBLEM ARISES...The formview has multiple DataKeyNames because the table that the SqlDataSource1 links to has a compound key. So to filter the Repeater correctly SqlDataSource2 needs to map 3 parameters to 3 data key values in the formview.Any ideas how to do this?I've tried setting the SqlDataSource2 parameters to:<SelectParameters>   <asp:ControlParameter ControlID="FormView1" Name="Parameter1" PropertyName="DataKey(0)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter2" PropertyName="DataKey(1)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter3" PropertyName="DataK

Mapping SqlDataSource Parameters to Formview with multiple DataKeys

  
Hi I have:a FormView that is linked to SqlDataSource1a Repeater control (inside the formview) that is linked to SqlDataSource2Thus representing a simple "1 to many" or "parent/child" relationship in the database!SqlDataSource1 is simple in that it just retrieves a straight set of records from a table and they are displayed back in the FormView (nothing special happening there). SqlDataSource2 has parameters that  filter the contents of the repeater based on the record that is shown in the Formview. This would normally be simple enough to set up by setting the parameter to the formview.selectedvalue property.HOWEVER - THE PROBLEM ARISES...The formview has multiple DataKeyNames because the table that the SqlDataSource1 links to has a compound key. So to filter the Repeater correctly SqlDataSource2 needs to map 3 parameters to 3 data key values in the formview.Any ideas how to do this?I've tried setting the SqlDataSource2 parameters to:<SelectParameters>   <asp:ControlParameter ControlID="FormView1" Name="Parameter1" PropertyName="DataKey(0)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter2" PropertyName="DataKey(1)" />   <asp:ControlParameter ControlID="FormView1" Name="Parameter3" PropertyName="DataK

SqlDataSource with a lot of parameters

  
Hi everyone,I have asked similiar question before where I have two control on the page header says "username" and "gender". User can select both the control or either one and the page will execute searching based on the criteria supplied. So if username suppliedSELECT * FROM [this_table] WHERE username = @username and if gender suppliedSELECT * FROM [this_table] WHERE gender = @gender and if both supplied SELECT * FROM [this_table] WHERE username = @username AND gender = @genderI know this sounds not very complicated but in my case, I am having like more than 10 controls up there. It is something like username, gender, age, date of brith, title, father name, frst name, last name, blur blur blur and it's a lot then I started to lost. if i were to use if else caluse on code-behind and produce query, it would end up writing a lot of if-else and if i use stored procedure (with if-else) then my stored procedure would contains a lot of if-else statement. I am fairly new to this concept and if I'm on the wrong track, please correct me. Is there any better way to save my hair without actually going through each if-else statement to build the query ? Thanks all.p.s. I'm using GridView and SqlDataSource command to display the result back on .aspx page.

SqlDataSource : in what order do parameters get fed to stored proc?

  
Hello, I'm wondering if someone can explain how a SqlDataSource determines the order in which parameters are fed to a stored procedure. I'm not able to find clear documentation.So suppose a proc has: CREATE PROCEDURE  updateOneCar@CarActiveFlagCheck int, @CommentsForCar varchar(1500) = NULL, @manufacturerID int...and then suppose the ASP.NET page has the manufacturerID supplied by a querystring, and has a FormView as follows: <asp:FormView ID="FormView1" runat="server" DataSourceID="SQL01"          DataKeyNames="manufacturerID" EditRowStyle-Wrap="False" RenderOuterTable="False">         <EditItemTemplate>         <div>             manufacturerID: <%# Eval("manufacturerID")%>             <br />             Is my Car on?: <%# Eval("carFlag")%>             <br />             Is my car Active Flag:             <asp:CheckBox ID="CarActiveFlagCheckBox" runat="server"              

SqlDataSource, FormView, and Update with stored procedure in code behind

  
Hello, Using a FormView with a SqlDataSource, I'm attempting to Update data by calling a stored proc in code behind. I was having trouble getting parameters using Update Parameters in the SqlDataSource, but found a working solution by coding the parameters. The problem now is I'm getting an "Updating is not supported by data source 'XYZ' unless UpdateCommand is specified'. I saw some previous posts on the forums, but didn't find them very enlightening. 

Input parameters from Workflow No-Code Or Xaml Only

  
I have many activities and a customized workflow Only Xaml and I can not pass a value to a property of a custom activity, following the example of Workflow1.xoml   <pre lang="x-xml"><SequentialWorkflowActivity x:Name="Workflow1" xmlns:ns0="clr-namespace:CustomActivities;Assembly=CustomActivities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/workflow"> <ns0:ReadLineActivity x:Name="readLineActivity1" InputText="" /> <ns0:WriteLineActivity x:Name="writeLineActivity1" OutputText="{ActivityBind readLineActivity1,Path=InputText}" /> </SequentialWorkflowActivity> based on this workflow I pass a value for the property InputText activity readLineActivity1   Thanks a lot!

All parameters null in Update of SQLDataSource?

  

Hi All,

I have a FormView which is bound to an SQLDataSource. When a user clicks on a save button the form, I need to write any changes made to the FormView back to the table.

I have a Stored Procedure on my MSSQL 2005 database that should be executed when the user clicks on the save button.

It appears the Update is firing, but when I trap the DbCommand object in the SQLDataSource's 'Updating' event, all of the parameters that should be passed to the Stored Procedure are coming through as NULLs. Consequently, the Stored Procedure isn't updating anything.

In the click event of the save button I'm explicitly firing the SQLDataSource's Update method:

this.SqlDataSource4.Update();


The parameters in the SQLDataSource <UpdateParameters></UpdateParameters> section appear to be correct.

Can anyone give me an idea on what to investigate to figure out why it appears the Update is only picking up NULLs?

Many thanks in adavnce,

pt


How to add Select Control parameters in Code Behind

  

I have a need to add my page controls dynamically in code behind.

I do well, adding a dropdownlist, a SQL datasource to populate it, setting it to autopostback=true, and adding a gridview and its SQL datasource.

What I'm unsure about is how to programmatically refer to the SelectParameter, being a control (the DropDownList), and its selected item.

any ideas? 


can you use if else in sqldatasource if else parameters?

  

I am trying to check whether a username exists in my database before inserting the new row & display a message to the user if it does.

On my SqlDataSource my InsertCommand looks like this:

InsertCommand="IF NOT EXISTS (SELECT * FROM Users WHERE UserName = @UserName) BEGIN INSERT [Users] ([UserName], [FirstName], [LastName], [City], [State], [Country], [CompanyName], [Active], [PasswordHash]) VALUES (@UserName, @FirstName, @LastName, @City, @State, @Country, @CompanyName, @Active, @PasswordHash) END"

 

This works as expected: If the username is unique, the row is created.

If the username already exists, the row is not added.

 

The problem is, if the row already exists, the insert is rejected but the user is not notified.

So my question is, is there a way I can notify the user?

Maybe something like: InsertCommand = "If Not Exists (select....) Begin Insert... Else Notify User Somehow End

 

This is a basic database, I'm not using the built-in membership provider nor will be.

 

Thanks!

 


SqlDataSource from code

  

Hi,

I have a listbox controll and i want to bind it with an SqlDataSource. SqlDataSource must be declared in the code behind but i don't know where declare it(page init,page load...) and how to add sql parameter dynamically(parameters are the selected values of listbox, so they are multiple).


Help please



Pls help to post the code to get data from sqldatasource into a list

  

I have this code but dunno how to get the data from the existing sqldatasource1 into the list. Pls help to post the code. 

ie the code for this line:        ' Get Data from Database and Fill DataReader and we will pass values to List

The sqldatasource1 returns only one field of data called holiday which is a date field. Thanks

 

<%@ Page Title="" Language="VB" MasterPageFile="~/00_MasterPage.master" %>

<script runat="server">

    Private Function GetPublicHolidays() As List(Of DateTime)


        Dim list As New List(Of DateTime)()

        ' Get Data from Database and Fill DataReader and we will pass values to List

    End Function

   
   
    </script>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>


How to change this code to get data from sqldatasource control instead of a list

  

I have googled a relevant code for my control on ASP.net page but it get data from a har code list. How to modify the code so it get the data from the sqldatasource which I have already built instead of getting data from the list?

Thanks

protected void calDT_DayRender(object sender, DayRenderEventArgs e)

    {

        if (dtholidays.Contains(e.Day.Date))

        {

            e.Day.IsSelectable = false;

        }

    }

 

private List<DateTime> GetPublicHolidays()

    {

        List<DateTime> list = new List<DateTime>(); 

        //populate from database or other sources

        list.Add(new DateTime(2010, 01, 01));

        list.Add(new DateTime(2010, 02, 02));

        list.Add(new DateTime(2010, 02, 14));

        list.Add(new DateTime(2010, 03, 02));

        list.Add

SqlDataSource WHERE Parameters - QueryString and Default value not working correctly?

  

This is really easy, but I can't seem to get this working correctly...

 

When using SqlDataSource and press on Configure Data Source, I have selected my data source.

I have selected * all records and pressed the WHERE button  (so it starts to bulld a query).

 

 

I have set the following values:

 

Column: NewOrUsed (only contains 1 of 2 values, either "New" or "Used")

Operator: =

Source: QueryString

QueryString Field: (Eval("NewOrUsed").ToString)

Default Value: New

 

 

However it doesn't seem to work, always returns no records even though I know there are a number of records matching "New"

 

 

What am I doing wrong?


c# code to create an array of parameters for oracle stored procedure

  

Hi

Working on a c# project that has oracle as backend.  I have problem creating a function in my codebehind page to create an array of paramenters for oracle stored procedure. for example i have the follwoing code for ms sql server...

SqlParameter[] parm = 

         {

                         DataCon.createSqlParameter ("@id", DataCon.DBNullIfBlank(txtid.Text.ToString()), SqlDbType.Char,9 ) ,

                         DataCon.createSqlParameter ("@FName", DataCon.DBNullIfBlank(txtFName.Text.ToString()), SqlDbType.VarChar,14),

                         DataCon.createSqlParameter ("@MI", DataCon.DBNullIfBlank(txtMI.Text.ToString()), SqlDbType.Char,1),

                                    }

 For the same in oracle I am using the followin

How to read parameters from URL and put them into HTML code?

  

I am writing an "external link exit page" where anytime a user clicks on an external link on my site, it navigates to a page exit.html that says "Please note that you are leaving our website, and therefore we are not responsible for the content of the website you are about to visit." I was thinking of puting the destination URL as a parameter, so for example a section of links on my website might look someting like this:

 

<h3>Other Sites You Might Find Useful</h3>
<a href="exit.html?url=http://www.google.com">Google</a><br />
<a href="exit.html?url=http://www.yahoo.com">Yahoo!</a><br />
<a href="exit.html?url=http://www.weather.com">weather.com</a><br />

 

So in exit.htm I have a parameter called url that needs to be decoded and placed in my HTML source. After I display the warning message I want to give the user the option to either go back or continue to the externall url.

 

<h3>You are now leaving our website. Please be advised that we are not responsible for the content of the website you are about to visit. </h3>
<h4>Are you sure you want to continue?</h4>

<a href="[URL parameter]">Yes! Continue to [URL parameter]</a><br /> <a href="javascri
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend