Insert system time in database

Dear Friends,

I want to insert in my database, in a table field the system time value on that moment.

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

I want to save in my database th system time...

Thanks!!

UPDATE TABLE1 SET MyFieldTime=getdate() WHERE MyFieldID=@.ID|||

it always good practice to use the UTC time instead System's local time. In future if you transfer the data from one server (time zone) to another you need not to applay any changes..

Another benifit on UI you can convert to any Local time from UTC with out any overhead...

use the following query..

Update Table1 Set MyFiedlTime = GetUTCDate() Where MyFieldId = @.ID

|||

You should do this using a DEFAULT on the column and then use DEFAULT keyword in the SET clause of whatever UPDATE statement that modifies the data. Using a separate SP is not really a good idea since you will decouple the actual update and the time when it was done. It is also costly to perform multiple updates on the same row when you can do it once.

You can make below changes:

alter table TABLE1 add default( CURRENT_TIMESTAMP ) for MyFieldTime

After that when you do the actual UPDATE then do:

UPDATE TABLE1

SET col1 = ...

, col2 = ...

, MyFieldTime = DEFAULT

WHERE ...

insert statement problem

Dear all
I have two db table

Product (ProdID, ProdName,....Condition1)
ProdSellRec (ProdDetailID, ProductDesc,......ProductID)

When i would like to create a insert statement into second table!

insert into ProdSellRec
( @.ProductDesc,......@.ProductID)
where @.ProductID = (select ProductID from Product where ......[some condition]..)

Howver , the db want me "Unable to parse query text."/

What should I change my sql statement?

Thanks!

Ad_dee

it might be easier if you show your whole sql statement. It is hard to tell from that
|||

Thanks , i will post the sql later

But I would lile know whether the method of nested query is corrected in my sql.

insert simultaneously in 2 tables

Dear All,

I am not an expert on T-SQL and I am trying out a small project to learn.

I

have encountered a problem, whereby I have 2 tables, 1 containing the

header (header_id, header_file, admin_menu_id and admin_submenu_id) and

then I have another table called header_details, where I am storing the

language details for this header, with the fields being

(header_details_id, fk_header_id, header_alt, header_caption and

fk_language_id)

Now I want to create a stored proc, first to

insert the header and then the header details. I also want that if the

header or header details already exist, I just do an update on these

tables.

I tried the following code but its not working:-

ALTER Procedure [dbo].[INSERT_Header]
(
@.admin_menu_idint,
@.admin_submenu_idint,
@.header_filevarchar(150),
@.header_altvarchar(150),
@.header_captionvarchar(200),
@.language_idint
@.outIDint OUTPUT
)
AS
BEGIN

-- First do a select on the header table to see if this header already exists

DECLARE @.count integer = 0

SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id

--if it exists, then update this header
IF @.count > 0
BEGIN
UPDATE [headers]
SET [header_file]= @.header_file

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@.header_file
,@.admin_menu_id
,@.admin_submenu_id)
-- Get the Inserted Header ID --
SET @.outID = SCOPE_IDENTITY()
END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @.count_details integer = 0

SELECT COUNT(*) as @.count_details
FROM header_detail
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
AND[fk_language_id] = @.language_id

--if it exists, then update this header
IF @.count_details > 0
BEGIN
UPDATE [header_detail]
SET[header_alt]= @.header_file
,[header_caption] = @.header_caption

WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id
ELSE
-- Insert header in header table --
BEGIN
INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@.@.out_ID
,@.header_alt
,@.header_caption
,@.language_id)
END

END

Can you help me out please?

Thanks a lot for your help and time

Johannhave you done a syntax check... your T-SQL is full of errors

1
DECLARE @.count integer = 0 is not valid, you cannot assign a value on a declaration

2
SELECT COUNT(*) as @.count
FROM headers
WHERE[fk_admin_menu_id] = @.admin_menu_id
AND[fk_admin_submenu_id] = @.admin_submenu_id
does not assign a value to @.count, to do do it you must write as follows:
SELECT @.count=COUNT(*)
FROM headers

.3

WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id
ELSE
is also invalid you must put END berfore else and maybe an AND on the where clause

Are you sure you've tried the statement in query analyzer or management studio ?

|||

Hi,

you should use If Exists construct instead o using Count(*) method as

If Exists (SELECT fk_admin_menu_id FROM headers
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
)

BEGIN

UPDATE [headers]
SET [header_file] = @.header_file
WHERE [fk_admin_menu_id] = @.admin_menu_id
[fk_admin_submenu_id] = @.admin_submenu_id

END
ELSE

-- Insert header in header table --
BEGIN
INSERT INTO [headers]
([header_file]
,[fk_admin_menu_id]
,[fk_admin_submenu_id])
VALUES
(@.header_file
,@.admin_menu_id
,@.admin_submenu_id)
-- Get the Inserted Header ID --
SET @.outID = SCOPE_IDENTITY()

END

-- Now do a select on the header_details table to see if this header with this language already exists
DECLARE @.count_details integer = 0

IF EXISTS (SELECT fk_admin_menu_id FROM header_detail
WHERE [fk_admin_menu_id] = @.admin_menu_id
AND [fk_admin_submenu_id] = @.admin_submenu_id
AND [fk_language_id] = @.language_id
)

BEGIN

UPDATE [header_detail]
SET [header_alt] = @.header_file
,[header_caption] = @.header_caption
WHERE [fk_header_id] = @.header_id
[fk_language_id] = @.language_id

END

ELSE
-- Insert header in header table --
BEGIN

INSERT INTO [header_detail]
([fk_header_id]
,[header_alt]
,[header_caption]
,[fk_language_id])
VALUES
(@.@.out_ID
,@.header_alt
,@.header_caption
,@.language_id)

END

insert query fails (if form fields left empty)

Dear All,

I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".

As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?

<script language="VB" runat="server" >

Sub Page_Load(Src As Object, e As EventArgs)


If Page.IsPostBack Then

Dim ConLath As SqlConnection
Dim comLath As SqlCommand
Dim insertcmd

conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password")
ConLath.Open()
insertcmd = "Insert into His_set values (@.t_d,@.s_p,@.p_s,@.v_oq,@.i_oq,@.v_qn,@.i_qn,@.v_qw,@.i_qw)"

comLath = New SqlCommand(insertcmd, ConLath)


comLath.Parameters.Add(New SqlParameter("@.t_d", SqlDbType.DateTime, 12))
comLath.Parameters("@.t_d").Value = trade_date.Text
comLath.Parameters.Add(New SqlParameter("@.s_p", SqlDbType.Decimal, 8))
comLath.Parameters("@.s_p").Value = sett_price.Text
comLath.Parameters.Add(New SqlParameter("@.p_s", SqlDbType.Decimal, 8))
comLath.Parameters("@.p_s").Value = post_close.Text
comLath.Parameters.Add(New SqlParameter("@.v_oq", SqlDbType.Int, 8))
comLath.Parameters("@.v_oq").Value = vol_oq.Text
comLath.Parameters.Add(New SqlParameter("@.i_oq", SqlDbType.Int, 8))
comLath.Parameters("@.i_oq").Value = oi_oq.Text
comLath.Parameters.Add(New SqlParameter("@.v_qn", SqlDbType.Int, 8))
comLath.Parameters("@.v_qn").Value = vol_qn.Text
comLath.Parameters.Add(New SqlParameter("@.v_qw", SqlDbType.Int, 8))
comLath.Parameters("@.v_qw").Value = vol_qw.Text
comLath.Parameters.Add(New SqlParameter("@.i_qn", SqlDbType.Int, 8))
comLath.Parameters("@.i_qn").Value = oi_qn.Text
comLath.Parameters.Add(New SqlParameter("@.i_qw", SqlDbType.Int, 8))
comLath.Parameters("@.i_qw").Value = oi_qw.Text

Try
comLath.ExecuteNonQuery()

Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try

comLath.Dispose()
ConLath.Close()




End If
End Sub

</script>

I'm not surprised if it fails when you leave the mandatory field empty. But I assume that's not what you meant, right?

The problem here relates to casting. Your empty text box returns an empty string. This would be fine for a varchar column, but if you try this with a column of type int, it will fail. You need to explicitly insert a null value in this case.

You could try something like this:

comLath.Parameters("@.v_oq").Value = (vol_oq.Text =="" ? DBNull.Value : vol_oq.Text);

|||

thanks for your prompt reply.

apparently the conditional operator ? works if u are using C#.

I am using the language vb, this implies i will have to use the if and then conditonal block for each, right?

|||

VB has the tertial operator IIF which is similar to ? operator, although it behaves somewhat differently (VB evaluates all parameters). 

comLath.Parameters("@.v_oq").Value = IIF(vol_oq.Text =="", DBNull.Value, vol_oq.Text)

|||

thanks so much it worked :)

however, the issue now is when i try to display the columns with null values, it reports an error -

i have explicitly casted these values with their corresponding data types to defualt value other than null. But the problem is like for eg, in case of any integer type it i set it to default of "0", for our business purpose its misleading as they would be expecting the sell of items for that day to be "0".

this is my code:

Public Function CheckDBNull(ByVal obj As Object, _
Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object
Dim objReturn As Object
objReturn = obj
If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then
objReturn = ""
ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then
objReturn = 0
ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then
objReturn = 0.0
End If
Return objReturn
End Function

|||

Then what do you want to display if an integer column is null? If you want to leave that field blank, simply return an empty string...

|||hi thanks for all your help... i have resolved the above query... much appreciated