Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shohamt
Starting Member
2 Posts |
 Posted - 2012-02-04 : 18:28:47
|
| helloi have a table like this:CREATE TABLE [dbo].[Survey_0212]( [FirstNameHe] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [LastName] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [Id] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [Phone1] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [Phone2] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [ContactEveryDay] [bit] NULL, [ContactHours1] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [ContactHours2] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [ContactHours3] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [ContactHours4] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [school] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [ParentName] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [Brothers] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [BirthCity] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [AnotherContact] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [ActionDate] [datetime] NULL) ON [PRIMARY]im trying to run this INSERT query:INSERT INTO [Survey_0212] ([FirstNameHe] ,[LastName] ,[Id] ,[Phone1] ,[Phone2] ,[ContactEveryDay] ,[ContactHours1] ,[ContactHours2] ,[ContactHours3] ,[ContactHours4] ,[school] ,[ParentName] ,[Brothers] ,[BirthCity] ,[AnotherContact] ,[ActionDate]) VALUES ([''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''] ,[''])but i'm getting this error:Msg 128, Level 15, State 1, Line 20The name "''" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.anyone have an idea? |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-04 : 21:48:15
|
if you enclose something in square braces you are telling sql server to treat the value as if it were a column or other object nametherefore your VALUES part is wrong. If you want to insert the empty string then it is justVALUES ('', '', '', '', '').....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-04 : 21:49:11
|
| but you shouldn't try putting the empty string into some of those columns. In particuar the BIT and DATETIME columns.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
shohamt
Starting Member
2 Posts |
Posted - 2012-02-05 : 01:54:36
|
quote:
Originally posted by Transact Charlie
if you enclose something in square braces you are telling sql server to treat the value as if it were a column or other object nametherefore your VALUES part is wrong. If you want to insert the empty string then it is justVALUES ('', '', '', '', '').....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
i removed the square braces and its working fine now...i thought i can use those braces anywhere on the query...thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-05 : 03:16:06
|
| "i thought i can use those braces anywhere on the query..."[SomeName] - a database Object Name or Alias Name((@Variable + @OtherVariable) / 1234) - an expression - which can be a single entity - @Variable = 2@Variable = (2)@Variable = (((((2)))))are all valid. Perhaps that is what you where thinking of ?Note that SQL is making an implicit conversion from '' to BIT / INT / etc. datatype columns - it would be better not to rely on that as you might get an effect you are not expecting. Give them a valid value, or use DEFAULT (for the default defined for the column) or NULL if there is currently no known value for that column.Or leave them out - the database will then use DEFAULT (if there is one defined) or NULL.If the column is defined as not allowing NULLs then you will get an error if you try to store one - which is a Good Thing because it will tell you that you are supposed to have a proper value. If you use '' then that is a real value, so the database will use that, but actually you don't have/know a value so you are fooling yourself/the database using a "dummy" value. |
|
|
|
|
|
|
|
|
The name "''" is not permitted in this context