DEFAULT Keyword Performance

Jul 20, 2005

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@TradeDate DATETIME = NULL
)
RETURNS @t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @TradeDate IS NULL
SET @TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @t
SELECT
@TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @TradeDate to 09/25/2003 it
returns in 8 minutes.

View 6 Replies


ADVERTISEMENT

SQL Server 2012 :: Use Of Default Keyword As Parameter Default - What Value Is It

Aug 11, 2015

@pvColumnName  VARCHAR(100) = Default,  

However, I am unable to determine what is the value for Default. Is it '' ?

Default is not permitted as a constant - below fails to parse:

WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

View 4 Replies View Related

Invalid Syntax Near Keyword Default (was Why Do I Get The Following Error?)

Mar 8, 2005

select * from CurrencyMaster where default=true

invalid syntax near keyword default.

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

[Performance Discussion] To Schedule A Time For Mssql Command, Which Way Would Be Faster And Get A Better Performance?

Sep 12, 2004

1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection

above, which way would be faster and get a better performance?

View 2 Replies View Related

In Keyword With INT

Oct 18, 2007

I am trying to pass several ids to use in a where clause.   1 For Each row as GridViewRow In gv_child.Rows
2 If row.RowType = DataControlRowType.DataRow Then
3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox)
4 If chk IsNot Nothing AndAlso chk.Checked Then
5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text)
6 ids &= "," & id
7 End If
8 End If
9 If ids.Length > 1 Then ids = ids.Substring(1)
10 'Submit to sql with ids as param...
11 Next

 
I am getting an exception.  Error converting '38,39' to a column of datatype int.  What am I doing wrong?

View 7 Replies View Related

BETWEEN Keyword

Mar 6, 2004

I need to retrieve records where the date is in between the current date and 4 days previous.

I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...

Can someone help out?

View 2 Replies View Related

Is Name A Keyword?

Dec 27, 2005

It's blue in SQL Studio but I can't find it in SQL Books on-line. Iwanted to use it as a field name but don't like using [].Is it reserved?

View 6 Replies View Related

GO Keyword

Nov 21, 2007

Hi guys,

Just a newbie question here.

Do we really need to use the GO keyword?

Thanks,

MeTitus

View 1 Replies View Related

Extremely Poor Query Performance - Identical DBs Different Performance

Jun 23, 2006

Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***

View 2 Replies View Related

Keyword Search

May 27, 2007

I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.
At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?

View 2 Replies View Related

Search By Keyword

Jan 31, 2008

 Greetings,  I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#?  I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up  having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword;      (or '@keyword)  which does not work.      however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas? 

View 7 Replies View Related

Using Keyword Question...

Apr 3, 2008

Hi everyone,
 I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that:
private SqlConnection connection;public SqlConnection Connection{get{  if (connection == null)                 connection = new SqlConnection();  if (connection.State != ConnectionState.Open)  {                connection.ConnectionString = ConnectionString;                 connection.Open();    }  return connection;}}
The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL();
userDAL.DoSomethingAndAccessConnectionProperty();

// Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
So, if I do the following:using (UserDAL userDAL = new UserDAL())
{
userDAL.DoSomethingAndAccessConnectionProperty();

DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
}
I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks

View 6 Replies View Related

Keyword Query

Jan 9, 2004

I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.

Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.

I wanted to select distinct all of the individual words from each keyword field in all of the records.

Can this be done? What would the query look like?

I am looking for a list like:

bull
barrel
rodeo
western
cowboy

Any suggestions?

Thanks,
Rob

View 6 Replies View Related

Use Of DISTINCT Keyword

Mar 8, 2004

If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?

ie,

SELECT DISTINCT fieldA, fieldB
FROM tableA


but not

SELECT fieldA, DISTINCT fieldB
FROM tableA

or

SELECT DISTINCT fieldA, DISTINCT fieldB
FROM tableA


thanks again, this is a great forum

ddave

View 6 Replies View Related

Regarding Distinct Keyword

Apr 5, 2008

hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?

.
.
.
.

here is my query :




SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j

Good luck for all the folks

View 4 Replies View Related

Using The DISTINCT Keyword

May 11, 2006

Hi all,

I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key).

The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book.

Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work:

SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID

But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.

View 3 Replies View Related

What Is The OrElse && Keyword In SQL?

Jan 28, 2008

and how do I write an if phrase?

Shimi

View 5 Replies View Related

Top Keyword And Sorting

Jul 23, 2005

I heard a claim recently at a SQL Server users group meeting that theTOP keyword forces sorting on a database server. I can't find anyreason this might be true and the Books Online say nothing about it.Can someone verify this claim one way or the other?

View 5 Replies View Related

Confusion Over ANY Keyword

Jul 23, 2005

I am studying for the MSCE/MCDBA exam 70-229. In the book I am using("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000Database Design and Implementation Exam 70-229, Second Edition") I amlooking at the section on the ANY/ALL keyword.<QUOTE>USE PubsSELECT TitleFROM TitlesWHERE Advance > ANY(SELECT AdvanceFROM Publishers INNER JOIN TitlesON Titles.Pub_id = Publishers.Pub_idAND Pub_name = 'Algodata Infosystems')This statement finds the titles that received an advance larger thanthe minimum advance amount paid by Algodata Infosystems (which, in thiscase, is $5,000). The WHERE clause in the outer SELECT statementcontains a subquery that uses a join to retrieve advance amounts forAlgodata Infosystems. The minimum advanceamount is then used to determine which titles to retrieve from theTitles table.</QUOTE>I don't understand why this references the "minimum advance". If yourun the subquery on its own, it returns the following values:5000.00005000.00005000.00007000.00008000.0000NULL[color=blue]>From my limited understanding, the "ANY" keyword applies to at least[/color]one value, but which one? How is this determined?Any help gratefully received.Edward--The reading group's reading group:http://www.bookgroup.org.uk

View 4 Replies View Related

EXEC Keyword

Aug 12, 2005

I am in the process of importing an Oracle database into SQL Server.Once of the tables has a field called "EXEC".SQL Server seems to reject any queries that include that particularfield because EXEC is a keyword. For eg.SELECT ID, EXEC from USERSresults in a syntax error near keyword EXEC.I can't change the fieldname becuase it will require reworking of awhole bunch of scripts.What can I do to adjust the query?Bijoy

View 3 Replies View Related

Another Question (IN Keyword)

Jul 20, 2005

Is possible in SQLSERVER to use a syntax like this?select x,y from table_1 where (x,y) not in (select h,k from table_2)I've tried, but it doen't work.Do you know any workaround?Thank youFede

View 3 Replies View Related

Keyword Nothing As The Scope.....

Mar 26, 2007

I've been trying to figure out the usage of "Nothing" as the scope parameter, and the more I try, the more I can confused.

It says on the RDL spec:

"For expressions inside data regions:

.......

Specifying the keyword Nothing as the scope is equivalent to specifying the
outermost data region containing the report item in which the aggregate is used."

It seems that what's it saying is that if I have an expression in a table as
= last(somefield, "the name of the table")
is the same as
= last(somefield,Nothing)
But, apparently it;s not. (I tried last, first,sum, count,CountRows, min, max...........)
it doesn't matter where I put "=last(somefield,Nothing)" in the table (i tried table header,footer, detail, and table group header, footer), none of them worked.
and I tried it everywhere in matrix, in charts.
It's just not working.
It always complains about the invalid scope not being the containing data region, containing grouping or dataset name.

However, the only way I get the "Nothing" as the scope to work is in a RunningValue function, not in any other
aggregate funcitons.

Anyone help me with this, please... I need a complete definition on the usage of Nothing as scope.

View 11 Replies View Related

Keyword Search

Apr 18, 2008


Hi,

I have a table like

ProductId, Description, Description2

where Description and Description2 are text datatypes.

I'm trying to return all records where myKeyword exists as a singular word in either of these two fields.

Should I create a child table where each word in each of these fields has its own row for each product and query against that or is there an efficient way of querying this result without creating the extra child table?

Many thanks for any pointers

Dan

View 4 Replies View Related

Use Of (optional) Keyword

Nov 26, 2007

I'm using an application that is generating some SQL scripts for SQL Server 2005. I'm trying tweak it so that I can run it on SQL Server 2000.

The line that I'm having trouble with is:


CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional)

The key word "(optional)" is causing trouble.
I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected.

What is the alternative syntax to use in SQL Server 2000?

Thanks,
-Q

View 1 Replies View Related

Substitute For First Keyword?

Mar 27, 2008


IS there ny substitute for this ?

=First(Fields!TouchedBy.Value, "Dataset1")

instead of keyword 'First' i need the current record , but also i need to refer dataset name also , is there any way to do that ?

Any idea?

thanks

View 7 Replies View Related

Date Picker Bug - Drops The Default Value And Displays Default Value As Todays Date

Apr 3, 2008



Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.


Any words of wisdom gratefully recieved,

Howard Stiles

View 1 Replies View Related

Keyword Not Supported: ',server'.

Jun 27, 2006

Hello there.
I'm developing an eCommerce solutions based on the ASP.NET 2.0 Commerce Starter Kit, architechture. It uses the Provider Pattern. In my web-application, i use the CatalogProvider, to retrieve data from a SQL Server 2005 database. I call the methods through a handler class, whoch excists inside the WebApp. I also use a ShoppingCartProvider, OrdersProvider, ShippingProvider etc. in the same way.
In my Web.Config file, i have all the provers listed, and on each provider, the name of the connectionString to use are given.
My connection string looks like this:"connString" connectionString="Server=xxxx;Database=xxxx;Trusted_Connection=True;" providerName="System.Data.SqlClient" />
 The problem is, that suddently, when browsing the website, that connects to the database through the providers, i get this error:
Keyword not supported: ',server'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Keyword not supported: ',server'.Source Error:




Line 31: public static IDataReader GetProductsByCategory(int categoryID)
Line 32: {
Line 33: return Commerce.Providers.CatalogProvider.Instance.GetProductsByCategory(categoryID);
Line 34: }
Line 35:
Source File: d:DevelopmentASPNETSeoShopApp_CodeHandlersCatalogManager.cs    Line: 33 Stack Trace:
If i then go back to my web.config file, and removes the providerName section, of the connectionString, the website works again, for a short period. When the error return, i undo the deletion of the providerName, and it will work again... For a short time...
I've also tried to use another connectionsString, like this:Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI
But then the keyword which is not supported is: ', data source'
 
Does anyone know what the issue might be?
Thanks in advance...

View 9 Replies View Related

TOP Keyword SQL Server 2005

Apr 2, 2007

I am using the top keyword as follows in the same SP. The select and the update statements are one below the other:
Select TOP(1) col1,col2 from table1 where col3='val1'
Update TOP(1) table1 set col2= 'val2' where col3='val3'
There is a primary key on the column which is not included in either the select or the update statement.
Can anyone confirm that the both the statments will return the same row?

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'ELSE'.

May 7, 2007

Hi,I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:Incorrect syntax near the keyword 'ELSE'.I am using Sql Server 2005.SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](    @ReportDate smalldatetime,    @SiteID int,    @DataFeedID int,    @FromCode varchar,    @Sent int,    @Delivered int,    @TotalOpens REAL,    @UniqueUserOpens REAL,    @UniqueUserMessageClicks REAL,    @Unsubscribes REAL,    @Bounces REAL,    @UniqueUserLinkClicks REAL,    @TotalLinkClicks REAL,    @SpamComplaints int,    @Cost int)ASDECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCode SELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN    SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDENDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost    FROM CrmPublisherSummary        WHERE @LastReportDate < @ReportDate        AND SiteID=@SiteID        AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET    Sent=@Sent,    Delivered=@Delivered,    TotalOpens=@TotalOpens,    UniqueUserOpens=@UniqueUserOpens,    UniqueUserMessageClicks=@UniqueUserMessageClicks,    UniqueUserLinkClicks=@UniqueUserLinkClicks,    TotalLinkClicks=@TotalLinkClicks,     Unsubscribes=@Unsubscribes,    Bounces=@Bounces,    SpamComplaints=@SpamComplaints,    Cost=@Cost    WHERE ReportDate=@ReportDate        AND SiteID=@SiteID        AND TagPKID=@PKIDELSE    SET NOCOUNT ON        INSERT INTO CrmPublisherSummary(    ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,    Bounces, SpamComplaints, Cost, DataFeedID, TagID)         SELECT    @ReportDate,    @SiteID,    @PKID,    @Sent,    @Delivered,    @TotalOpens,    @UniqueUserOpens,    @UniqueUserMessageClicks,    @UniqueUserLinkClicks,    @TotalLinkClicks,    @Unsubscribes,    @Bounces,    @SpamComplaints,    @Cost,    @DataFeedID,    @TagIDSET NOCOUNT OFF 

View 5 Replies View Related

Incorrect Syntax Near The Keyword 'FROM'.

May 16, 2007

Getting this error.. the page runs fine but it after entering the data it produces the following..  
ERROR:  Incorrect syntax near the keyword 'FROM'. with the following code...Please help!
<head runat="server"><title>Parts Lookup</title></head><body style="text-align: center"><form id="form1" runat="server"><div style="text-align: center"><br /><brpan style="font-size: 10pt; font-family: Tahoma">
Enter a Part Number</span>&nbsp;
<asp:TextBox ID="Productnbr" runat="server" Columns="4" Width="177px"></asp:TextBox><br /><asp:Button ID="DisplayPartNumberButton" runat="server" Text="Display Price, Description, Unit of Measure" Font-Names="Tahoma" /><br />
<br />
</div><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="PartFilterDataSource" EnableViewState="False" Width="431px" CellPadding="4" ForeColor="#333333" GridLines="None" Font-Bold="False"><Columns><asp:BoundField DataField="PartNbr" HeaderText="Part Number" SortExpression="PartNbr" /><asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /><asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" /><asp:BoundField DataField="UnitOfMeasure" HeaderText="Unit of Measure" SortExpression="UnitOfMeasure" /></Columns><FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /><RowStyle BackColor="#F7F6F3" ForeColor="#333333" /><EditRowStyle BackColor="#999999" /><SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /><PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /><HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /><AlternatingRowStyle BackColor="White" ForeColor="#284775" /></asp:GridView>&nbsp;<asp:SqlDataSource ID="PartFilterDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ManManSQLConnectionString %>" SelectCommand="SELECT PartNbr, Description, UnitOfMeasure, Price; FROM Tbl_ODBC_PartsList; WHERE PartNbr = @Productnbr"><SelectParameters><asp:ControlParameter ControlID="Productnbr" Name="Productnbr" PropertyName="Text"/></SelectParameters></asp:SqlDataSource></form></body></html>

View 2 Replies View Related

Having Trouble With In Keyword ; Maybe Need A Subquery?

Aug 2, 2007

 I am displaying advertisers on a dynamic .net page that users will
view via querystrings from a county  page,  then a city page , as they
can pick a zone , then view the advertiser's ad  predicated on the
zone they pick. I want to have advertisers on a premium plan that can
be viewed in all areas of the city, regardless what zone the user
picks. Trying the below does not suffice my resultset (the queriedzone
is a variable as the request.querystring for the zone ; 'all' is the
indicator for an advertiser who should be seen in all zones)

sqlstr = "select company, address, email from advertisers where zone IN ('" + queriedzone + "','all') "
??? thanks in advance ed

View 1 Replies View Related

Incorrect Syntax Near The Keyword 'AS'.

Apr 13, 2008

HiI am getting the following error on my Select statement: Incorrect syntax near the keyword 'AS'. SELECT [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], RowFROM(SELECT  ROW_NUMBER() OVER (ORDER BY [J] DESC)AS Row, [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K]FROM [TABLE]WHERE (([A] LIKE '%' + @A+ '%') OR ([K] LIKE '%' + @K+ '%')) AS LogWithRowNumbersWHERE (Row >=91  AND Row <= 100)  I used the following select statement as a template (which works fine): SELECT [A], [B],
[C], [D], [E], [F],
[G], [H], [I], [J],
[K], [L], Row

FROM (SELECT ROW_NUMBER() OVER (ORDER
BY [H] DESC)
AS Row, [A], [B],
[C], [D], [E], [F],
[G], [H], [I], [J],
[K], [L] FROM [TABLE]

WHERE (([J] = @J) AND
([E] >= @E)) AND
(([K] < [L]) OR (([K] = 0) AND
([L] = 0)))) AS LogWithRowNumbers
WHERE (Row >= 82 AND Row <= 90) What is the difference that would make one work and the other not work?Thanks if you can help,Jon 
 

View 2 Replies View Related







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