binding textbox text to a database field

hello

is it possible to to bind a single field from a database table to a textbox.

I am trying to open a web form with five textboxes on it. When it loads is it possible to have the textboxes filled with a different field from a row in a database, selected with a sql statment.

Thanks in advance NubNub

Wrong Forum.|||

Yes, that is quite possible.

But you will get better assistance if you post your question in one of the .NET ASP related forums.

sql

Binding last row in table to a label

Hi all. I have a label on my page and I want to bind it to a field in a table. The catch is that I want to bind it to the last row in the table. I think I can use the custom binding, but I don't know how to bind to the last row. Any Suggestions ?

p.s. The page is tied to an SqlDataSource that retrieves the data from the above table.

Thanks in advance.

Could you not change the datasource to only return the last row? Or did you want all the other rows as well?

What about

Label1.text = dt.Rows(dt.Rows.Count-1)("FieldName").ToString()

where dt is the datatable containing the data you are binding to.

|||

Thanks for the reply GavDraper,

but what is the type of dt, how can I use a sql server table in my code ? and what is the meaning of "FieldName" ?

|||

the type of dt is datatable. You would need to replace fieldname with the name of the field you want to pull data from. below is a very rough quick example although I'm unable to test this as im not at my development machine, you would also want to include some error handling

dim dt as new datatable()dim sqlCon as new sqlConnection(strCon)dim sqlAdp as new SqlDataAdapter()sqlAdp.SelectCommand.Connection = sqlCon()

sqlAdp.Selectcommand.CommandText ("SELECT * FROM tablename")sqlAdp.fill(dt)lbl1.Text = dt.Rows(dt.Rows.Count-1)("FieldName")

|||

I'll give it a try,

but is it possible to do it without the code behind ?

I have the label and I use databinding to bind the fields in the table using the sqldatasource, but somehow it gives me the first row in the table.

I use a stored procedure to select the data from the table and use parameters in that stored procedure. So is it possible to bind to these parameters declaratively so they would give me the fields in the last row ?

The stored procedure:

CREATE PROCEDURE dbo.createPage@.PageTitlenvarchar(300)OUTPUT, @.PageMetaDescnvarchar(300)OUTPUT,@.PageMetaWordsnvarchar(300)OUTPUT,@.PageDescnvarchar(300)OUTPUT,@.PageTemplateint OUTPUTASDECLARE @.pIdAS intSELECT @.pId =cast(SCOPE_IDENTITY()AS int)--selecting the id of the last row inserted--selecting the data from that rowSELECT PageTitle,PageMetaDesc,PageMetaWords,PageDesc,PageHTML, PageTemplate, PageSummaryHTMLFROM PagesWHERE PageId = @.pIdGO

|||

that stored procedure is returning the data in no particular order how can you be sure its always the last record you want? You could change the stored procedure to only return 1 record by using the TOP keyword but to make this accurate the data would have to be ordered to guarentee you will always get the correct row.

Bind computed textbox value to database field

Hi all,

I have three textboxes in a form view

1- Qty textbox

2- price textbox

3-TotalPrice textbox which value is the result of multiplying the previous two values

and then I want to Bind() the third value to the totalprice field in the database

how can I do that??

(without making the database field as a computed column)

thanks for any help

1. What database?
2. What language?
3. How are you retrieving the value of the third textbox currently?
4. How are you performing data access? SqlDataSource controls? ADO.NET code in your code-behind? Custom business classes? Typed DataSets?

You really should provide more information so that people can help you. The answer to your question is very simple. You simply run an Insert statement against the database. However, I suspect that no one has bothered to reply so far because how you run the insert and in what language isn't clear from your post.

If you can work out what to do from the "run an Insert statement" answer, that's great. But if you want some sample code offered, help us to help you.

|||

Hi

Thanks for reply,

1- SQL Express database

2- langauge= VB

3- I don't know (someone has made a visual basic 6 application and I don't have the source code of it so I am converting it into ASP.net)

4- I am using SQL data source control to select,update,insert and delete the records from the database

I want to make the third text box value as a result of multiplying the first two values

And then bind that value to the database

Thank you

|||

Frankly, I'm not sure about the need for a 3rd textbox. Your InsertCommand for the SqlDataSource should be something like "Insert Into mytable (price, quantity, total) Values (@.price, @.quantity, @.total)". You need 3 InsertParameters. The first 2 will be <asp:ControlParameters>, and the ControlID will be the textboxes for price and quantity. The third will be a straightforward <asp:parameter> of type Int32.

Create a SqlDataSource_Inserting event by selecting the datasource control in design view, then hit F4 to bring up the porperties. Double click the lightning bolt to bring up the events and double click the Inserting event. That will create code for handling the event. The code is straightofrward, something along the lines of:

Dim price As Integer = Convert.ToInt32(TextBox1.Text)
Dim qty As Integer = Convert.ToInt32(TextBox2.Text)
Dim total As Integer = price * qty

Then just add code to set the value of the plain parameter to the calculation:

SqlDataSource.InsertParameters("total").DefaultValue = total

Binary Primary Key

I have a 256 bit hash value to identify chunks of data. Would it be a good idea to create a 32-byte binary field as the primary key or encode the bytes to a string?

Well, to me if your natural key really is binary then that is the way I would designate the primary key -- as binary. I'd still like to hear other opinions, though

Binary File

Can someone firecct me to instructions on how to download a Binary file in a asp.net application.

I have a sql 2000 db that has a field that contains files and my users need access to them via my web app.At the end of this article it tells you how to retrieve binary data from the database and then shows you code to actually start the download:

http://www.developer.com/net/asp/article.php/3097661|||That works great thanx.

Binary field usage in SQL Server

Can anyone point me in the right direction to find documentation for the problem below?
I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]).
I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.

The post below is from SQL Server BOL (books online) documentation on BIT, BINARY and VARBINARY data types. I am assuming you know BIT is proprietry because of three valued logic there is no Boolean data type in ANSI SQL. If you need more information post again. Hope this helps.

bit
Integer data type 1, 0, or NULL.

Remarks
Columns of type bit cannot have indexes on them.

Microsoft? SQL Server? optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.

binary and varbinary
Binary data types of either fixed-length (binary) or variable-length (varbinary).

binary [ ( n ) ]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.

varbinary [ ( n ) ]

Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.

Remarks
When n is not specified in a data definition, or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Use binary when column data entries are consistent in size.

Use varbinary when column data entries are inconsistent in size.

bigint field vs int

i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int

i need to know about the performance and difference

will it affect the performance much and second will i have to change any code like delete,update

A standard Int ranges from -2,147,483,648 through 2,147,483,647 so you can use them to store values much larger than 100000.

If you do use bigints, you would need to use Int64 in your code

|||

HI,

There will be some performance difference in using int and bigint. If you use bigint the performance is slower in an 32 bit processer. But in case of a 64 bit processor the performance should not be a problem

|||

Int will give you up to 2,147,483,647 while Bigint gives up to 9,223,372,036,854,775,807. The only difference in your stored procedure is that you will need to change the parameter declarations from INT to BIGINT.

>i need to know about the performance and difference
Obviously more working memory will be required for BIGINT instead of INT (8 bytes instead of 4 for for each value), however SQL's will still work very efficiently provided there is enough RAM. If you only have enough RAM for INT, changing to BIGINT will put you at a disadvantage - so make sure you have enough RAM.

sql

Bigint autoincrement question

Hi All,
i wonder if i can get an bigint autoincrement field where the number begins with the current year + 1 autonumber
Does someone know if it is possible and if yes, how?
Already thanx.
Cheers Wimcreate table test_t
(col1 bigint identity (2004, 1), col2 char (5)
)
go

declare @.counter tinyint

set @.counter = 1

while @.counter <> 5
begin
insert into test_t
select 'test'
set @.counter = @.counter + 1
end
go

select * from test_t
go|||Originally posted by aldo_2003
create table test_t
(col1 bigint identity (2004, 1), col2 char (5)
)
go

declare @.counter tinyint

set @.counter = 1

while @.counter <> 5
begin
insert into test_t
select 'test'
set @.counter = @.counter + 1
end
go

select * from test_t
go

But it doen't change the year automatically|||How do you mean automatically ?

Can you clarify ?|||Originally posted by aldo_2003
How do you mean automatically ?

Can you clarify ?

The id should always start with the current year, so when it is 2005 the id should start with 2005 1 + increment|||Example please?|||Originally posted by aldo_2003
Example please?

It should be something like
create table test_t
(col1 bigint identity (datepart(yy, getdate())1, 1), col2 char (5)
)
go|||An example of what it would look like in a table.|||You should store your year value and your ID value separately, and then use a trigger to increment the ID value.

[YearColumn] defaults to year(getdate())

[IDColumn] is set by a trigger (Insert only!) to:

set [IDColumn] = isnull((select Max([IDCOLUMN]) from YourTable where [YearColumn] = Year(Getdate())), 0) + 1

Then you can combine the two values in your SQL code, or create a calculted column that combines the two.|||I like your solution, but the problem is that i cant set that column to primary key|||You can add a unique index to a computed column. You may be able to make it a primary key, thought I'm not certain about that. In any case, you could set the Year column and the ID column together as a composite primary key.

big problem with data conversion

Dear colleagues, I have a big problem with data conversion. For example, on my Windows form (C#) i have one text box and in my table I have field type float. When I use following conversion or casting:

float i = (float)Convert.ToDouble(textBox1.Text);

and insert number 12,3 in my table I have following number 12.30000000000032324!

What I must to do with this conversion and have in my table number 12,3?

Hi,

although this is not a C# forum, you should take a look at the Help for a more precise datatype than float.

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

In addition see the float definition in the BOL for more information about float in SQL Server.

You might want to have a look on the decimal type instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Big Challange

hi all

i need to write a function to replace one field from another field in strode procedure .

as a example

SELECT
profc.SURNAME + ', ' + profc.FORENAME AS ProfCarer,
SPECS.DESCRIPTION AS Speciality,
steams.STEAM_REFNO_NAME AS StaffTeam,
REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,
COUNT(REFS.REFRL_REFNO) AS NoOfOpenReferrals
FROM REFS
LEFT OUTER JOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO
LEFT OUTER JOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO
LEFT OUTER JOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO
INNER JOIN PrimaryCareTrust pct ON REFS.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

i need to replace REFS.PCT_CODE FROM GEOGAREA.PCT_CODE THIS FUNCTION NEED ONE INPUT PARAMETER CALLED REFS.REFTOPROCA_REFNO

Any Idea
Thank's

I'm a little confused.

You want to translate the REFS.PCT_CODE in order to use it in the joing on PrimaryCareTrust?

|||ya i need to replace refs.pct_code from another cord in all the strode procs|||

OK, what is the the code you want as the replacement?
Where does it come from?

Where does it go within the query?

Please provide more info.

|||the code is GEOGAREA.PCT_CODE its from GeographicArea table
it just replase that REFS.PCT_CODE
|||

This doesn't make sense, but anyway:

Code Snippet

SELECT

profc.SURNAME +', '+ profc.FORENAME AS ProfCarer,

SPECS.DESCRIPTION AS Speciality,

steams.STEAM_REFNO_NAME AS StaffTeam,

REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,

COUNT(REFS.REFRL_REFNO)AS NoOfOpenReferrals

FROM REFS

INNERJOIN GEOGAREA

ON REFS.PCT_CODE = GEOGAREA.PCT_CODE

LEFTOUTERJOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO

LEFTOUTERJOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO

LEFTOUTERJOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO

INNERJOIN PrimaryCareTrust pct ON GEOGAREA.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

|||ya i know this mate. i have lots of procedures so what i want to do is i need create a function that i execute that it'll go and search that REFS.PCT_CODE in each and every procedures and replace it by GEOGAREA.PCT_CODE

can we do this?|||

You bet.

REFS.PCT_CODE is the input

GEOGAREA.PCT_CODE is the output

Now, how are REFS and GEOGAREA related?
How does REFS.PCT_CODE find the correct entry in GEOGAREA?

|||

Spend a little bit time to change your quires(even it is on multiple stored procedures).

If you use function it may decrease the performance.

|||PCT_CODE STANDS geographic area code this are same but refs.pct_code is going to be change thats why.|||

Niranga,

Please provide some details and specifics.

What is the current DDL/schema?

How is it changing?

Provide some sample table data and your expected results.

BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
--
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
--
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
>> BETWEEN '20070601' AND '20070630'
>> The strings will be converted to datetime values (thanks to implicit datatype conversion):
>> 20070601 00:00:00 to 20070701 00:00:00
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>

BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00
The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP
|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>

BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatyp
e conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored
datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that
"June" was what
metaperl wanted, and for some reason this was transferred to me writing that
incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>

Between vs In

I have a need for a criteria to be = 32 or 33 and wondered if BETWEEN 32 AND
33 would be faster than IN(32,33). This is an indexed field. Thanks.
DavidWhen you timed this yourself, what was the result?
What did the query analyzer explain plans say?
I'd bet the optimizer would alter your code to
whatever it thinks is fastest (yes, sql server optimizer
does do this). That said, I'd bet you wouldn't
see a different in either approach.
I'd use the IN clause because it is a direct
comparison versus a math comparison.
Robbe Morris - 2004/2005 Microsoft MVP C#
EggHeadCafe's RSS Search Engine
http://www.eggheadcafe.com/articles...ch/default.aspx
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:e0%23qscwuFHA.2504@.tk2msftngp13.phx.gbl...
>I have a need for a criteria to be = 32 or 33 and wondered if BETWEEN 32
>AND 33 would be faster than IN(32,33). This is an indexed field. Thanks.
> David
>|||I know it would have been faster to try it than to ask that question. The
best answer to performance questions is to try it out for yourself. We don't
have access to your schema, your data and your hardware.
David Portas
SQL Server MVP
--|||Hi
as others suggested, you can test with execution paln, time etc.
But include OR also, that is the best.
IN may have additional ovehead.
Regards
R.D
"David Portas" wrote:

> I know it would have been faster to try it than to ask that question. The
> best answer to performance questions is to try it out for yourself. We don
't
> have access to your schema, your data and your hardware.
> --
> David Portas
> SQL Server MVP
> --
>
>|||On Fri, 16 Sep 2005 23:06:02 -0700, R.D wrote:

>Hi
>as others suggested, you can test with execution paln, time etc.
>But include OR also, that is the best.
>IN may have additional ovehead.
Hi R.D.,
IN(value, value, ...) is exactly equal to a set of OR conditions. Just
check out the execution plan of a query that uses an IN condition.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

between

Hi, can I use between for nvarchar field? can some one explain more detail
please...
fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
select * from tb1 where fd1 between '1000' and 2000'Yes, you can use BETWEEN for nvarchar comparisons. "WHERE a BETWEEN x
and y" is exactly the same as saying "WHERE a >= x AND a <= y", so for
an nvarchar column that simply does an alphanumeric comparison based on
the collation the column ("a" in this example) uses.
Your example query should return
1000
1000a
1000b
2000
because those values all satisfy "fd1 >= '1000' and fd1 <= '2000'" using
an alphanumeric comparison.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
js wrote:

>Hi, can I use between for nvarchar field? can some one explain more detail
>please...
>fd1 has: 1000, 1000a, 1000b, a1000, b1000, 2000, 2000a, 2000b, b2000.
>select * from tb1 where fd1 between '1000' and 2000'
>
>|||Thanks Mike...

between

SQL Server2000 SP3
Field date is smalldatetime
I have 8 rows with data='06-Aug-2003'
SELECT * FROM TEST
where data between '06-Aug-2003' and '06-Aug-2003'
Results: 0 Rows
SELECT * FROM TEST
where data>='06-Aug-2003' and data<='06-Aug-2003'
Results: 0 Rows
What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')What is the time portion of the field ?
What does this give you
SELECT * FROM TEST
where data between '20030806' and '20030807'
OR
SELECT * FROM TEST
where CONVERT(char(8),data,112) = '20030806'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dragos Hilbert" <dragos_hilbert@.yahoo.co.uk> wrote in message
news:eMh$KHAXDHA.652@.TK2MSFTNGP10.phx.gbl...
> SQL Server2000 SP3
> Field date is smalldatetime
> I have 8 rows with data='06-Aug-2003'
> SELECT * FROM TEST
> where data between '06-Aug-2003' and '06-Aug-2003'
> Results: 0 Rows
>
> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
> Results: 0 Rows
> What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')
>
>|||> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
SELECT * FROM test
WHERE data>='20030806' and data<'20030807'
(a) between can be ambiguous, I tend to avoid it for datetime queries.
(b) since datetime columns have time also, the above is converted to
midnight, so you won't likely have rows where "data" is between a day at
midnight and the same day at midnight.
(c) never use SELECT * in production code.
(d) consider changing the column name. "data" is not very descriptive at
all; I would never guess that it is a datetime column...

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
>

Best way to restrict a varchar field not to contain spaces?

Hi,

is this the best way?

ALTER TABLE MyTable
ADD CONSTRAINT CK_MyTable_NoSpace
CHECK (charindex(' ', MyColumn)=0)

Is there any other way?

Thnx

Not sure but you can also try NOT LIKE '% %'

Try them both. What is the reason you need this?

|||

Thanks Simon

SimonSa wrote:

NOT LIKE '% %'

It would have to be trimmed first no?

or maybe:

NOT LIKE '% %' AND NOT LIKE '% ' AND NOT LIKE ' %'

SimonSa wrote:

What is the reason you need this?

File system entries. The OS allows them but I want to restrict them. It is also being done in the DAL.

|||

Carl M. wrote:

Thanks Simon

SimonSa wrote:

NOT LIKE '% %'

It would have to be trimmed first no?

or maybe:

NOT LIKE '% %' AND NOT LIKE '% ' AND NOT LIKE ' %'

No. % can stand for any number of characters, even none. NOT LIKE '% %' would be correct.

|||Thank you.

Best way to insert a DateModified in updates

how do i update the date modified Field in my DB. i was thinking of update trigger? i am using ASP 2 with DetailsView for Editing(Updates in SQL term). i can make a Update SP but think that might not be needed

If you use the SP you can define the date now to a variable. @.datenow = GetDate()|||

In the Update procedure, you can just "SET dateModified=GETDATE()".

In my view, Triggers are a little bad in the respect that they are hidden. If you look at a simple SQL statement, you really can't "trace" the code execution completely if your DB has a whole pile of Triggers firing all the time, which means, things might be happening that the SQL statement writer isn't aware of, and that can cause problems.

Of course, their not overall bad, but my personal preference is to use an SP, unless a Trigger is specifically required or for some odd reason more effecient.

Best way to check if a field Is NULL

I usually check is a field is Null by doing something like this:

WHERE IsNull(myField,'') = ''

However, I just noticed that this is also return records where myField = 0

Does anyone know why?

What is the best and most common way to check if a field is null?

The best way to check if a field is null is

WHERE myField IS NULL

Are you by chance checking for a "blank" field instead of a null field?

|||

Zero is not null.

Try this:

Code Snippet

select *

from MyTable

where MyField IS NULL

|||Arnie,

The fact that rows with MyField = 0 are returned is not a problem with the query. If the type of MyField is integer and its value is zero, the query will test 0 = '', which is true. This is because '' is implicitly converted to integer in the comparison, and in SQL Server, cast('' as integer) has the value zero.

Steve Kass
Drew University
http://www.stevekass.com

|||

Steve,

...such rows would indicate that there is a problem in the query.

Thanks for the clarification. I was referring to the nature of the query -not the evaluation of implicit zero. The OP wanted NULL rows, so the way the query was written (invoking implicit conversions) would not satisfy his/her needs.

I could have more precise, but I dropped it. I'm glad you jumped in to dispel any misconceptions I may have created.