FilterExpression Syntax In C#

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


ADVERTISEMENT

Filterexpression In ASP 2.0

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

FilterExpression

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

SQLDatasource Filterexpression

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

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

FilterExpression And Listbox

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

Issue With SQLDataSource And FilterExpression

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

Multiple Searches For FilterExpression

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

Dynamically Change FilterExpression

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

FilterExpression And The SQLDataSource Control

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

A Smalldatetime Value To A Year Value In FilterExpression

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

Using A Stored Procedure With A FilterExpression ? How To Sort ?

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

Sqldatasource.filterexpression - Different Use For/of - Populate A Dataset

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

SqlDataSource.FilterExpression: Can't Get It Work With Dates?

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

Sqldatasource Filterexpression In Code Mode

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

Modified Sqldatasource.filterExpression While Sqldatasouce.selecting Event

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

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

Dec 14, 2003

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'


Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()


End Sub

Thankyou in advance.

View 3 Replies View Related

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

Mar 31, 2008

Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:

INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName

OR

WHERE f.Name = @FacilityName


My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?

Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?

Thanks!

View 4 Replies View Related

Converting Rrom Access Syntax To Sql Syntax

Sep 23, 2007


Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................

how can you convert it to sql syntax

I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end




Case Statement might be the solution but i could not do it.






Your input will be appreciated

Thank you

View 5 Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

May 27, 2008

This is the error it gives me for my code and then it calls out line 102.  Line 102 is my  buildDD(sql, ddlPernames)  When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one.  Could it not like something in my sql select statement.  thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
 
End Sub

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'SELECT'.Incorrect Syntax Near The Keyword 'else'.

May 22, 2008

What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.

View 5 Replies View Related

SQL Syntax

Apr 2, 2007

Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:

User ID
FirstName
LastName
Department
Site
i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results? 
 Any help would be appreciated. Thank you.

View 7 Replies View Related

Syntax

Aug 8, 2007

cmd.CommandText="Insert INTO personaldet(Firstname,Lastname,Username,password,dob,) values '('"+txtfname.Text.Replace("'", "''").ToString()+"','"+txtlname.Text+"','"+txtusername.Text+"','"+txtpassword.Text+"','"+txtdob.Text+"')'";
 
always says syntax error ')'.
check this plz
 

View 4 Replies View Related

Help With C# Syntax

Nov 11, 2007

Hello, I have at sp that return a value:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getLastActivityDate]
(
@userid nvarchar(256)
 
)
AS
DECLARE @ret nvarchar(256)
SET @ret = (SELECT CAST(LastActivityDate AS nvarchar)
FROM aspnet_Users
WHERE UserName = @userid)
RETURN @ret
 The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret?
// Tomas

View 2 Replies View Related

Bad Syntax

Feb 24, 2004

I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)

Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent

Thank you for any help,

View 1 Replies View Related

Help With LIKE Syntax...

May 17, 2004

HI All,

I am writing some code where i want to get a list of all the company names that start with say, M...

here is what i have:



Dim M_company, companyID_M

Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )



but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value???

thanks,
Leissa

View 4 Replies View Related

SQL Syntax Help

Jan 6, 2005

I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.

ALTER PROCEDURE dbo.AddItemToCart
(
@Cart nvarchar(50),
@ItmID int,
@Qty int,
@ItmTyp char
)
As

Declare @AllItmCount Int
SELECT
@AllItmCount = Count(ItmID)
FROM
ShoppingCart
WHERE
Cart = @Cart

IF @AllItmCount = 0 /*new shoppong cart*/
INSERT INTO ShoppingCart
( Cart, Qty, ItmID, ItmTyp, Line )
VALUES
( @Cart, @Qty, @ItmID, @ItmTyp, 1)

ELSE /*some item entered */

DECLARE @CountItems int

SELECT
@CountItems = Count(ItmID)
FROM
ShoppingCart
WHERE
ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'

IF @CountItems > 0 /* same item entered before then add together */

UPDATE ShoppingCart
SET Qty = (@Qty + ShoppingCart.Qty)
WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'

ELSE /* Find the last line number */

Declare @LastLine Int
SELECT @LastLine = Max(Line)
FROM ShoppingCart
WHERE Cart = @Cart

/*Add a new line */

INSERT INTO ShoppingCart
(Cart, Qty, ItmID, ItmTyp, Line)
VALUES
(@Cart, @Qty, @ItmID, @ItmTyp, @LastLine + 1)

View 6 Replies View Related

Help With SQL Syntax

Apr 23, 2005

Hi all,
I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use:
SELECT     ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies,                          (SELECT     MAX(AddedDate)                             FROM          (SELECT     ForumID, AddedDate, Author                                                    FROM          dbo.Forums_Topic                                                    UNION ALL                                                    SELECT     ForumId, AddedDate, Author                                                    FROM         dbo.Forum_Reply) AS dates                            WHERE      (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM         dbo.Forums
Any suggestion on how to show the author of the last message as well? Thanks in advance.

View 27 Replies View Related

Sql Syntax?

Dec 6, 2005

i want to find some repeatable data in a column,is there a sql syntax here?Thanks in advance.

View 1 Replies View Related

Syntax ?

Jan 7, 2002

Hello,

I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?

SELECT TimeZone.LongZone, SiteMain.SiteNumber,
SiteType.SiteTypeDescription
FROM SiteType INNER JOIN
TimeZone ON SiteType.id = TimeZone.id INNER JOIN
SiteMain ON TimeZone.id = SiteMain.TimeZone AND
SiteType.id = SiteMain.SiteType

View 1 Replies View Related

If Else Syntax

Feb 5, 2002

Can someone help me with the syntax for an if else statement such as:

select (if column(a) > 1) then print '+' else print '-')
,column(b)
,column(c)
,etc.
from table...

BOL doesn't seem to be much help for this
Thanks in advance!
CP

View 1 Replies View Related

SQL Syntax

May 15, 2002

I have some problem for construction Sql statement to execute in asp pages.
In the table the data is stored in such a way that
Worknote Id,IncidentId, SeqNo , Desc1,Desc2.
1111 2004 1 - Test1 Test2
2222 3000 1 - Test1 Test2
2222 3000 2 - Test3 Test3

I want to take the maximum of IncidentId (3000) and concatinate the description (Test1 TEst2 Test3 Test4)

I dono how to construct SQl to fetch records . I tried some way , it gives only the last row.

SELECT iWorkNoteID, SeqNum, vchWorkNote1, vchWorkNote2 from incidentworknote where iWorkNoteID = (Select max(iWorknoteID) from incidentworknote where iIncidentID = " & iIncidentId & ")"

Can anyone helop me to find out.

View 1 Replies View Related







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