ParameterDirection qustion

[jvcoach23]

I'm using Vb 2005 and Sql 2005. I'm trying to use the ParameterDirection.inputout... and it's not working.


With cm
.Parameters.Add("@intTblWaitingListId", SqlDbType.Int).Value = mintTblWaitingListId
.Parameters("@intTblWaitingListId").Direction = ParameterDirection.InputOutput
end with

cn.Open()
cm.ExecuteNonQuery()



when i trace this in sql.. this is what it is sending in


declare @p1 int
set @p1=8
exec spWaitingList_Save @intTblWaitingListId=@p1 output,@intTblCamperId=781,@intTblWeekId=1,@intPriority=0,@intMisses=0
select @p1


the set @p1=8.. the 8 seems to be the number of times I've tried running this for.. since it seems to increment each time I run it.

now.. if I take comment out the

.Parameters("@intTblWaitingListId").Direction = ParameterDirection.InputOutput


then the value getting passed into @intTblWaitingListId is the value I want.

anyone have any ideas...
thanks
shannon

 

[TechGnome]

What is the value you are expecting to be passed in? Right now it's 8. Is that not right.

-tg

 

[jvcoach23]

no, 8 is not what I'm wanting. when i step throught the code. when it goes to give the @intTblWaitingListId parm a value, the mintTblWaitingListId = 0 which is what the value should be. then, once the

.parameter("@intTblWaitingListId").direction=inputout (that syntax isn't exact.. but i'm not at my machine to do a copy and paste)... it appears that that line changes things..

that make sense why it would do that. I'm doing pretty much this same thing with other stored proc's and they are working... i just can't find where I'm doing something different.. but i must be doing something wrong

thanks
shannon

 

[TechGnome]

try posting your full code where you are calling the SP from...
You could also put a break point there, and *make* sure that your variable is in fact 0. If it's incrementing each time it's called, then there's something that is doing that. It's not arbitrary.

-tg

 

[jvcoach23]

Ok.. here it goes.. I'm using the BindingNavigatorSaveItem_Click event that does some validation checking, then calls a Private Sub called

Here is part of it..
Private Sub SaveWaitingListInfo()
If mId = 0 Then
Dim dataGridRow As DataGridViewRow
For Each dataGridRow In Me.WeekDataGridView.Rows
Debug.WriteLine("Row - " & CInt(dataGridRow.Cells("WeekId").Value) & " " & CBool(dataGridRow.Cells("bWeekSelected").FormattedValue))

'check to see if the week is selected and needs to be saved to the waiting list
If CBool(dataGridRow.Cells("bWeekSelected").FormattedValue) Then
Dim oWaitingList As New LE.WaitingList
With oWaitingList
.Id = mId
.intTblCamperId = CInt(Me.IntTblCamperIdTextBox.Text)
.intTblWeekId = CInt(dataGridRow.Cells("WeekId").Value)
.Priority = Nothing
.Misses = Nothing

.Save()
End With

End If ' item is checked
Next dataGridRow


Here is the WaitingList class that has the save in it.

Public Sub Save()
'Save record to database

Dim cn As New SqlConnection
Dim cm As New SqlCommand
Dim dbcon As New LE.DbInfo

Try
cn = New SqlConnection(dbcon.Monitor)

cm = New SqlCommand("spWaitingList_Save", cn)
cm.CommandType = Data.CommandType.StoredProcedure


With cm
.Parameters.Add("@intTblWaitingListId", SqlDbType.Int).Value = mintTblWaitingListId
.Parameters("@intTblWaitingListId").Direction = ParameterDirection.InputOutput
.Parameters.Add("@intTblCamperId", SqlDbType.Int).Value = mintTblCamperId
.Parameters.Add("@intTblWeekId", SqlDbType.Int).Value = mintTblWeekId
.Parameters.Add("@intPriority", SqlDbType.Int).Value = mintPriority
.Parameters.Add("@intMisses", SqlDbType.Int).Value = mintMisses
End With

cn.Open()
cm.ExecuteNonQuery()
mintTblWaitingListId = cm.Parameters("@intTblWaitingListId").Value
Catch ex As Exception
Debug.WriteLine("WaitingList Save Error: " & ex.Message.ToString)
Finally
cn.Dispose()
cm.Dispose()
cm = Nothing
cn = Nothing
End Try

I have stepped through it and the mintTblwaitingList is equal to 0 during the step through process..

hope this give you what you need.
thanks
shannon