Incorrect syntax near 'ISNULL'

I'm trying to get that ISNULL happy below but all my attempts (taking out the quotes, etc.) have not worked...so I'm posting to see if someone else can find the simple fix that I cannot. It must be simple?!?!

DECLARE @.iiCampusID AS INT,
@.iiStaffID AS INT,
@.TermID AS INT,
@.LastName AS varchar(50),
@.FirstName AS varchar(50),
@.CourseID INT,
@.StatusID INT,
@.debug BIT

SET @.FirstName = 'John'
SET @.TermID = 1
SET @.iiCampusID = 5
SET @.debug = 1

-- Get Parent Row information for Parent Results Grid
SELECT a.[FirstName] + ' ' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[iiStaffID],
s.[Email] AS AlternateEmail,
s.[Addr1] + ' ' + s.[Addr2] + ' ' + s.[City] + ',' + s.[State] + ' ' + s.[Zip] + ' ' + dbo.aaGetCountryDescription(s.[iiCountryId]) AS PrimaryAddress,
a.[PrefAddress1] + ' ' + a.[PrefAddress2] + ' ' + a.[PrefCity] + ' ' + a.[PrefState] + ' ' + a.[PrefZip] + ' ' + dbo.aaGetCountryDescription([PrefadCountryId]) AS ShippingAddress
FROM [aaStaff] a
INNER JOIN mydb.dbo.[iiStaff] s ON s.[iiStaffId] = a.[iiStaffId]
INNER JOIN Mydb2.dbo.EeUsers u ON u.iiStaffId = s.iiStaffID
LEFT JOIN mydb2.dbo.eeUserRoles ur ON ur.eeUserId = u.eeUserId AND ur.eeRoleId = 1
' + ISNULL('INNER JOIN mydb.dbo.AdClassSchedTerm st ON st.AdTermID = ' + convert(varchar, @.TermID), '') + '
WHERE AND u.[Active] = 1
AND u.[iiCampusID] = @.iiCampusID
IF @.FirstName IS NOT NULL
AND (a.[FirstName] LIKE @.FirstName OR @.FirstName = '%%' OR @.FirstName IS NULL)
IF @.LastName IS NOT NULL
AND (s.LastName like @.LastName OR @.LastName = '%%' OR @.LastName is null)

Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'ISNULL'.
Msg 170, Level 15, State 1, Line 35
Line 35: Incorrect syntax near ')'.

You can't use control of flow statements in a query. You could use CASE expression. But there are better ways to write this query. Please take a look at the link below for various techniques:

http://www.sommarskog.se/dyn-search.html

|||Yea, I was using that article this entire week. However, it's a huge amount of work to do the sp_executesql...in terms of getting the damn string of sql to be happy and all sorts of tricks. Also, it's very hard to troubleshoot when the sql is thrown into a variable as I found out|||

I don't know man, I just cannot get this thing to work, even with yours:

FROM [aaStaff] a

INNER JOIN mydb.dbo.[eeStaff] s ON s.[eeStaffId] = a.[eeStaffId]

INNER JOIN mydb2.dbo.iiUsers u ON u.eeStaffId = s.eeStaffID

LEFT JOIN mydb2.dbo.iiUserRoles ur ON ur.iiUserId = u.iiUserId AND ur.vcRoleId = 1

' + CASE WHEN @.TermID IS NULL THEN '' ELSE 'INNER JOIN mydb.dbo.iiClassSchedTerm st ON st.iiTermID = ' + convert(varchar, @.TermID) + '

WHERE AND u.[Active] = 1

Msg 156, Level 15, State 1, Line 31

Incorrect syntax near the keyword 'WHERE'.

Msg 170, Level 15, State 1, Line 36

Line 36: Incorrect syntax near ')'.

FROM [aaStaff] a

INNER JOIN mydb.dbo.[eeStaff] s ON s.[eeStaffId] = a.[eeStaffId]

INNER JOIN mydb2.dbo.iiUsers u ON u.eeStaffId = s.eeStaffID

LEFT JOIN mydb2.dbo.iiUserRoles ur ON ur.iiUserId = u.iiUserId AND ur.vcRoleId = 1

' + CASE WHEN @.TermID IS NULL THEN '' '' ELSE 'INNER JOIN mydb.dbo.iiClassSchedTerm st ON st.iiTermID = ' + convert(varchar, @.TermID) + '

WHERE AND u.[Active] = 1

same error after I changed it to '' ''

Incorrect syntax near [SQL UPDATE COMMAND] > cmd.ExecuteNonQuery()

Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot.

-------------------------------------

public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

using (cn)
{
cn.Open();
return cmd.ExecuteNonQuery() > 1;
}
}

capture the return value to a variable and return that. You are trying to use a shortcut that doesn't exist.

|||

I added a variable that will hold the INTEGER value of the rows updated, but it still generates the same error.
-----------------------------------------
public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

int rowsAffected;

using (cn)
{
cn.Open();
rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected > 1;
}
}

|||

try this

If (rowsAffected >1){

return true;}

else {

return false;}

|||

No luck... It still generates the same error even with the IF condition in place. Here is a copy of the error trace from the browser:

--------------------------------

Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Source Error:

Line 215: {
Line 216: cn.Open();
Line 217: rowsAffected = cmd.ExecuteNonQuery();
Line 218:
Line 219: if (rowsAffected > 1)

|||

post your stored procedure. This is where your problem is.

|||

I don't see the point of using rowsaffected >1

Why don't you just use return rowsAffected?

Isn't that going to give you the count anyway?

|||

Thanks for the quick reply.:

------------------------------

Create procedure UpdateInfoByAccountAndFullName(
@.newaccount varchar(50),
@.newfullname varchar(100),
@.rep varchar(10),
@.zip varchar(10),
@.comments varchar(2000),
@.oldaccount varchar(50),
@.oldfullname varchar(100)
)
as

update Info
Set ACCOUNT = @.newaccount,
FULL_NAME = @.newfullname,
REP = @.rep,
ZIP = @.zip,
COMMENTS = @.comments
where
ACCOUNT = @.oldaccount and
FULL_NAME = @.oldfullname

|||

Duduvi,

Why don't you just use return rowsAffected? Isn't that going to give you the count anyway? > I could use rowsAffected, but I don't really see the need of taking into account how many records where updated. This is why I used the boolean rowsAffected > 1. I just need to know that more than or 1 record(s) were updated.

|||

I don't see a problem. Try removing the return value. Just try executing the query and see if it works then you can eliminate the stored procedure as the problem.

|||

You are right. I dont see the problem neither. This is what freaks me out too. I already tried running the StoredProc and the SQL String from within SQL2005, and it did update the record. The only time it throws an exception is on my .NET code when I do a ExecuteNonQuery() with or without a return value.

|||

HA! I can't believe i missed it. you forgot to specify that your command is a stored procedure using the commandtype enum.

|||

Yep you're right. I forgot to specify the command type. Thanks again.

Incorrect syntax near @File

I am using this bulk insert command in procedure below. I am passing variable @.File inside of the procedure and I do not know the right syntax for it. Could you pls help me. When I enter the path for the file like 'C:\imp_file.csv' it works.

Thanks

ALTER procedure sp_BulkInsert1
@.File varchar(1000)

AS

BULK INSERT SQL_Tests.dbo.xRSA FROM @.File
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
CODEPAGE = 'RAW',
TABLOCK
)Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP|||:) Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP

Why do I need to use Execute Command?|||If you check BOL for the syntax of the BULK INSERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) command, you'll notice that the syntax requires a constant for the file name. The only way I know to make a variable appear as a constant is to execute it indirectly, via the EXECUTE statement. We're basically working around a limitation in the supoorted syntax.

-PatP|||Because it's dynamic sql...

Pat I can't get the injection in to the vien...maybe you can...I'm sure it can be done

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

CREATE PROC sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT myTable99 FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO

DECLARE @.x varchar(1000)
SELECT @.x = 'c:\config.sys' + '''' + ' GO SELECT ' + '''' + 'Lets execute some damaging sql' + '''' + ' GO'
EXEC sp_BulkInsert1 @.x
GO

DROP PROC sp_BulkInsert1
DROP TABLE myTable99
GO|||I'd use one of my quote fixers. I'm having to shoot from the hip since my system is toast at the moment, but it goes something like:CREATE FUNCTION dbo.FixQuote(@.pcIn VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
RETURN Replace(@.pcIn, '''', ''')
ENDGiven that little function, you could wrap it around the parameter to inhibit code injection. Note that it is MUCH better to prevent the injection at the source (the client/middleware machine) rather than trying to inhibit it at SQL Server.

-PatP

Incorrect syntax in user-defined function

In the script below is the DDL to create some tables and a UDF.

What I'm interested in is the UDF at the end. Specifically, these few
lines:

--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0

If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
message:

"Incorrect syntax near keyword ELSE"

Being a good little footsoldier, I want to release resources
explicitly, but clearly I'm putting the CLOSE and DEALLOCATE statements
in the wrong place.

Could someone please tell me where I ought to put them so that the
cursor is CLOSEd and DEALLOCATEd correctly.

By the way, I am not after negative comments on the data design, or the
logic (or lack of it) in the function, just why the syntax error
occurs.

Thanks as ever

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Employee]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[PurchaseOrder]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TimesheetItem]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[WorkOTRate]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[WorkOTRateDefaults]
GO

CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PurchaseOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[Complete] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NOT NULL ,
[TimeTo] [datetime] NOT NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
Function to determine the actual cost, in minutes, of a particular
segment of work. This is what it does, or is supposed to do.
1. From the PARAMETER WorkID, determine the conclusion of the work
block associated with the TimesheetID - i.e. StartTime + DurationMins
2. Establish whether there are records in the WorkOTRate table
corresponding to this particular WorkID, weekday and time period
3. If there are, get the amount of minutes by which the work block
coincides.
4. If there are no such records, get the default values from the
WorkOTRateDefaults table
5. If the block doesn't cross any boundaries then it's just regular
work, so just count the minutes.

25/08/2005 EC
*/

CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@.TimesheetID int)

RETURNS float

AS

BEGIN

DECLARE

@.OTRateTimeFrom datetime,
@.OTRateTimeTo as datetime,
@.OTRateMultiplier as float,
@.EndTime datetime,
@.ReturnValue as float,
@.OrderRatePerHour as money,
@.EmployeeRatePerHour as smallmoney,
@.NumRecords as int,
@.WorkID as int,
@.EmployeeID as int,
@.StartTime as smalldatetime,
@.Duration as int,
@.Found as int,
@.Chargeable as bit

-- Get the various bits and bobs needed for the calculation
SET @.ReturnValue = 0
SET @.Found = 0

SELECT
@.WorkID = WorkID,
@.EmployeeID = EmployeeID,
@.StartTime = Start,
@.Duration = DurationMins
FROM
TimesheetItem
WHERE
ItemID = @.TimesheetID

-- If this work is NOT chargeable, return 0
SELECT
@.Chargeable = Chargeable
FROM
[Work]
WHERE
WorkID = @.WorkID

IF @.Chargeable = 1

BEGIN
SET @.EndTime = DATEADD(mi, @.Duration, @.StartTime)

-- Get the rate per hour for this work
SELECT
@.OrderRatePerHour = RatePerHour
FROM
PurchaseOrder
WHERE
WorkID = @.WorkID

-- Get the rate per hour for the employee
SELECT
@.EmployeeRatePerHour = DefaultRatePerHour
FROM
Employee
WHERE
(EmployeeID = @.EmployeeID)

-- Find out if there's an OT Rate set up for this WorkID
SELECT
@.NumRecords = Count(*)
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dd, @.StartTime)))

IF @.NumRecords > 0
BEGIN

DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dw, @.StartTime)))

OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
WHILE (@.@.fetch_status=0)
BEGIN

-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)

-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)

IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT

NUBMER 2
S E
OT OT

NUMBER 3
S E
OT OT

NUBMER 4
S E
OT OT

*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0

BEGIN
DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRateDefaults
WHERE
(WorkDay = DATEPART(dw, @.StartTime))

OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier

WHILE (@.@.fetch_status=0)
BEGIN

-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)

-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)

IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT

NUBMER 2
S E
OT OT

NUMBER 3
S E
OT OT

NUBMER 4
S E
OT OT
*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END

FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
CLOSE OTRate
DEALLOCATE OTRate

-- If there were no matching OT records, it's just a regular block
of work in normal hours
IF @.Found = 0
SET @.ReturnValue = @.Duration
END

-- Finally we factor in the relation between the Employee's rate and
the Order's stated rate.
RETURN (@.ReturnValue * (@.EmployeeRatePerHour / @.OrderRatePerHour))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOteddysnips@.hotmail.com wrote:
> In the script below is the DDL to create some tables and a UDF.
> What I'm interested in is the UDF at the end. Specifically, these few
> lines:
> --CLOSE OTRate
> --DEALLOCATE OTRate
> ELSE-- @.NumRecords <= 0
I haven't actually read the code through thoroughly, so I don't know if
others are going to give you advice about doing it in a set oriented
fashion, but I believe that your close and deallocate are coming one
END too late. The two ENDs above them (to my reading) are the END of
the while loop and then the end of the if statement. When using ELSE,
the following should be adhered to:

IF <condition>
<statement or block>
ELSE
<statement or block
where statement is either a single statement or:

BEGIN
<statement> [<statement>...]
END

Damien|||Damien wrote:
> teddysnips@.hotmail.com wrote:
> > In the script below is the DDL to create some tables and a UDF.
> > What I'm interested in is the UDF at the end. Specifically, these few
> > lines:
> > --CLOSE OTRate
> > --DEALLOCATE OTRate
> > ELSE-- @.NumRecords <= 0
> I haven't actually read the code through thoroughly, so I don't know if
> others are going to give you advice about doing it in a set oriented
> fashion, but I believe that your close and deallocate are coming one
> END too late.

You're quite right - many thanks! As for doing it using sets - well, I
really don't have time!

Edward|||(teddysnips@.hotmail.com) writes:
> You're quite right - many thanks! As for doing it using sets - well, I
> really don't have time!

But you assume that anyone will have the time to run that code? I hope
that you can find the time to test it on full-size data, before you
devote your important time to something else!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Incorrect syntax

Hi Im trying to build a database in Microsoft SQL Server 2005.
Ive written the code, but when I execute it, I keep getting an error
message (below)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'customer'.
Code below, any help would be great!
--Table structure for table 'customer'
CREATE TABLE 'customer'(
'CustID' int(10) NOT NULL AUTO_INCREMENT,
'CustName' char(50) NOT NULL,
'Address' char(50) NOT NULL,
PRIMARY KEY ('CustID')
)
--Dumping data for table 'customer'
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
(3,'DailyMurkInc','Fleet Marina');
--Table Structure for table 'deliverynote'
CREATE TABLE 'deliverynote'(
'CATref' int(20) NOT NULL auto_increment,
'CustID' int(50) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipName' char(50) NOT NULL,
PRIMARY KEY ('CATref','CustID')
)
--Dumping data for table 'deliverynote'
INSERT INTO 'deliverynote' VALUES
('01235',2,'Domestic',1,'Fan'),('03278',3,'Domesti c',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
('04577',1,'Commercial',8,'Computer'),('07853',1,' Commercial',9,'Printer'),('08453',3,'Commercial',4 ,'Computer'),('08734',3,'Industrial',6,'Heater'),
('08897',1,'Commercial',10,'Fax'),('08924',3,'Dome stic',5,'Kettle'),('08992',3,'Commercial',3,'Monit or');
--Table Structure for table 'engineer'
CREATE TABLE 'engineer'(
'EngineerName' char(50) NOT NULL,
PRIMARY KEY ('EngineerName')
}
--Dumping data for table 'engineer'
INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
--Table Structure for table 'equipmentcat'
CREATE TABLE 'equipmentcat' (
'EquipCat' char(50) NOT NULL,
PRIMARY KEY ('EquipCat')
)
--Dumping data for table 'equipmentcat'
INSERT INTO 'equipmentcat' VALUES
('Commercial'),('Domestic'),('Industrial');
--Table Structure for table 'repairer'
CREATE TABLE 'repairer' (
'RepID' int(10) NOT NULL,
'RepName' char(50) NOT NULL,
PRIMARY KEY ('RepID')
)
--Dumping data for table 'repairer'
INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
White');
--Table Structure for table 'locationid'
CREATE TABLE 'locationid' (
'LocationName' char(50) NOT NULL,
PRIMARY KEY ('LocationName')
)
--Dumping data for table 'locationid'
INSERT INTO 'locationid' VALUES
('Despatch'),('Gone_Home'),('Goods_In'),('Repairer '),('Testing');
--Table Structure for table 'location'
CREATE TABLE 'location' (
'EquipNumber' int(20) NOT NULL,
'CATref' int(20) NOT NULL,
'Testing' char(50) NOT NULL,
'Despatchdate' char(50) NOT NULL,
PRIMARY KEY ('CATref')
FOREIGN KEY ('CATref')
)
--Dumping data for table 'location'
INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
(2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
1999'),
(5,'08924','April 12th 1999, April 17th 1999','April 20th
1999'),(6,'08734','April 13th 1999','April 14th
1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
1999'),
(8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
1999','May 5th 1999');
--Table Structure for table 'testrecord'
CREATE TABLE 'testrecord' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'Date' char(50) NOT NULL,
'EngineerName' char(50) NOT NULL,
'Pass/Fail' char(20) NOT NULL,
PRIMARY KEY ('CATref','CustID')
FOREIGN KEY ('CATref','CustID','EngineerName')
)
--Dumping data for table 'testrecord'
INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush', 'April 13th
1999','Perfect','Pass')
,('08453',3,4,'Computer','April 12th
1999','Botchit','Pass'),('084531',3,4,'Computer',' May 6th
2000','Perfect','Pass'),('084532',3,4,'Computer',' May 9th
2000','Botchit','Pass'),('08734',3,6,'Heater','Apr il 13th
1999','Botchit','Pass'),
('08924',3,5,'Kettle','April 12th
1999','Perfect','Fail'),('089248',3,5,'Kettle','Ma y 6th
2000','Fudgeit','Pass'),('08992',3,3,'Monitor','Ap ril 12th
1999','Fudgeit','Pass'),('089921',3,3,'Monitor','M ay 6th
2000','Perfect','Pass');
--Table Structure for table 'equipment'
CREATE TABLE 'equipment' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipName' char(50) NOT NULL,
'Goods_In_Date' char(50) NOT NULL,
'Repairer_Date' char (50) NOT NULL,
'Despatch_Date' char(50) NOT NULL,
'Home_Date' char(50) NOT NULL,
'RepID' int(10) NOT NULL,
PRIMARY KEY ('CATref', 'CustID')
FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
)
--Dumping data for table 'equipment'
INSERT INTO 'equipment' VALUES
('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
12th 1999','April 14th 1999',2),
('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
2000','May 20th 2000',3),
('03278',3,7,'Domestic','Toothbrush','April 12th 1999','April 15th
1999','April 16th 1999','April 20th 1999',1),
('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
1999'),
('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
15th 1999'),
('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
2000','May 8th 2000','May 10th
2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
15th 1999'),
('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
1999','April 17th 1999','April 20th 1999',2),
('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
2000');
Daz
You have to specify INSERT INTO for each data to be insterted in your case
See if this helps
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway Cuttings,
Euphoria')
INSERT INTO 'customer' VALUES (2,'Sinking.com','Virtual Lane, Peckham')
INSERT INTO 'customer' VALUES (3,'DailyMurkInc','Fleet Marina');
"Daz01" <dazzaf15@.hotmail.com> wrote in message
news:1166005705.720733.30310@.73g2000cwn.googlegrou ps.com...
> Hi Im trying to build a database in Microsoft SQL Server 2005.
> Ive written the code, but when I execute it, I keep getting an error
> message (below)
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'customer'.
>
> Code below, any help would be great!
> --Table structure for table 'customer'
>
> CREATE TABLE 'customer'(
> 'CustID' int(10) NOT NULL AUTO_INCREMENT,
> 'CustName' char(50) NOT NULL,
> 'Address' char(50) NOT NULL,
> PRIMARY KEY ('CustID')
> )
> --Dumping data for table 'customer'
> INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
> Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
> (3,'DailyMurkInc','Fleet Marina');
>
> --Table Structure for table 'deliverynote'
>
> CREATE TABLE 'deliverynote'(
> 'CATref' int(20) NOT NULL auto_increment,
> 'CustID' int(50) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> )
> --Dumping data for table 'deliverynote'
> INSERT INTO 'deliverynote' VALUES
> ('01235',2,'Domestic',1,'Fan'),('03278',3,'Domesti c',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
> ('04577',1,'Commercial',8,'Computer'),('07853',1,' Commercial',9,'Printer'),('08453',3,'Commercial',4 ,'Computer'),('08734',3,'Industrial',6,'Heater'),
> ('08897',1,'Commercial',10,'Fax'),('08924',3,'Dome stic',5,'Kettle'),('08992',3,'Commercial',3,'Monit or');
>
> --Table Structure for table 'engineer'
> CREATE TABLE 'engineer'(
> 'EngineerName' char(50) NOT NULL,
> PRIMARY KEY ('EngineerName')
> }
> --Dumping data for table 'engineer'
> INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
>
> --Table Structure for table 'equipmentcat'
> CREATE TABLE 'equipmentcat' (
> 'EquipCat' char(50) NOT NULL,
> PRIMARY KEY ('EquipCat')
> )
> --Dumping data for table 'equipmentcat'
>
> INSERT INTO 'equipmentcat' VALUES
> ('Commercial'),('Domestic'),('Industrial');
>
> --Table Structure for table 'repairer'
> CREATE TABLE 'repairer' (
> 'RepID' int(10) NOT NULL,
> 'RepName' char(50) NOT NULL,
> PRIMARY KEY ('RepID')
> )
> --Dumping data for table 'repairer'
> INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
> White');
>
> --Table Structure for table 'locationid'
> CREATE TABLE 'locationid' (
> 'LocationName' char(50) NOT NULL,
> PRIMARY KEY ('LocationName')
> )
> --Dumping data for table 'locationid'
>
> INSERT INTO 'locationid' VALUES
> ('Despatch'),('Gone_Home'),('Goods_In'),('Repairer '),('Testing');
>
> --Table Structure for table 'location'
> CREATE TABLE 'location' (
> 'EquipNumber' int(20) NOT NULL,
> 'CATref' int(20) NOT NULL,
> 'Testing' char(50) NOT NULL,
> 'Despatchdate' char(50) NOT NULL,
> PRIMARY KEY ('CATref')
> FOREIGN KEY ('CATref')
> )
> --Dumping data for table 'location'
>
> INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
> 1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
> (2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
> 1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
> 1999'),
> (5,'08924','April 12th 1999, April 17th 1999','April 20th
> 1999'),(6,'08734','April 13th 1999','April 14th
> 1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
> 1999'),
> (8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
> 1999','May 5th 1999');
>
> --Table Structure for table 'testrecord'
> CREATE TABLE 'testrecord' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'Date' char(50) NOT NULL,
> 'EngineerName' char(50) NOT NULL,
> 'Pass/Fail' char(20) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> FOREIGN KEY ('CATref','CustID','EngineerName')
> )
> --Dumping data for table 'testrecord'
> INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
> 1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
> 2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush', 'April 13th
> 1999','Perfect','Pass')
> ,('08453',3,4,'Computer','April 12th
> 1999','Botchit','Pass'),('084531',3,4,'Computer',' May 6th
> 2000','Perfect','Pass'),('084532',3,4,'Computer',' May 9th
> 2000','Botchit','Pass'),('08734',3,6,'Heater','Apr il 13th
> 1999','Botchit','Pass'),
> ('08924',3,5,'Kettle','April 12th
> 1999','Perfect','Fail'),('089248',3,5,'Kettle','Ma y 6th
> 2000','Fudgeit','Pass'),('08992',3,3,'Monitor','Ap ril 12th
> 1999','Fudgeit','Pass'),('089921',3,3,'Monitor','M ay 6th
> 2000','Perfect','Pass');
>
> --Table Structure for table 'equipment'
> CREATE TABLE 'equipment' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> 'Goods_In_Date' char(50) NOT NULL,
> 'Repairer_Date' char (50) NOT NULL,
> 'Despatch_Date' char(50) NOT NULL,
> 'Home_Date' char(50) NOT NULL,
> 'RepID' int(10) NOT NULL,
> PRIMARY KEY ('CATref', 'CustID')
> FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
> )
> --Dumping data for table 'equipment'
> INSERT INTO 'equipment' VALUES
> ('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
> 12th 1999','April 14th 1999',2),
> ('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
> 2000','May 20th 2000',3),
> ('03278',3,7,'Domestic','Toothbrush','April 12th 1999', 'April 15th
> 1999','April 16th 1999','April 20th 1999',1),
> ('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
> 1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
> 1999'),
> ('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
> 1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
> 15th 1999'),
> ('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
> 2000','May 8th 2000','May 10th
> 2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
> 15th 1999'),
> ('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
> 1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
> 1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
> 1999','April 17th 1999','April 20th 1999',2),
> ('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
> 2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
> 2000');
>

Incorrect syntax

Hi Im trying to build a database in Microsoft SQL Server 2005.
Ive written the code, but when I execute it, I keep getting an error
message (below)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'customer'.
Code below, any help would be great!
--Table structure for table 'customer'
CREATE TABLE 'customer'(
'CustID' int(10) NOT NULL AUTO_INCREMENT,
'CustName' char(50) NOT NULL,
'Address' char(50) NOT NULL,
PRIMARY KEY ('CustID')
)
--Dumping data for table 'customer'
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
(3,'DailyMurkInc','Fleet Marina');
--Table Structure for table 'deliverynote'
CREATE TABLE 'deliverynote'(
'CATref' int(20) NOT NULL auto_increment,
'CustID' int(50) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipName' char(50) NOT NULL,
PRIMARY KEY ('CATref','CustID')
)
--Dumping data for table 'deliverynote'
INSERT INTO 'deliverynote' VALUES
('01235',2,'Domestic',1,'Fan'),('03278',
3,'Domestic',7,'Toothbrush'),('03452
',2,'Domestic',2,'Fan'),
('04577',1,'Commercial',8,'Computer'),('
07853',1,'Commercial',9,'Printer'),(
'08453',3,'Commercial',4,'Computer'),('0
8734',3,'Industrial',6,'Heater'),
('08897',1,'Commercial',10,'Fax'),('0892
4',3,'Domestic',5,'Kettle'),('08992'
,3,'Commercial',3,'Monitor');
--Table Structure for table 'engineer'
CREATE TABLE 'engineer'(
'EngineerName' char(50) NOT NULL,
PRIMARY KEY ('EngineerName')
}
--Dumping data for table 'engineer'
INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
--Table Structure for table 'equipmentcat'
CREATE TABLE 'equipmentcat' (
'EquipCat' char(50) NOT NULL,
PRIMARY KEY ('EquipCat')
)
--Dumping data for table 'equipmentcat'
INSERT INTO 'equipmentcat' VALUES
('Commercial'),('Domestic'),('Industrial
');
--Table Structure for table 'repairer'
CREATE TABLE 'repairer' (
'RepID' int(10) NOT NULL,
'RepName' char(50) NOT NULL,
PRIMARY KEY ('RepID')
)
--Dumping data for table 'repairer'
INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
White');
--Table Structure for table 'locationid'
CREATE TABLE 'locationid' (
'LocationName' char(50) NOT NULL,
PRIMARY KEY ('LocationName')
)
--Dumping data for table 'locationid'
INSERT INTO 'locationid' VALUES
('Despatch'),('Gone_Home'),('Goods_In'),
('Repairer'),('Testing');
--Table Structure for table 'location'
CREATE TABLE 'location' (
'EquipNumber' int(20) NOT NULL,
'CATref' int(20) NOT NULL,
'Testing' char(50) NOT NULL,
'Despatchdate' char(50) NOT NULL,
PRIMARY KEY ('CATref')
FOREIGN KEY ('CATref')
)
--Dumping data for table 'location'
INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
(2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
1999'),
(5,'08924','April 12th 1999, April 17th 1999','April 20th
1999'),(6,'08734','April 13th 1999','April 14th
1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
1999'),
(8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
1999','May 5th 1999');
--Table Structure for table 'testrecord'
CREATE TABLE 'testrecord' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'Date' char(50) NOT NULL,
'EngineerName' char(50) NOT NULL,
'Pass/Fail' char(20) NOT NULL,
PRIMARY KEY ('CATref','CustID')
FOREIGN KEY ('CATref','CustID','EngineerName')
)
--Dumping data for table 'testrecord'
INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
1999','Botchit','Pass'),('012357',2,1,'F
an','May 2nd
2000','Fudgeit','Fail'),('03278',3,7,'To
othbrush','April 13th
1999','Perfect','Pass')
,('08453',3,4,'Computer','April 12th
1999','Botchit','Pass'),('084531',3,4,'C
omputer','May 6th
2000','Perfect','Pass'),('084532',3,4,'C
omputer','May 9th
2000','Botchit','Pass'),('08734',3,6,'He
ater','April 13th
1999','Botchit','Pass'),
('08924',3,5,'Kettle','April 12th
1999','Perfect','Fail'),('089248',3,5,'K
ettle','May 6th
2000','Fudgeit','Pass'),('08992',3,3,'Mo
nitor','April 12th
1999','Fudgeit','Pass'),('089921',3,3,'M
onitor','May 6th
2000','Perfect','Pass');
--Table Structure for table 'equipment'
CREATE TABLE 'equipment' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipName' char(50) NOT NULL,
'Goods_In_Date' char(50) NOT NULL,
'Repairer_Date' char (50) NOT NULL,
'Despatch_Date' char(50) NOT NULL,
'Home_Date' char(50) NOT NULL,
'RepID' int(10) NOT NULL,
PRIMARY KEY ('CATref', 'CustID')
FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
)
--Dumping data for table 'equipment'
INSERT INTO 'equipment' VALUES
('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
12th 1999','April 14th 1999',2),
('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
2000','May 20th 2000',3),
('03278',3,7,'Domestic','Toothbrush','Ap
ril 12th 1999', 'April 15th
1999','April 16th 1999','April 20th 1999',1),
('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
1999'),('04577',1,8,'Commercial','Comput
er','May 1st 1999','May 5th
1999'),
('07853',1,9,'Commercial','Printer','May
1st 1999','May 5th
1999'),('08453',3,4,'Commercial','Comput
er','April 12th 1999','April
15th 1999'),
('084531',3,4,'Commercial','Computer','M
ay 5th 2000','May 6th
2000','May 8th 2000','May 10th
2000',1),('08734',3,6,'Industrial','Heat
er','April 12th 1999','April
15th 1999'),
('08892',3,3,'Commercial','Monitor','Apr
il 12th 1999','April 15th
1999'),('08897',1,10,'Commercial','Fax',
'May 1st 1999','May 5th
1999'),('08924',3,5,'Domestic','Kettle',
'April 12th 1999','April 13th
1999','April 17th 1999','April 20th 1999',2),
('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
2000'),('089921',3,3,'Commercial','Monit
or','May 5th 2000','May 10th
2000');Daz
You have to specify INSERT INTO for each data to be insterted in your case
See if this helps
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway Cuttings,
Euphoria')
INSERT INTO 'customer' VALUES (2,'Sinking.com','Virtual Lane, Peckham')
INSERT INTO 'customer' VALUES (3,'DailyMurkInc','Fleet Marina');
"Daz01" <dazzaf15@.hotmail.com> wrote in message
news:1166005705.720733.30310@.73g2000cwn.googlegroups.com...
> Hi Im trying to build a database in Microsoft SQL Server 2005.
> Ive written the code, but when I execute it, I keep getting an error
> message (below)
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'customer'.
>
> Code below, any help would be great!
> --Table structure for table 'customer'
>
> CREATE TABLE 'customer'(
> 'CustID' int(10) NOT NULL AUTO_INCREMENT,
> 'CustName' char(50) NOT NULL,
> 'Address' char(50) NOT NULL,
> PRIMARY KEY ('CustID')
> )
> --Dumping data for table 'customer'
> INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
> Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
> (3,'DailyMurkInc','Fleet Marina');
>
> --Table Structure for table 'deliverynote'
>
> CREATE TABLE 'deliverynote'(
> 'CATref' int(20) NOT NULL auto_increment,
> 'CustID' int(50) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> )
> --Dumping data for table 'deliverynote'
> INSERT INTO 'deliverynote' VALUES
> ('01235',2,'Domestic',1,'Fan'),('03278',
3,'Domestic',7,'Toothbrush'),('034
52',2,'Domestic',2,'Fan'),
> ('04577',1,'Commercial',8,'Computer'),('
07853',1,'Commercial',9,'Printer')
,('08453',3,'Commercial',4,'Computer'),(
'08734',3,'Industrial',6,'Heater'),
> ('08897',1,'Commercial',10,'Fax'),('0892
4',3,'Domestic',5,'Kettle'),('0899
2',3,'Commercial',3,'Monitor');
>
> --Table Structure for table 'engineer'
> CREATE TABLE 'engineer'(
> 'EngineerName' char(50) NOT NULL,
> PRIMARY KEY ('EngineerName')
> }
> --Dumping data for table 'engineer'
> INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
>
> --Table Structure for table 'equipmentcat'
> CREATE TABLE 'equipmentcat' (
> 'EquipCat' char(50) NOT NULL,
> PRIMARY KEY ('EquipCat')
> )
> --Dumping data for table 'equipmentcat'
>
> INSERT INTO 'equipmentcat' VALUES
> ('Commercial'),('Domestic'),('Industrial
');
>
> --Table Structure for table 'repairer'
> CREATE TABLE 'repairer' (
> 'RepID' int(10) NOT NULL,
> 'RepName' char(50) NOT NULL,
> PRIMARY KEY ('RepID')
> )
> --Dumping data for table 'repairer'
> INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
> White');
>
> --Table Structure for table 'locationid'
> CREATE TABLE 'locationid' (
> 'LocationName' char(50) NOT NULL,
> PRIMARY KEY ('LocationName')
> )
> --Dumping data for table 'locationid'
>
> INSERT INTO 'locationid' VALUES
> ('Despatch'),('Gone_Home'),('Goods_In'),
('Repairer'),('Testing');
>
> --Table Structure for table 'location'
> CREATE TABLE 'location' (
> 'EquipNumber' int(20) NOT NULL,
> 'CATref' int(20) NOT NULL,
> 'Testing' char(50) NOT NULL,
> 'Despatchdate' char(50) NOT NULL,
> PRIMARY KEY ('CATref')
> FOREIGN KEY ('CATref')
> )
> --Dumping data for table 'location'
>
> INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
> 1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
> (2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
> 1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
> 1999'),
> (5,'08924','April 12th 1999, April 17th 1999','April 20th
> 1999'),(6,'08734','April 13th 1999','April 14th
> 1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
> 1999'),
> (8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
> 1999','May 5th 1999');
>
> --Table Structure for table 'testrecord'
> CREATE TABLE 'testrecord' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'Date' char(50) NOT NULL,
> 'EngineerName' char(50) NOT NULL,
> 'Pass/Fail' char(20) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> FOREIGN KEY ('CATref','CustID','EngineerName')
> )
> --Dumping data for table 'testrecord'
> INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
> 1999','Botchit','Pass'),('012357',2,1,'F
an','May 2nd
> 2000','Fudgeit','Fail'),('03278',3,7,'To
othbrush','April 13th
> 1999','Perfect','Pass')
> ,('08453',3,4,'Computer','April 12th
> 1999','Botchit','Pass'),('084531',3,4,'C
omputer','May 6th
> 2000','Perfect','Pass'),('084532',3,4,'C
omputer','May 9th
> 2000','Botchit','Pass'),('08734',3,6,'He
ater','April 13th
> 1999','Botchit','Pass'),
> ('08924',3,5,'Kettle','April 12th
> 1999','Perfect','Fail'),('089248',3,5,'K
ettle','May 6th
> 2000','Fudgeit','Pass'),('08992',3,3,'Mo
nitor','April 12th
> 1999','Fudgeit','Pass'),('089921',3,3,'M
onitor','May 6th
> 2000','Perfect','Pass');
>
> --Table Structure for table 'equipment'
> CREATE TABLE 'equipment' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> 'Goods_In_Date' char(50) NOT NULL,
> 'Repairer_Date' char (50) NOT NULL,
> 'Despatch_Date' char(50) NOT NULL,
> 'Home_Date' char(50) NOT NULL,
> 'RepID' int(10) NOT NULL,
> PRIMARY KEY ('CATref', 'CustID')
> FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
> )
> --Dumping data for table 'equipment'
> INSERT INTO 'equipment' VALUES
> ('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
> 12th 1999','April 14th 1999',2),
> ('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
> 2000','May 20th 2000',3),
> ('03278',3,7,'Domestic','Toothbrush','Ap
ril 12th 1999', 'April 15th
> 1999','April 16th 1999','April 20th 1999',1),
> ('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
> 1999'),('04577',1,8,'Commercial','Comput
er','May 1st 1999','May 5th
> 1999'),
> ('07853',1,9,'Commercial','Printer','May
1st 1999','May 5th
> 1999'),('08453',3,4,'Commercial','Comput
er','April 12th 1999','April
> 15th 1999'),
> ('084531',3,4,'Commercial','Computer','M
ay 5th 2000','May 6th
> 2000','May 8th 2000','May 10th
> 2000',1),('08734',3,6,'Industrial','Heat
er','April 12th 1999','April
> 15th 1999'),
> ('08892',3,3,'Commercial','Monitor','Apr
il 12th 1999','April 15th
> 1999'),('08897',1,10,'Commercial','Fax',
'May 1st 1999','May 5th
> 1999'),('08924',3,5,'Domestic','Kettle',
'April 12th 1999','April 13th
> 1999','April 17th 1999','April 20th 1999',2),
> ('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
> 2000'),('089921',3,3,'Commercial','Monit
or','May 5th 2000','May 10th
> 2000');
>|||Do not put 'single quotes' around the table and object names.
As already noted, each row INSERTed needs its own INSERT.
Once you get those taken care of the problems that remain will be
easier to see.
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 02:28:25 -0800, "Daz01" <dazzaf15@.hotmail.com> wrote:

>Hi Im trying to build a database in Microsoft SQL Server 2005.
>Ive written the code, but when I execute it, I keep getting an error
>message (below)
>Msg 102, Level 15, State 1, Line 1
>Incorrect syntax near 'customer'.
>
>Code below, any help would be great!
>--Table structure for table 'customer'
>
>CREATE TABLE 'customer'(
>'CustID' int(10) NOT NULL AUTO_INCREMENT,
>'CustName' char(50) NOT NULL,
>'Address' char(50) NOT NULL,
>PRIMARY KEY ('CustID')
> )
>--Dumping data for table 'customer'
>INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
>Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
>(3,'DailyMurkInc','Fleet Marina');

Incorrect syntax

Hi Im trying to build a database in Microsoft SQL Server 2005.
Ive written the code, but when I execute it, I keep getting an error
message (below)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'customer'.
Code below, any help would be great!
--Table structure for table 'customer'
CREATE TABLE 'customer'(
'CustID' int(10) NOT NULL AUTO_INCREMENT,
'CustName' char(50) NOT NULL,
'Address' char(50) NOT NULL,
PRIMARY KEY ('CustID')
)
--Dumping data for table 'customer'
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
(3,'DailyMurkInc','Fleet Marina');
--Table Structure for table 'deliverynote'
CREATE TABLE 'deliverynote'(
'CATref' int(20) NOT NULL auto_increment,
'CustID' int(50) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipName' char(50) NOT NULL,
PRIMARY KEY ('CATref','CustID')
)
--Dumping data for table 'deliverynote'
INSERT INTO 'deliverynote' VALUES
('01235',2,'Domestic',1,'Fan'),('03278',3,'Domestic',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
('04577',1,'Commercial',8,'Computer'),('07853',1,'Commercial',9,'Printer'),('08453',3,'Commercial',4,'Computer'),('08734',3,'Industrial',6,'Heater'),
('08897',1,'Commercial',10,'Fax'),('08924',3,'Domestic',5,'Kettle'),('08992',3,'Commercial',3,'Monitor');
--Table Structure for table 'engineer'
CREATE TABLE 'engineer'(
'EngineerName' char(50) NOT NULL,
PRIMARY KEY ('EngineerName')
}
--Dumping data for table 'engineer'
INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
--Table Structure for table 'equipmentcat'
CREATE TABLE 'equipmentcat' (
'EquipCat' char(50) NOT NULL,
PRIMARY KEY ('EquipCat')
)
--Dumping data for table 'equipmentcat'
INSERT INTO 'equipmentcat' VALUES
('Commercial'),('Domestic'),('Industrial');
--Table Structure for table 'repairer'
CREATE TABLE 'repairer' (
'RepID' int(10) NOT NULL,
'RepName' char(50) NOT NULL,
PRIMARY KEY ('RepID')
)
--Dumping data for table 'repairer'
INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
White');
--Table Structure for table 'locationid'
CREATE TABLE 'locationid' (
'LocationName' char(50) NOT NULL,
PRIMARY KEY ('LocationName')
)
--Dumping data for table 'locationid'
INSERT INTO 'locationid' VALUES
('Despatch'),('Gone_Home'),('Goods_In'),('Repairer'),('Testing');
--Table Structure for table 'location'
CREATE TABLE 'location' (
'EquipNumber' int(20) NOT NULL,
'CATref' int(20) NOT NULL,
'Testing' char(50) NOT NULL,
'Despatchdate' char(50) NOT NULL,
PRIMARY KEY ('CATref')
FOREIGN KEY ('CATref')
)
--Dumping data for table 'location'
INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
(2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
1999'),
(5,'08924','April 12th 1999, April 17th 1999','April 20th
1999'),(6,'08734','April 13th 1999','April 14th
1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
1999'),
(8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
1999','May 5th 1999');
--Table Structure for table 'testrecord'
CREATE TABLE 'testrecord' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'Date' char(50) NOT NULL,
'EngineerName' char(50) NOT NULL,
'Pass/Fail' char(20) NOT NULL,
PRIMARY KEY ('CATref','CustID')
FOREIGN KEY ('CATref','CustID','EngineerName')
)
--Dumping data for table 'testrecord'
INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush','April 13th
1999','Perfect','Pass')
,('08453',3,4,'Computer','April 12th
1999','Botchit','Pass'),('084531',3,4,'Computer','May 6th
2000','Perfect','Pass'),('084532',3,4,'Computer','May 9th
2000','Botchit','Pass'),('08734',3,6,'Heater','April 13th
1999','Botchit','Pass'),
('08924',3,5,'Kettle','April 12th
1999','Perfect','Fail'),('089248',3,5,'Kettle','May 6th
2000','Fudgeit','Pass'),('08992',3,3,'Monitor','April 12th
1999','Fudgeit','Pass'),('089921',3,3,'Monitor','May 6th
2000','Perfect','Pass');
--Table Structure for table 'equipment'
CREATE TABLE 'equipment' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipName' char(50) NOT NULL,
'Goods_In_Date' char(50) NOT NULL,
'Repairer_Date' char (50) NOT NULL,
'Despatch_Date' char(50) NOT NULL,
'Home_Date' char(50) NOT NULL,
'RepID' int(10) NOT NULL,
PRIMARY KEY ('CATref', 'CustID')
FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
)
--Dumping data for table 'equipment'
INSERT INTO 'equipment' VALUES
('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
12th 1999','April 14th 1999',2),
('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
2000','May 20th 2000',3),
('03278',3,7,'Domestic','Toothbrush','April 12th 1999', 'April 15th
1999','April 16th 1999','April 20th 1999',1),
('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
1999'),
('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
15th 1999'),
('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
2000','May 8th 2000','May 10th
2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
15th 1999'),
('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
1999','April 17th 1999','April 20th 1999',2),
('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
2000');Daz
You have to specify INSERT INTO for each data to be insterted in your case
See if this helps
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway Cuttings,
Euphoria')
INSERT INTO 'customer' VALUES (2,'Sinking.com','Virtual Lane, Peckham')
INSERT INTO 'customer' VALUES (3,'DailyMurkInc','Fleet Marina');
"Daz01" <dazzaf15@.hotmail.com> wrote in message
news:1166005705.720733.30310@.73g2000cwn.googlegroups.com...
> Hi Im trying to build a database in Microsoft SQL Server 2005.
> Ive written the code, but when I execute it, I keep getting an error
> message (below)
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'customer'.
>
> Code below, any help would be great!
> --Table structure for table 'customer'
>
> CREATE TABLE 'customer'(
> 'CustID' int(10) NOT NULL AUTO_INCREMENT,
> 'CustName' char(50) NOT NULL,
> 'Address' char(50) NOT NULL,
> PRIMARY KEY ('CustID')
> )
> --Dumping data for table 'customer'
> INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
> Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
> (3,'DailyMurkInc','Fleet Marina');
>
> --Table Structure for table 'deliverynote'
>
> CREATE TABLE 'deliverynote'(
> 'CATref' int(20) NOT NULL auto_increment,
> 'CustID' int(50) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> )
> --Dumping data for table 'deliverynote'
> INSERT INTO 'deliverynote' VALUES
> ('01235',2,'Domestic',1,'Fan'),('03278',3,'Domestic',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
> ('04577',1,'Commercial',8,'Computer'),('07853',1,'Commercial',9,'Printer'),('08453',3,'Commercial',4,'Computer'),('08734',3,'Industrial',6,'Heater'),
> ('08897',1,'Commercial',10,'Fax'),('08924',3,'Domestic',5,'Kettle'),('08992',3,'Commercial',3,'Monitor');
>
> --Table Structure for table 'engineer'
> CREATE TABLE 'engineer'(
> 'EngineerName' char(50) NOT NULL,
> PRIMARY KEY ('EngineerName')
> }
> --Dumping data for table 'engineer'
> INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
>
> --Table Structure for table 'equipmentcat'
> CREATE TABLE 'equipmentcat' (
> 'EquipCat' char(50) NOT NULL,
> PRIMARY KEY ('EquipCat')
> )
> --Dumping data for table 'equipmentcat'
>
> INSERT INTO 'equipmentcat' VALUES
> ('Commercial'),('Domestic'),('Industrial');
>
> --Table Structure for table 'repairer'
> CREATE TABLE 'repairer' (
> 'RepID' int(10) NOT NULL,
> 'RepName' char(50) NOT NULL,
> PRIMARY KEY ('RepID')
> )
> --Dumping data for table 'repairer'
> INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
> White');
>
> --Table Structure for table 'locationid'
> CREATE TABLE 'locationid' (
> 'LocationName' char(50) NOT NULL,
> PRIMARY KEY ('LocationName')
> )
> --Dumping data for table 'locationid'
>
> INSERT INTO 'locationid' VALUES
> ('Despatch'),('Gone_Home'),('Goods_In'),('Repairer'),('Testing');
>
> --Table Structure for table 'location'
> CREATE TABLE 'location' (
> 'EquipNumber' int(20) NOT NULL,
> 'CATref' int(20) NOT NULL,
> 'Testing' char(50) NOT NULL,
> 'Despatchdate' char(50) NOT NULL,
> PRIMARY KEY ('CATref')
> FOREIGN KEY ('CATref')
> )
> --Dumping data for table 'location'
>
> INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
> 1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
> (2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
> 1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
> 1999'),
> (5,'08924','April 12th 1999, April 17th 1999','April 20th
> 1999'),(6,'08734','April 13th 1999','April 14th
> 1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
> 1999'),
> (8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
> 1999','May 5th 1999');
>
> --Table Structure for table 'testrecord'
> CREATE TABLE 'testrecord' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'Date' char(50) NOT NULL,
> 'EngineerName' char(50) NOT NULL,
> 'Pass/Fail' char(20) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> FOREIGN KEY ('CATref','CustID','EngineerName')
> )
> --Dumping data for table 'testrecord'
> INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
> 1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
> 2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush','April 13th
> 1999','Perfect','Pass')
> ,('08453',3,4,'Computer','April 12th
> 1999','Botchit','Pass'),('084531',3,4,'Computer','May 6th
> 2000','Perfect','Pass'),('084532',3,4,'Computer','May 9th
> 2000','Botchit','Pass'),('08734',3,6,'Heater','April 13th
> 1999','Botchit','Pass'),
> ('08924',3,5,'Kettle','April 12th
> 1999','Perfect','Fail'),('089248',3,5,'Kettle','May 6th
> 2000','Fudgeit','Pass'),('08992',3,3,'Monitor','April 12th
> 1999','Fudgeit','Pass'),('089921',3,3,'Monitor','May 6th
> 2000','Perfect','Pass');
>
> --Table Structure for table 'equipment'
> CREATE TABLE 'equipment' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> 'Goods_In_Date' char(50) NOT NULL,
> 'Repairer_Date' char (50) NOT NULL,
> 'Despatch_Date' char(50) NOT NULL,
> 'Home_Date' char(50) NOT NULL,
> 'RepID' int(10) NOT NULL,
> PRIMARY KEY ('CATref', 'CustID')
> FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
> )
> --Dumping data for table 'equipment'
> INSERT INTO 'equipment' VALUES
> ('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
> 12th 1999','April 14th 1999',2),
> ('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
> 2000','May 20th 2000',3),
> ('03278',3,7,'Domestic','Toothbrush','April 12th 1999', 'April 15th
> 1999','April 16th 1999','April 20th 1999',1),
> ('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
> 1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
> 1999'),
> ('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
> 1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
> 15th 1999'),
> ('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
> 2000','May 8th 2000','May 10th
> 2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
> 15th 1999'),
> ('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
> 1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
> 1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
> 1999','April 17th 1999','April 20th 1999',2),
> ('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
> 2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
> 2000');
>|||Do not put 'single quotes' around the table and object names.
As already noted, each row INSERTed needs its own INSERT.
Once you get those taken care of the problems that remain will be
easier to see.
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 02:28:25 -0800, "Daz01" <dazzaf15@.hotmail.com> wrote:
>Hi Im trying to build a database in Microsoft SQL Server 2005.
>Ive written the code, but when I execute it, I keep getting an error
>message (below)
>Msg 102, Level 15, State 1, Line 1
>Incorrect syntax near 'customer'.
>
>Code below, any help would be great!
>--Table structure for table 'customer'
>
>CREATE TABLE 'customer'(
>'CustID' int(10) NOT NULL AUTO_INCREMENT,
>'CustName' char(50) NOT NULL,
>'Address' char(50) NOT NULL,
>PRIMARY KEY ('CustID')
>)
>--Dumping data for table 'customer'
>INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
>Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
>(3,'DailyMurkInc','Fleet Marina');

Incorrect sql statement - not reading variable

In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.

However this value is not then working in the following line:

strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

This statement is not reading any value for ProjectID and so is selecting all from the table....

How do I write this statement to pick up the variable above???

Thanks in advance for your help!

Clare

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"Dim connStringAsString ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"

da =

New Data.SqlClient.SqlDataAdapter(strSQL, connString)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

ForEach tempRowAs Data.DataRowIn ds.Tables(0).Rows

tempRow.Item(

"imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))Next

ImgGrid3.DataSource = ds

ImgGrid3.DataBind()

EndSub

hi,

try this

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.projectID"

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

yourcommand.Pramaters.Add(p)

|||

Thanks for your quick response but this is not working. I do not have a sql command declared, I am using strSQL ( a string), and da (a sqldataadaptor).

Can you help??

Thanks very much

|||

hi cabby

use following

Dim da As Data.SqlClient.SqlDataAdapterDim strSQL As StringDim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.ProjectID" dim sqlcmdas new sqlcommand(strsql, connstring)sqlcmd.parameter.addwithvalue("ProjectID", request.querystring("projectid"));Dim connString As String ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(sqlcmd) da.Fill(ds)in case your project idis char/varchar field use followingas parametervaluesqlcmd.parameter.addwithvalue("ProjectID","'" + request.querystring("projectid") +"'" ) 'see its included in single quotes

HTH

satish.

|||Hiya,|||

Hiya,

Thanks for the response. Sorry if I am asking simple questions but I have tried your code and am getting the following error:

Dim

sqlcmdAsNew sqlcommand(strSQL, connstring) - - - type 'sqlcommand' is not defined... so I change this to

Dim

sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString) -- and then I get connString - value of type string cannot be converted to 'System.Data.SqlClient.SqlConnection' and I also get an error on the next line

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid")) -- parameter is not a member of 'System.Data.SqlClient.SqlConnection'

My code now is:

Dim

dsAsNew Data.DataSetDim daAs Data.SqlClient.SqlDataAdapterDim strSQLAsStringDim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString)

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid"))Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

Thanks for the help!

Clare

|||first of all make sure you've imported system.data.sqlclient.

DimdsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"

Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL,new sqlconnection(connString) )

sqlcmd.parameters.addwithvalue(

"ProjectID", ProjectID) //make sure variable projectid is not null

Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

hope it will help now.

thanks,

satish.

|||

Thanks again, really appreciate the help.

Nearly there now I think...

I am getting to the line:

da.Fill(ds)

and getting the error:

Invalid column name'@.ProjectID'.

Any ideas?

Thanks

Clare

|||

hi,

try this

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

sqlcmd.Pramaters.Add(p)

|||

errr sorry appologies for silly mistake of me change following line of my code

sqlcmd.parameters.addwithvalue("ProjectID", ProjectID) //make sure variable projectid is not null

to

sqlcmd.parameters.addwithvalue("@.ProjectID", ProjectID) //make sure variable projectid is not null

thanks,

satish.

|||

Hi,

I am still getting the same error. Do I need to declare in my sub function header that it is taking in a parameter? And if so how do I write this on page load?

Thanks

Clare

|||

After all this I can just use:

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] =" & Request.QueryString("ProjectID")

Thanks for all the help

|||

hi

try this, it works fine

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =Request.QueryString("ProjectID")

p.SqlDbType =Data .SqlDbType .String;

sqlcmd.Pramaters.Add(p)

Incorrect Handling of Real Numbers

Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns th
e
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:

> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if th
is
> problem might be Windows or hardware based. I did this same test on sever
al
> different machines, (dual Xeons and P4 laptops) and every time it returns
the
> incorrect result.