delete data from a field in a table

Hi,
I am trying to delete data from a field in my table, I want to clear all the
data from one field in my table (assuming the table is Students and the
field is firstName)
Thank you in advance.
LamyDo you want to set the column to NULL or an empty string? In any case, it is
a simple UPDATE
statements:
UPDATE tblname
SET colname = NULL
or
UPDATE tblname
SET colname = ''
Note that both above will modify all rows. Add a WHERE clause of you want to
limit.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lamy" <lamine_d@.mpt-ltd.com> wrote in message news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl.
.
> Hi,
> I am trying to delete data from a field in my table, I want to clear all t
he data from one field
> in my table (assuming the table is Students and the field is firstName)
> Thank you in advance.
> Lamy
>|||If the column you need to delete is nullable, you simply can:
UPDATE Students
SET firstName = NULL
If the column isn't nullable you could
UPDATE Students
SET firstName = expression | DEFAULT
if some default is defined you can use it; expression is any expression
resulting in an empty field. Remember that NULL is different from "empty" or
blank.
Gilberto
"Lamy" wrote:

> Hi,
> I am trying to delete data from a field in my table, I want to clear all t
he
> data from one field in my table (assuming the table is Students and the
> field is firstName)
> Thank you in advance.
> Lamy
>
>|||Dear Tibor and Gilberto,
Silly me.
Many thanks guys.
Regards
Lamine
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOru0t2dHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Do you want to set the column to NULL or an empty string? In any case, it
> is a simple UPDATE statements:
> UPDATE tblname
> SET colname = NULL
> or
> UPDATE tblname
> SET colname = ''
> Note that both above will modify all rows. Add a WHERE clause of you want
> to limit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Lamy" <lamine_d@.mpt-ltd.com> wrote in message
> news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
>

delete data from a field in a table

Hi,
I am trying to delete data from a field in my table, I want to clear all the
data from one field in my table (assuming the table is Students and the
field is firstName)
Thank you in advance.
LamyDo you want to set the column to NULL or an empty string? In any case, it is a simple UPDATE
statements:
UPDATE tblname
SET colname = NULL
or
UPDATE tblname
SET colname = ''
Note that both above will modify all rows. Add a WHERE clause of you want to limit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Lamy" <lamine_d@.mpt-ltd.com> wrote in message news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to delete data from a field in my table, I want to clear all the data from one field
> in my table (assuming the table is Students and the field is firstName)
> Thank you in advance.
> Lamy
>|||If the column you need to delete is nullable, you simply can:
UPDATE Students
SET firstName = NULL
If the column isn't nullable you could
UPDATE Students
SET firstName = expression | DEFAULT
if some default is defined you can use it; expression is any expression
resulting in an empty field. Remember that NULL is different from "empty" or
blank.
Gilberto
"Lamy" wrote:
> Hi,
> I am trying to delete data from a field in my table, I want to clear all the
> data from one field in my table (assuming the table is Students and the
> field is firstName)
> Thank you in advance.
> Lamy
>
>|||Dear Tibor and Gilberto,
Silly me.
Many thanks guys.
Regards
Lamine
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OOru0t2dHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Do you want to set the column to NULL or an empty string? In any case, it
> is a simple UPDATE statements:
> UPDATE tblname
> SET colname = NULL
> or
> UPDATE tblname
> SET colname = ''
> Note that both above will modify all rows. Add a WHERE clause of you want
> to limit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Lamy" <lamine_d@.mpt-ltd.com> wrote in message
> news:%23Y0CLe2dHHA.2088@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I am trying to delete data from a field in my table, I want to clear all
>> the data from one field in my table (assuming the table is Students and
>> the field is firstName)
>> Thank you in advance.
>> Lamy
>sql

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:

> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> doesn't
>
>

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....
Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....
|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:

> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> doesn't
>
>

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:
> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> > I have a table wherein previous entries were deleted but the fields
> doesn't
> > go away
> > ex.
> > tbl_name
> > 1 name 1
> > 2 (the entry is deleted but this is still showing a blank space)
> > 3 (the entry is deleted but this is still showing a blank space)
> > 4 (the entry is deleted but this is still showing a blank space)
> > 5 name 2
> >
> > How do i delete the blank spaces in entries 2-4?
> > thanks....
>
>

Delete a Substring

Good morning,
I need to delete the first ten characters from a field, I don't know how to write this query to allow me to do so.update table
set COLUMN = LTRIM(NAME, SUBSTR(COLUMN, 0, 10));|||UPDATE conitemdescrip
SET descrip= LTRIM(descrip,SUBSTRING(descrip,0,10));

Msg 174, level 15 State 1
the function 'ltrim' requires 1 argument

Suggestions?|||You are using MS SQL Server 2000.

update table
set column = Right(column, Len(column) - 1)

- From Visual Basic|||No, I am using
SQL 6.5...sql

delete a field help?

I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?You don't have a primary key on that field with a unique constraint? (something like an identity column?)

If you don't have anything to uniquely identify the record, you will have to select one of the records into a temp table, delete the record, then insert the record back. There really isn't any other way to do it.|||UserId has an indentity set, but not in the table that it references to,it is not the primary key.

Could you explain a bit more what would I need to do, as I have only done regular delitions,where things go smooth.

I will attach a file with the error eventhough you probabaly know what it is

Thanks for help

I forgot to tell you that the record I am trying to delete it from is in a view,don't konw if that makes any difference?|||Can you post the table structure of the table you're trying to delete from and the table that's not letting you delete?|||ok,viewSurveySubmission is where the double record is and RelocateeServices will not let me delete it.|||You need to find where the problem is in the base tables and delete the record from there. The view will try to delete from both tables, which it cannot do because another record is still using one of the records from a table you are trying to delete from. Make sense?

In this case find the duplicate in the base table and delete that record.|||Thanks to all willing to help,but I have actually been able to do it myself.

Thanks

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
Don
Yes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don
|||Thank you very much
"Dan Guzman" wrote:

> Yes, there is a SET NULL option when the foreign key constraint is created.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
>
>

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
DonYes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don|||Thank you very much
"Dan Guzman" wrote:

> Yes, there is a SET NULL option when the foreign key constraint is created
.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
>
>

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
DonYes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don|||Thank you very much
"Dan Guzman" wrote:
> Yes, there is a SET NULL option when the foreign key constraint is created.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> > Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> > a
> > referenced field on delete of a record from the parent table?
> >
> > Any help is appreciated.
> >
> > Don
>
>

DEFINITIVE ANSWER PLEASE -- can you UPDATE ntext datatype field??

Hi, I've read conflicting articles on updating an ntext field in a
column.

My ntext field will exceed 8,000 characters (typically twice that size
-- but just a text string).

One article (I think from MicroSoft) said you could NOT use ntext in
an UPDATE statement, but I've seen examples from other people using
it...but don't know if it's related to the size/characters issue.

Is this true or not?

Thanks very much...KathyKathyB (KathyBurke40@.attbi.com) writes:
> One article (I think from MicroSoft) said you could NOT use ntext in
> an UPDATE statement, but I've seen examples from other people using
> it...but don't know if it's related to the size/characters issue.

Yes, you can update an ntext column directly in an UPDATE statement.
I dont think there is a limitation, but it may be unpractiable if
you have a string which is million characters long.

There is also UPDATETEXT which permits you change parts of an ntext
column, but this function is certainly more complex to use, so as long
as you can do it with plain UPDATE stay with it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, that's what I wanted to hear!

I don't need to save a million, just up to 50,000!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

define a default value ?

hallo

i am on the very beginning with sql.s,

how can i define a default value for the table field like its possible in access,

for example: the if the user not gonna enter a val so there is a default val predefined in the table field,

for example i would like to predefine the date now on the smalldate field....

thanks

maybe something like:

Code Snippet

create table dbo.yourTable

( anInteger int default (0)

)

Give a look to books online.

|||

so if i would like to predefine the date now on the smalldate field its will look like this

table dbo.myTable

( anInteger smalldate default (0)=new

)

|||

No, that would be more like:

createtable dbo.myTable

( anInteger smalldate default ( getdate() )

)

|||thanks

Deferrend Name Resolution for a field in a Stored Procedure

We are doing an upgrade in about a month and changing the account structure
in one of our tables. I am trying to write a routine that will check to see
if we are using the old format or the new format. So I wrote the following
stored procedure:
CREATE PROCEDURE [dbo].[sp_Account_Info] AS
if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
begin
select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
--Do more stuff
end
else
begin
select ACTNUMBR_4 from Account_Table
--Do more stuff
end
In the old format the columns stop at ACTUNUMBR_4, but in the new table
structure(Which has not been implmented yet) we will be adding ACTNUMBR_5
and ACTNUMBR_6. The problem I am having is SQLServer will not let me save
this stored procedure because it says I have an invalid column name. Is
there a way to save the stored procedure even though the new columns do not
yet exist?You could use dynamic SQL (www.sommarskog.se), but perhaps you should consid
er a stable data model
instead, which doesn't require you to add columns over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miles C" <mcousens@.clearwater.ca> wrote in message news:eZmKRAHlGHA.408@.TK2MSFTNGP03.phx.g
bl...
> We are doing an upgrade in about a month and changing the account structur
e in one of our tables.
> I am trying to write a routine that will check to see if we are using the
old format or the new
> format. So I wrote the following stored procedure:
> CREATE PROCEDURE [dbo].[sp_Account_Info] AS
> if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
> begin
> select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
> --Do more stuff
> end
> else
> begin
> select ACTNUMBR_4 from Account_Table
> --Do more stuff
> end
> In the old format the columns stop at ACTUNUMBR_4, but in the new table st
ructure(Which has not
> been implmented yet) we will be adding ACTNUMBR_5 and ACTNUMBR_6. The pro
blem I am having is
> SQLServer will not let me save this stored procedure because it says I hav
e an invalid column
> name. Is there a way to save the stored procedure even though the new col
umns do not yet exist?
>

Default Values in SQL Server

I am designing a new Database using SQL Server 2000 Enterprise Manager. I have a field called Phone, Fax and Zip.

Under Enterprise Manager I am trying to set up default values for these fields

Phone, Fax = 000-000-000
Zip = 00000

However Sql Server truncates it to 0.

How do I default value as shown about in SQL server?You must have the column set to int?

Change it to varchar(30) and you should be fine.

ScAndal|||I have Phone, Fax nvarchar(12) and Zip as nvarchar(5)

I don't know why this doesn't work !!|||Ok.. I got the solution

For default value instead of putting 000-000-000 with nvarchar(12) I put '000-000-0000' and it works fine.

1) How do I use 'Formula' field in Enterprise Manager works?
2) How do I work with various Timezone issue ?|||love,
How did you determine it is storing as 0? Did you select the data from query analyzer and it shows 0?

ScAndal|||I right clicked on table and selected 'Return All Rows' where I saw 0 instead of 00000

Another way was, once I put value in Default field as 00000 and save table, return to the same column it will indiate data as (0) for zip and (0-0-0) for phone and fax.

This and your data type indicated me that there is something wrong with my default value.

default values for database fields yes or no??

Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added.

I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls.

What is the right way??

Pros and cons.......

Newbie

Like a table that holds car information. If you have a field that holds the number of wheels, I would make that a default of 4.

Sort of like, if you don't mention how many wheels the car has, I'm going to assume 4. Now if the car has more/less than 4, you can tell me about it, and I'll remember.

It's not really what is "right" and what is "wrong". You can also say there is no default, and you don't tell me the number of wheels of every car, I'm not going to accept it. It forces you to make a choice for every record. Or you can have each record allow nulls, in which case if you don't mention it, we'll still accept it, but we won't make any assumption on the number of wheels.

My rule of thumb is, if it's necessary field, then no default value, and does not accept nulls.

If you can assume a value if one isn't specified (It rarely isn't a particular value), then I'll make it does not accept nulls, with a default value.

If I really don't care about the field at all, and it's a fluff field that I won't use, then I'll accept nulls and no default.

default values

I need to set the default value of a field in my table to 'Regular' if nothing is inserted to that field. Is it not as easy as putting ('Regular') in Default Value?

In the design properties for that row there should be a field to the effect of Default Value. Enter Regular there.

HTH,
Ryan

|||

I did that. Maybe something else is happening here tell me what you think. I have a dropdoen that is either enabled or not based on if you can select Regular or Decaf. I use a stored procedure for this and this: cmdAddItemToCartItems.Parameters.Add("@.regDec", ddlReg.SelectedValue)
If it's not enabled is the stored procedure inserting nothing and overriding the default value? I tried this in my code (with and without the default value set) too:
If Me.ddlReg.Enabled = False Then
cmdAddItemToCartItems.Parameters.Add("@.regDec", "Regular").ToString()
Else
cmdAddItemToCartItems.Parameters.Add("@.regDec", ddlReg.SelectedValue)
End If
It seemed like it worked and then didn't work... maybe I'm just crazy, I don't know. Any suggestions?

|||

I would do it this way. Don't provide the DropDownList if they can't select from it. Instead, use a Label control and display that it is Regular. Then, use that to determine what you are going to insert into the database for that row.

i.e.

If lblReadOnly.Visible Then
cmdAddItemToCartItems.Parameters.Add("@.regDec", lblReadOnly.Text)
Else
cmdAddItemToCartItems.Parameters.Add("@.regDec", ddlReg.SelectedItem.Value)
End If

HTH,
Ryan

|||Beautiful! Thank You!

Default values

I have a table call Master and a table called Rental. They both have a
status field that are named differently. What i want to happen is for the
Status in the Rental table to default to whatever the status is in the
Master table whenever it is imported to the Rental table. ANyone have any
suggestions for this. I dont even know where to start. Thanks>whenever it is imported to the Rental table
What do you mean by imported? What process is running?
One possible answer to your question, if my guess work happens to on
target:
INSERT Rental (A, B, C)
SELECT A, B, X
FROM Master
Roy
On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:
>I have a table call Master and a table called Rental. They both have a
>status field that are named differently. What i want to happen is for the
>Status in the Rental table to default to whatever the status is in the
>Master table whenever it is imported to the Rental table. ANyone have any
>suggestions for this. I dont even know where to start. Thanks
>|||It already holds the specific jobnumber in master but whenver a user enters
that specific job into the rental table via infopath I want it to
automatically take on the status from the master table. But allow it to be
changed in the rental table later. So basically just the first default and
then after that i want to be able to change it manually and it leave it like
that
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
> >whenever it is imported to the Rental table
> What do you mean by imported? What process is running?
> One possible answer to your question, if my guess work happens to on
> target:
> INSERT Rental (A, B, C)
> SELECT A, B, X
> FROM Master
> Roy
> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have any
>>suggestions for this. I dont even know where to start. Thanks|||I pretty much need taken thru this all the way, like where do I insert that
code?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
> >whenever it is imported to the Rental table
> What do you mean by imported? What process is running?
> One possible answer to your question, if my guess work happens to on
> target:
> INSERT Rental (A, B, C)
> SELECT A, B, X
> FROM Master
> Roy
> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have any
>>suggestions for this. I dont even know where to start. Thanks|||Two approaches. One is to write a subquery into the INSERT into the
Rental table. It might vaguely resemble something like:
INSERT Rental (jobnumber, B, RentalStatus)
SELECT 12345, B,
(select MasterStatus from Master
where master.jobnumber = '12345')
The other approach is an INSERT trigger on the Rental table, which
would include something like:
UPDATE Rental
SET RentalStatus = (select MasterStatus from Master
where master.jobnumber = Rental.jobnumber)
WHERE Rental.jobnumber IN
(select jobnumber from INSERTED)
Roy Harvey
Beacon Falls, CT
On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:
>It already holds the specific jobnumber in master but whenver a user enters
>that specific job into the rental table via infopath I want it to
>automatically take on the status from the master table. But allow it to be
>changed in the rental table later. So basically just the first default and
>then after that i want to be able to change it manually and it leave it like
>that
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have any
>>suggestions for this. I dont even know where to start. Thanks
>|||I f I did a trigger would it allow me to change the value whenever I wanted
and allow me to keep the change?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
> Two approaches. One is to write a subquery into the INSERT into the
> Rental table. It might vaguely resemble something like:
> INSERT Rental (jobnumber, B, RentalStatus)
> SELECT 12345, B,
> (select MasterStatus from Master
> where master.jobnumber = '12345')
> The other approach is an INSERT trigger on the Rental table, which
> would include something like:
> UPDATE Rental
> SET RentalStatus => (select MasterStatus from Master
> where master.jobnumber = Rental.jobnumber)
> WHERE Rental.jobnumber IN
> (select jobnumber from INSERTED)
> Roy Harvey
> Beacon Falls, CT
> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>|||What is wront with this trigger?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Rental_Statustrig]
ON [dbo].[Rental_Info]
Update Rental_Info
Set Rental_Status =
(select job_status
from Master
where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
where Rental_Info.wwpjobnumber IN
(select wwpjobnumber
from Master)
END
GO
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>I f I did a trigger would it allow me to change the value whenever I wanted
>and allow me to keep the change?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>
>|||If it is an INSERT trigger it will only execute when rows are
inserted. It would have to be an UPDATE trigger to mess up your
changes.
Roy
On Fri, 4 Aug 2006 10:24:12 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:
>I f I did a trigger would it allow me to change the value whenever I wanted
>and allow me to keep the change?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>
>|||Please do not make duplicate posts. You only waited one minute for a reply
before you decided to start a new thread with the same question.
The word END should not be included.
--
HTH
Kalen Delaney, SQL Server MVP
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:etGT709tGHA.2172@.TK2MSFTNGP05.phx.gbl...
> What is wront with this trigger?
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TRIGGER [Rental_Statustrig]
> ON [dbo].[Rental_Info]
>
> Update Rental_Info
> Set Rental_Status => (select job_status
> from Master
> where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
> where Rental_Info.wwpjobnumber IN
> (select wwpjobnumber
> from Master)
> END
> GO
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>>I f I did a trigger would it allow me to change the value whenever I
>>wanted and allow me to keep the change?
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>
>>
>|||I give up. Which thread are we working with here?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORxlP89tGHA.4852@.TK2MSFTNGP02.phx.gbl...
> Please do not make duplicate posts. You only waited one minute for a reply
> before you decided to start a new thread with the same question.
> The word END should not be included.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:etGT709tGHA.2172@.TK2MSFTNGP05.phx.gbl...
>> What is wront with this trigger?
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TRIGGER [Rental_Statustrig]
>> ON [dbo].[Rental_Info]
>>
>> Update Rental_Info
>> Set Rental_Status =>> (select job_status
>> from Master
>> where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
>> where Rental_Info.wwpjobnumber IN
>> (select wwpjobnumber
>> from Master)
>> END
>> GO
>> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
>> news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>>I f I did a trigger would it allow me to change the value whenever I
>>wanted and allow me to keep the change?
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it
>>to be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>>I have a table call Master and a table called Rental. They both have
>>>a
>>>status field that are named differently. What i want to happen is
>>>for the
>>>Status in the Rental table to default to whatever the status is in
>>>the
>>>Master table whenever it is imported to the Rental table. ANyone
>>>have any
>>>suggestions for this. I dont even know where to start. Thanks
>>>
>>
>>
>|||Thanks you all, I got it working using everyone of your expertise. Many
thanks.
Ben
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:74r6d251koh0rgerp3m6tt64rcm084mf2e@.4ax.com...
> If it is an INSERT trigger it will only execute when rows are
> inserted. It would have to be an UPDATE trigger to mess up your
> changes.
> Roy
> On Fri, 4 Aug 2006 10:24:12 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>>I f I did a trigger would it allow me to change the value whenever I
>>wanted
>>and allow me to keep the change?
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.4ax.com...
>> Two approaches. One is to write a subquery into the INSERT into the
>> Rental table. It might vaguely resemble something like:
>> INSERT Rental (jobnumber, B, RentalStatus)
>> SELECT 12345, B,
>> (select MasterStatus from Master
>> where master.jobnumber = '12345')
>> The other approach is an INSERT trigger on the Rental table, which
>> would include something like:
>> UPDATE Rental
>> SET RentalStatus =>> (select MasterStatus from Master
>> where master.jobnumber = Rental.jobnumber)
>> WHERE Rental.jobnumber IN
>> (select jobnumber from INSERTED)
>> Roy Harvey
>> Beacon Falls, CT
>> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>It already holds the specific jobnumber in master but whenver a user
>>enters
>>that specific job into the rental table via infopath I want it to
>>automatically take on the status from the master table. But allow it to
>>be
>>changed in the rental table later. So basically just the first default
>>and
>>then after that i want to be able to change it manually and it leave it
>>like
>>that
>>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
>>news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.4ax.com...
>> >whenever it is imported to the Rental table
>> What do you mean by imported? What process is running?
>> One possible answer to your question, if my guess work happens to on
>> target:
>> INSERT Rental (A, B, C)
>> SELECT A, B, X
>> FROM Master
>> Roy
>> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
>> <ben.watts@.aaronnickellhomes.com> wrote:
>>I have a table call Master and a table called Rental. They both have a
>>status field that are named differently. What i want to happen is for
>>the
>>Status in the Rental table to default to whatever the status is in the
>>Master table whenever it is imported to the Rental table. ANyone have
>>any
>>suggestions for this. I dont even know where to start. Thanks
>>

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
JeffInstead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default values

How can I assign a field in a table a default date. I know that I can use
getdate(), but I don't want to store the time, only the date. Can this be
done in the table definition?
Thanks
Jeff
Instead of...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT GETDATE())
...use...
CREATE TABLE foo(dt SMALLDATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112))
Note that a time portion will still be stored, but it will be midnight...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>
|||Try making your default something like
convert(datetime, convert(varchar(20), getdate(), 1))
Jeff Duncan
MCDBA, MCSE+I
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>
|||This is covered in http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message news:e$B9q6rMEHA.620@.TK2MSFTNGP10.phx.gbl...
> How can I assign a field in a table a default date. I know that I can use
> getdate(), but I don't want to store the time, only the date. Can this be
> done in the table definition?
> Thanks
> Jeff
>

Default values

I have a table call Master and a table called Rental. They both have a
status field that are named differently. What i want to happen is for the
Status in the Rental table to default to whatever the status is in the
Master table whenever it is imported to the Rental table. ANyone have any
suggestions for this. I dont even know where to start. Thanks>whenever it is imported to the Rental table
What do you mean by imported? What process is running?
One possible answer to your question, if my guess work happens to on
target:
INSERT Rental (A, B, C)
SELECT A, B, X
FROM Master
Roy
On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:

>I have a table call Master and a table called Rental. They both have a
>status field that are named differently. What i want to happen is for the
>Status in the Rental table to default to whatever the status is in the
>Master table whenever it is imported to the Rental table. ANyone have any
>suggestions for this. I dont even know where to start. Thanks
>|||It already holds the specific jobnumber in master but whenver a user enters
that specific job into the rental table via infopath I want it to
automatically take on the status from the master table. But allow it to be
changed in the rental table later. So basically just the first default and
then after that i want to be able to change it manually and it leave it like
that
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.
4ax.com...[vbcol=seagreen]
> What do you mean by imported? What process is running?
> One possible answer to your question, if my guess work happens to on
> target:
> INSERT Rental (A, B, C)
> SELECT A, B, X
> FROM Master
> Roy
> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>|||I pretty much need taken thru this all the way, like where do I insert that
code?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.
4ax.com...[vbcol=seagreen]
> What do you mean by imported? What process is running?
> One possible answer to your question, if my guess work happens to on
> target:
> INSERT Rental (A, B, C)
> SELECT A, B, X
> FROM Master
> Roy
> On Fri, 4 Aug 2006 09:47:26 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>|||Two approaches. One is to write a subquery into the INSERT into the
Rental table. It might vaguely resemble something like:
INSERT Rental (jobnumber, B, RentalStatus)
SELECT 12345, B,
(select MasterStatus from Master
where master.jobnumber = '12345')
The other approach is an INSERT trigger on the Rental table, which
would include something like:
UPDATE Rental
SET RentalStatus =
(select MasterStatus from Master
where master.jobnumber = Rental.jobnumber)
WHERE Rental.jobnumber IN
(select jobnumber from INSERTED)
Roy Harvey
Beacon Falls, CT
On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:

>It already holds the specific jobnumber in master but whenver a user enters
>that specific job into the rental table via infopath I want it to
>automatically take on the status from the master table. But allow it to be
>changed in the rental table later. So basically just the first default and
>then after that i want to be able to change it manually and it leave it lik
e
>that
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:i1o6d2lk9en6v1rb7fju5ad1tidn3a4mvm@.
4ax.com...
>|||I f I did a trigger would it allow me to change the value whenever I wanted
and allow me to keep the change?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.
4ax.com...[vbcol=seagreen]
> Two approaches. One is to write a subquery into the INSERT into the
> Rental table. It might vaguely resemble something like:
> INSERT Rental (jobnumber, B, RentalStatus)
> SELECT 12345, B,
> (select MasterStatus from Master
> where master.jobnumber = '12345')
> The other approach is an INSERT trigger on the Rental table, which
> would include something like:
> UPDATE Rental
> SET RentalStatus =
> (select MasterStatus from Master
> where master.jobnumber = Rental.jobnumber)
> WHERE Rental.jobnumber IN
> (select jobnumber from INSERTED)
> Roy Harvey
> Beacon Falls, CT
> On Fri, 4 Aug 2006 10:07:43 -0500, "Ben Watts"
> <ben.watts@.aaronnickellhomes.com> wrote:
>|||What is wront with this trigger?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Rental_Statustrig]
ON [dbo].[Rental_Info]
Update Rental_Info
Set Rental_Status =
(select job_status
from Master
where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
where Rental_Info.wwpjobnumber IN
(select wwpjobnumber
from Master)
END
GO
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>I f I did a trigger would it allow me to change the value whenever I wanted
>and allow me to keep the change?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.
4ax.com...
>|||If it is an INSERT trigger it will only execute when rows are
inserted. It would have to be an UPDATE trigger to mess up your
changes.
Roy
On Fri, 4 Aug 2006 10:24:12 -0500, "Ben Watts"
<ben.watts@.aaronnickellhomes.com> wrote:

>I f I did a trigger would it allow me to change the value whenever I wanted
>and allow me to keep the change?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:cso6d250qdsr3crt82hpgut3a0h42obvo4@.
4ax.com...
>|||Please do not make duplicate posts. You only waited one minute for a reply
before you decided to start a new thread with the same question.
The word END should not be included.
HTH
Kalen Delaney, SQL Server MVP
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:etGT709tGHA.2172@.TK2MSFTNGP05.phx.gbl...
> What is wront with this trigger?
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TRIGGER [Rental_Statustrig]
> ON [dbo].[Rental_Info]
>
> Update Rental_Info
> Set Rental_Status =
> (select job_status
> from Master
> where Master.wwpjobnumber = Rental_Status.wwpjobnumber)
> where Rental_Info.wwpjobnumber IN
> (select wwpjobnumber
> from Master)
> END
> GO
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:uRgS3n9tGHA.3964@.TK2MSFTNGP04.phx.gbl...
>|||I give up. Which thread are we working with here?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORxlP89tGHA.4852@.TK2MSFTNGP02.phx.gbl...
> Please do not make duplicate posts. You only waited one minute for a reply
> before you decided to start a new thread with the same question.
> The word END should not be included.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:etGT709tGHA.2172@.TK2MSFTNGP05.phx.gbl...
>