Data fetch

Hi,

I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks

hi,

yes, it's possible... you just have to change your mind about how you fetch and work with data... do no longer "think" in terms of "tables" like in older JET paradgm where you opened a table and navigate via seek methods to the required "record", but "think" in terms of compacted recordsets.. thus filter the data you really need both in horizzontal and vertical terms..

in horizzontal term, returns only the actual columns you need, so provide the restricted column list of the projection you want (do not write SELECT * FROM but SELECT colX, colY, colZ FROM)..

in vertical term, provide a filter condition to the FROM clause in order to return just the "bunch" of rows you are interested with, so write

SELECT <col_list> FROM dbo.Customers WHERE CustomerID = theCustomerYouAreLookingFor

instead of

SELECT * FROM dbo.Customers

and the navigate to the customer row via client side Ado.Net/ADO/whatever seek methods..

regards

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

grbourque wrote:

...

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 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.

Data Base Mirroring in SQL server 2005 Express Edition

HI,

Does SQL server 2005 Express Edition or

Does SQL server 2005 Express Edition Sp1 supports Data base Mirroring?

Here is direct quote from BOL,

http://msdn2.microsoft.com/en-us/library/ms188712.aspx

Before you use the Mirroring page to configure database mirroring, ensure that the following requirements have been met:

The principal and mirror server instances must be running the same edition of SQL Server-either Standard Edition or Enterprise Edition. Also, we strongly recommend that they run on comparable systems that can handle identical workloads.

Note:

The witness server instance can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.

|||It answers to my question. Thank you.

Damn Newbie

I am looking to make a program using express edition Visual C#. I want my
application to interact with Microsoft SQL server, create tables, inserts,
lookups, the normal.

First I would like to read the documentation,and now to the point of my
post. Due to the (recently pointed out) ineptitude of my googling skills I
cannot find what I am looking for.

I presume this is an obvious and easy request so all comments on that front
will be directed to the bit bucket :-)

TIAShane (shane@.weasel.is-a-geek.net) writes:

Quote:

Originally Posted by

I am looking to make a program using express edition Visual C#. I want my
application to interact with Microsoft SQL server, create tables, inserts,
lookups, the normal.
>
First I would like to read the documentation,and now to the point of my
post. Due to the (recently pointed out) ineptitude of my googling
skills I cannot find what I am looking for.
>
I presume this is an obvious and easy request so all comments on that
front will be directed to the bit bucket :-)

Not sure what documentation you are asking for. But SQL Server comes with
Books Online, see also links below.

I don't know what Visual C# Express comes with, but you should find MSDN
Library on msdn.microsoft.com.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

DADIFF expressed as float

Hello,
I am trying to calculate an age in terms of years and express the result as
a float, such as 3.75 years or 5.33333 years etc. I have tried things
like...
CONVERT(float, DATEDIFF(d, MyStartDate, getdate()) ) /365 as YearsOld
But this gives results as whole numbers like 3.0 and 5.0. What do I need to
change in the syntax? (I realize that dividing by 365 is inaccurate, but it
is close enough for my purposes). Thanks in advance.Sorry for dup - my newsreader gave me an error and it didnt look like the
first one posted.
"Mark Hoffy" <mark@.here.com> wrote in message
news:vnuIe.291$Zo3.240@.fe03.lga...
> Hello,
> I am trying to calculate an age in terms of years and express the result
as
> a float, such as 3.75 years or 5.33333 years etc. I have tried things
> like...
> CONVERT(float, DATEDIFF(d, MyStartDate, getdate()) ) /365 as YearsOld
> But this gives results as whole numbers like 3.0 and 5.0. What do I need
to
> change in the syntax? (I realize that dividing by 365 is inaccurate, but
it
> is close enough for my purposes). Thanks in advance.
>|||Mark,
I can't reproduce the problem you are having:
declare @.t table (
MyStartDate datetime
)
insert into @.t values ('20030102')
select CONVERT(float, DATEDIFF(d, MyStartDate, getdate()) ) /365 as YearsOld
from @.t
Result:
YearsOld
----
2.5890410958904111
Can you possibly post a complete script that demonstrates the problem?
Steve Kass
Drew University
Mark Hoffy wrote:

>Hello,
>I am trying to calculate an age in terms of years and express the result as
>a float, such as 3.75 years or 5.33333 years etc. I have tried things
>like...
>CONVERT(float, DATEDIFF(d, MyStartDate, getdate()) ) /365 as YearsOld
>But this gives results as whole numbers like 3.0 and 5.0. What do I need t
o
>change in the syntax? (I realize that dividing by 365 is inaccurate, but i
t
>is close enough for my purposes). Thanks in advance.
>
>|||See other thread for reply.
SK
Mark Hoffy wrote:

>Sorry for dup - my newsreader gave me an error and it didnt look like the
>first one posted.
>"Mark Hoffy" <mark@.here.com> wrote in message
>news:vnuIe.291$Zo3.240@.fe03.lga...
>
>as
>
>to
>
>it
>
>
>

Dabase Creation

Is it possible to create a database structure in MS-Access and somehow import it into Visual Web Developer 2005 Express or SQL Server Management Studio 2005 Express?

Yes.

In MSAccess, go to the Tools->Database Utilities menu. Select the Upsizing Wizard.

If you are lucky, it will work. If not, there are other options (DTS).