Best way to copy database: values

Showing posts with label values. Show all posts

Tuesday, March 27, 2012

binding a database records to a dropdownlist

Hey I just wanted to know if anyone could tell me how to bind values from a database to a dropdownlist?

DropDownlist.DataSource = dataset; // dataset returned from the database

DropDownlist.DataBind();

|||

Hi here is a detailed code fromdata bind a DropDownList declaratively to a data source control:

<html><head> <script language="VB" runat="server"> Sub SubmitBtn_Click(sender As Object, e As EventArgs) Label1.Text = "You chose: " + DropDown1.SelectedValue End Sub </script></head><body> <h3><font face="Verdana">DataBinding DropDownList</font></h3> <form runat=server> <asp:DropDownList id="DropDown1" DataSourceID="SqlDataSource1" DataTextField="au_lname" DataValueField="au_id" runat="server" /> <asp:SqlDataSource id="SqlDataSource1" ConnectionString='<%$ ConnectionStrings:Pubs%>' SelectCommand="select DISTINCT au_id, au_lname from authors" runat="server"/> <asp:button Text="Submit" OnClick="SubmitBtn_Click" runat=server/> <p> <asp:Label id=Label1 Font-Names="Verdana" font-size="10pt" runat="server" /> </form></body></html>

Hope it helps.

Thursday, March 8, 2012

BI Accelerator: problem with current day setting

quote:
Originally posted by Eugene Frolov
Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?

|||Hi!
Checkout this:
http://support.microsoft.com/defaul...b;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message447789.html
>

BI Accelerator: problem with current day setting

Hi, All!
I'm experimenting with MSSABI and look this problem - after running
SetCurentDay.dts package every measure values mutiplied by 6.
Here is my steps:
1. Create Analytical, SM and staging databeses with Analytics builder
utility for SMA template (unmodified).
2. Manualy run Master Import DTS package for loading sample data in Satging
DB.
3. Manualy run Master Update DTS package for loading sample data in Subject
Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1 for
automatic processing disable).
4. Manualy run processing of Sales cube in Ananlytical DB and browse data
after its finishing.
Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim -
in rows), calculated members in Time.Standard dim - empty due to current
day is not set.
5. Manualy run SetCurrentDay.dts package with gsCurrentDay = '10.05.2001'
(October 5 2001)
6. Again manualy run processing of Sales cube in Ananlytical DB and browse
data after its finishing.
Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard dim -
in rows), calculated members in
Time.Standard dim have some values.
This behaviour fist time was occured in my custom Analytical app.
constructed using MSSABI, the standard template (Sales and marketing)
behaviour is the same.
Where is the problem - is MSSABI or I'm doing something wrong?
Hi
I have already seen this problem a while ago. Checkout
http://www.webservertalk.com/showthread.php?t=895520
I don't really understand what the guy that replied means by "Setting
the all tu current".
What I did at the time (and of course that is not the solution) was to
remove the Weel.Standard dimension from the cube.
Did you found a solution.?
Eugene Frolov wrote:
> *Hi, All!
> I'm experimenting with MSSABI and look this problem - after running
> SetCurentDay.dts package every measure values mutiplied by 6.
> Here is my steps:
> 1. Create Analytical, SM and staging databeses with Analytics
> builder
> utility for SMA template (unmodified).
> 2. Manualy run Master Import DTS package for loading sample data in
> Satging
> DB.
> 3. Manualy run Master Update DTS package for loading sample data in
> Subject
> Matter DB (with gbManualProcessDim and gbManualProcessFact set to -1
> for
> automatic processing disable).
> 4. Manualy run processing of Sales cube in Ananlytical DB and browse
> data
> after its finishing.
> Measure Actual Invoice Count is 2843 for 2001 year (Time.Standard dim
> -
> in rows), calculated members in Time.Standard dim - empty due to
> current
> day is not set.
> 5. Manualy run SetCurrentDay.dts package with gsCurrentDay =
> '10.05.2001'
> (October 5 2001)
> 6. Again manualy run processing of Sales cube in Ananlytical DB and
> browse
> data after its finishing.
> Measure Actual Invoice Count is 17058 for 2001 year (Time.Standard
> dim -
> in rows), calculated members in
> Time.Standard dim have some values.
> This behaviour fist time was occured in my custom Analytical app.
> constructed using MSSABI, the standard template (Sales and
> marketing)
> behaviour is the same.
> Where is the problem - is MSSABI or I'm doing something wrong? *
rsada
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message447789.html
|||Hi!
Checkout this:
http://support.microsoft.com/default...;en-us;834285.
Data in cubes become correct after delete ALL date dims exept only one with
custom rollups.
"rsada" <rsada.1fzsd0@.mail.webservertalk.com> /
: news:rsada.1fzsd0@.mail.webservertalk.com...
> Hi
> I have already seen this problem a while ago. Checkout
> http://www.webservertalk.com/showthread.php?t=895520
> I don't really understand what the guy that replied means by "Setting
> the all tu current".
> What I did at the time (and of course that is not the solution) was to
> remove the Weel.Standard dimension from the cube.
> Did you found a solution.?
>
>
> Eugene Frolov wrote:
>
> --
> rsada
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message447789.html
>

Wednesday, March 7, 2012

BETWEEN statement

I was wondering if someone can correct me on this. When I run this query I get no results displayed. By default, I made sure I used all the values from beginning to end to test it.

USE POS410
GO
SELECT SSN#, Hire_date
FROM Employee
WHERE Hire_date BETWEEN 1/1/1995 AND 12/31/2005
GO1/1/1995 is an arithmetic expression meaning "1 divided by 1 divided by 1995"

this will round off to zero

you will probably get some results if you write this --

WHERE Hire_date BETWEEN '1995-01-01' AND '2005-12-31'|||that did the trick! thank you

Saturday, February 25, 2012

Between clause...

Hi all, I need write a stored proc with a clause like "between like xxx AND
like xxx" so, I can't. Thats is, a stored proc that returns values between
two parameters that use wildcards (for instance "LIKE @.date").
Any Ideas. I read all the books and, I encounter information about BETWEEN
usage and LIKE usage, but not how to use BOTH in the same SP.
Thanks an avanceNando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AN
D
> like xxx" so, I can't. Thats is, a stored proc that returns values between
> two parameters that use wildcards (for instance "LIKE @.date").
> Any Ideas. I read all the books and, I encounter information about BETWEEN
> usage and LIKE usage, but not how to use BOTH in the same SP.
> Thanks an avance
Depends on what your LIKE clause is and what the desired result is. It
seems to me that "between" two wildcards would only make sense for a
limited number of cases. For example:
CREATE tbl (col VARCHAR(10) NOT NULL, ...);
SELECT col FROM tbl
WHERE col LIKE '[a-c]%';
could be rewritten as:
..
WHERE col >= 'a' AND col < 'd';
but this is obviously different from:
..
WHERE col BETWEEN 'a' AND 'c';
Could you give a better explanation of what you want to achieve please.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can you post the query. Seems very interesting.|||hi all, the query seems like this:
ALTER PROCEDURE dbo.Prueba_Entre
(@.vI nchar(100),
@.vF nchar(100))
AS SELECT Codigo_TSA, Id_Producto, Nombre_Emp, Ingresos, Egresos
FROM dbo.Vista_Prueba
WHERE (Codigo_TSA BETWEEN @.vI AND @.vF)
I want to substitute the parameters @.vI, @.vF for something like this:
LIKE @.vI AND LIKE @.vI, since I need that the parameters accept wildcards (%)
.
I currently use SQL server 2000 and, for simplicity use ACCESS 2003 to
graphically design the SP.
In other words, I need to retrieve values between two parameters, both MUST
accept wildcards, It's possible?
"Omnibuzz" wrote:

> Can you post the query. Seems very interesting.|||Thanks for the input.
But we need to know the functionality behind using the like operator.
Because from my experience, I would say it is logically wrong in any
situation to use like and between in the following cases
(pseudo code, of course :)
col1 between like '%abc%' and like '%xyz%'
of course if you want it to be compared between something like
'%abc' and '%xyz'
or
'abc%' and 'xyz%'
then you can use something like this in the where condition.
left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
left operator in this case
or
right(col1,3) between 'abc' and 'xyz'
Boy.. lack of info leads to lots of typing :)
Hope this helps.|||thanks for you time, the case is: I have a product table, I want to retrieve
a range of these by product code, ie: 10xxxx to 25xxx, because I want the
user have the facility to write ONLY the first characters of product code to
establish the range
I it's possible?
Thanks
"Omnibuzz" wrote:

> Thanks for the input.
> But we need to know the functionality behind using the like operator.
> Because from my experience, I would say it is logically wrong in any
> situation to use like and between in the following cases
> (pseudo code, of course :)
> col1 between like '%abc%' and like '%xyz%'
> of course if you want it to be compared between something like
> '%abc' and '%xyz'
> or
> 'abc%' and 'xyz%'
> then you can use something like this in the where condition.
> left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
> left operator in this case
> or
> right(col1,3) between 'abc' and 'xyz'
> Boy.. lack of info leads to lots of typing :)
> Hope this helps.
>
>|||Is it an integer column or a char column (or does the column hold integer
values only?).
And do they always enter the first 2 digits? or it can be 1 or 3 digits?
"Nando_uy" wrote:
> thanks for you time, the case is: I have a product table, I want to retrie
ve
> a range of these by product code, ie: 10xxxx to 25xxx, because I want the
> user have the facility to write ONLY the first characters of product code
to
> establish the range
> I it's possible?
> Thanks
> "Omnibuzz" wrote:
>|||thanks Omnibuzz, it's a character column and until now I don't decide the
numbers of characters the user input, but say, 2 characters will be fine!!!
Looks better?
"Omnibuzz" wrote:
> Is it an integer column or a char column (or does the column hold integer
> values only?).
> And do they always enter the first 2 digits? or it can be 1 or 3 digits?
>
> "Nando_uy" wrote:
>|||Anyways.. check this out..
create table #temp( a varchar(10))
insert into #temp
select '12ewew'
union all
select '13rere'
union all
select '23dds'
union all
select '26rerere'
union all
select '2454cdfd'
select * from #temp where a between '12' and '25'
Hope this helps.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It
> seems to me that "between" two wildcards would only make sense for a
> limited number of cases. For example:
> CREATE tbl (col VARCHAR(10) NOT NULL, ...);
> SELECT col FROM tbl
> WHERE col LIKE '[a-c]%';
> could be rewritten as:
> ...
> WHERE col >= 'a' AND col < 'd';
Maybe, but it would not necessarily generate the same result:
CREATE TABLE #tmp (a varchar(19) COLLATE Finnish_Swedish_CS_AS NOT NULL)
go
INSERT #tmp(a) VALUES ('Annichen')
INSERT #tmp(a) VALUES ('avfallskvarn')
INSERT #tmp(a) VALUES ('Beatrice')
INSERT #tmp(a) VALUES ('betongarbetare')
INSERT #tmp(a) VALUES ('Cecilia')
INSERT #tmp(a) VALUES ('citrusfrukt')
INSERT #tmp(a) VALUES ('Daneiella')
INSERT #tmp(a) VALUES ('daggfuktig')
go
SELECT a FROM #tmp WHERE a LIKE '[a-c]%' -- five rows
SELECT a FROM #tmp WHERE a >= 'a' AND a < 'd' -- six rows
go
DROP TABLE #tmp
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

BETWEEN clause & <= operators

Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.

HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --

where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th

using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)

Better way to check row for values?

I have a group of fields that I want to check for len() = 0. If one of
them in that group is blank len()=0 then I will set some output
parameter to yes. Then the client will report to the user that they need
to complete that section.
My solution now is to set a each field to a variable and if any one of
them are 0 (using or), set the parameter.
Is there a better way to do this?
I was thinking it would be great if there was a function that would just
return true or false if any field in that function was null/len()=0
Example
IFEmpty(Field1, Field2, etc) return True
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comyes, use NullIf in combination with Coalesce...
NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
Null, or if ColA = '' (empty)
Then compare Coalesce of all columns together
If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
NullIf(ColD, ''), etc, ) Is Null,
-- Then at one (or more) of them is null or empty
"Steve Lewis - Website Nation" wrote:

> I have a group of fields that I want to check for len() = 0. If one of
> them in that group is blank len()=0 then I will set some output
> parameter to yes. Then the client will report to the user that they need
> to complete that section.
> My solution now is to set a each field to a variable and if any one of
> them are 0 (using or), set the parameter.
> Is there a better way to do this?
> I was thinking it would be great if there was a function that would just
> return true or false if any field in that function was null/len()=0
> Example
> IFEmpty(Field1, Field2, etc) return True
>
> --
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>|||Cool, thanks, I will check that out. I have used NullIf before, but not
Coalesce. That looks like it will solve my problem.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 8:49 PM, CBretana wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||I believe that your suggestion will test whether all of the fields are empty
.
Whereas the original poster was trying to test for any one of the fields
being empty.
You could try something like...
create table testtable
(
colA varchar(10),
colB varchar(10)
)
go
insert into testtable values ('', '')
insert into testtable values (null, null)
insert into testtable values ('a', '')
insert into testtable values ('a', null)
insert into testtable values (null, 'a')
insert into testtable values ('', 'a')
insert into testtable values ('a', 'a')
go
select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb,
''), 1, 1) is null then 1 else 0 end from testtable
go
drop table testtable
"CBretana" wrote:
> yes, use NullIf in combination with Coalesce...
> NullIf(CoalA, '') returns a Null if COlA = '' (empty String), else it
> returns value of ColA... So NullIf(ColA, '') will return Null if ColA IS
> Null, or if ColA = '' (empty)
> Then compare Coalesce of all columns together
> If Coalesce(NullIf(ColA, ''), NullIf(ColB, ''), NullIf(ColC, ''),
> NullIf(ColD, ''), etc, ) Is Null,
> -- Then at one (or more) of them is null or empty
> "Steve Lewis - Website Nation" wrote:
>|||Yes, I am trying to test for any one of the fields. But, between the two
if you, I think I have an answer.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/9/2005 9:55 PM, Mike Gemmell wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||Mike,
Right, Coalesce returns the first non-null parameter in the parameter list,
So only if all of them are null, will the whole Coalesce expression will be
null.
Concatenating the NullIfs, as you suggest, is the right answer...
"Mike Gemmell" wrote:
> I believe that your suggestion will test whether all of the fields are emp
ty.
> Whereas the original poster was trying to test for any one of the fields
> being empty.
> You could try something like...
> create table testtable
> (
> colA varchar(10),
> colB varchar(10)
> )
> go
> insert into testtable values ('', '')
> insert into testtable values (null, null)
> insert into testtable values ('a', '')
> insert into testtable values ('a', null)
> insert into testtable values (null, 'a')
> insert into testtable values ('', 'a')
> insert into testtable values ('a', 'a')
> go
> select case when substring(nullif(colA, ''), 1, 1) + substring(nullif(colb
,
> ''), 1, 1) is null then 1 else 0 end from testtable
> go
> drop table testtable
> "CBretana" wrote:
>|||OK , I see how the concatenation will work, but doesn't
CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
According to books online:
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
a string yields a NULL result. For example, SELECT 'abc' + NULL yields
NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
value with a string yields the string itself (the null value is treated
as an empty string). For example, SELECT 'abc' + NULL yields abc.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:03 AM, CBretana wrote:
> Mike,
> Right, Coalesce returns the first non-null parameter in the parameter lis
t,
> So only if all of them are null, will the whole Coalesce expression will
be
> null.
> Concatenating the NullIfs, as you suggest, is the right answer...
>
> "Mike Gemmell" wrote:
>|||OK , i just found that is on by default:
Option Default Setting
Set nocount OFF
Set noexec OFF
Set parseonly OFF
Set concat_null_yields_null ON
Set rowcount 0
Set ansi_defaults ON
Set arithabort ON
Set showplant_text OFF
Set statistics time OFF
Set statistics 10 OFF
Set ansi_nulls ON
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Set cursor_close_on_commit OFF
Set implicit_transactions OFF
Set quoted_identifier ON
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
> OK , I see how the concatenation will work, but doesn't
> CONCAT_NULL_YIELDS_NULL have to be set ON or is that ON by default?
> According to books online:
> When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with
> a string yields a NULL result. For example, SELECT 'abc' + NULL yields
> NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null
> value with a string yields the string itself (the null value is treated
> as an empty string). For example, SELECT 'abc' + NULL yields abc.
>
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:03 AM, CBretana wrote:
>|||Ok , this is what I have. I will test it tomorrow:
select case when substring(nullif(colA, ''), 1, 1) +
substring(nullif(colb, ''), 1, 1) is null then set @.Incomplete = 'Yes'
else set @.Incomplete = 'No' end from tblEmployee where EmployeeID =
@.EmployeeID
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 3/10/2005 12:17 AM, Steve Lewis - Website Nation wrote:
> OK , i just found that is on by default:
> Option Default Setting
> Set nocount OFF
> Set noexec OFF
> Set parseonly OFF
> Set concat_null_yields_null ON
> Set rowcount 0
> Set ansi_defaults ON
> Set arithabort ON
> Set showplant_text OFF
> Set statistics time OFF
> Set statistics 10 OFF
> Set ansi_nulls ON
> Set ansi_null_dflt_on ON
> Set ansi_padding ON
> Set ansi_warnings ON
> Set cursor_close_on_commit OFF
> Set implicit_transactions OFF
> Set quoted_identifier ON
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 3/10/2005 12:15 AM, Steve Lewis - Website Nation wrote:
>|||Why don't you use CHECK constraints to validate the data on entry?
CREATE TABLE foo (... col VARCHAR(10) NOT NULL CHECK (col > '')...)
David Portas
SQL Server MVP
--

Friday, February 24, 2012

best way to store simple up or down values

I'm creating a table for maintenance records.

In each record, many of the values are simply checkboxes (on the UI).

In the database table for these attributes, is a good way to store the
state of these checkboxes as simple as 0 for false, 1 for true?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0402110709.30e1e084@.posting.google.c om...
> I'm creating a table for maintenance records.
> In each record, many of the values are simply checkboxes (on the UI).
> In the database table for these attributes, is a good way to store the
> state of these checkboxes as simple as 0 for false, 1 for true?
> -David

There are two common ways to do this - either use a bit column, or use
something like a char(1) with a check constraint to ensure the values are
T/F or Y/N. The second solution is more portable, if that's a concern.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

On the other hand it is more sensitive to localization issues. We used
to have such columns in our databases, but I think all are gone now. The
values we used where J/N.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEE4DEAA00Yazorman@.127.0.0.1>...
> Simon Hayes (sql@.hayes.ch) writes:
> > There are two common ways to do this - either use a bit column, or use
> > something like a char(1) with a check constraint to ensure the values are
> > T/F or Y/N. The second solution is more portable, if that's a concern.
> On the other hand it is more sensitive to localization issues. We used
> to have such columns in our databases, but I think all are gone now. The
> values we used where J/N.

That's a good point, although I would guess (very possibly
incorrectly) that many IT people would be familiar with 'True' and
'False' as Boolean values in various programming languages, even if
their own natural language isn't English. Personally, I think a bit is
the most obvious data type for flags, but then that seems to invite a
lecture from Joe Celko...

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<402a789f$1_3@.news.bluewin.ch>...

> There are two common ways to do this - either use a bit column, or use
> something like a char(1) with a check constraint to ensure the values are
> T/F or Y/N. The second solution is more portable, if that's a concern.

Thanks, I went with the bit method. I've been working on projects
involving Oracle and other databases for the past few months and hope
not to port anything to any of them! I'm content to be back to a SQL
Server db.

-David|||Simon Hayes (sql@.hayes.ch) writes:
> That's a good point, although I would guess (very possibly
> incorrectly) that many IT people would be familiar with 'True' and
> 'False' as Boolean values in various programming languages, even if
> their own natural language isn't English. Personally, I think a bit is
> the most obvious data type for flags, but then that seems to invite a
> lecture from Joe Celko...

Already when you give the choice of Y/N and T/F you have give a choice
that can be source for confusion. One of the DBA goes for the former,
another for the latter, guess if developers will mess up.

As for localisation, recall that some of this data may make to a GUI.
That was the case with our J/N, which our Swedish users had no problem to
understand. They might be able to make out Y/N too, but I would guess that
T/F, or even S/F, would leave them completely in the dark.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I'm creating a table for maintenance records. In each record, many
of the values are simply checkboxes (on the UI). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

When you convert the *record* on the paper forms into one or more
*rows* in one or more tables in the database, what does the data look
like?

What I have seen is for maintenance databases is that "yes/no" is not
good enough. You need to know temporal information for each task,
like "scheduled time" and "completed time" as minimal data for
computing MTBF and other things. Would you write an accountign system
in which you had flag for "paid/not paid" and leave out the amounts
and dates?

Simple yes/no flags are all too often computed columns that can be
deduced from the other attributes in the database.

Sunday, February 19, 2012

Best way to search

I have a stored procedure declared (shown below) The intent of the stored
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?
CREATE PROCEDURE dbo.pSearch
@.strFirstName varchar(50) = NULL,
@.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
@.iYear int = null
SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
(@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
(@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
(@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
(@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>I have a stored procedure declared (shown below) The intent of the stored
>proc is to return all records where the field values match the criteria
>specified in the stored proc parameters. I want to specify some or all of
>the parameter values. What I have written works, but I don't think it is
>very efficient, any ideas?
> CREATE PROCEDURE dbo.pSearch
> @.strFirstName varchar(50) = NULL,
> @.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
> @.iYear int = null
>
> SELECT TOP 50
> p.[ID],
> np.[Name] as Prefix,
> p.[FirstName],
> p.[MiddleName],
> p.[LastName],
> p.[DateOfBirth]
> FROM
> [Patient] p
> JOIN
> [NamePrefix] np ON p.NamePrefixID = np.[ID]
> WHERE
> (@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
> (@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
> (@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
> (@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
> (@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
> ORDER BY
> p.[LastName],
> p.[MiddleName],
> p.[FirstName]
> GO
>

Tuesday, February 14, 2012

Best way to insert large amounts of data from a webform to SQL Server 2005

I have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).

What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.

Any ideas?
Thanks
Ed

Hi,

If you gonna insert or update in single table then use SQL Data adapter. But it needs to have primary or unique key to create update query via sql command builder otherwise its just insert all rows in your table.

If you want to update for above scenario there are two ways.

1. Create staging table in your database and insert your records using sqlbulkcopy class which can insert lakhs of data/s (i'm not sure abt amt), then create a stored procedure to update or insert rows from staging table to main table

2. You can write your update and insert command in sqldataadapter.sqlcommand property.

for example

create a table as testtable with id number and name as text

Sql query will be like this

update testtable
set name = @.namecolumn
Where id = @.idColumn
If @.@.Rowcount = 0
Insert into testtable (id,name) values(@.idColumn,@.nameColumn)

then add sql parameters(@.idcolumn,@.nameColumn) with your data table column names

then adapter will execute the query while you call update method

hope it helps

Tuesday, March 27, 2012

binding a database records to a dropdownlist

Thursday, March 8, 2012

BI Accelerator: problem with current day setting

BI Accelerator: problem with current day setting

Wednesday, March 7, 2012

BETWEEN statement

Saturday, February 25, 2012

Between clause...

BETWEEN clause & <= operators

Better way to check row for values?

Friday, February 24, 2012

best way to store simple up or down values

Sunday, February 19, 2012

Best way to search

Tuesday, February 14, 2012

Best way to insert large amounts of data from a webform to SQL Server 2005

Best way to copy database

Blog Archive

About Me