I'm getting this error when I try to preview my report: The value expression
for the textbox 'RepairOrderID' refers to a field. Fields cannot be used in
page headers or footers.
I searched through the news groups and a found suggestion to replace:
Fields!RepairOrderID.Value
with
ReportItems!RepairOrderID.Value
I tried this, it compiles now but when the report renders "#Error" displays
where the RepairOrderID should appear. I found another suggestion to use the
First function this does not work either...
Can someone please help....?
Thanks!!!
DanLooks like your textbox is referring to itself. You need to refer to a
textbox from report body.
More reliable solution is to create read-only parameter with default value
from query and then to use this parameter in the page header textbox.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> I'm getting this error when I try to preview my report: The value
> expression
> for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> in
> page headers or footers.
> I searched through the news groups and a found suggestion to replace:
> Fields!RepairOrderID.Value
> with
> ReportItems!RepairOrderID.Value
> I tried this, it compiles now but when the report renders "#Error"
> displays
> where the RepairOrderID should appear. I found another suggestion to use
> the
> First function this does not work either...
> Can someone please help....?
> Thanks!!!
> Dan
>|||I don't think my textbox is referring to its self... The name of the textbox
is textbox18, besides I'm explicitly qualifying the Value property with
Fields! or ReportItems!. How do I create a read-only parameter from
query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
Thanks,
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> Looks like your textbox is referring to itself. You need to refer to a
> textbox from report body.
> More reliable solution is to create read-only parameter with default value
> from query and then to use this parameter in the page header textbox.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> > I'm getting this error when I try to preview my report: The value
> > expression
> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> > in
> > page headers or footers.
> >
> > I searched through the news groups and a found suggestion to replace:
> >
> > Fields!RepairOrderID.Value
> > with
> > ReportItems!RepairOrderID.Value
> >
> > I tried this, it compiles now but when the report renders "#Error"
> > displays
> > where the RepairOrderID should appear. I found another suggestion to use
> > the
> > First function this does not work either...
> >
> > Can someone please help....?
> >
> > Thanks!!!
> >
> > Dan
> >
> >
>|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
Create a parameter without Prompt , set its default value to From Query,
select your dataset and ReportOrderID as value field.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
>I don't think my textbox is referring to its self... The name of the
>textbox
> is textbox18, besides I'm explicitly qualifying the Value property with
> Fields! or ReportItems!. How do I create a read-only parameter from
> query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> Thanks,
> Dan
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
>> Looks like your textbox is referring to itself. You need to refer to a
>> textbox from report body.
>> More reliable solution is to create read-only parameter with default
>> value
>> from query and then to use this parameter in the page header textbox.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
>> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
>> > I'm getting this error when I try to preview my report: The value
>> > expression
>> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
>> > used
>> > in
>> > page headers or footers.
>> >
>> > I searched through the news groups and a found suggestion to replace:
>> >
>> > Fields!RepairOrderID.Value
>> > with
>> > ReportItems!RepairOrderID.Value
>> >
>> > I tried this, it compiles now but when the report renders "#Error"
>> > displays
>> > where the RepairOrderID should appear. I found another suggestion to
>> > use
>> > the
>> > First function this does not work either...
>> >
>> > Can someone please help....?
>> >
>> > Thanks!!!
>> >
>> > Dan
>> >
>> >
>>
>|||Excellent!!... That's what I needed... Thanks..:)
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:udrYUvEfEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
> Create a parameter without Prompt , set its default value to From Query,
> select your dataset and ReportOrderID as value field.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
> >I don't think my textbox is referring to its self... The name of the
> >textbox
> > is textbox18, besides I'm explicitly qualifying the Value property with
> > Fields! or ReportItems!. How do I create a read-only parameter from
> > query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> >
> > Thanks,
> >
> > Dan
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> >> Looks like your textbox is referring to itself. You need to refer to a
> >> textbox from report body.
> >> More reliable solution is to create read-only parameter with default
> >> value
> >> from query and then to use this parameter in the page header textbox.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> > wrote
> >> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> >> > I'm getting this error when I try to preview my report: The value
> >> > expression
> >> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
> >> > used
> >> > in
> >> > page headers or footers.
> >> >
> >> > I searched through the news groups and a found suggestion to replace:
> >> >
> >> > Fields!RepairOrderID.Value
> >> > with
> >> > ReportItems!RepairOrderID.Value
> >> >
> >> > I tried this, it compiles now but when the report renders "#Error"
> >> > displays
> >> > where the RepairOrderID should appear. I found another suggestion to
> >> > use
> >> > the
> >> > First function this does not work either...
> >> >
> >> > Can someone please help....?
> >> >
> >> > Thanks!!!
> >> >
> >> > Dan
> >> >
> >> >
> >>
> >>
> >
> >
>
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Tuesday, March 27, 2012
data extension error
Im in the process of creating my first data extension for reporting
services.
Im getting an error while creating a new report via the add report
wizard. My entry shows up in the type dropdown just as expected but
the next screen has a text box that says querystring. When I click
next I get the following error:
"an error occurred while the query design method was being saved.
Object not set to an instance of an object."
How might I know where this occured? Any help is appreciated.And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.|||And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.
services.
Im getting an error while creating a new report via the add report
wizard. My entry shows up in the type dropdown just as expected but
the next screen has a text box that says querystring. When I click
next I get the following error:
"an error occurred while the query design method was being saved.
Object not set to an instance of an object."
How might I know where this occured? Any help is appreciated.And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.|||And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.
Sunday, March 25, 2012
data entry..
sirs,
when i try to enter the data in the every field of table,
i couldn't enter the data and the error message dispaly
with the error "Key column information is insufficient or
incorrect. Too many rows were affected by update." I even
couldn't delete the entire row and get the same error
message. now that specific row has value NULL in every
fields. pls help.What's the structure of your table? Write you SQL commands which you are
entering to INSERT, UPDATE and DELETE the record(s).
"sushil" <anonymous@.discussions.microsoft.com> wrote in message
news:2b0a101c392d5$e458f3b0$a601280a@.phx.gbl...
> sirs,
> when i try to enter the data in the every field of table,
> i couldn't enter the data and the error message dispaly
> with the error "Key column information is insufficient or
> incorrect. Too many rows were affected by update." I even
> couldn't delete the entire row and get the same error
> message. now that specific row has value NULL in every
> fields. pls help.
>|||Seems like you forgot to specify a primary key for your table.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"sushil" <anonymous@.discussions.microsoft.com> wrote in message
news:2b0a101c392d5$e458f3b0$a601280a@.phx.gbl...
> sirs,
> when i try to enter the data in the every field of table,
> i couldn't enter the data and the error message dispaly
> with the error "Key column information is insufficient or
> incorrect. Too many rows were affected by update." I even
> couldn't delete the entire row and get the same error
> message. now that specific row has value NULL in every
> fields. pls help.
>sql
when i try to enter the data in the every field of table,
i couldn't enter the data and the error message dispaly
with the error "Key column information is insufficient or
incorrect. Too many rows were affected by update." I even
couldn't delete the entire row and get the same error
message. now that specific row has value NULL in every
fields. pls help.What's the structure of your table? Write you SQL commands which you are
entering to INSERT, UPDATE and DELETE the record(s).
"sushil" <anonymous@.discussions.microsoft.com> wrote in message
news:2b0a101c392d5$e458f3b0$a601280a@.phx.gbl...
> sirs,
> when i try to enter the data in the every field of table,
> i couldn't enter the data and the error message dispaly
> with the error "Key column information is insufficient or
> incorrect. Too many rows were affected by update." I even
> couldn't delete the entire row and get the same error
> message. now that specific row has value NULL in every
> fields. pls help.
>|||Seems like you forgot to specify a primary key for your table.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"sushil" <anonymous@.discussions.microsoft.com> wrote in message
news:2b0a101c392d5$e458f3b0$a601280a@.phx.gbl...
> sirs,
> when i try to enter the data in the every field of table,
> i couldn't enter the data and the error message dispaly
> with the error "Key column information is insufficient or
> incorrect. Too many rows were affected by update." I even
> couldn't delete the entire row and get the same error
> message. now that specific row has value NULL in every
> fields. pls help.
>sql
Data entry in a datetime field.
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.
Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1
Here are the contents of the same -
Post#1
Orginally posted by me -
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
Post#2
Originally posted by Jens K. Suessmeyer -
Hi,
you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Post#3
Originally posted by me -
Hi,
I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.
Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
The only time value(s) I didn't try was/were - hh:mm
s tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.
I also tried entering a time value standalone, without the date. Still no go.
(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)
Now I'm in a real fix because of the inability to enter date/time values.
Could you please help me out?
Post#6
Originally posted by me -
I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?
Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.
Post#7
Originally posted by me this afternoon, but it never showed up on the page -
Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.
Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.
I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?
Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
What's the matter? I've created 2 threads for this topic, but it seems no one wants to touch it with a ten-foot pole. Why isn't anyone addressing this issue and giving me a reply on either of my threads?|||You're probalby not getting a reply because your issue doesn't make sense. There seems to be a terminology issue here... You keep saying that the date value can only be inserted via an Insert statement - well, that's the only way to add new records to a database. An Update statment will work as well if you are updating an existing record.
Date controls are provided that support databinding and the datagrid also supports datetime entries. Perhaps you need to do one of the VB training walkthroughs on data access. Most likely you have a problem with your core data access structure and this datetime issue is just a side effect.
|||No, it is not a terminology issue. Allow me to demonstrate.
(God bless ImageShack, I wasn't aware of it when I posted this issue in my original thread).
(I'm using SQL Server 2005 Express SP2 and Management Studio Express)
Observe the data in the database as it stands. The view is in Management Studio Express, and I selected the table in Object Explorer, right clicked it, and selected Open Table. The table opens in a datagrid view in the results pane.
http://img217.imageshack.us/img217/3618/datetime1yf0.jpg
Now, I try to insert a record using the same results pane. Note that everything is entered fine, till I get to the datetime field. (I am using the right arrow key to move to each succesive field, after I have entered data in the previous field). I have entered the data in the datetime field. So far so good.
http://img441.imageshack.us/img441/6154/datetime2fc9.jpg
Now note, the second I click the left arrow key, to indicate that I have finished entering data in the field, and that I need to go to another field, I get the following error message.
http://img227.imageshack.us/img227/377/datetime3sq6.jpg
So I have to hit escape twice to get rid of all the data I tried to insert, and we get back to the image displayed in the first link I posted above.
In this test design, I have kept the datetime field to allow nulls, so I am bypassing the field altogether, just to demonstrate that I can enter the rest of the record, as well as the rest of the datatypes perfectly well. (My issue relates to both smalldatetime, and datetime types).
http://img441.imageshack.us/img441/408/datetime4ou5.jpg
Now, note, I am going to copy the datetime value from the above record, and paste the exact same value into the current record I am inserting, but I still get the same error. (The above records were inserted using insert statements from the query pane).
http://img441.imageshack.us/img441/6073/datetime5kl5.jpg
I tried to paste the copied datetime value from the above record into the record below, where I had left it null. Get the error again.
I tried to paste the copied value in a new record I created below, leaving the null value in the above record intact, and I still get the same 'ol error message.
http://img441.imageshack.us/img441/5144/datetime6ui9.jpg
http://img228.imageshack.us/img228/8219/datetime7vm1.jpg
Now, on to VBExpress. We open a project first. Now we go to the database explorer pane, go to our database, select the table in question, right click and select 'show table data' to get this tab -
http://img408.imageshack.us/img408/3094/datetime8er3.jpg
Same ol' story here as well, so I won't go into the details like above. Just adding a record here as well, getting to the date value, entering it and hitting the right arrow key, and this is what I get, here as well.
http://img228.imageshack.us/img228/4053/datetime9tz7.jpg
Now, I tried databinding the fields to textboxes and datetimepickers in a form once, and then tried running it, and entered a date value. It corrupted my entire .mdf file as well as the log file, and I had to restore the database from a backup. It was a lot of hassle. So I don't fancy attempting doing it again, for the purpose of demonstration. I think you guys get the general idea anyway.
(Please pardon the calculations in the rate x qty= amt, I entered everything in a hurry, and didn't pause to think how many zeroes there were in the quantities. That's why the wierd amounts. I noticed only when it was too late.)
(I haven't entered any records between purchase no 1 and 21, values are entered arbitrarily. It is the PK field).
(I also had to blacken out my machine name for security issues).
I hope now people have a better idea of what this issue is.I have elaborated the issue quite lucidly. Hopefully, it is a bug, and will be fixed my MS soon.
|||That is a better explaination - thank you. However, I can't reproduce the problem. The SQLExpress data base should allow you to enter the value as intended. It sounds like your database and/or development environment are corrupt somehow... Granted, I only tested creating a SQLEx database from within VS (full version), but the table viewer in VS allowed me to enter the date "1-2-2007" without a problem.|||I don't understand how it could get corrupted everytime. I have installed SQL Express non-service pack version, I have at one point used both SP1 and SP2 (each instance was preceeded by a full system/OS reinstall), yet I faced the same issue everytime.|||You are installing US-EN versions of SQL and VS right?
When you do a fresh install, are you only installing Windows, Visual Studio, SQL Express, and necessary drivers and updates? No extra software like security or other apps? You want to be sure there isn't a conflict. Also, is your software legit (legal copy of windows, vs, etc)? You might even try a different computer (bad harddisk or ram could result in recurring issues) with fresh downloads of the installers for VS and SQL (corrupt installers). Granted, these are unlikely fixes, but I'm out of suggestions.
When no one else reports the same issue, and we can't reproduce it, it almost always means its something specific to your computer.
|||I am not able to duplicate your issue.You must have a trigger or a FK or something which is causing the error you are seeing. "Array out of bounds" should not be generated by a datetime field. Even though, I realize, the error is saying it comes from that field, it must be doing something behind the scene causing the error.
Script out your table and post it here.
Also, I see a red ! on your images from the BILLID field. What does the ! say? That might actually be the problem.
|||
Reed,
Yup, both are the US-En versions.
I have 2 totally different computers, both running Win XP Pro though. One is Intel Celeron 800 MHz based, with only 256 MB of memory, and the other has an AMD Opteron 165 with a gig of ram. (Both are home computers, and yes, I did manage to run SQL Server on the older machine satisfactorily, but have given up on it since I got the new machine). I have faced the exact same issue on both systems. So I don't think it could be machine specific. Although, I never tried it on a fresh install of Win XP with only the drivers and SQLExpress and VBExpress. Since these are home machines, I have a ton of other stuff installed on them.
And I reserve the installation of SQL Server and VB for last, since I don't include them in the Norton Ghost image that I make of my installations with all apps installed and settings tweaked.
Tom,
I don't know how to create triggers yet, so there are none in any of my databases. I face this issue in every single (datetime or smalldatetime) field in every single table in every single database attached to SQLExpress, regardless of whether I have specified Foreign Key, or even Primary Key constraints or not.The red exclamation mark just notes that changes to the database (such as updates or inserts) haven't been saved yet.
(I checked the mouse hover tip and it says "This Cell has changed. The change has not been committed to the database. The original data is NULL." NOthing to worry about. It does that to all fields that are set to allow nulls, I think.
Something about what Reed said set me thinking. I tweak almost every OS setting to my exacting requirements whenever I first install it. I have tweaked the datetime settings of Windows. I also disable unnecessary services. Does that have anything to do with the way SQL Server is behaving? What windows services should be running for SQL Server to run without hiccups? Is there any incompatibility with tweaked windows datetime settings?
Here's the scripted table as requested. It's the same as the one I've used in my example pics. I have removed the database name wherever it appears for security and privacy concerns, so don't be alarmed or think that the issue is because I don't have the dbname specified where it ought to be. ;-) 
(Note, I added the check constraints and the last column after I posted the pics).
Code Snippet
USE [.MDF]
GO
/****** Object: Table [dbo].[Purchases] Script Date: 06/14/2007 17:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Purchases](
[PurchaseNo] [bigint] NOT NULL,
[BillNo] [varchar](50) NULL,
[Date] [datetime] NULL,
[Broker] [varchar](50) NULL,
[ShareName] [varchar](50) NULL,
[Quantity] [int] NULL,
[Rate] [numeric](12, 2) NULL,
[Amount] [numeric](12, 2) NULL,
[Old] [char](1) NULL,
[Balance] [char](1) NULL,
CONSTRAINT [PK_Purchases] PRIMARY KEY CLUSTERED
(
[PurchaseNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Balancecheck] CHECK (([Balance]='e' OR [Balance]='p' OR
[Balance]='f'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Balancecheck]
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Oldcheck] CHECK (([Old]='n' OR [Old]='y'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Oldcheck]
Ok, it may very well be a result of "tweaking" the windows datetime settings. The services thing wouldn't cause it - if you disabled a service that SQL needed, it wouldn't run.
If you know precisely what changes you've made to the default date/time settings you should undo them one at a time and test the app until it works. That would at least let you understand which tweak caused the issue. Its possible that the change you made broke the table editors in question and that the failure is expected. It is also possible that this is indeed a bug in the software due to an oversight in properly checking the
System.Globalization.DateTimeFormatInfo
before trying to parse a date.
See where undoing your tweaks gets you. If you have the resources, it might be even better to start with a clean, fresh install of windows and then apply your tweaks one at a time until the program breaks.
|||I am unable to duplicate your problem using the code you posted on SQL 2005 SP1 (not SQLExpress but should be the same).What do you mean by "tweaked datetime settings"? What exactly did you change and how? And I guess more importantly why?
I THINK the Mgt Console uses the Windows date format to know what to expect. If you changed the format, that is very likely your issue.
|||
This is what has been tweaked. The values are not the default ones that windows supplies when doing a fresh install. I don't recall what the default values are.
http://img233.imageshack.us/img233/8202/date1wi1.jpg
http://img233.imageshack.us/img233/2007/time1li4.jpg
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
I have tried entering these very combinations through both insert statements, and the GUIs. Only one works. So it doesn't really matter if I've changed the way dates are displayed in Windows, does it? The GUI should still accept the values, especially if the code is accepting the same values.
|||
reachrishikh wrote:
...
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
...The GUI should still accept the values, especially if the code is accepting the same values.
I don't think this is the case... SQL has it's own formats that it expects values to be entered in. I don't think it takes its formats from Windows. However, text input boxes, such as those found in a data grid of some kind, would most likely be affected by Windows format settings. I might be wrong here, but this is what makes me think it is a settings problem and not an issue with SQL.
There's still the possibility of some other software conflict or corruption since you haven't tried a clean install (using a disk image doesn't count as a "clean" install).
|||So could any of you please supply me with the default windows date and time settings, so I can (for the time being) revert to the original settings and see if it works?
Also, I haven't got the time this week, so I'll set aside a day next week, and try doing the clean install as you guys suggested, and see if I still get the error even then.
|||Orignal 2 digit year was 2029
Short date format was M/d/yyyy
Date separator was /
Data entry in a datetime field.
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.
Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1
Here are the contents of the same -
Post#1
Orginally posted by me -
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
Post#2
Originally posted by Jens K. Suessmeyer -
Hi,
you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Post#3
Originally posted by me -
Hi,
I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.
Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.
I also tried entering a time value standalone, without the date. Still no go.
(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)
Now I'm in a real fix because of the inability to enter date/time values.
Could you please help me out?
Post#6
Originally posted by me -
I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?
Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.
Post#7
Originally posted by me this afternoon, but it never showed up on the page -
Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.
Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.
I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?
Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
What's the matter? I've created 2 threads for this topic, but it seems no one wants to touch it with a ten-foot pole. Why isn't anyone addressing this issue and giving me a reply on either of my threads?|||You're probalby not getting a reply because your issue doesn't make sense. There seems to be a terminology issue here... You keep saying that the date value can only be inserted via an Insert statement - well, that's the only way to add new records to a database. An Update statment will work as well if you are updating an existing record.
Date controls are provided that support databinding and the datagrid also supports datetime entries. Perhaps you need to do one of the VB training walkthroughs on data access. Most likely you have a problem with your core data access structure and this datetime issue is just a side effect.
|||No, it is not a terminology issue. Allow me to demonstrate.
(God bless ImageShack, I wasn't aware of it when I posted this issue in my original thread).
(I'm using SQL Server 2005 Express SP2 and Management Studio Express)
Observe the data in the database as it stands. The view is in Management Studio Express, and I selected the table in Object Explorer, right clicked it, and selected Open Table. The table opens in a datagrid view in the results pane.
http://img217.imageshack.us/img217/3618/datetime1yf0.jpg
Now, I try to insert a record using the same results pane. Note that everything is entered fine, till I get to the datetime field. (I am using the right arrow key to move to each succesive field, after I have entered data in the previous field). I have entered the data in the datetime field. So far so good.
http://img441.imageshack.us/img441/6154/datetime2fc9.jpg
Now note, the second I click the left arrow key, to indicate that I have finished entering data in the field, and that I need to go to another field, I get the following error message.
http://img227.imageshack.us/img227/377/datetime3sq6.jpg
So I have to hit escape twice to get rid of all the data I tried to insert, and we get back to the image displayed in the first link I posted above.
In this test design, I have kept the datetime field to allow nulls, so I am bypassing the field altogether, just to demonstrate that I can enter the rest of the record, as well as the rest of the datatypes perfectly well. (My issue relates to both smalldatetime, and datetime types).
http://img441.imageshack.us/img441/408/datetime4ou5.jpg
Now, note, I am going to copy the datetime value from the above record, and paste the exact same value into the current record I am inserting, but I still get the same error. (The above records were inserted using insert statements from the query pane).
http://img441.imageshack.us/img441/6073/datetime5kl5.jpg
I tried to paste the copied datetime value from the above record into the record below, where I had left it null. Get the error again.
I tried to paste the copied value in a new record I created below, leaving the null value in the above record intact, and I still get the same 'ol error message.
http://img441.imageshack.us/img441/5144/datetime6ui9.jpg
http://img228.imageshack.us/img228/8219/datetime7vm1.jpg
Now, on to VBExpress. We open a project first. Now we go to the database explorer pane, go to our database, select the table in question, right click and select 'show table data' to get this tab -
http://img408.imageshack.us/img408/3094/datetime8er3.jpg
Same ol' story here as well, so I won't go into the details like above. Just adding a record here as well, getting to the date value, entering it and hitting the right arrow key, and this is what I get, here as well.
http://img228.imageshack.us/img228/4053/datetime9tz7.jpg
Now, I tried databinding the fields to textboxes and datetimepickers in a form once, and then tried running it, and entered a date value. It corrupted my entire .mdf file as well as the log file, and I had to restore the database from a backup. It was a lot of hassle. So I don't fancy attempting doing it again, for the purpose of demonstration. I think you guys get the general idea anyway.
(Please pardon the calculations in the rate x qty= amt, I entered everything in a hurry, and didn't pause to think how many zeroes there were in the quantities. That's why the wierd amounts. I noticed only when it was too late.)
(I haven't entered any records between purchase no 1 and 21, values are entered arbitrarily. It is the PK field).
(I also had to blacken out my machine name for security issues).
I hope now people have a better idea of what this issue is.I have elaborated the issue quite lucidly. Hopefully, it is a bug, and will be fixed my MS soon.
|||That is a better explaination - thank you. However, I can't reproduce the problem. The SQLExpress data base should allow you to enter the value as intended. It sounds like your database and/or development environment are corrupt somehow... Granted, I only tested creating a SQLEx database from within VS (full version), but the table viewer in VS allowed me to enter the date "1-2-2007" without a problem.|||I don't understand how it could get corrupted everytime. I have installed SQL Express non-service pack version, I have at one point used both SP1 and SP2 (each instance was preceeded by a full system/OS reinstall), yet I faced the same issue everytime.|||You are installing US-EN versions of SQL and VS right?
When you do a fresh install, are you only installing Windows, Visual Studio, SQL Express, and necessary drivers and updates? No extra software like security or other apps? You want to be sure there isn't a conflict. Also, is your software legit (legal copy of windows, vs, etc)? You might even try a different computer (bad harddisk or ram could result in recurring issues) with fresh downloads of the installers for VS and SQL (corrupt installers). Granted, these are unlikely fixes, but I'm out of suggestions.
When no one else reports the same issue, and we can't reproduce it, it almost always means its something specific to your computer.
|||I am not able to duplicate your issue.You must have a trigger or a FK or something which is causing the error you are seeing. "Array out of bounds" should not be generated by a datetime field. Even though, I realize, the error is saying it comes from that field, it must be doing something behind the scene causing the error.
Script out your table and post it here.
Also, I see a red ! on your images from the BILLID field. What does the ! say? That might actually be the problem.
|||
Reed,
Yup, both are the US-En versions.
I have 2 totally different computers, both running Win XP Pro though. One is Intel Celeron 800 MHz based, with only 256 MB of memory, and the other has an AMD Opteron 165 with a gig of ram. (Both are home computers, and yes, I did manage to run SQL Server on the older machine satisfactorily, but have given up on it since I got the new machine). I have faced the exact same issue on both systems. So I don't think it could be machine specific. Although, I never tried it on a fresh install of Win XP with only the drivers and SQLExpress and VBExpress. Since these are home machines, I have a ton of other stuff installed on them.
And I reserve the installation of SQL Server and VB for last, since I don't include them in the Norton Ghost image that I make of my installations with all apps installed and settings tweaked.
Tom,
I don't know how to create triggers yet, so there are none in any of my databases. I face this issue in every single (datetime or smalldatetime) field in every single table in every single database attached to SQLExpress, regardless of whether I have specified Foreign Key, or even Primary Key constraints or not.The red exclamation mark just notes that changes to the database (such as updates or inserts) haven't been saved yet.
(I checked the mouse hover tip and it says "This Cell has changed. The change has not been committed to the database. The original data is NULL." NOthing to worry about. It does that to all fields that are set to allow nulls, I think.
Something about what Reed said set me thinking. I tweak almost every OS setting to my exacting requirements whenever I first install it. I have tweaked the datetime settings of Windows. I also disable unnecessary services. Does that have anything to do with the way SQL Server is behaving? What windows services should be running for SQL Server to run without hiccups? Is there any incompatibility with tweaked windows datetime settings?
Here's the scripted table as requested. It's the same as the one I've used in my example pics. I have removed the database name wherever it appears for security and privacy concerns, so don't be alarmed or think that the issue is because I don't have the dbname specified where it ought to be. ;-)
(Note, I added the check constraints and the last column after I posted the pics).
Code Snippet
USE [.MDF]
GO
/****** Object: Table [dbo].[Purchases] Script Date: 06/14/2007 17:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Purchases](
[PurchaseNo] [bigint] NOT NULL,
[BillNo] [varchar](50) NULL,
[Date] [datetime] NULL,
[Broker] [varchar](50) NULL,
[ShareName] [varchar](50) NULL,
[Quantity] [int] NULL,
[Rate] [numeric](12, 2) NULL,
[Amount] [numeric](12, 2) NULL,
[Old] [char](1) NULL,
[Balance] [char](1) NULL,
CONSTRAINT [PK_Purchases] PRIMARY KEY CLUSTERED
(
[PurchaseNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Balancecheck] CHECK (([Balance]='e' OR [Balance]='p' OR
[Balance]='f'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Balancecheck]
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Oldcheck] CHECK (([Old]='n' OR [Old]='y'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Oldcheck]
Ok, it may very well be a result of "tweaking" the windows datetime settings. The services thing wouldn't cause it - if you disabled a service that SQL needed, it wouldn't run.
If you know precisely what changes you've made to the default date/time settings you should undo them one at a time and test the app until it works. That would at least let you understand which tweak caused the issue. Its possible that the change you made broke the table editors in question and that the failure is expected. It is also possible that this is indeed a bug in the software due to an oversight in properly checking the
System.Globalization.DateTimeFormatInfo
before trying to parse a date.
See where undoing your tweaks gets you. If you have the resources, it might be even better to start with a clean, fresh install of windows and then apply your tweaks one at a time until the program breaks.
|||I am unable to duplicate your problem using the code you posted on SQL 2005 SP1 (not SQLExpress but should be the same).What do you mean by "tweaked datetime settings"? What exactly did you change and how? And I guess more importantly why?
I THINK the Mgt Console uses the Windows date format to know what to expect. If you changed the format, that is very likely your issue.
|||
This is what has been tweaked. The values are not the default ones that windows supplies when doing a fresh install. I don't recall what the default values are.
http://img233.imageshack.us/img233/8202/date1wi1.jpg
http://img233.imageshack.us/img233/2007/time1li4.jpg
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
I have tried entering these very combinations through both insert statements, and the GUIs. Only one works. So it doesn't really matter if I've changed the way dates are displayed in Windows, does it? The GUI should still accept the values, especially if the code is accepting the same values.
|||
...
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
...The GUI should still accept the values, especially if the code is accepting the same values.
I don't think this is the case... SQL has it's own formats that it expects values to be entered in. I don't think it takes its formats from Windows. However, text input boxes, such as those found in a data grid of some kind, would most likely be affected by Windows format settings. I might be wrong here, but this is what makes me think it is a settings problem and not an issue with SQL.
There's still the possibility of some other software conflict or corruption since you haven't tried a clean install (using a disk image doesn't count as a "clean" install).
|||So could any of you please supply me with the default windows date and time settings, so I can (for the time being) revert to the original settings and see if it works?
Also, I haven't got the time this week, so I'll set aside a day next week, and try doing the clean install as you guys suggested, and see if I still get the error even then.
|||Orignal 2 digit year was 2029
Short date format was M/d/yyyy
Date separator was /
Data entry in a datetime field.
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.
Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1
Here are the contents of the same -
Post#1
Orginally posted by me -
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
Post#2
Originally posted by Jens K. Suessmeyer -
Hi,
you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Post#3
Originally posted by me -
Hi,
I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.
Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.
I also tried entering a time value standalone, without the date. Still no go.
(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)
Now I'm in a real fix because of the inability to enter date/time values.
Could you please help me out?
Post#6
Originally posted by me -
I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?
Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.
Post#7
Originally posted by me this afternoon, but it never showed up on the page -
Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.
Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.
I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?
Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
What's the matter? I've created 2 threads for this topic, but it seems no one wants to touch it with a ten-foot pole. Why isn't anyone addressing this issue and giving me a reply on either of my threads?|||You're probalby not getting a reply because your issue doesn't make sense. There seems to be a terminology issue here... You keep saying that the date value can only be inserted via an Insert statement - well, that's the only way to add new records to a database. An Update statment will work as well if you are updating an existing record.
Date controls are provided that support databinding and the datagrid also supports datetime entries. Perhaps you need to do one of the VB training walkthroughs on data access. Most likely you have a problem with your core data access structure and this datetime issue is just a side effect.
|||No, it is not a terminology issue. Allow me to demonstrate.
(God bless ImageShack, I wasn't aware of it when I posted this issue in my original thread).
(I'm using SQL Server 2005 Express SP2 and Management Studio Express)
Observe the data in the database as it stands. The view is in Management Studio Express, and I selected the table in Object Explorer, right clicked it, and selected Open Table. The table opens in a datagrid view in the results pane.
http://img217.imageshack.us/img217/3618/datetime1yf0.jpg
Now, I try to insert a record using the same results pane. Note that everything is entered fine, till I get to the datetime field. (I am using the right arrow key to move to each succesive field, after I have entered data in the previous field). I have entered the data in the datetime field. So far so good.
http://img441.imageshack.us/img441/6154/datetime2fc9.jpg
Now note, the second I click the left arrow key, to indicate that I have finished entering data in the field, and that I need to go to another field, I get the following error message.
http://img227.imageshack.us/img227/377/datetime3sq6.jpg
So I have to hit escape twice to get rid of all the data I tried to insert, and we get back to the image displayed in the first link I posted above.
In this test design, I have kept the datetime field to allow nulls, so I am bypassing the field altogether, just to demonstrate that I can enter the rest of the record, as well as the rest of the datatypes perfectly well. (My issue relates to both smalldatetime, and datetime types).
http://img441.imageshack.us/img441/408/datetime4ou5.jpg
Now, note, I am going to copy the datetime value from the above record, and paste the exact same value into the current record I am inserting, but I still get the same error. (The above records were inserted using insert statements from the query pane).
http://img441.imageshack.us/img441/6073/datetime5kl5.jpg
I tried to paste the copied datetime value from the above record into the record below, where I had left it null. Get the error again.
I tried to paste the copied value in a new record I created below, leaving the null value in the above record intact, and I still get the same 'ol error message.
http://img441.imageshack.us/img441/5144/datetime6ui9.jpg
http://img228.imageshack.us/img228/8219/datetime7vm1.jpg
Now, on to VBExpress. We open a project first. Now we go to the database explorer pane, go to our database, select the table in question, right click and select 'show table data' to get this tab -
http://img408.imageshack.us/img408/3094/datetime8er3.jpg
Same ol' story here as well, so I won't go into the details like above. Just adding a record here as well, getting to the date value, entering it and hitting the right arrow key, and this is what I get, here as well.
http://img228.imageshack.us/img228/4053/datetime9tz7.jpg
Now, I tried databinding the fields to textboxes and datetimepickers in a form once, and then tried running it, and entered a date value. It corrupted my entire .mdf file as well as the log file, and I had to restore the database from a backup. It was a lot of hassle. So I don't fancy attempting doing it again, for the purpose of demonstration. I think you guys get the general idea anyway.
(Please pardon the calculations in the rate x qty= amt, I entered everything in a hurry, and didn't pause to think how many zeroes there were in the quantities. That's why the wierd amounts. I noticed only when it was too late.)
(I haven't entered any records between purchase no 1 and 21, values are entered arbitrarily. It is the PK field).
(I also had to blacken out my machine name for security issues).
I hope now people have a better idea of what this issue is.I have elaborated the issue quite lucidly. Hopefully, it is a bug, and will be fixed my MS soon.
|||That is a better explaination - thank you. However, I can't reproduce the problem. The SQLExpress data base should allow you to enter the value as intended. It sounds like your database and/or development environment are corrupt somehow... Granted, I only tested creating a SQLEx database from within VS (full version), but the table viewer in VS allowed me to enter the date "1-2-2007" without a problem.|||I don't understand how it could get corrupted everytime. I have installed SQL Express non-service pack version, I have at one point used both SP1 and SP2 (each instance was preceeded by a full system/OS reinstall), yet I faced the same issue everytime.|||You are installing US-EN versions of SQL and VS right?
When you do a fresh install, are you only installing Windows, Visual Studio, SQL Express, and necessary drivers and updates? No extra software like security or other apps? You want to be sure there isn't a conflict. Also, is your software legit (legal copy of windows, vs, etc)? You might even try a different computer (bad harddisk or ram could result in recurring issues) with fresh downloads of the installers for VS and SQL (corrupt installers). Granted, these are unlikely fixes, but I'm out of suggestions.
When no one else reports the same issue, and we can't reproduce it, it almost always means its something specific to your computer.
|||I am not able to duplicate your issue.You must have a trigger or a FK or something which is causing the error you are seeing. "Array out of bounds" should not be generated by a datetime field. Even though, I realize, the error is saying it comes from that field, it must be doing something behind the scene causing the error.
Script out your table and post it here.
Also, I see a red ! on your images from the BILLID field. What does the ! say? That might actually be the problem.
|||
Reed,
Yup, both are the US-En versions.
I have 2 totally different computers, both running Win XP Pro though. One is Intel Celeron 800 MHz based, with only 256 MB of memory, and the other has an AMD Opteron 165 with a gig of ram. (Both are home computers, and yes, I did manage to run SQL Server on the older machine satisfactorily, but have given up on it since I got the new machine). I have faced the exact same issue on both systems. So I don't think it could be machine specific. Although, I never tried it on a fresh install of Win XP with only the drivers and SQLExpress and VBExpress. Since these are home machines, I have a ton of other stuff installed on them.
And I reserve the installation of SQL Server and VB for last, since I don't include them in the Norton Ghost image that I make of my installations with all apps installed and settings tweaked.
Tom,
I don't know how to create triggers yet, so there are none in any of my databases. I face this issue in every single (datetime or smalldatetime) field in every single table in every single database attached to SQLExpress, regardless of whether I have specified Foreign Key, or even Primary Key constraints or not.The red exclamation mark just notes that changes to the database (such as updates or inserts) haven't been saved yet.
(I checked the mouse hover tip and it says "This Cell has changed. The change has not been committed to the database. The original data is NULL." NOthing to worry about. It does that to all fields that are set to allow nulls, I think.
Something about what Reed said set me thinking. I tweak almost every OS setting to my exacting requirements whenever I first install it. I have tweaked the datetime settings of Windows. I also disable unnecessary services. Does that have anything to do with the way SQL Server is behaving? What windows services should be running for SQL Server to run without hiccups? Is there any incompatibility with tweaked windows datetime settings?
Here's the scripted table as requested. It's the same as the one I've used in my example pics. I have removed the database name wherever it appears for security and privacy concerns, so don't be alarmed or think that the issue is because I don't have the dbname specified where it ought to be. ;-)
(Note, I added the check constraints and the last column after I posted the pics).
Code Snippet
USE [.MDF]
GO
/****** Object: Table [dbo].[Purchases] Script Date: 06/14/2007 17:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Purchases](
[PurchaseNo] [bigint] NOT NULL,
[BillNo] [varchar](50) NULL,
[Date] [datetime] NULL,
[Broker] [varchar](50) NULL,
[ShareName] [varchar](50) NULL,
[Quantity] [int] NULL,
[Rate] [numeric](12, 2) NULL,
[Amount] [numeric](12, 2) NULL,
[Old] [char](1) NULL,
[Balance] [char](1) NULL,
CONSTRAINT [PK_Purchases] PRIMARY KEY CLUSTERED
(
[PurchaseNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Balancecheck] CHECK (([Balance]='e' OR [Balance]='p' OR
[Balance]='f'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Balancecheck]
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Oldcheck] CHECK (([Old]='n' OR [Old]='y'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Oldcheck]
Ok, it may very well be a result of "tweaking" the windows datetime settings. The services thing wouldn't cause it - if you disabled a service that SQL needed, it wouldn't run.
If you know precisely what changes you've made to the default date/time settings you should undo them one at a time and test the app until it works. That would at least let you understand which tweak caused the issue. Its possible that the change you made broke the table editors in question and that the failure is expected. It is also possible that this is indeed a bug in the software due to an oversight in properly checking the
System.Globalization.DateTimeFormatInfo
before trying to parse a date.
See where undoing your tweaks gets you. If you have the resources, it might be even better to start with a clean, fresh install of windows and then apply your tweaks one at a time until the program breaks.
|||I am unable to duplicate your problem using the code you posted on SQL 2005 SP1 (not SQLExpress but should be the same).What do you mean by "tweaked datetime settings"? What exactly did you change and how? And I guess more importantly why?
I THINK the Mgt Console uses the Windows date format to know what to expect. If you changed the format, that is very likely your issue.
|||
This is what has been tweaked. The values are not the default ones that windows supplies when doing a fresh install. I don't recall what the default values are.
http://img233.imageshack.us/img233/8202/date1wi1.jpg
http://img233.imageshack.us/img233/2007/time1li4.jpg
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
I have tried entering these very combinations through both insert statements, and the GUIs. Only one works. So it doesn't really matter if I've changed the way dates are displayed in Windows, does it? The GUI should still accept the values, especially if the code is accepting the same values.
|||
...
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
...The GUI should still accept the values, especially if the code is accepting the same values.
I don't think this is the case... SQL has it's own formats that it expects values to be entered in. I don't think it takes its formats from Windows. However, text input boxes, such as those found in a data grid of some kind, would most likely be affected by Windows format settings. I might be wrong here, but this is what makes me think it is a settings problem and not an issue with SQL.
There's still the possibility of some other software conflict or corruption since you haven't tried a clean install (using a disk image doesn't count as a "clean" install).
|||So could any of you please supply me with the default windows date and time settings, so I can (for the time being) revert to the original settings and see if it works?
Also, I haven't got the time this week, so I'll set aside a day next week, and try doing the clean install as you guys suggested, and see if I still get the error even then.
|||Orignal 2 digit year was 2029
Short date format was M/d/yyyy
Date separator was /
Thursday, March 22, 2012
Data Driven Subscriptions Bug with XQuery?
Hello all,
I'm not sure if what I'm encountering is a bug, an intended feature, or user error...
I've got two tables set up to assist with some data driven reports we want to run. There are two tables listed below:
CREATE TABLE [dbo].[MS_REPORT_SUBSCRIPTION](
[ID] [int] NOT NULL,
[REPORT_NAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,[REPORT_DESCRIPTION] [varchar](60) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL)
This table holds an instance of an intended data driven subscription report.
CREATE TABLE [dbo].[MS_REPORT_PARAMETERS](
[ID] [int] NOT NULL,
[REPORT_ID] [int] NOT NULL,
[REPORT_PARAMETERS] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL)
This table will hold the report parameters. The REPORT_PARAMETERS column holds an xml string (formatted the same as in the Reporting Services database. This will allow a single instance to have a variable number of parameters.
This is the query I use to get the information:
SELECT
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[1]', 'int') SupervisorID,
'\\sxcorp1\temp\dmlenz\is_docs\' + convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[2]', 'varchar(255)') FilePath,
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[3]', 'varchar(3)') Department
FROM DW_DIMENSION..MS_REPORT_SUBSCRIPTION MRS
JOIN DW_DIMENSION..MS_REPORT_PARAMETERS MRP ON MRS.ID = MRP.REPORT_ID
WHERE MRS.ID = 1
This works great when I run it within Management Studio. The problem happens when I try to use it for my data driven query. I get the error below. I guess it doesn't know how to parse it? I'm not sure why this happens since the database doesn't have a problem with it (or does it - see exception below). This is the error I get in Report Manager:
The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help
I looked in the logs and found the exception below. Does anyone have an idea as to why this is happening? I do see the 'ARITHABORT' part below, but am not sure if this is something that just comes back from the exception or if it is truly set incorrectly. If the latter is the case, then is this something that Management Studio sets correctly? Anyone have any idea what those settings are? I guess I'm trying to understand why Mgmt Studio would work but not Report Manager..
Thanks in advance for any insight you all might have.
Regards,
Dan
End of inner exception stack trace
w3wp!library!a!06/08/2006-10:17:57:: i INFO: Call to GetSystemPermissions
w3wp!library!d!06/08/2006-10:18:04:: i INFO: Call to GetSystemPermissions
w3wp!library!d!06/08/2006-10:18:04:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.CannotPrepareQueryException: The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. > System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.Library.SubscriptionManager.PrepareQuery(DataSource dataSource, DataSetDefinition dataSet, ReportParameter[]& parameters, Boolean& changed)
End of inner exception stack trace
This link has some info:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
I found this bullet point interesting:
The SET option settings must be the same as those required for indexed views and computed column indexes. Specifically, the option ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the xml column. For more information, see SET Options That Affect Results.|||I've attempted many things up to this point. For some reason setting the options before the data driven query still doesn't get rid of the error message above.
I have no gone as far as to create a table valued function that will allow me to SELECT * FROM fn_function. This parses ok, but when I actually schedule & run the data driven subscription, I get the following error in the subscription status: Error: Cannot read the next data row for the data set . Looks like functions don't work quite right either.
Ryan: That article helped clear up a lot of information. Unfortunately I couldn't seem to get anything working yet. Thanks for the link.
If anyone else has any suggestions, I'd be willing to try anything at this point...
Regards,
Dan
|||Alright. This is another response to my own question. The good news is, I've figured a way around the limitation.
First I tried this (using the set arithabort on option):
SET ARITHABORT on
SELECT convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[1]', 'int') SupervisorID,
'\\sxcorp1\temp\dmlenz\is_docs\' + convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[2]', 'varchar(255)') FilePath,
convert(xml, MRP.REPORT_PARAMETERS).value('(/ParameterValues/ParameterValue/Value)[3]', 'varchar(3)') Department
FROM DW_DIMENSION..MS_REPORT_SUBSCRIPTION MRS
JOIN DW_DIMENSION..MS_REPORT_PARAMETERS MRP ON MRS.ID = MRP.REPORT_ID
WHERE MRS.ID = 1
It didn't work. The next thing I tried is to create a proc that set the appropriate options. The Data Driven Subscription Engine didn't understand the proc. This was no longer an option.
I stated above that I couldn't get functions to work - this isn't completely true. The reason they didn't work for me was because, in the log files, I was getting the same exception as my original post. At this point, I figured it was a lost cause, however, I was able to set an option before the function sql. Not sure why this worked and the query above didn't.
This is the query that ultimately worked follows:
set ARITHABORT ON
SELECT SM.SupervisorID, '\\sxcorp1\temp\dmlenz\is_docs\zService Scorecards\' + SM.FilePath, SM.FName FileName, SM.Department FROM fn_DataDriven_IS_Service_Metrics(1) SM
I don't understand why setting the option before the xml query didn't work, but I suppose, as long as I got it to work...
I hope this will eventually help someone else.
Regards,
Dan
sqlWednesday, March 21, 2012
Data does not fit....
I am doing a simple update statement but am getting an error.
Cannot create a row of size 10675 which is greater than the allowable maximum of 8060.
The statement has been terminated.
I am inserting data that is as big as 7500 characters into a varchar(7500) field. I have made sure that my column is 7500 in length. the only way it fits is if I cut it down to 4950 characters...
Any Ideas?
William,
Total columns length for row is max 8060 (not a single column). Maybe you would want to put your new concatenated string into a text/ntext column.
PS: BTW check another thread about concatenating text fields earlier today.
|||In SQL Server 2000, the entire row's data must be <= 8060 bytes, not just a single column. (this allows it to fit on a single page)
In SQL Server 2005, you can put > 8060 bytes on a row, but it is not advisable in most cases. Any rows that are larger than that spill over into a different page.
|||I know about the limitation and I implement restrictors to make sure my working tables do not go over the given length that I need.
My data has a max length of 7500 because I populated the field with another process that only allows the data to be 7500 in length. So I do not know how the data is growing...
I am just setting a column (varchar 7500) to the value of another column (varchar 7500) and that is what does not make sense to me.
|||Can you post the script of the table?|||this table gets populated by a process that limits the data to 7500
CREATE TABLE [dbo].[Search_hold] (
[id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credits] [varchar] (7500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
When I do a simple update to this table is where I get the error... I am only posting the column that is effected because the table is 30+ columns wide....
CREATE TABLE [dbo].[Search] (
[prod_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credits] [varchar] (7500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[srch_field] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateadded] [smalldatetime] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The first table is to get the data and the main table is the second one. Alot more data is in the table that these columns...
Monday, March 19, 2012
data convertion with dynamic sql
Hi, All,
I;m new to SQl server and having a problem with dynamic sql within an
procedure.
I got error(the error msg and procedure are below) when passing an smallint
value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
datatype, how to do it?
THanks a lot.
Here is the error message I got:
**********
0
ST_ccst_code_value
Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
Conversion failed when converting the nvarchar value 'Update
ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
Here is the procedure:
****
ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
@.BatchName nvarchar(30)
AS
Declare @.batch_ID smallint
select @.batch_ID = 0
Declare @.SQL VarChar(1000)
Declare @.ST_table nvarchar(30)
exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
print @.batch_ID
select @.ST_table = 'ST_'+ @.BatchName
print @.ST_table
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
Exec (@.SQL)
GOWhen you try to add (concatenate) the value for @.batch_ID to the query
string, SQL tries to implicitly convert the string to an integer, because in
t
has a higher data type precedence than char data types.
Try:
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ CAST(@.batch_ID
as nvarchar(10))
Exec (@.SQL)
"Jessie" wrote:
> Hi, All,
> I;m new to SQl server and having a problem with dynamic sql within an
> procedure.
> I got error(the error msg and procedure are below) when passing an smallin
t
> value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
> datatype, how to do it?
> THanks a lot.
> Here is the error message I got:
> **********
> 0
> ST_ccst_code_value
> Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
> Conversion failed when converting the nvarchar value 'Update
> ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
> Here is the procedure:
> ****
> ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
> @.BatchName nvarchar(30)
> AS
> Declare @.batch_ID smallint
> select @.batch_ID = 0
> Declare @.SQL VarChar(1000)
> Declare @.ST_table nvarchar(30)
>
> exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
> print @.batch_ID
> select @.ST_table = 'ST_'+ @.BatchName
> print @.ST_table
> SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
> Exec (@.SQL)
> GO
I;m new to SQl server and having a problem with dynamic sql within an
procedure.
I got error(the error msg and procedure are below) when passing an smallint
value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
datatype, how to do it?
THanks a lot.
Here is the error message I got:
**********
0
ST_ccst_code_value
Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
Conversion failed when converting the nvarchar value 'Update
ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
Here is the procedure:
****
ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
@.BatchName nvarchar(30)
AS
Declare @.batch_ID smallint
select @.batch_ID = 0
Declare @.SQL VarChar(1000)
Declare @.ST_table nvarchar(30)
exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
print @.batch_ID
select @.ST_table = 'ST_'+ @.BatchName
print @.ST_table
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
Exec (@.SQL)
GOWhen you try to add (concatenate) the value for @.batch_ID to the query
string, SQL tries to implicitly convert the string to an integer, because in
t
has a higher data type precedence than char data types.
Try:
SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ CAST(@.batch_ID
as nvarchar(10))
Exec (@.SQL)
"Jessie" wrote:
> Hi, All,
> I;m new to SQl server and having a problem with dynamic sql within an
> procedure.
> I got error(the error msg and procedure are below) when passing an smallin
t
> value (@.batch_ID)to the dynamic sql, do I need to conver it to didferent
> datatype, how to do it?
> THanks a lot.
> Here is the error message I got:
> **********
> 0
> ST_ccst_code_value
> Msg 245, Level 16, State 1, Procedure CR_Update_Stage_tables, Line 23
> Conversion failed when converting the nvarchar value 'Update
> ST_ccst_code_value Set DTS_BATCH_ID =' to data type smallint.
> Here is the procedure:
> ****
> ALTER PROCEDURE [dbo].[CR_Update_Stage_tables]
> @.BatchName nvarchar(30)
> AS
> Declare @.batch_ID smallint
> select @.batch_ID = 0
> Declare @.SQL VarChar(1000)
> Declare @.ST_table nvarchar(30)
>
> exec SP_CR_Get_Batch_ID @.BatchName, @.batch_ID output
> print @.batch_ID
> select @.ST_table = 'ST_'+ @.BatchName
> print @.ST_table
> SELECT @.SQL = 'Update ' + @.ST_table + ' Set DTS_BATCH_ID ='+ @.batch_ID
> Exec (@.SQL)
> GO
Data Convertion error help
Hi,
I have an error:"*erro while update quantity. Error converting data type nvarchar to int "while i try update data through form page. Does anybody have any idea how can i correct the error??
I didnt try two methods but both given same error and failed update: -
1) Dim sqlcomm As New SqlCommand(sSaveQuote, rConnect)
....
sqlcomm.Parameters.AddWithValue("@.employeeID", sUserID)
sqlcomm.Parameters.AddWithValue("@.quantity", txtquantity.Text)
2)Error converting data type nvarchar to int ??
Dim rConnect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("CRNS_CustomerConnectionString").ConnectionString)
Dim command As SqlCommand = New SqlCommand("UpdateOrder", rConnect)
command.CommandType = Data.CommandType.StoredProcedure
If OrderInfo.Verified.ToUpper = "True" Or OrderInfo.Verified = 1 Then
command.Parameters.Add("@.Verified", Data.SqlDbType.Bit)
command.Parameters("@.Verified").Value = 1
Else
command.Parameters.Add("@.Verified", Data.SqlDbType.Bit)
command.Parameters("@.Verified").Value = 0
End If
command.Parameters.Add("@.Comment", Data.SqlDbType.VarChar, 100)
command.Parameters("@.Comment").Value = OrderInfo.Comment
command.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
command.Parameters("@.ProductID").Value = OrderInfo.ProductID
command.Parameters.Add("@.OrderDate", Data.SqlDbType.SmallDateTime)
command.Parameters("@.OrderDate").Value = OrderInfo.OrderDate
Cheers:)
Christe
Hi,
Thks for kind replied. Do you have any idea or hint, how can i correct the error?? or how i can convert the nvarchar to int before update in dbs??
cheers:)
|||Hi
You could debug your program to see if the value you pass to parameter is right ,especiallyOrderInfo.ProductID.
Data Convertion Error
I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.
Stored Procedure
ALTER Procedure [dbo].[WDR_CityAdd1]
(
@.CountryID int,
@.CityName nvarchar(50),
@.InternetAvail bit,
@.FlowersAvail bit,
@.CityID int OUTPUT
)
AS
IF EXISTS(SELECT 'True' FROM city WHERE CityName = @.CityName AND CountryID = @.CountryID)
BEGIN
SELECT
@.CityID = 0
END
ELSE
BEGIN
INSERT INTO City
(
CountryID,
CityName,
InternetAvail,
FlowersAvail
)
VALUES
(
@.CountryID,
@.CityName,
@.InternetAvail,
@.FlowersAvail
)
SELECT
@.CityID = 'CityID' ( I have also tried = @.@.Identity but that never returned anything it is an identity column 1,1)
END
Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.
Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterCityName As New SqlParameter("@.CityName", SqlDbType.NVarChar, 50)
parameterCityName.Value = CityName
myCommand.Parameters.Add(parameterCityName)
Dim parameterCityID As New SqlParameter("@.CityID", SqlDbType.Int, 4)
parameterCityID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCityID)
Try
dbConn.Open()
myCommand.ExecuteNonQuery()
dbConn.Close()
Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)
'AlertMessage = "City Added"
Catch ex As Exception
AlertMessage = ex.ToString
End Try
Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError
Thanks
Jerry
There are a couple of issues:
...
SELECT @.CityID = 'CityID'
...
Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)
...
--> Conversion failed when converting the varchar value 'CityID' to data type int.
In this location,
SELECT @.CityID = 'CityID'
You are attempting to assign the @.CityID (an integer) the string value 'CityID'. That is what caused the error you reported.
I suggest using SCOPE_IDENTITY to capture the IDENTITY value of the last row entered by the current user/session.
SET @.CityID = SCOPE_IDENTITY()
Also, the RETURN value 'should' be an integer, and you are attempting to return a string value.
('Normally', one would use the RETURN value for success/failure reporting.)
Somewhere prior to the RETURN statement, you 'should' assign the parameterCityID value to a previously declared variable, then use that variable in your application.
|||Arnie
I had tried playing around with the 'cityID' fieldname and was getting different error messages. I have re-written the code with your help and it works. Sometimes this just amazes me. I do appreciate the help. Maybe with time I will be able to help others as I get a handle on this.
Jerry
Data Conversion failed due to Potential Loss of data
Hi,
I am getting this error when my ssis package is running
Data Conversion failed due to Potential Loss of data
the input column is in string format and output is in sql server bigint
the error is occuring when there is an empty string in the input. what should i do to overcome this
It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.
Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:
ISNULL(<<input field>>) ? 0 : <<input field>>
In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.
|||
I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:
ISNULL(<<input field>>) ? 0 : <<input field>>
In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.
NULL and "empty string" are two very different things.
To expand on what I suggested earlier and desibull's code above:
ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]
OR
ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]
Sunday, March 11, 2012
Data Conversion Error on Excel Destination
I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.
I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.
But its gives the following error.
[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".
[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.
Can someone please tell me what should I do to make it work?
Thanks,
Did you set the error output of the OLE Destination to "Redirect Rows"?You might also have metadata problems and could maybe stand to recreate the Excel destination.|||
I have set the error ouput to redirect rows
Actually I have a Data flow task with Oledbsource -->Oledbdestination (Redirect Error Rows ) -- >Data Conversion -- >Excel Destination.
When I do this its gives me the following error.
Data conversion failed while converting column "A" (53) to column "A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABL
[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.
But for this data flow task Oledbsource -- > Data Conversion - > Excel Destination. Its works fine.
I am doing the same data conversion in both data flow tasks.
Please let me know what I am doing wrong.
|||Well, I suppose check and double check your column mappings going into and out of the OLE DB destination.|||I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.
I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.
But its gives the following error.
[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".
[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.
Can someone please tell me what should I do to make it work?
Thanks,
Actually I would check the Data conversion transform. Specifically the column A; since is there where the error is being generated. Try placing a data view to inspected the values that go into the data conversion. What happens if you delete column A from the data conversion? Does it fail in a different column?
|||I have checked the Data conversion transform again. I have removed column A and used another column. Still it fails.
This is kind of weird. I cant figure out where the problem is .
Is it that EXCEL Destination cannot be used to redirect rows that have errors? Because its seems to work perfectly when I do OLEDBSource -> Data Conversion ->Excel Destination.
|||Well, if the errors are created (redirected) because of conversion errors, then it is natural that the redirected error rows don't match your destination data types. They can't because that's why they are in error.Make sure that the data types of the Excel destination match the data types of your SOURCE data, not the data types of the OLE DB Destination table. Not sure if this will work because the metadata probably won't match...|||
Hi all,
I think I have exactly the same problem using a sql destination for error output.
Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
SSIS package "GS_ODSREC.dtsx" finished: Failure.
-
I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.
Regards
Ayzan
|||Hi all,
I think I have exactly the same problem using a sql destination for error output.
Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
SSIS package "GS_ODSREC.dtsx" finished: Failure.
-
I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.
Regards
Ayzan
Okay, you *don't* have the same problem as the OP. If you are using a SQL Server destiantion, SSIS will not automatically cast/convert datatypes for you. The data flow datatypes (metadata) will need to match EXACTLY with the SQL Server destination table.|||
Thank you Phil Brammer,
Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes
Regards
Ayzan
|||Thank you Phil Brammer,
Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes
Regards
Ayzan
Ah, see, you left out valuable information! So I'll ask this as I have done before. Before we continue, please provide the metadata information as it stands before going into the destination. Then, please provide the table column information for the destination as well.
One other thing to note is that there could be one bad row that is causing this error... Have you redirected errors and inspected them?|||
Ok,
There is a simple Data Flow, so as to reproduce the error:
#Data Source, OLEDB, AdventureWorksDW
#OLE DB Source, SQL Command :
SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance
#OLE DB Destination
AdventureWorks.FactFinance
#Error Output to Data Conersion
TimeKey -> DT_STR
#DataReader Destination
Enjoy !
|||Ok,
There is a simple Data Flow, so as to reproduce the error:
#Data Source, OLEDB, AdventureWorksDW
#OLE DB Source, SQL Command :
SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance#OLE DB Destination
AdventureWorks.FactFinance
#Error Output to Data Conersion
TimeKey -> DT_STR
#DataReader Destination
Enjoy !
I don't have a AdventureWorks.FactFinance table. I have the data warehouse table... Are you creating your own FactFinance table in the AdventureWorks table? The mere prefix of "Fact" indicates that it should be in AdventureWorksDW.|||
Sorry,
#OLE DB Destination
AdventureWorksDW.FactFinance
Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.
Regards
Ayzan
|||Sorry,
#OLE DB Destination
AdventureWorksDW.FactFinance
Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.
Regards
Ayzan
I'm utterly confused now. So your source and destination tables are the same? What are you doing exactly, and what are you expecting to see? Why would I select the top 10 records from FactFinance and then turn around and insert them again? Regardless, nowhere should a DT_STR datatype be picked up on the Timekey field because it is an integer field.
Nevermind. I understand now. Hang on.
Subscribe to:
Posts (Atom)