Data entry in a datetime field

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?

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

|||

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:ss 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?

|||Guys, I need your help. Please give me a solution to my problem. (This post has already been relegated to the second page without any replies).|||People, I need some help here. Any replies addressing my issue will be greatly appreciated. Thank You in advance.|||

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.

|||

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?

sql

Data entry in a datetime field

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?

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

|||

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:ss 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?

|||Guys, I need your help. Please give me a solution to my problem. (This post has already been relegated to the second page without any replies).|||People, I need some help here. Any replies addressing my issue will be greatly appreciated. Thank You in advance.|||

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.

|||

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?

Data Diagrams in SQL Server Management Studio

I can create a data diagram and all is well and good. Life cannot be better

If I attempt to create a second diagram in the same database I can lay out my tables (or even just a single comment) and I can print and all appears good but!

When I go to save I get this message. How can I create two diagrams for one database?

TITLE: Microsoft SQL Server Management Studio

Cannot insert the value NULL into column 'diagram_id', table 'ReportData.dbo.sysdiagrams'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. (.Net SqlClient Data Provider)

BUTTONS:

OK

You might want to try the SQL Tools General forum for this, as it doesn't seem to relate to SSIS.

|||

Moving thread...

|||

Which service pack is installed on the machine ? Where did you try to create the second diagram ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

check whether this helps u

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=357447&SiteID=1

Madhu

Data Connections for SQL Server Express in Visual Studio Express

I have an Excel add-in that connects to a SQL Server Express 2005
database. I've decided to create a configuration piece for this add-in
in Visual Studio 2005 Express. I added a data connection using the data
connection wizard and all appeared to go well. Anyways when I attempt
to open SQL Server Express to administer the database, it was corrupted
and I had to restore it.

I eventually got it to work correctly (I'm pretty sure I followed
pretty much the same steps as before), but I was just wondering if
anyone had experienced problems like this? I find it a bit scary that
it may be that easy to corrupt the database by just creating a data
connection.Whats the error message of the database, that you mean that the db is
corrupt ? Maybe you did something wrong in there. The be would be to
post you used code here as well as the error message you are facing.

HTH, jens Suessmeyer.|||I have to apologize -- I can't recreate the error and I didn't write
the orginal error message down.

If I see it again I will post. Thanks for your response.

Crazy

Jens wrote:
> Whats the error message of the database, that you mean that the db is
> corrupt ? Maybe you did something wrong in there. The be would be to
> post you used code here as well as the error message you are facing.
> HTH, jens Suessmeyer.

DAC not supported.

SQL2K5
SP1
Howdy all. I'm trying to open up Dedicated Admins Connection in Management
Studio and getting "DAC's are not supported. (Object Explorer)"
Any idea's?
TIA, ChrisRYou may need to enable the sp_configure option 'remote admin connections'.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> SQL2K5
> SP1
> Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> Studio and getting "DAC's are not supported. (Object Explorer)"
> Any idea's?
> TIA, ChrisR|||DAC is not supported for the object explorer, only for the query window.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> SQL2K5
> SP1
> Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> Studio and getting "DAC's are not supported. (Object Explorer)"
> Any idea's?
> TIA, ChrisR|||Hi,
you may check my screencasts on my site, one topic is about enabling
DAC.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks Kalen, that was it.
"Kalen Delaney" wrote:
> DAC is not supported for the object explorer, only for the query window.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> > SQL2K5
> > SP1
> >
> > Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> > Studio and getting "DAC's are not supported. (Object Explorer)"
> >
> > Any idea's?
> >
> > TIA, ChrisR
>
>

DAC not supported.

SQL2K5
SP1
Howdy all. I'm trying to open up Dedicated Admins Connection in Management Studio and getting "DAC's are not supported. (Object Explorer)"
Any idea's?
TIA, cfrBy default, the DAC is only available via the SQLCMD command line utility. I think it can be opened up to other things, but MS figured that ocule be a security risk.|||Got it. It cant be used through MGMT Studio, only a query window.

Thanks.

CVS - Columns not exporting when Visibility>Hidden has a parameter in it

Hi there,
I am using SQL 2005, with Reporting Services 2005 and Visual Studio
2005 Team Suite.
I have a report which uses 52 parameters to determine which columns are
shown.
Each of the columns have a value in the visibilty>hidden field of
something like '=IIF(Parameters!showname.Value,False,True)' where
showname is a boolean parameter.
It displays perfectly in html & pdf - if the parameter is false, the
column is hidden, if the parameter is true, the column is hidden.
When i try to export to csv, it doesn't show the column at all. Even
if the expression is changed to '=IIF(1=1,False,True)' - the only way I
can get the column to show in the csv output is by setting the hidden
value to 'False' which means that its shown regardless of the
parameter.
Any ideas?I got a reply from a MS contact on msdn. Here is his reply for anyone
else with the same problem...
John,
You can't conditionally hide and show data in data renderers (CSV,
XML). This is by design. If you have an expression in the Hidden field
and 'Auto' in DataOutput tab for text boxes in the column, your data
will not be rendered into CSV or XML.
You can set DataElementOutput to Output for textboxes in the cells and
in the header, and the coulmn will always be in the output file.
Thanks!
----
- DenisL (SQL RS Team)
johnburns007@.gmail.com wrote:
> Hi there,
> I am using SQL 2005, with Reporting Services 2005 and Visual Studio
> 2005 Team Suite.
> I have a report which uses 52 parameters to determine which columns are
> shown.
> Each of the columns have a value in the visibilty>hidden field of
> something like '=IIF(Parameters!showname.Value,False,True)' where
> showname is a boolean parameter.
> It displays perfectly in html & pdf - if the parameter is false, the
> column is hidden, if the parameter is true, the column is hidden.
> When i try to export to csv, it doesn't show the column at all. Even
> if the expression is changed to '=IIF(1=1,False,True)' - the only way I
> can get the column to show in the csv output is by setting the hidden
> value to 'False' which means that its shown regardless of the
> parameter.
> Any ideas?

Customizing Report Model AutoGeneration

Is there any way to customize the expressions that are generated when using the AutoGenerate wizard to produce a model in Visual/BI Studio? There are certain expressions that my team always has to generate for nearly every datetime attribute. Adding the expressions manually is very tedious since the expression window does not allow simple copy & paste.

There is no supported way to customize the fields created by the model generation wizard. However, if you poke around a little on your file system (for instance, under C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ReportModelProjects), you might find an undocumented file that, when modified, seems to influence the output of the model generation wizard. Remember, though, you're on your own. :)

Customizing Report Model AutoGeneration

Is there any way to customize the expressions that are generated when using the AutoGenerate wizard to produce a model in Visual/BI Studio? There are certain expressions that my team always has to generate for nearly every datetime attribute. Adding the expressions manually is very tedious since the expression window does not allow simple copy & paste.

There is no supported way to customize the fields created by the model generation wizard. However, if you poke around a little on your file system (for instance, under C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ReportModelProjects), you might find an undocumented file that, when modified, seems to influence the output of the model generation wizard. Remember, though, you're on your own. :)