Roger Twomey said:
yes, to let you all know, this is stumped microsoft who I have shared
the code and the db with, over 3 days on the phone and they can't
figure this out, this is absolutely obsurd, personally I think its an
ado.net bug but in either case here you go..
ASP.NET Code
Public Sub BulkUpdate(ByVal UpdateOpt As String, ByVal paramstring As
String, ByVal chkdata As String)
Dim conn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings.Get("constring"))
Dim comm As SqlCommand = New SqlCommand
comm.Connection = conn
Dim chkparameter As SqlParameter = New
SqlParameter("@CheckID", SqlDbType.Int, 4)
Dim wheredepparameter As SqlParameter = New
SqlParameter("@WhereDep", SqlDbType.NVarChar, 50)
Dim dateparameter As SqlParameter = New
SqlParameter("@txtDate", SqlDbType.DateTime, 4)
Dim useridparam As SqlParameter = New SqlParameter("@UserID",
SqlDbType.Int, 4)
Dim userid As Integer =
HttpContext.Current.Request.Cookies("UserCookie").Value
useridparam.Value = userid
dateparameter.Value = Today()
comm.CommandType = CommandType.StoredProcedure
Dim checkarray As Array
checkarray = Split(chkdata, "|")
comm.Parameters.Add(dateparameter)
comm.Parameters.Add(chkparameter)
comm.Parameters.Add(useridparam)
Dim counter As Integer
Dim top As Integer = checkarray.GetUpperBound(0)
Dim trxdesc As String
Select Case UpdateOpt
Case "DepositDate"
Dim txt2 As SqlParameter = New
SqlParameter("@chgtext", SqlDbType.NVarChar, 800)
txt2.Direction = ParameterDirection.Output
wheredepparameter.Value = Trim(paramstring)
comm.Parameters.Add(wheredepparameter)
comm.Parameters.Add(txt2)
comm.CommandText = "sp_bulkcheckDep"
conn.Open()
For counter = 1 To top
comm.Parameters.Remove(chkparameter)
chkparameter.Value = checkarray.GetValue(counter)
comm.Parameters.Add(chkparameter)
comm.ExecuteNonQuery()
/*only just today added this to see why the heck inside the stored
proc the @chgtext variable nulls itself out.. normally this was not
here, but this shows the same thing as the issue which is that
@chgtext is null everytime!
Dim s As String = txt2.Value
HttpContext.Current.Response.Write(s)
HttpContext.Current.Response.End()
Next
--------------------------------------
here is the stored proc:
CREATE PROCEDURE [sp_BulkCheckDep]
@CheckID int,
@txtDate smalldatetime,
@UserID int,
@WhereDep char (50),
@chgtext varchar(800) OUTPUT
/*@outtxt varchar(800) OUTPUT*/
as
Begin
Declare
@OrigWhereDep char(50),
@OrigDD smalldatetime
/*Get Original Values*/
Select @OrigWhereDEP=WhereDeposited, @OrigDD=DateDeposited From
TblCheckLog Where Checkid=@CheckID
if @Checkid is null
select @chgtext='nullcheck'
/*NOTE HAD TO CHECK FOR THIS THINKING IT WAS @ORIGDD CAUSING THE ISSUE
BUT EVEN WITH THAT CHECK @CHGTEXT IS NULL (bizarre that the whole
string of @chgtext is null if indeed it is date fields causing issue*/
if @OrigDD is null
begin
set @chgtext='VIA BULK Updated WhereDeposited From ' +@OrigWhereDEP +
' To ' + @WhereDep + ' <BR> Updated Deposit Date From nothing to ' +
Convert(varchar(50),GetDate())
end
else
begin
set @chgtext='VIA BULK Updated WhereDeposited From ' + @OrigWhereDEP
+ ' To ' + @WhereDep + ' <BR> Updated Deposit Date From ' +
Convert(char(50),@OrigDD) + ' to '+ Convert(char(50),GetDate())
end
select @chgtext /*the problem is right here, this is null when called
through asp.net, but fine through query analyzer
/*Update*/
Update TblCheckLog SET DateDeposited=@txtDate,
WhereDeposited=@WhereDep Where CheckID=@CheckID
/*Now Update TrxRecord
Insert Into TblTrx(UserAction,UserID,CheckID,TrxDate)
Values(@chgtext,@UserID,@CheckID,GetDate())*/
End
GO