Executing a SQL2005 Stored Procedure

13 Jun 2006 3:52 PM

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.OperatorLogon"
        STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
        STR_SQLCOMMAND.CommandTimeout = 30

        With STR_SQLCOMMAND
            ' Set the 1st Parameter
            .Parameters("@OperatorName").Value = InputString(0)
            .Parameters("@OperatorPassword").Value = InputString(1)
            .Parameters("@PasswordLife").Value = 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

13 Jun 2006 4:20 PM

IdleBrain

Hello,
See if this helps:

Dim sqlConn As New SqlConnection(gstrSqlConn)
Dim sqlComm As New SqlCommand("EXECUTE StoredProcedureName", sqlConn)

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

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

13 Jun 2006 4:36 PM

Izzy

This is what your looking for:

Dim Reader as SqlDataReader
Dim cmdSQL as SqlCommand
Dim Conn as New SqlConnection(strconn)

cmdSQL = New SqlCommand
Conn.Open()

            With cmdSQL
                .CommandText = "SVTN_EDI_EXCEPTIONS"
                .CommandType = CommandType.StoredProcedure
                .Connection = Conn
                .Parameters.Add("@DOC_ID", SqlDbType.VarChar, 30).Value
= DocID
                .Parameters.Add("@EXCEPTIONDESC", SqlDbType.VarChar,
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:
Show quoteHide quote

> Hello,
> See if this helps:
>
> Dim sqlConn As New SqlConnection(gstrSqlConn)
> Dim sqlComm As New SqlCommand("EXECUTE StoredProcedureName", sqlConn)
>
> 'Open the sql connection
> sqlConn.Open()
> Dim sqlDReader As SqlDataReader = sqlComm.ExecuteReader()
>
> While sqlDReader.Read()
> 'Obtain values from each row here...
> gstrOperatorName = CStr(sqlDReader("OperatorName")).Trim
> End While

13 Jun 2006 4:54 PM

Cor Ligthert [MVP]

Peter,

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

After that you can decide if you use an
executenonscalar '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

Show quoteHide quote

"Peter Newman" <PeterNew***@discussions.microsoft.com> schreef in bericht
news:8AEF7ABD-2663-467A-B854-AC60EE1253E0@microsoft.com...
> 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.OperatorLogon"
>        STR_SQLCOMMAND.CommandType = CommandType.StoredProcedure
>        STR_SQLCOMMAND.CommandTimeout = 30
>
>
>        With STR_SQLCOMMAND
>            ' Set the 1st Parameter
>            .Parameters("@OperatorName").Value = InputString(0)
>            .Parameters("@OperatorPassword").Value = InputString(1)
>            .Parameters("@PasswordLife").Value = 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