hi. i am relatively new to sql. i am wondering how to resolve my "incorrect syntax error" on an IP Address string i am trying to add.
the table has IPAddress as data type "text" and length 16 (which won't let me change the length). It seems that sql is looking for a data type with no more than one decimal (money, float, etc). How do i resolve this? thanks in advance. PeterYou need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).
-PatP|||Hi. THanks for the reply. Well, i am new to sql but not that new. i have tried changing the data type a number of times. I tried varchar, text, nvarchar, and a few others. I did this in the designer view of the SQL EM. I still get the same response... "incorrect syntax error". For numerical data types that expect one decimal place, i understand this, but i don't get why i am getting this error for string-like data types such as var char, etc. more suggestions appreciated.
Originally posted by Pat Phelan
You need to change the datatype of the column. This can be done using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_5rg2.asp) or the ALTER TABLE ALTER COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) command from SQL Query Analyzer (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp).
-PatP|||This requires the "digital ball pein" to fix it, because you can't change the type of a TEXT, NTEXT, or IMAGE column. The following script demonstrates:CREATE TABLE dbo.foo2 (
fooId INT IDENTITY
, thingie TEXT
)
GO
ALTER TABLE dbo.foo2
ALTER COLUMN thingie VARCHAR(20) -- Server: Msg 4928, Level 16, State 1, Line 1
-- Cannot alter column 'thingie' because it is 'text'.
GO
CREATE TABLE dbo.foo3 (
fooId INT IDENTITY
, thingie VARCHAR(20)
)
GO
INSERT INTO dbo.foo3 (thingie)
SELECT thingie
FROM dbo.foo2The short answer is that you must build a new table, and copy the data from the old table into the new table. Then you should be good to go.
-PatP|||hi. thanks again. i droped the table as you suggested and redesigned it
with the IPAddress row as varchar(30) instead of text and ran the insert statement and sql is still complaining about anything that comes after the first decimal point in the IP address. for and inserted IP of 121.111.12.1 sql complains that there is an "incorrect syntax near '.12'".
i am really stumped. if you have other suggestions, much appreciated.|||You never showed us what your insert statment looked like. I ran this and it worked fine.
insert into foo3 ([thingie]) values('121.111.12.1')|||Here's the whole thing(ie) ;-)
CREATE TABLE dbo.foo3 (
fooId INT IDENTITY
, thingie VARCHAR(20)
)
GO
insert into foo3 ([thingie]) values('121.111.12.1')
GO
SELECT [fooId], [thingie] FROM [TESTDB].[dbo].[foo3]
GO|||It would help if you posted the actual SQL command it's performing.
From what you have said it looks like you aren't quoting the IP string.
Since it's a VARCHAR field, you should insert/update the IP with single quotes around it.
Sloppy oracle-centric SQL warning!
insert into iplist using select 'SERVERNAME','121.111.12.1' from other_table
Showing posts with label address. Show all posts
Showing posts with label address. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Include Tick ('') in varchar?
Hi guys,
Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?
Address3 Varchar(100) is where I want to put it.
Many thanks,
Millicent.
Hi,
If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.
Ray|||I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much
I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.
|||Thanks all.
Millie.
|||Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).
http://universitywriting.shu.ac.uk/punct/advice/d_double.htm
Ray
Subscribe to:
Posts (Atom)