Thursday, March 22, 2012

trouble with passing date to stored procedure

Trying to create a app that will read through our log files. I am reading through a directory and for each file I loop line by line and split the log into an array by SPACE and then send the IP, name of the log file, referer URL, and datetime to a stored procedure (below). The following code just returns 0 as a result and is not true. I believe my issue is in the asp.net datetime vs the datetime format in my database.

The datetime sent to the SP is: 10/17/2004 12:00:19 AM
The datetime in the field DT_TM I am comparing to is: 2004-06-23 10:17:23.000

Just hoping someone has a good grasp on this datetime format issue since I am clueless at this point on what to do. Cause I was sending in without converting to a date and kept getting errors.

This section of code is a portion taken from my .aspx file


'----
Dim dtLogDateTime as DateTime
adoCon = New SqlConnection("Server=xx.xx.xx.xx;Database=dbname;Uid=user;Pwd=pwd")
cmdSQL = New SqlCommand("sc_add_referer", adoCon)
cmdSQL.CommandType = CommandType.StoredProcedure

'Input Parameters
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_IP", arrArray2(5))
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_LOG_FILE", arFile(arFile.Length -1))
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_REFERER", arrArray2(8))
dtLogDateTime = arrArray2(0) & " " & arrArray2(1)
cmdSQL.Parameters.Add("@dotnet.itags.org.SENT_DT_TM", dtLogDateTime)

'Output parameter
parmSQL = cmdSQL.Parameters.Add("ReturnValue", SqlDbType.Int)
parmSQL.Direction = ParameterDirection.ReturnValue

adoCon.Open()
cmdSQL.ExecuteNonQuery()
iReturnVal = cmdSQL.Parameters("ReturnValue").Value

If iReturnVal = -2 Then
Response.Write("<br>" & arrArray2(3) & " " & arrArray2(5))
End if
adoCon.Close()
'----

The following is the SQL Server stored procedure I am calling


CREATE PROCEDURE sc_add_referer
(
@dotnet.itags.org.SENT_IP varchar(15),
@dotnet.itags.org.SENT_LOG_FILE varchar(15),
@dotnet.itags.org.SENT_REFERER text,
@dotnet.itags.org.SENT_DT_TM datetime
)
AS
DECLARE @dotnet.itags.org.ROW_CNT INT
DECLARE @dotnet.itags.org.ROW_ORDERID INT
DECLARE @dotnet.itags.org.ROW_DT_TM INT
DECLARE @dotnet.itags.org.SUB_CNT INT

--FIRST NEED TO FIND OUT IF IN MAIN TABLE
SELECT @dotnet.itags.org.ROW_CNT = count(*), @dotnet.itags.org.ROW_ORDERID = ORDER_ID
FROM sc_ip_tracking
WHERE IP = @dotnet.itags.org.SENT_IP
GROUP BY ORDER_ID

SELECT @dotnet.itags.org.SUB_CNT = count(*)
FROM sc_ip_tracking_referers
WHERE ORDER_ID = @dotnet.itags.org.ROW_ORDERID
AND LOG_FILE = @dotnet.itags.org.SENT_LOG_FILE

IF @dotnet.itags.org.ROW_CNT > 0
-- IP FOUND SO SEE IF FOUND IN REFERER LOG
IF @dotnet.itags.org.SUB_CNT > 0
--ROW ROUND
RETURN 0
ELSE
-- NO ROW FOUND SO NEED TO ADD INFO
BEGIN
SELECT @dotnet.itags.org.ROW_DT_TM = datediff(hour, @dotnet.itags.org.SENT_DT_TM, DT_TM) FROM sc_ip_tracking WHERE IP = @dotnet.itags.org.SENT_IP AND ORDER_ID = @dotnet.itags.org.ROW_ORDERID

IF @dotnet.itags.org.ROW_DT_TM >= 0 AND @dotnet.itags.org.ROW_DT_TM <= 8
BEGIN
INSERT INTO sc_ip_tracking_referers (ORDER_ID, LOG_FILE, REFERER, LAST_CHANGE_DT)
values (@dotnet.itags.org.ROW_ORDERID, @dotnet.itags.org.SENT_LOG_FILE, @dotnet.itags.org.SENT_REFERER, getdate())
END
RETURN -2
END
ELSE
RETURN 0
GO

Replace the "/" in the date with "-" and drop the " AM" at the end of the string. You should be good to go.

0 comments:

Post a Comment