Executing a SQL2005 Stored Procedure

[Peter Newman]

In running vb.net 2003 and am trying to run a stored procedure

Dim InputString() As String
InputString = Split(InputParm , ";")
' set the query commands
STR_SQLCOMMAND. CommandText = "BossData.dbo.O peratorLogon"
STR_SQLCOMMAND. CommandType = CommandType.Sto redProcedure
STR_SQLCOMMAND. CommandTimeout = 30


With STR_SQLCOMMAND
' Set the 1st Parameter
.Parameters("@O peratorName").V alue = InputString(0)
.Parameters("@O peratorPassword ").Value = InputString(1)
.Parameters("@P asswordLife").V alue = InputString(2)
End With

The Connection to the Database is already set as SQL_CONNECTED
i got so far but dont know how to execute it or get the return ..... Help

[IdleBrain]

Re: Executing a SQL2005 Stored Procedure

Hello,
See if this helps:

Dim sqlConn As New SqlConnection(g strSqlConn)
Dim sqlComm As New SqlCommand("EXE CUTE StoredProcedure Name", sqlConn)

'Open the sql connection
sqlConn.Open()
Dim sqlDReader As SqlDataReader = sqlComm.Execute Reader()

While sqlDReader.Read ()
'Obtain values from each row here...
gstrOperatorNam e = CStr(sqlDReader ("OperatorName" )).Trim
End While

[Izzy]

Re: Executing a SQL2005 Stored Procedure

This is what your looking for:

Dim Reader as SqlDataReader
Dim cmdSQL as SqlCommand
Dim Conn as New SqlConnection(s trconn)

cmdSQL = New SqlCommand
Conn.Open()

With cmdSQL
.CommandText = "SVTN_EDI_EXCEP TIONS"
.CommandType = CommandType.Sto redProcedure
.Connection = Conn
.Parameters.Add ("@DOC_ID", SqlDbType.VarCh ar, 30).Value
= DocID
.Parameters.Add ("@EXCEPTIONDES C", SqlDbType.VarCh ar,
50).Value = ExceptionDesc
.Parameters.Add ("@IDENTITY_COL ", SqlDbType.Int,
4).Value = IdentityCol
Reader = .ExecuteReader( )
End With

While Reader.Read
'row data is accessed through the reader. For example "Reader(0)"
End While

Reader.Close()
Conn.Close()

If the sp returns a single value then use "variable = .ExecuteScalar"
instead.


IdleBrain wrote:[color=blue]
> Hello,
> See if this helps:
>
> Dim sqlConn As New SqlConnection(g strSqlConn)
> Dim sqlComm As New SqlCommand("EXE CUTE StoredProcedure Name", sqlConn)
>
> 'Open the sql connection
> sqlConn.Open()
> Dim sqlDReader As SqlDataReader = sqlComm.Execute Reader()
>
> While sqlDReader.Read ()
> 'Obtain values from each row here...
> gstrOperatorNam e = CStr(sqlDReader ("OperatorName" )).Trim
> End While[/color]

[Cor Ligthert [MVP]]

Re: Executing a SQL2005 Stored Procedure

Peter,

In addition to the others, the main thing you are missing is as Izxy showed
as well.
.CommandType = CommandType.Sto redProcedure

After that you can decide if you use an
executenonscala r 'to return one value
a datareader 'to return one row and set the cursor to the next to get the
next
a dataadapter (or from that inherited tableadapter) 'to get a complete
resultset as table (which is using the datareader behind the scene)

Cor

"Peter Newman" <PeterNewman@di scussions.micro soft.com> schreef in bericht
news:8AEF7ABD-2663-467A-B854-AC60EE1253E0@mi crosoft.com...[color=blue]
> In running vb.net 2003 and am trying to run a stored procedure
>
> Dim InputString() As String
> InputString = Split(InputParm , ";")
> ' set the query commands
> STR_SQLCOMMAND. CommandText = "BossData.dbo.O peratorLogon"
> STR_SQLCOMMAND. CommandType = CommandType.Sto redProcedure
> STR_SQLCOMMAND. CommandTimeout = 30
>
>
> With STR_SQLCOMMAND
> ' Set the 1st Parameter
> .Parameters("@O peratorName").V alue = InputString(0)
> .Parameters("@O peratorPassword ").Value = InputString(1)
> .Parameters("@P asswordLife").V alue = InputString(2)
> End With
>
> The Connection to the Database is already set as SQL_CONNECTED
> i got so far but dont know how to execute it or get the return ..... Help[/color]