I am trying to run the plan analyser on a SQL query that I cut and pasted from a Siebel trace. I get the following error
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect
There is no count in the SQL query, and there doesn;t seen to be a message number to look up, anyone got any ideas?
I have done the obvious stuff like changing the DB name in the query, but to no avail....
Cheers
Mike
I'm stuck. This is in C#.
I am making the following query:
string query = INSERT INTO region_info(prefix, region, last_update) VALUES ('" + RegionInfo.Prefix + "', '" + region + "', ???TIMESTAMP???);
and then executing
query = query.Replace("???TIMESTAMP???", "'" + DateTime.Now.ToString("yyyyMMdd") + "'");
Thus, an example query is:
INSERT INTO region_info(prefix, region, last_update) VALUES ('907209', 'Alaska-Juneau', '20040921');
When I execute this query through my program(uses ADO.net), I get a "COUNT field incorrect or syntax error" exception, but if I run this same query through the query analyzer, it works fine.
Any ideas on what'z going wrong?
Thanks
I am moving along but I still get this pesky error. In a field on the user form for comments - when there is a ' character - I get this syntax error. But, I have removed the ' character with this code:
fieldname= Replace(Request.Form("fieldname"), "'", " ")
So how do I get rid of this error? Beats me.
Anyone know of resources I can read about this?
Thanks in advance...................
Set objConn = Server.CreateObject("ADODB.Connection")
Set rs = server.createobject("ADODB.Recordset")
objConn.open "Driver={SQL Server};Server=server;DSN=SQL3;Database=requests;U ID=probe;PWD=;"
strDocument= request.form("qryName")
strName= request.form("name")
strDescription= request.form("description")
strEmail= request.form("email")
StrTeam= request.form("shift")
StrSection= request.form("pickone")
StrComments= request.form("comments")
StrDatein= request.form("datein")
StrType= request.form("type")
StrDcfnumber=CounterHits
sql = "Insert into dcf_submit (qryName,name,description,email_requestor,shift,se ction_pickone,change_type,comments,date_submit,dcf _number) values('" & _
strDocument & "','" & _
strName & "','" & _
strDescription & "','" & _
strEmail & "','" & _
StrTeam & "','" & _
StrSection & "','" & _
StrType & "','" & _
StrComments & "','" & _
StrDatein & "','" & _
StrDcfnumber & "')"
objConn.execute sql
rs.Close
Set rs = Nothing
Hi,
When i exceute my report i get the following .. An error occured during local report processing. Query exceuteion failed for dataset 'orders' Incorrect syntax near ')'
If i give NULL i get the above error... But if give one or 2 values it works. but If I run the query in my Sql server mgt studio it works fine too and i run in the BIDS or VS2005 it works fine.. but something happens when i generate the report in preview mode. I am using multivalue parameter
This is my sproc
Code Snippet
ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@StartDate datetime,
@EndDate datetime,
@ClientId nvarchar(max)= NULL
AS
Declare @SQLTEXT nvarchar(max)
if @ClientId is NULL
BEGIN
SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--c.ClientId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId --AND cp.ClientId in ('+ convert(Varchar, @ClientId) + ' )
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + convert(Varchar,@ClientId) + ')
AND
o.OrderDate BETWEEN ''' + Convert(varchar, @StartDate) + ''' AND ''' + convert(varchar, @EndDate) + '''
ORDER BY
o.OrderId DESC'
exec(@SQLTEXT)
END
any help will be appreciated..
Thanks
Karen
Naming convention and what am I doing wrong here:
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:
Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine.
CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red
ELSE '3' --White
END
END
else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
[code]....
I checked the NRESULT field, and there are not any NULL values in there, either.
Hello,
Could someone please have a look at this query and let me know what is wrong? I am trying to count the records based on the where clasue criteria:
SELECT count(*),
created_on,
creditrange = CASE WHEN grandtotal > '10000' THEN '10k+'
WHEN grandtotal >= '3001' THEN '3k-10k'
WHEN grandtotal >= '1001' THEN '1k-3k'
WHEN grandtotal > '100' THEN '101-1k'
ELSE '1-100'
END,
id AS 'ID',
area AS 'Area',
grandtotal AS 'GT Credit',
grandt AS 'GT Debit',
UPPER(status) AS 'Status',
FROM vwCreditNote cnv
WHERE AREA != 'Bb'
and CREATED_ON > '2008-04-01'
and (status = 'Calculated BO' OR status = 'REJECTED')
Thanks,
Rhonda
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
Hi -
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
Where do I change the security settings?
Thanks - Grace
Sorry to be an ignorant American here, but I'm having a challenge I just can't seem to wrap my head around.
I am attempting to make a nice query for a report that will show how our SSIS jobs are doing. All my packages use the native SQL Server logging capability. The table is sysdtslog90 and it stores the message to a field called 'message' (crazy, I know) with a datatype of nvarchar(4096).
The challenge I'm having is that I'd like to grab all lines where my message is X. The following queries will return no results. The first is me typing in the message I see in the window, the second is copying the value and pasting it in. I know there are instances where that message exists, sadly, in the log table.
Code Snippet
SELECT
L.*
FROM
sysdtslog90 L
WHERE
L.message = N'A commit failed.'
SELECT
L.*
FROM
sysdtslog90 L
WHERE
L.message = N'A commit failed. '
If I were to change that clause out to a like, it'd work just fine. Is that the appropriate way to work with unicode literals? It doesn't feel right.
Code Snippet
SELECT
L.*
FROM
sysdtslog90 L
WHERE
L.message LIKE N'A commit failed.%'
I have exported my sysdtslog90 table to a unicode flat file and looked at this message in a hex editor and it looks fine, nothing looked awry. I then opened the file up in SSMS, thinking perhaps it's a silent conversion issue with pasting, but to no avail. Anyone have some guidance for me?
I have a table like this. Each ConNo can have many boxes. The number of the box is an identfier, not a quantity.ie Box 3 = 1 box.A box may have a letter as a suffix or not.
I want to group by ConNo and total the boxes so I know how many Boxes are in each ConNo eg ConNo 200 5 boxes
TransId 1234
ConNo 200
BoxNo 1
BoxSuffix
TransId 1235
ConNo 200
BoxNo 1
BoxSuffix
TransId 1236
ConNo 200
BoxNo 2
BoxSuffix
TransId 1237
ConNo 200
BoxNo 2
BoxSuffix A
ConNo 200
BoxNo 1
TransId 1238
ConNo 201
BoxNo 1
TransId 1239
ConNo 201
BoxNo 1
TransId 1240
ConNo 201
BoxNo 3
TIA
I've got a stored proc which returns a location and corrosponding defects for that location. My matrix has the locations as rows and a count of defects as the data.
so...
LOCATION COUNT
--------------- ----------
AREA1 10
AREA2 5
AREA3 3
AREA4 2
AREA5 1
How can I manipulate the matrix (or table?) so I'll only show the Top 3 highest counts? I've tried screwing around with the Top N Filter, but as soon as I do =COUNT(Field!Defect.Value) on it the report wont run anymore. If the data came over in the above format that would make my life so much easier...but my matrix is all about aggregate data
Also I cant modify the stored proc as that dataset is used on multiple areas in the report
Any help would be great
Thanks,
Steve
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!
Hi,
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable
(
CustomerID,
FirstName,
LastName,
DateAdded,
AlbumPicture,
LayoutCount
)
SELECT
Albums.CustomerID,
Customers.FirstName,
Customers.LastName,
DateAdded,
AlbumPicture,
COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID
FROM
Albums JOIN
Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
Many thanks!!
Eric
I want to get a count of how many PosAnswer are associated with the QuesToAsk 'List your top 5 favorite places'
This is the syntax I am using to achieve this, and it works, but wasn't sure if there was a better way (changing the data structure is unfortunately not an option in this situation). Here is my table structure
Code:
Create Table Test
(
QuesToAsk varchar(1000),
PosAnswer varchar(1000)
[Code] ....
And this is the syntax I use to get the count I am after
Code:
DECLARE @VariableName varchar(25)
DECLARE @FormattedVariableName varchar(25)
DECLARE @FieldCount int
SET @VariableName =
[Code] ....
I am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results.
$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate()))
[code]....
Hopefully this makes sense. I am new to SQL Reporting. My code is below. I am trying to get my query to display 0 when a category (prob_ctg.sym, does not have a Count value.
SELECT COUNT(*) AS Expr2, ISNULL(COUNT(*), '0') AS Total, CONVERT(varchar(20), Requestor.c_last_name) + ', ' + CONVERT(varchar(20),
Requestor.c_first_name, 112) AS Customer, Calls.ref_num, Calls.category, prob_ctg.sym, Calls.summary, DATEADD(s, Calls.open_date - 21600,
'1/1/70 12:00:00 am') AS OpenDT, DATEADD(s, Calls.close_date - 21600, '1/1/70 12:00:00 am') AS CloseDT, [Group].c_last_name AS GroupName
FROM ctct Requestor INNER JOIN
ctct [Group] INNER JOIN
call_req Calls ON [Group].id = Calls.group_id ON Requestor.id = Calls.customer INNER JOIN
int_org INNER JOIN
prob_ctg ON int_org.id = prob_ctg.organization ON Calls.category = prob_ctg.persid
WHERE (int_org.iorg_c_id LIKE '%400111%') AND (prob_ctg.del = 0) AND (DATEADD(s, Calls.open_date - 21600, CONVERT(DATETIME, '1970-01-01 00:00:00',
102)) BETWEEN @stdt AND @eddt)
GROUP BY Calls.category, prob_ctg.sym, Calls.summary, Calls.ref_num, Calls.open_date, Calls.close_date, [Group].c_last_name, int_org.iorg_name,
int_org.iorg_c_id, Requestor.c_last_name, Requestor.c_first_name
I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25)
BI int
Track int
CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
Trial BI Track CAT
A 5 3 1
B 5 3 2
[code]...
Hi All ,
I have one field in SQL Server Report Called Status which is text field.
that status can be "Abesent" , Late In", "Early Out" , "Early Out and Late in"
, its in table Like this.
Emp no | Name | Date | Time | Status
1 Kaisar 1-1-07 7:15 Late In
1 Kaisar 1-2-07 17:15 Early Out
1 Kaisar 1-4-07 - Absent
1 Kaisar 1-5-07 - Absent
1 Kaisar 1-6-07 - Absent
And So On......
----------------------------------------------------------------
i want to get count some thing like this
Total Of Absent : 3
Total Of Late in : 4
Total Of Early Out :5
any idea how can i do this ?
HELP! im running this sql and get the following error - If i manually start the dts package it is fine....also if i use DTSrun from my pc it also works ok..
USE master
EXEC xp_cmdshell 'DTSrun /S BTN_AH_TWB02NEW /U pkent /P clarke05 /N pbk'
output
DTSRun: Loading...
Error: -2147024809 (80070057); Provider Error: 0 (0)
Error string: The parameter is incorrect.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 713
(9 row(s) affected)
Hello everyone,
Please i need your help...
I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though....Just write it in the mail as part of message body...
I use this SQL select statement to retrieve the strTitle and datBorrowed fields
strSQL += @"Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";
Now, I have the following code to write the email
static void SendTest()
{
int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}
And the body of the email is this......
Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***" +
"" +
"Books Borrowed Yesterday are:" +
"" +
"" +
"Thank you," +
"" +
"eLibrarian" +
"" +
"================================================== ===============" +
"" +
"This e-mail is automatically generated by the Library system." +
"Please do not reply.");
i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:" +
So how should i do this?
How can i create a stored procedure that count or sum value of field
e.g.
f1 f2
f3 f4 f5
record 1
1 2
3 1
and get answer like this 1=4 - 2=1 - 3=1
I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.
I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...
So far I have this:
INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);
And its not going too well.
I want to count the values of 2 field on particular date
I have user table with 3 field
1.from_userid
2.to_userid
3.Date
Now i want to count the no. of files from_userid have send, no. of files to user_id have received on particular day. The data is like this
From_userid to_userid date
3953 6274 10/22/2014
3953 6152 10/22/2014
1112 2710 10/22/2014
3953 1851 10/23/2014
3953 4302 10/23/2014
4302 2710 10/23/2014
I need to figure out the number of managers without listing them. Label the column Number of Managers.
any help is appricated.
Number of Managers
-------------------------
6
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc
All help is greatly appreciated.
Is it possible to count the same field with different criteria. It would be something like this.
car_table
car_id
car_name
car_brand
So you would execute a statement which would count(car_brand) with two different criteria.
I am not sure if this is possible or if there is another way to approach it.
I have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
I am getting an error saying, "Incorrect syntax near keyword 'ON'" Could someone please help? Here is my code:
str = "SELECT FAC_REQUEST.*, StateFAC.*, STATUS.sStatus, FAC_TYPE.Facility_Type FROM FAC_TYPE INNER JOIN ((FAC_REQUEST INNER JOIN STATUS ON FAC_REQUEST.iStatusID = STATUS.iStatusID) ON StateFAC.DVN = FAC_REQUEST.DVN) ON FAC_TYPE.Faciltiy_Type = FAC.FacilityType WHERE iRequestID=" & iRequestID
Thanks
Hi,
When i exceute my report i get the following .. An error occured during local report processing. Query exceuteion failed for dataset 'orders' Incorrect syntax near ')'
If i give NULL i get the above error... But if give one or 2 values it works. but If I run the query in my Sql server mgt studio it works fine too and i run in the BIDS or VS2005 it works fine.. but something happens when i generate the reports... ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@ClientId nvarchar(max)= NULL,
@StartDate datetime,
@EndDate datetime
AS
Declare @SQLTEXT nvarchar(max)
If @ClientId IS NULL
Begin
Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + @ClientId + ')
AND
o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + '
ORDER BY
o.OrderId DESC'
execute (@SQLTEXT)
END
any help will be appreciated.
Regards
Karen
Hi! I need some help with this error which I'm unable to understand.
Any help will be highly appreciated.
<Error>
Line 1: Incorrect syntax near 'Number'.
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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'Number'.
Source Error:
Line 69: Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Line 70: Dim ds As DataSet = New DataSet
Line 71: da.Fill(ds, "Equip")
Line 72:
Line 73: dgdSearch.DataSource = ds.Tables("Equip").DefaultView
Source File: c:inetpubwwwrootEquip logDemoWebForm3.aspx.vb Line: 71
Stack Trace:
[SqlException: Line 1: Incorrect syntax near 'Number'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
Demo.WebForm3.butSearch_Click(Object sender, EventArgs e) in c:inetpubwwwrootEquip logDemoWebForm3.aspx.vb:71
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain() +1292
</Error>
Private Sub butSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSearch.Click
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objAdapter As SqlDataAdapter
Dim objDataReader As SqlDataReader
Dim objDataSet As DataSet
Dim strSearch As String
Dim StrSQLQuery As String
'Dim mySQL As String
'Get Search
strSearch = txtSearch.Text
'If there's nothing to search for then don't search
' o/w build our SQL Query execute it.
If Len(Trim(strSearch)) > 0 Then
'Set up our connection.
Dim strConn As String = ("server=(local);Integrated Security=SSPI;database=Equipment Log")
'-- setup the select command
Dim mySQL As String = "Select * From Equip Where " & ddlSearch.SelectedValue.ToString() & " Like '" & txtSearch.Text & " %' Order by DemoNum"
Dim MyConn As New SqlConnection(strConn)
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(mySQL, MyConn)
MyConn.Open()
'--execute the query and fill a dataset with the results
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet = New DataSet
da.Fill(ds, "Equip")
dgdSearch.DataSource = ds.Tables("Equip").DefaultView
'Create new command object passing it our SQL Query
'and telling it which connection to use.
objCommand = New SqlCommand(strSearch, objConnection)
'DataBind DG to DS
ddlSearch.DataBind()
dgdSearch.DataBind()
MyConn.Close()
Else
txtSearch.Text = "Enter Serach Here"
End If
I am getting the following error when attempting to call a certain function:
"Incorrect Syntax near spListPrograms, Line 1"
Here is the sproc:
ALTER PROCEDURE spListPrograms
@parentID int = 0
AS
SELECT pwbsID, pwbsTitle FROM ProgramWBS WHERE pwbsParent = @parentID
It is being called from this function:
Public Shared Function ListProgramChildNodes(Optional ByVal parentID As Integer = 0) As SqlDataReader
Dim cmd As New SqlCommand("spListPrograms", strConn)
cmd.Parameters.AddWithValue("@parentID", parentID)
Try
strConn.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
Catch e As SqlException
Dim errorMessages As String = ""
Dim i As Integer
For i = 0 To e.Errors.Count - 1
errorMessages += "Index #" & i.ToString() & ControlChars.NewLine _
& "Message: " & e.Errors(i).Message & ControlChars.NewLine _
& "LineNumber: " & e.Errors(i).LineNumber & ControlChars.NewLine _
& "Source: " & e.Errors(i).Source & ControlChars.NewLine _
& "Procedure: " & e.Errors(i).Procedure & ControlChars.NewLine
Next i
MsgBox(errorMessages)
Finally
strConn.Close()
End Try
End Function
The sproc works just fine when I execute it from the database directly in SQl Express. What could cause an incorrect syntax error? There's hardly any syntax there for an error to occur Thanks for any help you can give me.