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.ReturnValueadoCon.Open()
cmdSQL.ExecuteNonQuery()
iReturnVal = cmdSQL.Parameters("ReturnValue").ValueIf 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
Replace the "/" in the date with "-" and drop the " AM" at the end of the string. You should be good to go.
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_IDSELECT @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_FILEIF @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_ORDERIDIF @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
0 comments:
Post a Comment