How to insert time in MS SQL DB

hi..

i want to store value of time in the database i.e. the 'time in' and 'time out' value for a particular entry of an employee. i have take datetime as a datatype in MS SQL 2000 database and my language is vb.net. How can i store time value in my database?

There is no datatype strictly for the time. I suggest storing the entire date and time value. What if your employee clocks in at 7:00 PM and clocks out at 3:00 AM?|||You told me write to store time in datetime data type along with date. But in what format should I enter the values if I want to store the values in 24 hrs. time format?|||Well, I didn't tell you to store the data in a datetime data type, but I did suggest it. You can see from thedatetime and smalldatetime documentation that SQL Server stores the data in two 4-byte integers, the first 4 bytes store the offset from the base date (January 1, 1900). The other 4 bytes store the number of milliseconds after midnight.

You might use something like this to set a date/time as a parameter value:

myCommand.Parameters.Add("@.DateTimeIn", SqlDbType.DateTime).Value = DateTime.Parse("2006-03-04 23:01:00")

|||

Datetime values by their nature do not have a "format". It's when you convert the datetime to a string that the string has a "format". (format is quoted because, yes, of course datetime has a format, as tmorton pointed out, but it's not what most people mean)

Just to point this out further, in tmorton's MyCommand.Parameters.Add statement above, you are giving the .value a datetime object. Datetime.Parse("some string"), takes the string in a format that datetime can covert to a valid datetime object, based on your current culture's standard date and time formats. The format tmorton chose (YYYY-MM-DD HH:MM:DD.SSS) is the one least likely to give any problems because it is universally available in all cultures, unlike say MM/DD/YYYY which is available in en-US, or (guessing) DD/MM/YYYY in en-GB, or DD.MM.YYYY in fr-FR (Sorry if these aren't the correct formats for England and/or France).

|||

Hi..

I got my problem soluation but a small problem still remailns.

I am displaying my records in datagrid with code as...

<asp:Label ID="timeinlbl" Width = 30 Runat = server text='<%#DataBinder.Eval(Container,"DataItem.timein","{0:hh:mm}")%>'>

It shows me time in '08:30' format. I want to also display am/pm format. What should I add to display the format 'am/pm'?

|||

geek.net wrote:

What should I add to display the format 'am/pm'?

Starting from here:BoundColumn.DataFormatString Property, I followed the link to:Formatting Overview. From there I followed the link to:Date and Time Format Strings, and then followed the final link to:Custom DateTime Format Strings. This page should help you discover the exact format string you need.

How to insert the value from the text box (ASP.NET 2.0) to the microsoft sql server 2000 d

Hello Friends,

I have a problem with ASP.net with dynamic data transfer from asp page to microsoft sql server 2000. For example , I have asp web page with one text field and a buttion.When I click the buttion, the value entered in the text field should be transfered from the text field to database.

Kindly See the following section c# code ....

SqlConnection objconnect = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = objconnect;

cmd.CommandText = " select * from Table_Age where Age = @.Age";

cmd.CommandType = CommandType.Text;

SqlParameter parameter = new SqlParameter();

parameter.ParameterName = "@.Age";

parameter.SqlDbType = SqlDbType.VarChar;

parameter.Direction = ParameterDirection.Output;

parameter.Value = TextBox1.Text;

objconnect.Open();

// Add the parameter to the Parameters collection.

cmd.Parameters.Add(parameter);

SqlDataReader reader = cmd.ExecuteReader();

********************this section c# code is entered under the button control************************************

connection string is mentioned in the web.config file.

In the above c# code , I have a text field , where I entered the age , the value entered in the text field should be sent to database. I have used SqlParameter for selecting the type of data should be sent from ASP.NET 2.0 to the microsoft sql server 2000. I am facing a problem where I am unable to sent the random datas from a text field to the database server.I have a created a database file in the microsoft server 2000.

after the excution of the fIeld value is assinged to NULL in the main database i.e microsoft the sql server 2000.

Can anyone help with this issue.

My mail id phijop@.hotmail.com

- PHIJO MATHEW PHILIP.

Hi

Remove :

parameter.Direction = ParameterDirection.Output;

since your direction should be input.

How to insert the date

Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +

> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT

How to insert right justifies leading zeros

I have a business rule in my environment where I need to insert right justified leading zeros in the column. For example if the value to be inserted is 12 than it should be inserted as 0000012. How can I do this

Chintan.

Here it is:

SELECT RIGHT('000000' + CAST(12 as SYSNAME), 7)

Thanks,
Zuomin
|||

The "leading zeroes" issue might be more of a display issue than a storage issue. Is your column a character field, integer field, or what exactly?

If your field is character (or varchar) then you can use concatenation and the RIGHT function to pre-pend your string with zero characters; however, if your field is integer understand that it is stored as a binary encoded integer and the zeroes are understood.

You can do something like zuomin has suggested for display purposes. Also, if you MUST carry this as a zero-filled key, you can store it in the table as a computed column -- again as a CHAR field similar to zuomin's suggestion.

|||

the rule applies to about 9600 rows so....what can be the best solution

Chintan

|||

as of now the columns are in Varchar, but this whole table is getting converted to Flatfile of fixed width and than it is submitted to client. In this case wot shud I allocate the column as Varchar or Integer taking flatfile into consideration.

|||

OK, but what is the datatype of your column?

|||

Varchar.

|||

I would suggest using something like zuomin's RIGHT solution.

|||

A bit of editorial: I have never really liked doing this. This makes it possible to have many different '1' records. Now it is possible to have record '1', '01', '001', etc as valid primary keys to this table. And I have seen this problem manifest several times. For me, I would rather store the integer field and have the application do the formatting.

|||

the problem here is 1. there is no primary keys

2. there is no application which will insert leading zeros

3. it is goin as a simple text files

so now if i use

SELECT RIGHT('000000' + CAST(trtyc as SYSNAME), 7)

this is just a select statement, but i need to insert leading zeros into the db so tht whenever the data goes in to flat files instead of showing 12 it will show 0000012

it might some kinda update statment

Chintan

|||

UPDATE yourTable SET yourVarCharColumn= RIGHT('000000'+yourVarCharColumn,7)

Without WHERE condtion, you will update all your records.

How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to insert NULL when using bcp_moretext?

How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order must al
so be bound with a NULL vardata -- i.e. once you use bcp_moretext for a 'tex
t'/'image', it's really all *following* that must be also sent with bcp_more
text. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with length
0, for that column. Setting length to SQL_NULL_DATA will cause an error late
r, whether you try to call bcp_moretext with length SQL_NULL_DATA, with leng
th 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since the d
ocumentation doesn't cover this at all.
Thanks,
Jim FloodCan I expect some sort of response from Microsoft in this group within two d
ays, as part of the benefit of my MSDN subscription?
-- Jim Flood wrote: --
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order mu
st also be bound with a NULL vardata -- i.e. once you use bcp_moretext for a
'text'/'image', it's really all *following* that must be also sent with bcp
_moretext. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with le
ngth 0, for that column. Setting length to SQL_NULL_DATA will cause an error
later, whether you try to call bcp_moretext with length SQL_NULL_DATA, with
length 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since
the documentation doesn't cover this at all.
Thanks,
Jim Flood|||I'm checking with our BCP guy.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <bezspam@.bezspam.cz> wrote in message
news:C95C8F5A-BF58-48F3-A516-AC5980947BDE@.microsoft.com...
> Can I expect some sort of response from Microsoft in this group within two
days, as part of the benefit of my MSDN subscription?
> -- Jim Flood wrote: --
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5
DB-LIB BCP). Setting the param length to zero (bcp_bind or bcp_collen) for
7.0 BCP tells it that you are sending a zero-length param. You still need to
call bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Brannon,
Have you heard back about the BCP problem yet? Is there anything else I need
to do besides post to this group, to get the two-day response from Microsof
t? It's been well over two days.
Thanks,
Jim Flood|||Hi Jim,
I've faced the same problem. I can't insert NULLs using bcp_moretext.
Have you found a solution?
Thanks in advance,
Alberto.
"Jim Flood" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:0BB0853D-430D-4299-88D2-7343C6C024A8@.microsoft.com...
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB
BCP). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP
tells it that you are sending a zero-length param. You still need to call
bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Alberto,
If all of your NULLs are lined up contiguous to the left in the set of colum
ns for a given row, then just avoid bcp_moretext for those columns -- althou
gh, according to the documentation, this isn't supposed to work. But, once y
ou've set bcp_moretext for
a column, going left-to-right, then all other columns to the right will have
to be bcp_moretext'ed as well, and then you can't set any of them to NULL.
So, I have no solution.
Can I please get some kind of response from Microsoft on this problem? I bel
ieve my MSDN subscription should guarantee a two-day response time, and it h
as been *far* more than two days.
Jim Flood

how to insert null value into database?

hi guys. i'm using vb.net with vs 2003 and MSSQL Managment Studio Express as my database server.
i want to insert null value into database, so i use System.DBNull.Value
but it doesn't show NULL, but a empty field. I tried System.DBNull.Value.ToString, it gives me same result.
what can i do to have NULL in that field?
thanks for any advise, i would appreciate it very much.

Hello,

If you use an INSERT statement and don't mention the columns you want null values in then they will contain null values for the inserted row.

--Bonnie

|||Could you please show us your query ? I am not sure, from which application you want to insert the NULL value, either through code (then we need your code to investigate your problem) or through the GUI (then you will simply have to press STRG+0 staying inside the actual data cell)

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to INSERT INTO [Table] ([Field]) VALUES(I Have a in value)

Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')

please teach me how to

xxxINSERT INTO [Table] ([Field]) VALUES('I Have a '' in value')

instead of one single quote, u need to add one more.|||Use Parameterized Queries...you woudnt have to worry about escape characters besides saving your db from sqlinjection attacks.

hth

How to INSERT DBNull.Values ? ...

Hello,

If I have a database table with column that can accept null values and I want to insert a null value into that column, what is the correct syntax?

db_cmd = New SqlCommand( "sp_stored_procedure", db_connection )
db_cmd.CommandType = CommandType.StoredProcedure
db_cmd.Parameters.Add( "@.col_to_set_to_null", ? )

ie: what expression can be used for "?"

Ideally, I want to check if a string value is empty and if so insert a null, but I have yet to see any examples, ie:

if ( str.empty ) then
insert DBNull.value into column
else
insert str into column
end if

Sincerely,

Brent D.db_cmd.Parameters.Add( "@.col_to_set_to_null", System.DBNull.Value )

How to insert a zero length string into a field using SqlDataSource?

I have a few columns in table with default value defined as zero length string (''). I want to insert record from DetailsView which uses SqlDataSource as DataSource. In the ItemInserting event, if the data is not valid, I want to use zero length string for the column. But I always get Null instead of zero length string. The code in ItemInserting event looks like this:

If objddl.SelectedIndex > 0 Then
e.Values("myFld") = objddl.SelectedItem.Value
Else
e.Values("myFld") = ""
End If

The line: e.Values("myFld") = "" put Null in the column.

How can I set a column as zero length string using the SqlDataSource?

Any help is appreciated.

Thanks.

Try:

e.Values("myFld") =string.Empty

|||Check the advanced properties of the parameter in your upate statement. You probably have the property (Sorry, the exact name eludes me) "ChangeEmptyStringToNull" set to true (true is the default).|||Thank you very much. "ConvertEmptyStringToNull" worked perfectly.

how to insert a NULL in a table

hello,
in a table i have a field of type bit which allows NULL.
if a new record is created and the field is not set a NULL value is created.
Now my question: if the field was set to the value 0 or 1 how can i reset
that field manualy to NULL. Can i use some key STRG + ...?
thanksUsing the Enterprise manager you can use STRG + 0 to insert a NULL,
using an update statement it would be:
UPDATE Sometable SET SomeColumn = NULL Where is = SomeIdValue
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||thanks for your help
"Jens" wrote:

> Using the Enterprise manager you can use STRG + 0 to insert a NULL,
> using an update statement it would be:
> UPDATE Sometable SET SomeColumn = NULL Where is = SomeIdValue
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Through the GUI its usually CTRL 0 together.
With command line its
UPDATE <table>
SET yourcol = NULL
WHERE ...
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B7AB5BA4-8AAB-425C-9668-1C435E96A613@.microsoft.com...
> hello,
> in a table i have a field of type bit which allows NULL.
> if a new record is created and the field is not set a NULL value is
> created.
> Now my question: if the field was set to the value 0 or 1 how can i reset
> that field manualy to NULL. Can i use some key STRG + ...?
> thanks
>|||thanks Toni
"Tony Rogerson" wrote:

> Through the GUI its usually CTRL 0 together.
> With command line its
> UPDATE <table>
> SET yourcol = NULL
> WHERE ...
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B7AB5BA4-8AAB-425C-9668-1C435E96A613@.microsoft.com...
>
>

How to insert a node, with value queried from another node

We're trying to move some of our data between nodes in an XML column.
I basically want to migrate:
FROM
<OldNodeParent>
<OldNode>1234</OldNode>
</OldNodeParent>
TO
<OldNodeParent>
<OldNode>1234</OldNode>
</OldNodeParent>
<NewNode>1234</NewNode>
I was trying something like (which doesn't work):
UPDATE MyTable
SET XmlColumn.modify('
insert <NewNode>(/OldNodeParent/OldNode)[1]</NewNode> as last
into (/)[1]
')
WHERE XmlColumn.exist('/OldNodeParent/OldNode') = 1
AND XmlColumn.exist('/NewNode') = 0
Any ideas on how this could be achieved?
Thanks,
LubdhaTry this
UPDATE MyTable
SET XmlColumn.modify('
insert
if (count(/NewNode) = 0)
then element NewNode {(/OldNodeParent/OldNode/text())[1]}
else ()
as last into (/)[1]
')|||More correctly
UPDATE MyTable
SET XmlColumn.modify('
insert
if ( (/OldNodeParent/OldNode/text()) and not (/NewNode) )
then element NewNode {(/OldNodeParent/OldNode/text())[1]}
else ()
as last into (/)[1]
')|||Wow! That worked like a charm, thanks a lot!

How to insert a datetime into sql sever

Hi everyone,

How do you insert this string value lable1.text="2006-08-30 09:00:00" into a data column like startdate (type: datetime) in sql sever?

How do I convert this string value before I insert it into sql sever?

Thank you very much.

a123.

Here is a quick sample:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn2000"].ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tbl_dt (dt) SELECT @.dt", conn);
cmd.Parameters.Add("@.dt", SqlDbType.DateTime).Value = DateTime.Parse(TextBox1.Text);
cmd.ExecuteNonQuery();

}

How to include a dataset field value in the header

Since I can not directly use a field value of a dataset in the header and
footer section of a report, I'm trying to store this field value in a
variable in the custom code.
Is there a way to do this?
Thanks...I would suggest using parameter with default value from query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:5F028ED8-E8BC-4795-A79E-3481CECC9EA6@.microsoft.com...
> Since I can not directly use a field value of a dataset in the header and
> footer section of a report, I'm trying to store this field value in a
> variable in the custom code.
> Is there a way to do this?
> Thanks...sql

How to improve query

Following is a query based on Northwind. I need to output the highest
order value for each date.
The following works. However I want to see if there is any way to
remove the nested query and do some joins.
select a.orderid, a.orderdate, sum(b.quantity * b.unitprice) as total
from orders a, [order details] b
where a.orderid = b.orderid
group by a.orderid, a.orderdate
having sum(b.quantity * b.unitprice) = (select max(total1)
from (select sum(quantity*unitprice) as total1, orders.orderdate as
date, orders.orderid
from [order details], orders
where [order details].orderid = orders.orderid
group by orders.orderdate, orders.orderid
) as C
where c.date = a.orderdate)
order by a.orderdate asc
Thanks,Here is another way (but I'm not sure if it's better, though; in fact,
the execution plan indicates that it's worse):
select x.orderid, x.orderdate, x.total
from (
select o1.orderid, o1.orderdate,
sum(d1.quantity * d1.unitprice) as total
from orders o1
inner join [order details] d1 on o1.orderid = d1.orderid
group by o1.orderid, o1.orderdate
) x inner join (
select orderdate, max(total) as max_total
from (
select o2.orderid, o2.orderdate,
sum(d2.quantity * d2.unitprice) as total
from orders o2
inner join [order details] d2 on o2.orderid = d2.orderid
group by o2.orderid, o2.orderdate
) y
group by orderdate
) z on x.orderdate=z.orderdate and x.total=z.max_total
order by x.orderdate
We can use a view (or a CTE in SQL Server 2005), instead of the x and y
derived tables, but this will not improve the performance (just the
readability).
Razvan

How to import XML file into SQL.

I want to import XML file as the '@.doc' value when I execute 'sp_xml_preparedocument', many thanks!Check out this article:

http://gridviewguy.com/ArticleDetails.aspx?articleID=76

How to implement a DropDownList w/o a Table

I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?

If you want a static set of items in the dropdown list you can define them in the property panel under the property Items you will find a collection, click the "..." and add items with value and text to appear in the dropdown list.

Best regards,
Per Salmi

|||

If you're trying to get the values from the database, you can do something like this:

Sub Page_Load(ByVal senderAs Object,ByVal eAs EventArgs)
If Not Page.IsPostBackThen GetData()End If
 End Sub
 Sub GetData()
Dim connStringAs String
 Dim conAs SqlConnection
Try connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
 con =New SqlConnection(connString)
Dim cmdAs SqlCommand =New SqlCommand()
Dim readerAs SqlDataReader
 cmd.Connection = con
 cmd.CommandText ="EXECUTE dbo.GetDepartmentCode" reader = cmd.ExecuteReaderIf reader.HasRowsThen
 While (reader.Read)
Dim newItemAs New ListItem
 newItem.Text = (reader.Item("DeptCode")).ToString
 newItem.Value = (reader.Item("DeptCode")).ToString
 deptCodeDDL.Items.Add(newItem)
End While
 End If reader.Close()Catch exAs Exception
 Response.Write(ex)
Finally con.Close() con.Dispose()End Try
 Catch exAs ApplicationException
 Response.Write("Could not load the database")
End Try
 End Sub

The ASP.Net side would like this:

<asp:DropDownList ID="deptCodeDDL" runat="server"></asp:DropDownList>

A few things to note though.

In the code I've posted, I use a stored procedure (sproc) to retrieve the data from the database. If you don't what sprocs are, you can read about themhere. Using sprocs is generally considered a better way of accessing the db.

Also, you will of course, have to replace DeptCode with the column name from where you will be getting the values.

Finally, I use a ConnectionString, which is being retrieved from the Web.config file. You should have that in you application folder.

If you don't, given below is a sample:

<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
--><configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString1" connectionString="Data Source=SERVERNAME\SQLSERVER;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 --><compilation debug="false"
</compilation>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 --><authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace.
 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 --></system.web>
</configuration>

You will have to replace SERVERNAME with you server name and DBNAME with your db name.

How to I retrieve the first value from a table in T-SQL?

I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.

Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?

Thanks in advance.

SELECT

top 1 yourCol

FROM

T1

ORDER

BYyourCol|||Well that returns a table with the first record BUT I wanted the thing returned as a value.

Say I declared a integer variable with

DECLARE @.x, I wanted the integer variable to be equal to the first record which would be an "age" column in the recordset

SELECT age FROM PEOPLE

I can't do @.x=SELECT age FROM people|||

CREATE

PROCEDURE [dbo].[sp__top1Name]

@.myAge

intOUTPUTAS

BEGIN

SET @.myAge=(SELECTtop 1 Agefrom T1ORDERBY Age)

END

In your code, you need to grab this value from stored procedure OUTPUT parameter which is the age you are looking for.