Adding, Deleting rows from Visual Basic 2005 Express Edition

Hi,

I'm a complete novice concerning SQL Server (Express Edition)

I'm trying to Add or Delete rows froma VB 2005 Express Function or Sub. While the program is running everything is ok. Except when restarted added records are gone and deleted records are back.

Have i missed an option during installation?

Thx,

Steven

Your installation of SQL Server 2005 Express may be operating in 'Snapshot Isolation' mode. Refer to Books Online for more details.

You may also find this series of instructional videos to be useful.

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

|||

Make sure that you did not specify the datafile for "Always copy", if you did this, the file will always be copied from scratch upon new start of the Visual Studio debug session.

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

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Arnie,

Thx for your trouble, but i got the 'Always Copy' option wrong!

Greetings,

Steven

|||

Hi Jens,

Your answer was the correct one.

Thx,

Steven

adding leading zeros in select

Hi all,

I'm trying to select a bigint field and format it with leading zeros. I've tried the convert function but it does not seem to support this basic feature. There is no reference in the document on CONVERT or CAST, it only refers to formatting dates.

example:
table contents
123
456
789

desired select result:
000123
000456
000789

Who has a solution for this problem?select
replicate('0', 6-datalength(convert(varchar,NumericField)))
from MyTable

Or, better:

create function LZero(@.MyNum int, @.MyLength int)
returns varchar
as
begin
declare @.MyStr varchar
set @.MyStr = convert(varchar,@.MyNum)
if @.MyLength>datalength(@.MyStr)
set @.MyStr = replicate('0',(@.MyLength-datalength(@.MyStr)))+@.MyStr
return @.MyStr
end
go
select dbo.LZero(NumericField,6) from MyTable|||Thanx for your reply.

The first solution you offer works fine and suits my needs. I can't get the second to work properly however. The query only returns '0' for every row.

But this will get me where I want. Thank you very much|||Ok.
I have no SQL Server near me to debug it, so go for the 1st option.|||Originally posted by kukuk
Ok.
I have no SQL Server near me to debug it, so go for the 1st option.

OK, I will, thanx again|||the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.|||Originally posted by jora
the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.

Thanx for your reply, I already found it out and fixed it.

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
Mike
You are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
|||Thanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:[vbcol=seagreen]
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
MikeYou are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Note that when you use dateadd, the second parameter should be a number
representing the number of ... (in your case days) that should be added.
If Def_Date is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
Gert-Jan
Michael wrote:
> Thanks for your reply.
> I am using float type date to convert to a datetime. Please see the
> following codes.
> select dateadd(dd, Def_Date, '1/1/1960') from one;
> Thanks,
> Mike
> Jens wrote:
> > You are exceeding the valid datetime range differs if you use datetime
> > or smalldateimte, which command did you use ? could you please post the
> > commandtext you are using ? What is the datatype of you are doing the
> > dateadd operation.
> >
> > HTH, Jens K. Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --|||Thanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:
> >
> > Thanks for your reply.
> >
> > I am using float type date to convert to a datetime. Please see the
> > following codes.
> >
> > select dateadd(dd, Def_Date, '1/1/1960') from one;
> >
> > Thanks,
> > Mike
> >
> > Jens wrote:
> > > You are exceeding the valid datetime range differs if you use datetime
> > > or smalldateimte, which command did you use ? could you please post the
> > > commandtext you are using ? What is the datatype of you are doing the
> > > dateadd operation.
> > >
> > > HTH, Jens K. Suessmeyer.
> > >
> > > --
> > > http://www.sqlserver2005.de
> > > --

Adding a value to a 'datetime' column caused overflow.

Hi,
When I use dateadd function to a table containing around 10000 values,
it gave the following msg. Adding a value to a 'datetime' column caused
overflow. What does it mean?
Thanks,
MikeThanks a lot! This is exactly the problem!
Thanks,
Mike
Gert-Jan Strik wrote:[vbcol=seagreen]
> Note that when you use dateadd, the second parameter should be a number
> representing the number of ... (in your case days) that should be added.
> If Def_Date is a float "representing" a date, then this value is likely
> to be too high. If it exceeds 2936549 you will get an out or range error
> (or similar error). If it represents a date, you should cast it to a
> datetime.
> Gert-Jan
>
> Michael wrote:|||You are exceeding the valid datetime range differs if you use datetime
or smalldateimte, which command did you use ? could you please post the
commandtext you are using ? What is the datatype of you are doing the
dateadd operation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks for your reply.
I am using float type date to convert to a datetime. Please see the
following codes.
select dateadd(dd, Def_Date, '1/1/1960') from one;
Thanks,
Mike
Jens wrote:
> You are exceeding the valid datetime range differs if you use datetime
> or smalldateimte, which command did you use ? could you please post the
> commandtext you are using ? What is the datatype of you are doing the
> dateadd operation.
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Note that when you use dateadd, the second parameter should be a number
representing the number of ... (in your case days) that should be added.
If Def_Date is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
Gert-Jan
Michael wrote:[vbcol=seagreen]
> Thanks for your reply.
> I am using float type date to convert to a datetime. Please see the
> following codes.
> select dateadd(dd, Def_Date, '1/1/1960') from one;
> Thanks,
> Mike
> Jens wrote: