I'm using sql server 2005 and in sql management studio I am getting a syntax error from this simple below code:
WITH CustListTemp AS
(SELECT OrderID, Locked, OrderStatus, Resubmissions, ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber
FROM dbo.OrderInfo
)
Can somebody please tell me what ther error is so I can correct it? I even had someone at work take a look at it and couldn't get it either.
in SQL query analyzer i have this skeleton test query:
DECLARE @nLen int
DECLARE @strData varchar(200)
DECLARE @strDelim varchar(50)
DECLARE @nPos int
SELECT
OwnersName1
= (IF len(OwnersName) > 0 OwnersName
-- IF dbo.InString(OwnersName,'&',1) > 0 'yes'
--if dbo.InString(OwnersName,' AND ',1) > 0 'yes'
--if dbo.InString(OwnersName,', ',2) > 0 'yes'
--ELSE OwnersName
)
,
OwnersName2 = OwnersName
--@strDelim as Delim
FROM dbo.TateWake
WHERE OwnersName>'9'
i get this error:
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'OwnersName'.
if i replace
IF len(OwnersName) > 0 OwnersName
with: len(OwnersName)
there is no error.
what the heck is going on?
thanks...
this is my 3rd attempt at posting this. other attempts did not post for some reason. now i'm home from work and so i can't cut and paste. this is from memory.
in query analyzer, i made a ridiculously simple query which returns a syntax error that i can't resolve:
SELECT name1 =
(
IF len(name) > 0 len(name)
),
name2 = name
FROM ownertable
this gives an error something like:
syntax error near IF on line 3
when i remove the IF len(name) > 0
the expression is evaluated as expected with no error. what is going on?
I must apologize for the simplification, but my posts kept going to the ether for some reason. I have had other dba's look at it and no one knows why this is happening in my office. nor can i understand why my posts don't stick. i even got a message from the forum app that i posted a duplicate message!
I'm new to SQL I don't know how to write code for the following problem:
Attached is the pic of my table. What I would like to do is compare the Open column of 2008-02-22 with the Close column of 2008-02-21.
Then I would like to know if the Open of 2008-02-22 is greater or less than the Close of 2008-02-21.
- If it greater, I would like to know if the Low of 2008-02-22 is ever less than or equal to the close of 2008-02-21.
- But if it is Less, then I would like to know if the High of 2008-02-22 is greater than or equal to the close of 2008-02-21.
The other issues is that the date have to be dynamic of course. I used 2008-02-22 and 2008-02-21 as examples. Ideally it would be the Open Column of Row 1 compared to the Close column of Row 2.
Sorry if I did not explain it properly, this new way of thinking is kinda foreign to me and I may sound like an idiot. Any help would be much appreciated.
Hi I have the following statement, where I'm passing any arrayList (as a string) this works fine, however when I add in the "WHERE" section 'AND (Keywords Like ...)' I get the error "Incorrect syntax ner 'tool' ", must have something to do with '%'
Any help would be great. Thanks in advance
DECLARE @Array varchar(50)SET @Array = '47, 23'DECLARE @SearchText varchar(50)SET @SearchText = 'tool'
DECLARE @SQL varchar(700)
SET @SQL = 'SELECT *FROM ImageGallery_ItemsWHERE CategoryID IN (' + @Array + ')AND ( Keywords LIKE %' + @SearchText + '% )'
EXEC(@SQL)
I rarely use loops, but I have a need right now to create one. Can someone please tell me the proper syntax for creting one. BOL doesn't help much
Thanks!
In advance forgive my ignorance - I'm developing in Access 2003....
I have a query that delivers an accurate dataset - Each church is submitting three proposals which are numbered with their church number (four digits) - their "first choice" has a "-1" after their church number. So church number "1101"'s first choice proposal is "1101-1". Each Church has 3 proposals (1101-1, 1101-2, 1101-3). There are 266 total churches. The proposals are picking other churches as part of the complete record. So each church can pick up to 6 other churches. But the "church of record" is the one at the top of the report. In other words, when I base the report on the query, if the report is about church "1101" I need the query to list Church 1101's 3 records at the top, to then be compared to the grouped (three) proposals from other church's that mentioned 1101 in their proposals.
RIght now the query works fine in that all of the records are accurately filtered into the dataset. So I have 1101's 3 proposals in the query results, along with all other churches that mentioned 1101 - no problem there. Here's where the question comes in. I'm going to give the user an input form to choose which report to run. So the user will pick "1101 St. Mary's" off of a combo box and hit the report button.
In SQL - how do I specify that if the ParishID of the Church (chosen in the combobox) equals 1 to 3 parishID's in the dataset, that those three records should appear at the top of the dataset in ascending order (1101-1, 1101-2, 1101-3) - then the rest of the records should appear ascending by proposal number.
Thanks so much in advance. I didn't include field names or anything because I'm guessing it's some simple SQL standard syntax that I can adapt. I know how to do the runtime connect to the query using the combobox - I'm OK there....
OvernightGeek
dear friends,
i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database.
i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder.
but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :
--------------------------------------------------------- Beginning of Error Message --------------------------------------------------
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
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. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
--------------------------------------------------------- End of Error Message --------------------------------------------------
then when i checked the Machine.config file, i found this....
<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /> </connectionStrings>
then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition.
but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or 2) Web.Config file and/or 3) any other way, but please help i want to do this, please......
regards and thanks
Gurjit Singh
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO
Thanx in front
Cya
Hi all,
View 4 Replies View RelatedHello,
the following alter table statement:
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED
is answered with:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.
which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.
Note that:
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL
DOES get executed, and
ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY
produces the same error.
Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.
So - anyone an idea?
Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.
CREATE PROC CabsSchedule_Insert
{
@JulianDatesmallint,
@SiteCodesmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvd varchar(30),
@Remarksvarchar(50)
}
AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)
Thanks,
I have a multi-step DTS package that I get the following error when I try to execute it:
Package Error
Error Source : microsoft ole db provider for sql server
Error Description : [DBNETLIB][ConnectionOpen (connect()).]SQL Server does not exist or access denied
Am I missing something obvious? I'm sure it is something simple but I can't seem to get it. Thank you in advance for you assistance.
Hey all,i have a students_table which i want to do a multiple update on.Update student_tableset grant = 35000where course_id = 2There are lots of students in the student table which have course_id of2.I get this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used as anexpression.The statement has been terminated.Does anyone know how to do a multiple update like this.It seems such asimple thing but im confused*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHello all!
Working with SQL 2000. If I create a rollback trigger for a value that can not be less 0. After it rolls back, can I throw a custom error message in my .net application from SQL?
Thanks!
Hi Guys, I'm hoping somebody can help me with this really frustrating problem that I'm having.......
I'm developing a peer to peer file sharing application (final year degree project) in which I use a web service & sql database as the management server. For some strange reason, I'm getting an SQL syntax error on some machines but not on others when I call the method to submit a file list to the server (see below for code for the method). Another strange thing is that on different machines, I'm getting a different error. I've seen "incorrect syntax near 'd' ". and also "incorrect syntax near 've' ", while on two other machines it works just fine - It appears that the connection to the webservice and to the database is working just fine on all machines as before this method I have a login which works perfectly and the data is represented in the database.
Does anybody have any pointers or even the slighest idea what can cause an error like this or have seen anything like this before. Hoping to get this sorted pretty soon as the deadline is nearing. All and any help is very much appreciated!!!!
Kevin
public void submitFiles(FileObject[] files, string peerID)
{
foreach (FileObject fo in files)
{ System.Text.StringBuilder submit = new System.Text.StringBuilder("INSERT INTO SharedFiles (FileID, FileName, FileType, FileSize, PeerID) VALUES ('" + fo.guid.ToString() + "', '" + fo.name + "' ,'" + fo.name + "', '" + fo.size + "', '" + peerID + "')");
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(submit.ToString(), con);
try { con.Open();
cmd.ExecuteNonQuery();
} finally { con.Close(); }
}
}
Hello, I have this sql quiery: sqlcommand2.CommandText = "Select Count(UserIP) From InboundTraffic Where InboundURL Contains('" & SiteDomain(i).ToString & "') and DateTimeReceived > " & Last30Days
SiteDomain is placing a string variable such as website.com and Last30Days is a date variable which = now - 30days
Im getting this error "Syntax error (missing operator) in query expression 'InboundURL Contains('website.com') and DateTimeReceived > 7/9/2007 8:20:30 PM'"
What am I mising? THANKS!
Could someone help me with this error message:
Syntax error converting the varchar value '180 Ways to Walk the Leadership Talk by John Baldoni' to a column of data type int.
Getting error on the Titles.title column. Tried casting it but it still does not work. What am I missing?
CREATE procedure GetRequestInfo@Requestorid int
ASselect distinct requestors.Requestorid, CAST(Titles.title AS VARCHAR(255)), requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip,LibraryRequest.ShipDate,LibraryRequest.DueDate,LibraryRequest.ReturnDate,
Cast(DATEPART(m, requestors.requestDate) as Varchar(5)) + '/' +Cast(DATEPART(d, requestors.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, requestors.RequestDate) as Varchar(5)) as 'RequestDate'
from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.Titleid = LibraryRequest.Titleidwhere Requestors.requestorid = requestors.requestoridGO
Can anyone tell me why I get a syntax error on the THEN and the ELSE?
@Start datetime,@End datetime,@EmailAck bit,@SelectedProcess nvarchar(25)ASBEGINIF @SelectedProcess = 'Monthly' THENUPDATE tblReminderSchedule SETPrintedDate=GETDATE(),[Status]=1FROM tblReminderSchedule INNER JOIN tbllOAN ON tblReminderSchedule.lOAN_ID = tbllOAN.lOAN_IDWHERE (tblReminderSchedule.ReminderDate BETWEEN @Start AND @End) AND (dbo.tblReminderSchedule.ReceivedDate IS NULL) AND (tbllOAN.ReminderByEmail = @EmailAck) AND (tbllOAN.Frequency = 'Monthly')ELSEUPDATE tblReminderSchedule SETPrintedDate=GETDATE(),[Status]=1FROM tblReminderSchedule INNER JOIN tbllOAN ON tblReminderSchedule.lOAN_ID = tbllOAN.lOAN_IDWHERE (tblReminderSchedule.ReminderDate BETWEEN @Start AND @End) AND (dbo.tblReminderSchedule.ReceivedDate IS NULL) AND (tbllOAN.ReminderByEmail = @EmailAck) AND (tbllOAN.Frequency <> 'Monthly')END
I am trying to open a table in my DB to check for login ids:
The code I used is below> I will appreciate any help on this matter
<CODE>
Public Function Authorize(ByVal Username As String, ByVal Password As String) As Integer
Dim sql As String
Dim con As New SqlConnection("data source=localhost; initial catalog=Jasist; Integrated Security = SSPI")
sql = "Select * from USER Where User_Name = '" & Username & "' and User_Passwd = '" & Password & "'"
con.Open()
Dim cmd As New SqlCommand(sql, con)
Dim Id1 As Integer
Dim dr As SqlDataReader = cmd.ExecuteReader
If dr.Read Then
Id1 = 1
con.Close()
dr.Close()
Return Id1
Else
con.Close()
dr.Close()
Return 0
End If
End Function
<CODE>
THE ERROR GENERATED WAS->
Server Error in '/Jasist' Application.
--------------------------------------------------------------------------------
Incorrect syntax near the keyword 'USER'.
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: Incorrect syntax near the keyword 'USER'.
Source Error:
Line 19: con.Open()
Line 20: Dim cmd As New SqlCommand(sql, con)
Line 21: Dim Id1 As Integer
Line 22: Dim dr As SqlDataReader = cmd.ExecuteReader
Line 23:
Source File: C:InetpubwwwrootJasistClass1.vb Line: 21
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Hi, hope someone can spend a minute checking out my script error. The following is part of my SQL statement. It has got syntax error near "="
I was hoping the script could run 100 times and print number 1 to 100.
DECLARE @ID int
SET @id = 1
EXEC (' WHILE ' + @id + ' <= 100 ' + ' BEGIN SELECT ' + @id +
' SET ' + @id + ' = ' + @id + ' + 1 ' + ' END ' )
Any input is most welcome.
Richard
Can anyone tell me what is wrong with the script below?
INSERT INTO #SUBSCRIBERLIST(EMEMBER_GUID,EHBSID)SELECT (M.EMEMBER_GUID,@EHBSID)
FROM EMEMBER M WHERE M.EMEMBER_GUID = @EM_ID1 AND M.MEMBERTYPE <>1
This is the error I am getting.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ','.
Thanks in advance.
Matt
hi
i am getting this error while running SQL query : syntax error near '='
Query is :
SELECT People.People, People.Name,
Sum(([ProjectStatusReport].[Week]), 0, ([ProjectStatusReport].[Week] = #8/27/2004#, 1, 0)) AS Created,
Sum((ProjectStatusReport.Week), 0, (ProjectStatusReport.Week = #8/27/2004#, (ProjectStatusReport.Accomplishments) Or (ProjectStatusReport.Plans), 0, 1, 0)) AS Complete,
People.email FROM Register INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status
LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register INNER JOIN People ON StakeHolders.People = People.People
WHERE SR_Status.Status='Active' AND StakeHolders.Status = 'Yes' GROUP BY People.People, People.Name, People.email
HAVING (Sum(ProjectStatusReport.Week), 0, ((ProjectStatusReport.Week) = #8/27/2004#, 1, 0) > 0))
AND Sum(ProjectStatusReport.Week),0,((ProjectStatusRep ort.Week) = #8/27/2004#,(ProjectStatusReport.Accomplishments) Or (ProjectStatusReport.Plans),0,1,0)>0
ORDER BY People.People
Any help
Regards
Hi All,
When I run the following portion of my stored procedure, I get the following error message:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Here is the script:
set @dttimestr = 'select @dttime = substring(convert(varchar(11),getdate(),120),1,4)+
substring(convert(varchar(11),getdate(),120),5,3)'
EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output
print @dttime
Can you please tell me how to fix it?
Quick question..
SELECT * FROM Table_1
INTERSECT
SELECT * FROM Table_2
INTERSECT
SELECT * FROM Table_3;
Syntax error in FROM clause, and it highlights the second select.. whats wrong with this statement? does it not like the *'s?
Thx
I get the following errors with the script below:
I am novice with SQL and have been unable to figure out the syntax just yet. The line for the errors are marked blue
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'user'.
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'auto_increment'.
Server: Msg 170, Level 15, State 1, Line 44
Line 44: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 53
Line 53: Incorrect syntax near '='.
-----------------------------------------------------------
CREATE TABLE admin (
user varchar(100) NOT NULL default,
pass varchar(100) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO admin VALUES ( 'admin', 'pass456');
CREATE TABLE affiliates (
refid varchar(30) NOT NULL default '',
pass varchar(20) NOT NULL default '',
company varchar(100) NOT NULL default '',
title varchar(5) NOT NULL default '',
firstname varchar(40) NOT NULL default '',
lastname varchar(40) NOT NULL default '',
website varchar(100) NOT NULL default '',
email varchar(100) NOT NULL default '',
payableto varchar(100) NOT NULL default '',
street varchar(100) NOT NULL default '',
town varchar(100) NOT NULL default '',
county varchar(100) NOT NULL default '',
postcode varchar(20) NOT NULL default '',
country varchar(100) NOT NULL default '',
phone varchar(30) NOT NULL default '',
fax varchar(30) NOT NULL default '',
date varchar(40) NOT NULL default ''
) TYPE=MyISAM;
CREATE TABLE banners (
number int(3) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
image varchar(60) NOT NULL default '',
description varchar(200) NOT NULL default '',
PRIMARY KEY (number)
) TYPE=MyISAM;
CREATE TABLE clickthroughs (
refid varchar(20) default 'none',
date date NOT NULL default '0000-00-00',
time time NOT NULL default '00:00:00',
browser varchar(200) default 'Could Not Find This Data',
ipaddress varchar(50) default 'Could Not Find This Data',
refferalurl varchar(200) default 'none detected (maybe a direct link)',
buy varchar(10) default 'NO'
) TYPE=MyISAM;
CREATE TABLE sales (
refid varchar(20) NOT NULL default '',
date date NOT NULL default '0000-00-00',
time time NOT NULL default '00:00:00',
browser varchar(100) NOT NULL default '',
ipaddress varchar(20) NOT NULL default '',
payment varchar(10) NOT NULL default ''
) TYPE=MyISAM;
HELP!
Code:
str_IntroducerInfo = "UPDATE tblPolicy SET " & _ "Saluation = '" & saluation & "', " & _
"ClientName = '" & tempName & "', " & _
"ClientNRIC = '" & nric & "', " & _
"Submitted = '" & submitted & "', " & _
"Inforced = '" & inforced & "', " & _
"PolicyNo = '" & policyno & "', " & _
"ProductName = '" & productname & "', " & _
"ProductProvider = '" & productprovider & "', " & _
"ModalPremium = '" & modalpremium & "', " & _
"Freq = '" & freq & "', " & _
"Method = '" & method & "', " & _
"NextBirth = '" & nextbirth & "', " & _
"PremTerm = '" & premiumterm & "', " & _
"PolicyTerm = '" & policyterm & "', " & _
"Status = '" & pstatus & "', " & _
"ApplType = '" & appltype & "', " & _
"ApplType(A&H) = '" & appltypeah & "', " & _
"SupervisorAgree = '" & supervisoragree & "', " & _
"SupervisorRemarks = '" & superremarks & "', " & _
"Remarks = '" & remarks & "', " & _
"DateSig = '" & datesig & "', " & _
"AuthPerson = '" & authPerson & "', " & _
"CorFax = '" & corfax & "', " & _
"Contact = '" & number1 & "', " & _
"Contact1 = '" & number2 & "', " & _
"CorAddress = '" & address & "', " & _
"CorEmail = '" & email & "', " & _
"CorNature = '" & nature & "', " & _
" WHERE ID = '" & introducerID & "';"
response.Write(str_IntroducerInfo)
set rs_IntroducerInfo=conn.Execute(str_IntroducerInfo)
The output from the line
Code:
UPDATE tblPolicy SET Saluation = 'Miss', ClientName = 'Karlam Pte Ltd', ClientNRIC = 'S7908121X', Submitted = '24 February 2003', Inforced = '25 February 2003', PolicyNo = '30', ProductName = 'Shop Care', ProductProvider = 'Liberty Insurance', ModalPremium = '50000', Freq = 'Yearly', Method = 'Cheque', NextBirth = ' ', PremTerm = '5', PolicyTerm = '2', Status = 'EE', ApplType = 'AWS', ApplType(A&H) = 'A', SupervisorAgree = 'Yes', SupervisorRemarks = 'Good', Remarks = 'Bad', DateSig = '21 March 2004', AuthPerson = 'Ho LiLi', CorFax = '68525687', Contact = '68523652', Contact1 = '65825588', CorAddress = '20 Robotson Road, Robot Tower B, #03-98, S(263673)', CorEmail = 'karlam@kar.com', CorNature = 'Trading', WHERE ID = '80017-S7908121X';
The error
Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.
/Ivan-Test/ops/comm.asp, line 1851
My Access table fields
ID
PolicyNo
Submitted
Inforced
Saluation
ClientName
ClientNRIC
FACode
Type
ProductProvider
ProductName
ModalPremium
Freq
Method
NextBirth
PremTerm
PolicyTerm
Status
ApplType
ApplType(A&H)
SupervisorAgree
DateSig
SupervisorRemarks
Remarks
AuthPerson
Contact
Contact1
CorAddress
CorFax
CorEmail
CorNature
I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."
Here comes the precudere:
DECLARE @returnDay int
DECLARE @query varchar(8000)
--Looking at current date,
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 8
BEGIN
SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c: est.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @query
EXEC master.dbo.xp_sendmail @recipients=test@test.com',
@copy_recipients = test@test.com',
@message='Submitting BloodBank Results for the previous month.
@subject='BloodBank results for the previous month',@attachments = '\testc$ est.txt'
SELECT @@ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy......here...
Thanks
I have a syntax error of
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '<'.
When running the following.
I have highlighted the offending character.
DECLARE @Todayvarchar(30)SET @Today = GetDate()
DECLARE @Aprilvarchar(30)SET @April = '01/04/'
DECLARE @Augustvarchar(30)SET @August = '01/08/'
DECLARE @LastYear varchar(30)SET @LastYear = YEAR(DATEADD(yy,-1,GetDate()))
DECLARE @ThisYear varchar(30)SET @ThisYear = YEAR(GetDate())
SELECTe.employee_numberAS 'Employee Number',
e.known_as_and_surnameAS 'Name',
b.benefit_codeAS 'Benefit Code'
FROMpwa_master.employeee,
pwa_master.benefitb
WHEREe.unique_identifier = b.parent_identifier
ANDb.benefit_code = 'BONUS'
ANDe.company_reference IN ('NGB','NGBO','BMS')
ANDe.payment_method = 'MONTHLY'
ANDb.benefit_start_date >=
(
CASE @Today < @April + @ThisYear
THEN
@LastYear
ELSE
(
CASE @Today > @April + @ThisYear
THEN
(
CASE e.company_reference WHEN 'NGB'
THEN
@April + @ThisYear
ELSE
@April + @LastYear
END
)
ELSE
(
CASE @Today > @August + @ThisYear
THEN
@April + @ThisYear
END
)
END
)
END
)
GROUP BY employee_number, known_as_and_surname, benefit_code, e.company_reference
Can anyone tell me why this happens?
I swear I have made case statements like this before (usign variables and greater-than signs etc).
It is hopefully me just being a dumba** but I can't see what's wrong with it.
-GeorgeV
Hi All,
When I run the following portion of my stored procedure, I get the following error message:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Here is the script:
set @dttimestr = 'select @dttime = substring(convert(varchar(11),getdate(),120),1,4)+
substring(convert(varchar(11),getdate(),120),5,3)'
EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output
print @dttime
Can you please tell me how to fix it?