Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Controls > Help with Code to Execute a Stored Procedure

Reply
Thread Tools

Help with Code to Execute a Stored Procedure

 
 
Jeff Thur
Guest
Posts: n/a
 
      02-07-2005
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(connectionStri ng)


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(1 : 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
~~~~~~~~~
..


 
Reply With Quote
 
 
 
 
jennifer1970@hotmail.com
Guest
Posts: n/a
 
      02-16-2005
Jeff,

I'll show you how I've done this in the past. I go about it a little
differently, in that I return a dataview and then use that to bind to
the datagrid. Personally, I just like it better.

But anyway...


Private Function GetDailySales(ByVal Unit As String, ByVal dDate As
String, ByVal dDate2 As String) As DataView
Dim dtbl As DataTable
Dim cn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim p1 As SqlClient.SqlParameter
Dim p2 As SqlClient.SqlParameter
Dim p3 As SqlClient.SqlParameter

Dim da As SqlClient.SqlDataAdapter
'This just returns a connection to the database
Dim Con As New ConnectDB()
cn = Con.ConnectDB

dtbl = New DataTable("USP_Get_Daily_Sales")
cmd = New SqlClient.SqlCommand("USP_Get_Daily_Sales", cn)
cmd.CommandTimeout = 1200

cmd.CommandType = CommandType.StoredProcedure
p1 = cmd.Parameters.Add("@Unit", SqlDbType.Int)
p2 = cmd.Parameters.Add("@Date", SqlDbType.SmallDateTime)
p3 = cmd.Parameters.Add("@Date2", SqlDbType.SmallDateTime)

p1.Value = Unit
p2.Value = dDate
p3.Value = dDate2

da = New SqlClient.SqlDataAdapter(cmd)
da.Fill(dtbl)
Dim custDV As DataView
custDV = dtbl.DefaultView
Return custDV
End Function

And this is how I use the dataview to bind the grid:

Private Sub FillGrid(ByVal SortExp As String)
Dim dvMain As DataView
Dim X As Int64
Dim dtMain As New DataTable()
Dim DR As DataRow
Dim Update_User As String

If txtStartDate.Text <> "*" Then
If txtEndDate.Text = "*" Then Exit Sub
End If

dvMain = GetDailySales(txtUnitID.Text, txtStartDate.Text,
txtEndDate.Text)

If SortExp <> "" Then
dvMain.Sort = SortExp
End If

Dim Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10
As String

Col1 = "Unit"
Col2 = "Date"
Col3 = "Status"
Col4 = "Deposit" & vbCrLf & "Evening" & vbCrLf & "$"
Col5 = "Deposit" & vbCrLf & "Early_Bird" & vbCrLf & "$"
Col6 = "Deposit" & vbCrLf & "Day" & vbCrLf & "$"
Col7 = "Deposit" & vbCrLf & "Special" & vbCrLf & "$"
Col8 = "Deposit" & vbCrLf & "Total" & vbCrLf & "$"
Col9 = "Credit_Card" & vbCrLf & "Visa/Debit" & vbCrLf & "$"
Col10 = "Credit_Card" & vbCrLf & "MasterCard" & vbCrLf & "$"

dtMain.Columns.Add(Col1)
dtMain.Columns.Add(Col2)
dtMain.Columns.Add(Col3)
dtMain.Columns.Add(Col4)
dtMain.Columns.Add(Col5)
dtMain.Columns.Add(Col6)
dtMain.Columns.Add(Col7)
dtMain.Columns.Add(Col
dtMain.Columns.Add(Col9)
dtMain.Columns.Add(Col10)

Dim BD As Date
For X = 0 To dvMain.Count - 1
DR = dtMain.NewRow

DR(Col1) = dvMain(X)("Unit_ID")
BD = dvMain(X)("Business_Date")
DR(Col2) = BD.ToShortDateString
DR(Col3) = dvMain(X)("Status")
DR(Col4) = Format(dvMain(X)("Deposit_Eve_Amt"), "N")
DR(Col5) = Format(dvMain(X)("Deposit_EB_Amt"), "N")
DR(Col6) = Format(dvMain(X)("Deposit_Day_Amt"), "N")
DR(Col7) = Format(dvMain(X)("Deposit_Special_Amt"), "N")
DR(Col = Format(dvMain(X)("Deposit_Total_Amt"), "N")
DR(Col9) = Format(dvMain(X)("CC_Visa_Debit_Amt"), "N")
DR(Col10) = Format(dvMain(X)("CC_MasterCard_Amt"), "N")

dtMain.Rows.Add(DR)
Next

dgDSR.DataSource = dtMain
dgDSR.DataBind()


End Sub

HTH,
Jennifer


Jeff Thur wrote:
> 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(connectionStri ng)
>
>
> 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(1 : 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
> ~~~~~~~~~
> .


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
stored procedure takes too much time to execute nicholas ASP .Net 7 08-03-2005 07:20 PM
Stored Procedure vs direct execute SQL =?Utf-8?B?SklNLkgu?= ASP .Net 7 05-27-2005 10:12 PM
Help with Code to Execute a Stored Procedure Jeff Thur ASP .Net 2 02-07-2005 08:58 PM
How can I execute a stored procedure in .net? FireListen ASP .Net 1 06-27-2003 01:39 PM



Advertisments