Of Multiple Parameters, SqlDataSource And Text Boxes

Feb 12, 2006

Hi,
Hope if someone can help me here. Keep in mind I an fairly new to .NET and SQL and am learning to break my MS Access habit :)

I have a web form that is using a SqlDataSource and a FormView control. In addition to this I have 2 text boxes. What I am trying to do is display results in the FormView based on what a user types into one of the Text Boxes (one or the other…Not both)
 
The SELECT statement in the SqlDataSource looks like this in concept.
SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @Field1) AND (Field2 IS NULL)
OR  (Field2 = @Field2) AND (Field1 IS NULL)
 
I have the two text boxes pointing at the parameters (@Field1 and @Field2) so in theory I would expect that when a user populates one of the text boxes and clicks a button to databind the FormView it would display a record matching that criteria…. But it’s not all I get is a blank/missing FormView.
I tried different variations on the SQL statement and tried using  = ''  instead of IS NULL but still the same results.
However, if I populate one text box with a value that I know is not in my table and populate the other with a value of which I know exists in my table is…It works.
What am I missing?


 

View 13 Replies


ADVERTISEMENT

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Passing Multiple Parameters To Stored Procedure Using SqlDataSource

Oct 9, 2007

Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan 

View 2 Replies View Related

Text Boxes

Jan 29, 2008

Hi,
I am doing a report in which i have to create Wall chart kind of layout and for that i have to place Text Boxes on the
report at run time.Now In my Layout i have some Text Boxes which are going Out of the page(Starts from page 1 and Ending on page 2)but when i am looking my report in the Print Preview the Report Viewer is shifting the entire Text Box to Page 2. Can i do something to print the report as they are seen in the Normal layout.

Can any one help me out with this.......
Amit

View 6 Replies View Related

Concatenating 2 Text Boxes

Feb 13, 2006

Hi,

How do we concate a normal text with an underlined text?

eg: I would like to concate :

textbox1 = my email address:

and

textbox2 = xxx@hotmail.com

into a new textbox3.

expected result: textbox3 : my email address:xxx@hotmail.com

Really appreciate your help.

Thanks

View 4 Replies View Related

SelectCommand Multi Text Boxes

Nov 7, 2007

I would like a form that has 3 text boxes. These 3 boxes are going to be used for entering search criteria. The first box is for searching a field called CasePK. The second is for searching a field called LinePK and the third is for searching a field called DOS. DOS is a date field. The other two are text fields.I am using a SQLDATASOURCE and a Gridview. What will the SelectCommand look like so that I can search the 3 fields using the data from the textboxes?

View 1 Replies View Related

Problems With Null Text Boxes When Doing Like Search

Apr 10, 2007

I have three text boxes firstname, lastname, dob.  It is set up to do a like search on the the text boxes.  If a user wants to do a search for just the last name it will not work.  You must type in something in all three boexes.  Doesn't anybody know how to correct  this.  Here is the code.   Thanks.
 
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
GridView1.Visible = False
GridView2.Visible = False
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
GridView1.DataSourceID = "SqlDataSource1"
GridView1.DataBind()
GridView2.DataSourceID = Nothing
GridView2.DataBind()
GridView1.Visible = True
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
GridView2.DataSourceID = "SqlDataSource2"
GridView2.DataBind()
GridView1.DataSourceID = Nothing
GridView1.DataBind()
GridView2.Visible = True
End Sub
 
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Meditech Radiology Numbers</title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div id="master_headertop"><asp:HyperLink ID="HomePageLink" runat="server" ImageUrl="~/images/headertop_img.jpg" /></div>
 
<div id="master_menu">
<div class="sidebar">
<div class="sidebarheader">
Demographic search</div>
<div id="master_sidebarSamples" class="sidebarcontent">
&nbsp;
<table>
<tr>
<td style="width: 100px">
<asp:Label ID="Label1" runat="server" Text="First"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="FirstName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px; height: 26px;">
<asp:Label ID="Label2" runat="server" Text="Last"></asp:Label></td>
<td style="width: 100px; height: 26px;">
<asp:TextBox ID="LastName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
<asp:Label ID="Label3" runat="server" Text="DOB"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="DOB" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Button ID="Button1" runat="server" Text="Search" /></td>
</tr>
</table>
</div>
</div>
<div class="sidebar">
<div class="sidebarheader">
Search by Mr#</div>
<div id="master_sidebarWalkthroughs" class="sidebarcontent">
&nbsp;
&nbsp;<table>
<tr>
<td style="width: 100px">
<asp:Label ID="Label5" runat="server" Text="MR #"></asp:Label></td>
<td style="width: 100px">
<asp:TextBox ID="MR" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Button ID="Button2" runat="server" Text="Search" /></td>
</tr>
</table>
</div>
</div>
&nbsp;<br />
&nbsp; &nbsp; &nbsp;
</div>
<div id="master_content" style="height: 399px">
 
&nbsp;
&nbsp;&nbsp;&nbsp;<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="LAST" HeaderText="LAST" SortExpression="LAST" />
<asp:BoundField DataField="FIRST" HeaderText="FIRST" SortExpression="FIRST" />
<asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" />
<asp:BoundField DataField="RAD #" HeaderText="RAD #" SortExpression="RAD #" />
<asp:BoundField DataField="MR#" HeaderText="MR#" SortExpression="MR#" />
<asp:BoundField DataField="EXAM#" HeaderText="EXAM#" SortExpression="EXAM#" />
<asp:BoundField DataField="EXAM DATE" HeaderText="EXAM DATE" SortExpression="EXAM DATE" />
<asp:BoundField DataField="EXAM NAME" HeaderText="EXAM NAME" SortExpression="EXAM NAME" />
<asp:BoundField DataField="ORDER DOC" HeaderText="ORDER DOC" SortExpression="ORDER DOC" />
<asp:BoundField DataField="Column 9" HeaderText="Column 9" SortExpression="Column 9" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RADConnectionString2 %>"
SelectCommand="SELECT * FROM [Full File through 2006] WHERE (([FIRST] LIKE '%' + @FIRST + '%') AND ([LAST] LIKE '%' + @LAST + '%') AND ([DOB] LIKE '%' + @DOB + '%'))" OnSelecting="SqlDataSource1_Selecting">
<SelectParameters>
<asp:ControlParameter ControlID="FirstName" DefaultValue=" " Name="FIRST" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="LastName" DefaultValue=" " Name="LAST" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="DOB" DefaultValue=" " Name="DOB" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="LAST" HeaderText="LAST" SortExpression="LAST" />
<asp:BoundField DataField="FIRST" HeaderText="FIRST" SortExpression="FIRST" />
<asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" />
<asp:BoundField DataField="RAD #" HeaderText="RAD #" SortExpression="RAD #" />
<asp:BoundField DataField="MR#" HeaderText="MR#" SortExpression="MR#" />
<asp:BoundField DataField="EXAM#" HeaderText="EXAM#" SortExpression="EXAM#" />
<asp:BoundField DataField="EXAM DATE" HeaderText="EXAM DATE" SortExpression="EXAM DATE" />
<asp:BoundField DataField="EXAM NAME" HeaderText="EXAM NAME" SortExpression="EXAM NAME" />
<asp:BoundField DataField="ORDER DOC" HeaderText="ORDER DOC" SortExpression="ORDER DOC" />
<asp:BoundField DataField="Column 9" HeaderText="Column 9" SortExpression="Column 9" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:RADConnectionString3 %>"
SelectCommand="SELECT * FROM [Full File through 2006] WHERE ([MR#] = @column1)">
<SelectParameters>
<asp:ControlParameter ControlID="MR" Name="column1" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
<br />
<br />
&nbsp; &nbsp;
 
</form>
</body>
</html>

View 18 Replies View Related

Toggling Visibility Between Two Text Boxes In Ssrs

Oct 9, 2012

how visibility can be toggled between two textboxes in ssrs? For example if textbox1 is visible and textbox2 is hidden initially and textbox2 should be visible when clicked on textbox1 and textbox1 should go invisible when textbox2 is shown... and then again when clicked on textbox2, textbox1 should be visible and textbox2 should go invisible.In a short, it is like switching visibility between 2 textboxes... A click on first textbox should display the second textbox and another click on second text box should display first text box.I cannot provide parameter as it is not there in the requirement. how to toggle the text within a single text box to fulfill above requirement...

View 1 Replies View Related

Allowing For Blank Fields In Date Text Boxes

Jun 27, 2005

Several textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown).  However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).My question is as follows:  How is it possible to leave a date textbox field blank, and either send 000000 or spaces to the SQL Server 2000 database datetime field?Any advice/insight is appreciated!  Rob

View 2 Replies View Related

How To Link Combo Boxes And A Text Box In A Datagrid View?

Mar 11, 2008

Hi folk..
can some body help me with this problem..???

I have a grid view on a form which has got colums which are set as data combo boxes. and a third column whish is set to a text box. all the 3 controls on the datagrid view are bound with sql server table.
they are asociated with two saparate tables within 1 database...
now what i want is that the user of the application can select either combo box 1 of teh 2nd combo box.
and automatically the syncronize and also the text column asociates with related data..

for example

Country City Pincode
India vasco 403802

now while flling the grid vew.. the user can select either country or city.. automatically
both syncronize with related data and so does the text box.


plz help me with the code.. as well..


thanks a million

View 1 Replies View Related

Reporting Services :: Dynamic Positioning Of Text Boxes In SSRS

Sep 30, 2015

I have a user table with Label and value fields where i would like to control the display of the text boxes based on the values selected in my user table.Can we adjust the text box positions dynamically based on the user table values.

Ex: Table

Label1 Field1 Label2  Field2  Label3 Field3
ID       100                           Dept   Sales

Report Design :

Label1: ID    Field1:100
Label2:          Field2:
Label3:Dept   Field3:Sales

Expected Result :

Label1: ID    Field1:100
Label3:Dept   Field3:Sales

In my table i don't have values for Label 2 & Field 2 , can we adjust the spacing conditionally to be utilized by Label3 & Field3 in SSRS.

Note : Above mentioned data is just for an example and in my actual report i can have more than 3 columns and report is looking ugly with all the spacing if i don't have data in all the fields.

View 9 Replies View Related

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

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

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 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







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