Desparete for advice on stored proc from asp.net

R

Rob

I have no hairs left and what is has gone gray.
this makes no sense

I have a datagrid page which allows the user to select certain records
to do a bulk update... within my stored procedure that gets called the
first "half" of the stored proc always fires fine, which all it does
is update one table, the second half actually calls another stored
proc which supposed to update a different table. THE catch is the
second half never works the first time the code behind is called, upon
refreshing the page or hitting the button on the web form again, the
second half finishes.. I get NO SQL errors at all...

any ideas?
 
A

Alvin Bruney [MVP]

post some code that reproduces the problem.
curiousity begs me to ask if you have a page.ispostback check
 
B

b s

Why would it matter about page.ispostback (Which I do have a check for)
the point is the stored procedure only does 2 out of 3 steps the first
time, then on refresh does all three...


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
R

Rob

I have no hairs left and what is has gone gray.
this makes no sense

I have a datagrid page which allows the user to select certain records
to do a bulk update... within my stored procedure that gets called the
first "half" of the stored proc always fires fine, which all it does
is update one table, the second half actually calls another stored
proc which supposed to update a different table. THE catch is the
second half never works the first time the code behind is called, upon
refreshing the page or hitting the button on the web form again, the
second half finishes.. I get NO SQL errors at all...

any ideas?

A little bit more info apparantly for some god foresaken reason, the
whole issue lies in the fact that I cant seem to put a date variable
into a nvarchar variable. here is sample code
Set @usertxt=@UserName + 'has was converted on this date' +
Convert(char(12),@DateVar)
if I take away the convert statement, the @usertxt variable gets set
fine, but as soon as the date gets put in their, the whole @usertxt
variable becomes NULL

Even more bizarre, this DOES NOT happen through SQL Query analyzer..
only through asp.net application
PLEASE HELP
 
R

Rob

Roger Twomey said:
Can you show the code?

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
 
R

Rob

for anyone that cares or runs into this same issue, the issue lies in
a setting in SQL server, you must use SET CONCAT_NULL_YIELDS_NULL OFF,
although it does not throw an error by default if you are trying to
concatenate (sp?) a string in a stored proc like I was, and if it
happens part of the string has variables or any data that is null, if
you do not set this to off, it will nullify the whole string.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top