Help with Code to Execute a Stored Procedure

[Jeff Thur]

I am trying to Execute a simple Stored Procedure using
ASP/VB. I have spent numerous hours with this,
researching
books and looking on the internet. I can't get a direct
answer. THis is my program, followed by the errors, Can
anyone Please Help, Please, Please, Please.

Sub cmdGO_Click(sender As Object, e As EventArgs)
DataGrid1.DataSource = GetResults(TxtSort.Text)
DataGrid1.DataBind()
End Sub


Function GetResults(ByVal txtSort As String) As
System.Data.DataSet
Dim connectionString As String
= "server='(local)'; user id='sa'; password='fritz';
database='Cutis'"
Dim dbConnection As System.Data.IDbConnection =
New System.Data.SqlClient.SqlConnection(connectionString)


Dim cmdGO As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand "EMSTONY",
(connectionString)
cmdGO.CommandText = queryString
cmdGO.CommandType = CommandType.StoredProcedure

Dim dbParam_txtSort As System.Data.IDataParameter
= New System.Data.SqlClient.SqlParameter
dbParam_txtSort.ParameterName = "@TxtSort"
dbParam_txtSort.Value = txtSort
dbParam_txtSort.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_txtSort)

Dim dataAdapter As System.Data.IDbDataAdapter =
New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function



ERRORS:

:\BegASPNET11\Ch04\Baldwin20.aspx(18) : error BC30205:
End
of statement expected.

Dim cmdGO As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand "EMSTONY",
(connectionString)


~~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\BegASPNET11\Ch04\Baldwin20.aspx(19) : error
BC30456: 'CommandText' is not a member
of 'System.Web.UI.WebControls.Button'.

cmdGO.CommandText = queryString
~~~~~~~~~~~~~~~~~
C:\BegASPNET11\Ch04\Baldwin20.aspx(19) : error BC30451:
Name 'queryString' is not declared.

cmdGO.CommandText = queryString
~~~~~~~~~~~
C:\BegASPNET11\Ch04\Baldwin20.aspx(20) : error
BC30456: 'CommandType' is not a member
of 'System.Web.UI.WebControls.Button'.

cmdGO.CommandType =
CommandType.StoredProcedure

~~~~~~~~~~~~~~~~~
C:\BegASPNET11\Ch04\Baldwin20.aspx(20) : error BC30451:
Name 'CommandType' is not declared.

cmdGO.CommandType =
CommandType.StoredProcedure

~~~~~~~~~~~
C:\BegASPNET11\Ch04\Baldwin20.aspx(26) : error BC30451:
Name 'dbCommand' is not declared.

dbCommand.Parameters.Add(dbParam_txtSort)
~~~~~~~~~
C:\BegASPNET11\Ch04\Baldwin20.aspx(29) : error BC30451:
Name 'dbCommand' is not declared.

dataAdapter.SelectCommand = dbCommand
~~~~~~~~~
..

 

[Guest]

1. If the wraps exist in your file, and are not an artifact of posting, you
need the continue line character (underscore = _) on the first line

2. It is easier, for me, to code like so:

'NOTE: Items in {} are items you need to fill in
Dim connString As String = "{Your Conn String Here}"
Dim sproc As String = "{sproc name here}"
Dim paramSize as Int = {size of varchar here}

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sproc)
cmd.CommandType = CommandType.StoredProcedure

'Explicitly create (this is a simpler overload)
Dim param as New SqlParameter("@txtSort", DbType.String, paramSize)
param.Value = txtSort.Text

cmd.Parameters.Add(param)

Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet("{NameOfDataSet}")

da.TableMappings.Add("Table", "{FriendlyResultTableName}")

Try
conn.Open()
da.Fill(ds)
Catch ex As Exception
'Error handling here
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If

conn.Dispose()
End Try

Return ds


That should give you some ideas, at least

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

-----Original Message-----
1. If the wraps exist in your file, and are not an artifact of posting, you
need the continue line character (underscore = _) on the first line

2. It is easier, for me, to code like so:

'NOTE: Items in {} are items you need to fill in
Dim connString As String = "{Your Conn String Here}"
Dim sproc As String = "{sproc name here}"
Dim paramSize as Int = {size of varchar here}

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sproc)
cmd.CommandType = CommandType.StoredProcedure

'Explicitly create (this is a simpler overload)
Dim param as New SqlParameter("@txtSort", DbType.String, paramSize)
param.Value = txtSort.Text

cmd.Parameters.Add(param)

Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet("{NameOfDataSet}")

da.TableMappings.Add

("Table", "{FriendlyResultTableName}")

Try
conn.Open()
da.Fill(ds)
Catch ex As Exception
'Error handling here
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If

conn.Dispose()
End Try

Return ds


That should give you some ideas, at least

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


.

I have followed your Code But No Luck. Error on this
Statement.



cmd.CommandType = CommandType.StoredProcedure


BC30188 DECLARATION EXPECTED.