Accessing SQL Stored Procedures from VB code

[Evan Kontos]

I am having an issue w/regard to stored procedures. If I do the
following:

Dim OccCnctn As New OleDb.OleDbConnection
Dim OccCmd As New OleDb.OleDbCommand
Dim OccRdr As OleDb.OleDbDataReader
OccCnctn.ConnectionString = "Integrated Security=SSPI;Packet
Size=4096;Data Source='COMTEK-9100WXP';Tag with column collation when
possible=False;Initial Catalog=Move Manager;Use Procedure for
Prepare=1;Auto Translate=True;Persist Security
Info=False;Provider='SQLOLEDB.1';Workstation ID='COMTEK-9100WXP';Use
Encryption for Data=False"

OccCnctn.Open()
OccCmd.Connection = OccCnctn
OccCmd.CommandText() = "SELECT max_occup, No_of_Occup FROM
MM_Spaces_Existing where site = '" & DDLTSite.SelectedItem.Text & "' and
Building = '" & DDLTBldg.SelectedItem.Text & "' and Flr = '" &
DDLTFlr.SelectedItem.Text & "' and room = '" & DDLTRm.SelectedItem.Text
& "'"

OccRdr = OccCmd.ExecuteReader()
OccRdr.Read()

If OccRdr.HasRows Then

When I get to the HasRows it is True which is correct. If I try to use
a stored procedure and vb code as follows:

CREATE PROCEDURE dbo.MM_Get_Max_No_Occs @Site nvarchar, @Bldg nvarchar ,
@Floor nvarchar, @Room nvarchar

AS
Declare @ErrSave int
Set @ErrSave = 0

SELECT max_occup, No_of_Occup FROM MM_Spaces_Existing
where Site = @Site and Building = @Bldg and Flr = @Floor and Room =
@Room;

If (@@ERROR <> 0)
Set @ErrSave = @@ERROR

return @ErrSave
GO
with the following VB code

Dim OccCnctn As New OleDb.OleDbConnection
Dim OccCmd As New OleDb.OleDbCommand
Dim OccRdr As OleDb.OleDbDataReader
OccCnctn.ConnectionString = "Integrated Security=SSPI;Packet
Size=4096;Data Source='COMTEK-9100WXP';Tag with column collation when
possible=False;Initial Catalog=Move Manager;Use Procedure for
Prepare=1;Auto Translate=True;Persist Security
Info=False;Provider='SQLOLEDB.1';Workstation ID='COMTEK-9100WXP';Use
Encryption for Data=False"

OccCnctn.Open()
OccCmd.Connection = OccCnctn

OccCmd.CommandType = CommandType.StoredProcedure
OccCmd.Parameters.Add("@Site", OleDb.OleDbType.VarChar).Value =
DDLTSite.SelectedItem.Text
OccCmd.Parameters.Add("@Bldg", OleDb.OleDbType.VarChar).Value =
DDLTBldg.SelectedItem.Text
OccCmd.Parameters.Add("@Floor", OleDb.OleDbType.VarChar).Value =
DDLTFlr.SelectedItem.Text
OccCmd.Parameters.Add("@Room", OleDb.OleDbType.VarChar).Value =
DDLTRm.SelectedItem.Text
OccCmd.CommandText = "MM_Get_Max_No_Occs"

OccRdr = OccCmd.ExecuteReader()
OccRdr.Read()

If OccRdr.HasRows Then

then hasrows is false. Any suggestions?

Evan Kontos | EK*****@comtekcadd.com

27 Whitehall St. | 3rd Floor | New York, NY 10004
Tel [phone] | Fax [phone] | Personal Fax [phone]
www.comtekcadd.com

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

[Herfried K. Wagner [MVP]]

* Evan Kontos <ek*****@comtekcadd.com> scripsit:

I am having an issue w/regard to stored procedures. If I do the
following:

Dim OccCnctn As New OleDb.OleDbConnection

Notice that a separate group for ADO.NET related question is available:

<news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<http://msdn.microsoft.com/newsgroups/default.asp?url=/newsgroups/loadframes.asp?icp=msdn&slcid=us&newsgroup=microso ft.public.dotnet.framework.adonet>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

<http://www.plig.net/nnq/nquote.html>