Executing a SQL2005 Stored Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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
 
Back
Top