Or Statement In A FilterExpression
May 31, 2007
I have a filter expression and I have 2 data types one is an int and the other is a date format. I want to setup 2 textboxes to search for either the int or the date. I have the FilterExpression setup as follows:
FilterExpression="PROJECT_CODE = '{0}' or PROJECT_DATE L= '{0}'"<FilterParameters>
<asp:ControlParameter ControlID="TextBoxProjectCode" Name="PROJECT_CODE" PropertyName="Text" Type="Int16" />
<asp:ControlParameter ControlID="TextBoxProjectDate" Name="PROJECT_DATE" PropertyName="Text" /></FilterParameters>
I can take out one or the other and run the search and return results, but can not do it with both. Is there a way to do this and what do I need to do to make it work?
View 3 Replies
ADVERTISEMENT
Aug 25, 2006
I am new to ASP. I am trying to use "Filterexpression".
FilterExpression="(Address_no >= '@Startno') AND (Address_no <= '@Endno')" >
I could not get it work. Could someone help me? Here is my code.
__________________________________________________________________________________
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="FarmedStreet.aspx.vb" Inherits="FarmedStreet" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvFarm" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
CellPadding="3" CellSpacing="2" DataKeyNames="StreetID" DataSourceID="dsfarm"
Font-Bold="False" Font-Names="Arial" Font-Size="11pt" PageSize="5">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<Columns>
<asp:CommandField ShowSelectButton="True">
<ItemStyle ForeColor="#00C000" />
</asp:CommandField>
<asp:BoundField DataField="StreetID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="StreetID" />
<asp:BoundField DataField="StartNo" HeaderText="StartNo" SortExpression="StartNo" />
<asp:BoundField DataField="EndNo" HeaderText="Endno" SortExpression="EndNo" />
<asp:BoundField DataField="Street" HeaderText="Street" SortExpression="Street" />
<asp:BoundField DataField="Zip" HeaderText="Zip" ReadOnly="True" SortExpression="Zip" />
<asp:CheckBoxField DataField="IsFarming" HeaderText="?" SortExpression="IsFarming" />
<asp:CommandField ShowEditButton="True">
<ItemStyle ForeColor="#00C000" />
</asp:CommandField>
</Columns>
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="dsfarm" runat="server" ConnectionString="<%$ ConnectionStrings:cnFarming %>"
SelectCommand="SELECT __Street.StreetID, __Street.Street, __Street.ZipID, __Street.IsFarming, __Street.StartNo, __Street.EndNo, __Zip.Zip FROM __Street INNER JOIN __Zip ON __Street.ZipID = __Zip.ZipID WHERE (NOT (__Street.StartNo IS NULL)) AND (NOT (__Street.EndNo IS NULL))">
</asp:SqlDataSource>
<asp:Button ID="btnResident" runat="server" BackColor="#00C000" Enabled="False" Font-Bold="True"
ForeColor="Yellow" Text="Resident" />
</div>
<asp:SqlDataSource ID="dsResidentA" runat="server" ConnectionString="<%$ ConnectionStrings:cnFarming %>"
SelectCommand="SELECT ProspectID, FName, LName, SAddress, SCity, SState, SZip, MAddress, MCity, MState, MZip, Bed, Bath, [S FT], phone, City, Address_no, Street, CatID, PropID, StreetID, ZipID, Apt FROM __Prospect WHERE (StreetID = @StreetID)"
FilterExpression="(Address_no >= '@Startno') AND (Address_no <= '@Endno')" >
<SelectParameters>
<asp:ControlParameter ControlID="gvFarm" Name="StreetID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="gvFarm" Name="Startno" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="gvFarm" Name="Endno" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="dsResidentA" PageSize="5">
<Columns>
<asp:BoundField DataField="ProspectID" HeaderText="ProspectID" InsertVisible="False"
SortExpression="ProspectID" />
<asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" />
<asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" />
<asp:BoundField DataField="Address_no" HeaderText="Address_no" SortExpression="Address_no" />
<asp:BoundField DataField="Apt" HeaderText="Apt" SortExpression="Apt" />
<asp:BoundField DataField="PropID" HeaderText="PropID" SortExpression="PropID" />
</Columns>
</asp:GridView>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</form>
</body>
</html>
View 1 Replies
View Related
Jun 6, 2008
I have a CheckBoxList which is bound to a SQLDataSource and populated with a list of courses. Under this I have a calendar control which is bound to another SQLDataSource control. When the page is first loaded this is empty because no courses are selected. I'd like it so that the user can select one or more course from the CheckBoxList and the appropraite events are loaded into the calendar control bound to the second SQLDataSource. I've tried using FilterExpression but this seems to work on the basis of a complete resultset being loaded and then filtered-out using the FilterExpression. I'm starting with no records and would like to Filter-In data from the database.
Is this possible using FilterExpression?
View 3 Replies
View Related
Aug 4, 2006
I have a some search components to filter a GridView follows:
<asp:DropDownList ID="ddFilterType" runat="server" ToolTip="Select field to search"> <asp:ListItem Selected="True">(ALL)</asp:ListItem> <asp:ListItem>SKU</asp:ListItem> <asp:ListItem>Vendor Name</asp:ListItem> <asp:ListItem>Item Name</asp:ListItem></asp:DropDownList><asp:TextBox ID="txtSearchFilter" runat="server"></asp:TextBox><asp:LinkButton ID="btnSearchFilter" runat="server">Filter</asp:LinkButton>
and C# code as follows:
protected void Page_Load(object sender, EventArgs e){ if (IsPostBack) { if (ddFilterType.SelectedValue != "ALL") { dsItems.FilterExpression = (ddFilterType.SelectedValue = txtSearchFilter.Text); gvItems.DataBind(); } else { gvItems.DataBind(); } }}
But when I run the search, I get:Exception Details: System.Data.EvaluateException: Cannot find column [the value in txtSearchFilter.Text].
How should I do my C# syntax to get this to work? Also, I would like to add further logic to check if the field is not the SKU field and have my expression do a LIKE expression instead of checking for equality. Any help with this is much appreciated.
Marc
View 1 Replies
View Related
Feb 5, 2007
Hi
on my page: two sqldatasources one has the sqlexperession:
SELECT DISTINCT [Skil] FROM [UsersView]UNIONSELECT 'Alles'
the other one :
SELECT [UserName], [Afdeling], [Skil], [RoepNaam] FROM [UsersView]
the first source is bound to a dropdown list. witch I want to use for filtering.
the second to a gridview.
the code for the dropdownlist selectedindexchanged is:protected void SkillDDL_SelectedIndexChanged(object sender, EventArgs e)
{
if (SkillDDL.SelectedValue.ToString() == "Alles")
{
SqlDataSource3.FilterExpression = "";
}
else
{
SqlDataSource3.FilterExpression = "Skil = '" + SkillDDL.SelectedValue + "'";
}
}
The strange thing is that initially 'Alles' is selected in the dropdown list and I see all the records. then I select an other value in the dropdownlist. and I get the filtered records, like I would expect.
then when I select 'Alles' again I keep my last selected records in view. I would expect that the filter is now an empty string and I would get all the records again. what do I do wrong?
thanks
Rob Warning
View 2 Replies
View Related
Nov 12, 2007
Hi. I want to use a FilterExpression in my sqldatasource that verify if a value is in a listbox with several values. How can I do that? Thanks
View 3 Replies
View Related
Feb 21, 2007
I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into.
My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...)
MY SQLSDATASOURCE <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="ClientSearch" SelectCommandType="StoredProcedure">
<FilterParameters>
<asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" />
</FilterParameters>
</asp:SqlDataSource>MY CODEBEHIND Dim FilterExpr As String If SearchLastName.Text = "" And _ SearchFirstName.Text = "" And _ SearchEmail.Text = "" And _ SearchComment.Text = "" And _ SearchAddress.Text = "" Then lblMessage.Text = "You didn't enter any search parameters. Please try Again." Me.GridViewSearch.DataSourceID = "" Exit Sub Else Me.GridViewSearch.DataSourceID = "SqlDataSource1" End If FilterExpr = "" If SearchLastName.Text <> "" Then FilterExpr = FilterExpr & "LastName Like '" & _ SearchLastName.Text & "%" & "' AND " End If If SearchFirstName.Text <> "" Then FilterExpr = FilterExpr & "FirstName Like '" & _ SearchFirstName.Text & "%" & "' AND " End If If SearchEmail.Text <> "" Then FilterExpr = FilterExpr & " Like '" & _ SearchEmail.Text & "%" & "' AND " End If If SearchComment.Text <> "" Then FilterExpr = FilterExpr & "[Comments] Like '" & "%" & _ SearchComment.Text & "%" & "' AND " End If If SearchAddress.Text <> "" Then FilterExpr = FilterExpr & "[Address] Like '" & "%" & _ SearchAddress.Text & "%" & "' AND " End If If Right(FilterExpr, 4) = "AND " Then FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4) End If Try Me.SqlDataSource1.FilterExpression = FilterExpr Me.SqlDataSource1.DataBind() Me.GridViewSearch.DataBind() Me.lblMessage.Text = Me.SqlDataSource1.FilterExpression Catch objException As SqlException Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next End Try End Sub
MY SPROCGO
ALTER PROCEDURE [dbo].[ClientSearch]
AS
SELECT C.ClientID, C.FirstName, C.LastName, A.Address,
C.Comments, C.EMail
FROM tblClient C INNER JOIN
tblClientAddresses A ON C.ClientID = A.ClientID
View 1 Replies
View Related
May 31, 2007
This is what I have:
<asp:TextBox ID="TextBoxProjectCode" runat="server"></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server" Text="Search" /> <br>
<asp:TextBox ID="TextBoxProjectDate" runat="server" ></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server" Text="Search" />
SelectCommand="SELECT CLIENT_NAME, CLIENT_CODE, PROJECT_CODE, PROJECT_NAME, PROJECT_DESCRIPTION, EMPLOYEE_ID, ENTRY_DATE, ENTRY_TIME, ENTRY_DESCRIPTION, COMBINED_CODE, TIME_ENTRY_ID, BILLABLE FROM VIEW_TIME WHERE (EMPLOYEE_ID = @SELECT_EMPLOYEE_ID) ORDER BY ENTRY_DATE DESC" FilterExpression="PROJECT_CODE = '{0}' or ENTRY_DATE = '#{0}#' "
<FilterParameters>
<asp:ControlParameter ControlID="TextBoxProjectCode" Name="PROJECT_CODE" PropertyName="Text" Type="Int32" />
<asp:ControlParameter ControlID="TextBoxProjectDate" Name="ENTRY_DATE" PropertyName="Text" Type="DateTime" /></FilterParameters>
Why do I keep getting errors. What am I missing?
View 3 Replies
View Related
Oct 1, 2007
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.
Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx
Here is some of my code..Private Sub ApplyFilter()
Dim _filterExpression As String = ""
If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then
_filterExpression = "CategoryName = '{0}'"
End If Me.SqlDataSource1.FilterExpression = _filterExpression
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
ApplyFilter()
End Sub
But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
View 5 Replies
View Related
Feb 16, 2008
I have a SQLDatasource control on a web page. It is the datasource for a gridview control.I want the gridview to show all jones if the FirstName textbox is left blank or empty. Right now I have to put a % in the FirstName textbox to get what I want. If I make the FirstNameTextBox empty or remove the % from the FirstNameTextbox it returns all the names in the database no matter what the last name is.How do I get it to work without having to use the % in the FirstName Textbox? THANKS!FilterExpression="LastName LIKE '%{0}%' and FirstName LIKE '%{1}%'"><FilterParameters> <asp:ControlParameter ControlID="LastNameTextBox" Name="LastName" PropertyName="Text" DefaultValue="" /> <asp:ControlParameter ControlID="FirstNameTextBox" Name="FirstName" PropertyName="Text" DefaultValue="" /></FilterParameters>
Last Name: Jones___________First Name: %_____________FILTERBUTTON
GridviewLast Name First NameBob JonesBill Jones
View 6 Replies
View Related
Mar 25, 2008
Hi.
Is there a way to convert a smalldatetime value to a year value (1/1/2001 -> 2001) in this case?
I tried year(thisIsSmalldatetimeField) and datepart(yyyy, thisIsSmalldatetimeField)
Dim FilterExpression As String = String.Concat("thisIsSmalldatetimeField=" & DropDownList.SelectedValue & "")
mySqlDataSource.FilterParameters.Clear()
mySqlDataSource.FilterExpression = FilterExpression
View 6 Replies
View Related
Jun 12, 2007
Hello,
I have a Repeater control getting data from a SqlDataSource.The SqlDataSource uses a stored procedure which accepts a input parameter with the name "sort" (@sort).
How do I use the FilterExpression with FilterParameter for defining the input value for the stored procedure ?The input value comes from an asp:QueryStringParameter.Or can the FilterExpression only be used with SQL Statements defined directly (as for defining the where clause) ?
I guess the best way is to use the FilterExpression soo the data is cached in the Dataset which is needed/required when using FilterExpression.Alternativly I could use the SelectParameter instead FilterParameters, but I guess the data wont be cached then.Anyone who can help me and guide me in the right direction ?
Best regardsMartin
View 2 Replies
View Related
Aug 31, 2007
How does one programmatically retrieve the results from a sqldatasource that has had a filterexpression applied to it?
Let me elaborate, here is my code:
SqlDataSource1.FilterExpression = "MenuType = 'myfilteredvalue'"
Dim _dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)
_dv.Table.TableName = "Menu" Dim ds As New DataSet("Menus") ds.Tables.Add(_dv.Table.Copy()) 'Add relation: ParentID under MenuID
Dim relation As New DataRelation("ParentChild", ds.Tables!Menu.Columns!MenuID, ds.Tables!Menu.Columns!ParentID, True) relation.Nested = True
ds.Relations.Add(relation)
What I'm doing is, I have a sqlDataSource that pulls all the data from my DB. With this data in the sqlDataSourceSource, I want to then populate an XMLDatasource (via a dataset) with only the records I need, i.e. the filter. However, after I apply my filter above, the sqlDataSoruce still contains all the records. I thought maybe if I did a sqlDataSource.Bind() after my SqlDataSource.FilterExpression, but that didn't do anything either.In laymans terms:I want to say something like: dataset = sqldatasource.filter(my filter expression).
View 2 Replies
View Related
Mar 18, 2008
I am using the FilterExpression of an SqlDataSource object to filter the rows of a gridview. It works with searching text values but I cannot for the life of me figure out to get it to work with dates. The following is what I do for character searches: SomeDataSource.FilterExpression = fieldToSearch + " LIKE '%" + SearchTextBox.Text + "%'"; This works. However when I try to search for dates using the following it doesn't work (startDate and endDate are DateTime objects):SomeDataSource.FilterExpression = fieldToSearch + " BETWEEN #" + startDate.ToString("yyyy-MM-dd") + "# AND #" + endDate.ToString("yyyy-MM-dd") + "#";// or
SomeDataSource.FilterExpression = fieldToSearch + " = #" + startDate.ToString("yyyy-MM-dd") + "#";// or
SomeDataSource.FilterExpression = fieldToSearch + " = #'" + startDate.ToString("MM/dd/yyyy HH:mm:ss") + "'#";
View 6 Replies
View Related
Apr 14, 2008
I have wrote some codes as follows:
Dim sqldatasource3 As New SqlDataSource sqldatasource3.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("aspnetConnectionString").ConnectionString sqldatasource3.SelectCommand = "select stepno,steppersontype,stepname,steptype,fchoice,fforward,schoice,sforward from flow " sqldatasource3.FilterExpression = "stepname = '" & GridView1.SelectedRow.Cells(4).Text.Trim & "'" sqldatasource3.DataSourceMode = SqlDataSourceMode.DataSet Dim dv As DataView = sqldatasource3.Select(DataSourceSelectArguments.Empty()) dv.RowFilter = "stepname = '" & GridView1.SelectedRow.Cells(4).Text.Trim & "'"
but i found the filterexpression didn't work. No matter what value GridView1.SelectedRow.Cells(4).Text.Trim be,sqldatasource always return the whole dataset.I wanna know where these code are wrong? Can anyone help?
View 1 Replies
View Related
Apr 25, 2008
I facing a problem when i want to modified the sqldatasource.filterExpression while trigger sqldatasource.selecting event. 1 Protected Sub SqlDsProduct_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
2 Dim SqlDsProduct As SqlDataSource = CType(CompleteGridView1.DetailRows(0).FindControl("SqlDsProduct"), SqlDataSource)
3 SqlDsProduct.FilterExpression = Session("filter") ' filter i add up after user press search button
4 End subActually i'm using the CompleteGridview, which i downloaded from the web. the "SqlDsProduct" i reffering to is inside the CompleteGridView1. I'm using complet grid view because i want the hierarchy look for the gridview. So please help me how i gonna change the filter expression of the 2nd sqldatasource inside detailsTemplate of the completeGridview. Thank you. best regardvince
View 6 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Feb 4, 2006
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.
Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:
Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
1 <asp:sqldatasource runat="server"></asp:sqldatasource>
2 <%@ Page Language="C#"%>
3 using System;
4 using System.Data;
5 using System.Collections;
6 using System.Data.SqlClient;
7
8 <script runat=server>
9
10 protected void Page_Load(object o, EventArgs e)
11 {
12 ObjectDataSource dsa; // This works no problems from the compiler here
13 SqlDataSource ds; // This works no problems from the compiler
14
15 if (IsPostBack)
16 {
17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))
18 {
19 instructions.Text = "Congratulations, your authenticated!";
20 instructions.ForeColor = System.Drawing.Color.Red;
21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";
23 }
24 else
25 {
26 instructions.Text = "Please try again!";
27 instructions.ForeColor = System.Drawing.Color.Red;
28 }
29 }
30
31 }
32
33 bool AuthenticateUser(string username, string password)
34 {
35 // Authentication code goes here
36
37 }
View 1 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Feb 20, 2008
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows
as per data in MS Sql
my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --
2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no
3)Other_project
fields are -- other_proj_id,other_custname,po
for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20
Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222
material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output
plz send a sql query
View 8 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Jul 11, 2007
Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated
View 2 Replies
View Related
Jan 11, 2006
for complex views should I use "where" statements or "joins" in terms of performance?
Which one is faster?
View 6 Replies
View Related
May 15, 2008
Hi,
I am a newbie to this site and hope someone can help....
I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:
if(TL_flag= '1', "yes") as [Trial Leave]
it is coming up with an error.... I can use Select case but I should not need to as this should work?
Any ideas?
View 2 Replies
View Related
Jun 7, 2007
hi all,
after convert from DTS to SSIS, how can i open the SQL statement? because i only saw a line in the properties of the task.
i open the DTSX in vs2005, but i can open to view the SQL statement.
in sql 2000 just double click on the SQL Task, then will popup a dialog form to show the SQL task.
please help.
thanks a lot
View 1 Replies
View Related
