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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Velemoochi
Imran Ghani
Post New Web Links

SQL Query within a form fails with "parser error" - syntax not correct?

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :ASP.Net
 

Hello, I've created an Expression Web form with a SQL datasource which is doing a query/select statement.

Somehow it appears that I'm making a syntax error. When I divide the query in two and execute them separate, it works fine.

I need to filter for an EventType, Region and Today's date + a custom interval (like 7 day's for a week)

The parameters come from a search form where the user can select these values via a dropdown list.

The two separate (and working) queries within my form are:

 

Query nr.1 :

"Select * from Events WHERE  (([EventType] LIKE '%' + @EventType + '%') AND ([Region] LIKE '%' + @Region + '%'))">

 

<asp:formparameter DefaultValue="%" FormField="EventType" />

<asp:formparameter DefaultValue="%" FormField="Region" />

 

Query nr.2 :

"SELECT * FROM Events

WHERE StartDate &gt;= DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) AND StartDate &lt; DATEADD(DAY, @NrDays, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));">

 

<SelectParameters>

                        <asp:fo


View Complete Post


More Related Resource Links

Syntax error (missing operator) in query expression

  
Good Day Gurus!

I have a Excel user application which has a user form (named 'Registo') that displays criteria and an image that has been entered in it's corresponding spreadsheet. This works the way it should.  There's also the ability to search the spreadsheet via a form (by clicking 'Pesquisar' button) this opens a search form. However, I having a bit of a problem with it. When I try to search for something it basically doesn't do anything at all. It just sits there. So I tried to debug it and I think I'm having a problem with either the JET db engine or somethign with teh query or maybe I don't have the correct reference.

I 'borrowed' this Excel application from another forum because it's exactly what I'm looking for! However, I suck at vb.

So I was hoping somebody could take a look at the code and see if I'm missing something?  I'm kinda' desparate to get this working because I'm been trying to figure it out for days and I'm running out of time.  Cry
 
Option Explicit

'constantes para auxiliar na verificação do código
Private Const Ascendente As Byte = 0

Using Infopath form fieldnames in a workflow fails

  

Hi,

I designed a form in Infopath and I published it on a SP2007 server to use in a workflow in a library. That goes fine. In the workflow I send an email message. I can use the fields from my Infopath form in the message that I create. 
If the workfow is starts, Shrepoints sends me the message. But it does not use the fields from my InfoPtah form in the message in stead I see questionmarks.

Why doesn't it show the fields?

I alse saw (maybe this makes it clear) that when I let it dipslay the URL in the message, it show: http://1.portal.lucasonderwijs, but the actual site is whithout the "1" and it should also start with https://

Has anyone seen this before?

Thank you,

Ronald


On New Item form, select item on choice dropdown based on query result.

  

I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

DateTime custom managed property search fails with " Your query is malformed. Please rephrase your q

  
I've created a front end search page in Silverlight 4 for SharePoint Search.  Currently we are using WSS 3.0 + Search Server Express 2008 (v 12.0.0.6421).  The page interacts with the MOSS version of the search web service at:   <site collection>/_vti_bin/search.asmx The reason for this approach is the unique way the requirements specify the look and behavior of the results.  The built-in Search Center page or web parts will not satisfy these requirements. We are going into production but I still cannot figure out how to search against my custom managed properties that are of Type DateTime.  If I cannot get this to work, it is a showstopper. I've tried numerous variations on the SQL Syntax and cannot get it to work.  I know the error is in the date portion of the WHERE clause since I can remove it and searches work fine. Here is a working query: SELECT Size, Rank, Path, Title, Description, Write, RequestSubmittedDate, DocumentType, RequestPromisedDate, ProblemDescription, DatePublished, RequestOwner, RequestingDepartment, DocumentAuthor, ProjectNumber, ProjectDescription, RequestStatus FROM scope() WHERE CONTAINS ('"nmr"') ORDER BY "Rank" DESC yet if I try to use a date literal in the WHERE clause as in the following query modification, I get the "malformed"

Balloon Tooltip doesn't display at the correct position when form is maximized

  
Having a problem with display balloon tooltip at the right bottom of form, the balloon tooltip displays fine if form is not maximized. However, the balloon tooltip doesn't display at the correct location when form is maximized(I guess because there is no room to display it on the right bottom of screen, so it moves up a bit). Is there a way to correct it? Such as displaying the balloon tooltip box above the control with stem points to the control. It displays the tooltip box below  the control right now which might cause the issue. Any help will be great appreciated.

Correct BCP syntax

  
I am a first time BCP user.  I have an XML file that I need to import into a new table in SQL Server.  This is using SQL Server 2008 and BCP version 10.50.16.  I read through the documentation but I get 370,000 errors!  (This is a very large file).  When I open the error log, I just see a bunch of question marks.  About this XML file: it does use a new line to delineate rows, however, it uses the space character to separate fields.  So I'm not sure if that is what causes it to fail.  The failure message is: BCP copy in failed I wish it were more descriptive.  Is there a way to get a more detailed error message? Otherwise, here is my command: d:\SQL Tables\data>bcp Development.dbo.wbl_zSkywardEnrollment2 in zSkywardEnroll ment.xml -fSE_format.fmt -m50 -eseErrorLog -b100 -t0x20 -T -F107 All of these files are in this same directory.  I know it says to specify full file path, but since they're all in this same directory and also I saw another example where they didn't specify directories I thought this would suffice. I have tried this without the format file also, doing it interactively but get same errors. And I have tried this starting with row 1.  I am curious which line should BCP start on?  Line 1 of this XML starts with Schema and ElementType info.  But on line 107 is where rs:data section

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

  
Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

Balloon Tooltip doesn't display at the correct position when form is maximized

  
Having a problem with display balloon tooltip at the right bottom of form, the balloon tooltip displays fine if form is not maximized. However, the balloon tooltip doesn't display at the correct location when form is maximized(I guess because there is no room to display it on the right bottom of screen, so it moves up a bit). Is there a way to correct it? Such as displaying the balloon tooltip box above the control with stem points to the control. It displays the tooltip box below the control which might cause the issue. Any help will be great appreciated.

Sub Query Syntax

  
Hi All, I know this is a repost but I have struggled though it and managed to nut most of the issues from this query. An issue I am still getting are multiple values returned by a sub query... Which one SQL doesn't like to clue me into. Below is the stored procedure and I know it is mess, this is reverse engineering a terrible application.   USE [CMI] GO /****** Object: StoredProcedure [dbo].[CMIReport] Script Date: 09/02/2010 09:49:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[CMIReport] @startdate datetime, @enddate datetime as BEGIN ---- ~This drops the tables created by the report before recreating it~ ---- DROP TABLE CMI_Report DROP TABLE CMI_Report_Final_Data ---- ~This Query defines the dates early on in the procedure~ ---- SET NOCOUNT ON select * into CMI_Report from Case_Query_View where end_dt Is Null Or end_dt > @startdate and end_dt < @enddate --- ~This Query Replaces Name Veiw in the Report~ ---- select * into CMI_Report_Final_Data from Name_View ---- ~This is the defining query, This controls YTD and such! ---- SELECT nq.surname, nq.sw_ur, nq.MaxOfnext_rev_dt, cq.CTenddt, aq.LastOfadm_dt, aq.LastOfsep_dt, nq.end_dt, nq.clnt_id, nq.case_id, nq.HCPName, nq.hcp_id, cq.LastOfstart_dt, nq.MaxOfwellb_dt, (select MAX(dq.icd_desc) from Diagnosis_View dq where nq.clnt_id = dq.clnt_id) AS Diag

Content Query Web Part (CQWP) fails to show picture items from a document/picture library in an anon

  
I have a Picture Library and am trying to use the CQWP to display a list of items in the library.  The web part shows the items fine in authenticated mode, but it fails to show the items to anonymous users. The error log message is: Error while executing web part: System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.SharePoint.Publishing.WebControls.ContentByQueryWebPart.SetDocumentIconUrlAndOnClickString(SPWeb web, DataRow row, String strDefaultItemOpen, Boolean fSetDocIcon, Boolean fSetOnClick, String fileRefColumnRef, String progIdColumnRef, String fsobjTypeColumnRef, String permMaskColumnRef)     at Microsoft.SharePoint.Publishing.WebControls.ContentByQueryWebPart.PreProcessForDocumentIconAndOnClickAttribute()     at Microsoft.SharePoint.Publishing.WebControls.ContentByQueryWebPart.AddContentQueryData()     at Microsoft.SharePoint.Publishing.WebControls.ContentByQueryWebPart.GetXPathNavigator(String viewPath)     at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform) I'm running SP 2010.  I've tried various scenarios including: 1. Using the default publishing template. 2. Using the blank site template with publishing and lock down features enabled. 3. Us

syntax problem passing parameter into Indexing Service Query

  
Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

Infopath 2007: Cant we fetch data from a list or form library without using a Query to the data conn

  
I am trying to fetch data from a form library and a list (both secondary data sources). But cant do it without using a rule (Query using a data connection). However when I use the 'Refresh' control on the form it brings the value. I find it quite weird, as the connections are made properly and logically speaking all the data connetions should fetch dynamic data once the form opens or loads. Cant we do it without using a (data query) rule? Update: I am using a web based infopath forms, while using the same form on client its working fine. Why is that?

Generic Query Designer displaying non standard syntax

  
I've just discovered a problem with the Generic Query Designer on my machine. When I do a simple join of two tabels it does not display in the SQL pane as standard syntax, so instead of SELECT a.1 from ainner join b on a.1=b.1It does SELECT a.1from a, bwhere a.1 = b.1I am doing this against an oracle 10g database, it will run the query, but I want the query designer to out put at standard SQLI am presuming that it is a setting in SSBIDS, but I don't know why it is doing this. Any suggestions?

Query returns correct rows but I am not able to concatenate the rows into a variable :(

  

Hi all,

Why in the code below I am able to list all rows and display the field 'stepID' but I am not able to concatenate them all in the variable @stepIds?

declare @date as datetime
set @date = '2010-09-20 23:01:20:000'
declare @instance as uniqueidentifier
set @instance = '04658F34-CBF1-4CCD-A683-F23BDB669D35'

  Declare @CrLf char(2)
  Set @CrLf = Char(13) + Char(10)
	Declare @stepIds varchar(8000)
	Set @stepIds = ''
	
	Select
		--@stepIds = @stepIds + ' - Step: ' + CONVERT(varchar(4), stepID)
		stepID
	From
		Uds.RealtimeLogging
	

Setting SharePoint 2010 Form Fields Using Query String Variables

  

Hi All,

I have a requirement to populate sharepoint form field value with query string while uploading document to document library.

While uploading the document to document library, I want to populate claim number available from query string in a pop up page i.e in my case EditForm.aspx, Located at http://servername:port/sitename/documentlibraryname/forms/EditForm.aspx.

Additional Information:

  • Created document library and added custom columns like claim number etc..
  • Created a view to filter uploaded documents as per claim number.

Server Details:

  • Windows Server 2008 R2
  • SharePoint 2010
  • SQL Server 2008 R2

I have written javascript for the same, but not sure where exactly to add the code in EditForm.aspx page.

Any help on this would be greatly appreciated.

Thanking you in advacne.

Regards

Seva


Nested Full Text Query and SQL syntax

  

Hi,

Quick question, is it possible to use the SQL TOP syntax in a Full Search Query something like

Select Top 1 title from SCOPE() where = 'All Sites' AND Productid= '1234556'

Also

Can you use nesteed select statments ?

Select title from SCOPE() where = 'All Sites' AND Productid=  (select productid from scope() where scope = 'all sites' AND product = 'example')

Cheers

Dave Roberts

 


Help Identifying What Wrong W/ Query Syntax - sp_send_dbmail

  

Hello,

I know that I have probably done more than a few incorrect things in this statement but it works when I run it in the studio so I am not sure why it will not work when trying to use it from within sp_send_dbmail. I am pretty much new and learning as I go but this one has me lost.

My Error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is what I am trying to run from Studio to validate that the syntax is correct before attempting to set it as a scheduled task.

DECLARE @like1 varchar(100);
SET @like1 = '%-LOST-%'; 
DECLARE @like2 varchar(100);
SET @like2 = 'LOST%-'; 
DECLARE @sql NVARCHAR(MAX);
SET @sql = (SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10e.ocx' 
	  WHERE ProfileDate > GetDate()-1 
	  and (ProfileName not like @like1 and ProfileName not like @like2)
	  and version <> '10,1,82,76'
	  UNION ALL
	  SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10i.ocx' 
	  WHERE ProfileDate > GetDate()-1
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

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