Combo box does not display the list created by sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Combo box does not display the list created by sp

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-04 : 00:37:25

Combo box does not display the list created by a stored procedure.
Access 2002 ADP and SQL 2000 Server

I have four combo boxes. Dept, SO, Item, and Sectionno.
1) I selected an item from the Dept combo box
2) Next, I selected an item from the Item combo box.
When this occured, the
stored procedure Get_SO_Number suppose to bring all of the
SONumber that matches the criteria
(Example: Dept = Custom and Item = A40)
as shown in the stored procedure. Instead I get a blank combo box
and the SONumber list in the SO combo box is empty.

I then looked at the So combo box property and it
showed 4 parameters
Dept = 5, SO = 0, Item = A40, and Section = 0
Row Source: Exec [Get_SO_Number]'5', '0', 'A40', '0'

It seemed to me that since the parameter for SO and Section
are both NULL or 0, the stored procedure does not return
any row. How do I modify the stored procedure so
that when any of the parameter
is NULL, it treated as if there is no parameter there.
In this case,
there would be two parameters instead of 4 because two are NULL.
Therefore , when I looked at the property for the So combo
box property, it should look like this:
Row Source: Exec [Get_SO_Number] '5', 'A40'

instead of this:
Row Source: Exec [Get_SO_Number] '5', '0', 'A40', '0'

/////////////////////////////////////////////////////////////////////////////

Private Sub Item_AfterUpdate()
Dim iDept As Integer
Dim LgSO As Long
Dim Item_Number As String
Dim Section_Number As Integer

If Not IsNull(Me.Dept) Then 'Department
iDept = CInt(Me.Dept)
End If

If Not IsNull((Me.so)) Then 'SO_Number
LgSO = CLng(Me.so)
End If

If Not IsNull(Me.Item) Then 'Item_Number
Item_Number = CStr(Me.Item)
End If

If Not IsNull(Me.Sectionno) Then 'Section_Number
Section_Number = CInt(Me.Sectionno)
End If

Me.so.RowSource = "Exec [Get_SO_Number]'" & iDept & "', '" & LgSO & "', '" & Item_Number & "', '" & Section_Number & "'"

End Sub

///////////////////////////////////////////////////////////////////

CREATE PROCEDURE Get_SO_Number
@iDept int = Null, @LgSO int = Null, @strItem varchar(5) = Null, @Section_Number int = Null
AS SELECT DISTINCT [1_Job - Parent].SONumber, Ref_DepartmentID.ID
FROM dbo.[1_Job - Parent]
INNER JOIN dbo.Ref_DepartmentID
ON dbo.[1_Job - Parent].Department_Name = dbo.Ref_DepartmentID.DepartmentName
WHERE dbo.Ref_DepartmentID.ID = isnull(@iDept, dbo.Ref_DepartmentID.ID)
AND SONumber = isnull(@LgSO, dbo.[1_Job - Parent].SONumber)
AND ItemNumber = isnull(@StrItem, dbo.[1_Job - Parent].ItemNumber)
AND SectNumber = isnull(@Section_Number, dbo.[1_Job - Parent].SectNumber)
ORDER BY SONumber
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources