Help with Code to Execute a Stored Procedure

Discussion in 'ASP .Net Web Controls' started by Jeff Thur, Feb 7, 2005.

  1. Jeff Thur

    Jeff Thur Guest

    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
    ~~~~~~~~~
    ..
    Jeff Thur, Feb 7, 2005
    #1
    1. Advertising

  2. Jeff Thur

    Guest

    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(Col8)
    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(Col8) = 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(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
    > ~~~~~~~~~
    > .
    , Feb 16, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. FireListen
    Replies:
    1
    Views:
    481
    James J. Foster
    Jun 27, 2003
  2. Jeff Thur
    Replies:
    2
    Views:
    1,011
    Guest
    Feb 7, 2005
  3. =?Utf-8?B?SklNLkgu?=

    Stored Procedure vs direct execute SQL

    =?Utf-8?B?SklNLkgu?=, May 27, 2005, in forum: ASP .Net
    Replies:
    7
    Views:
    5,020
    Robbe Morris [C# MVP]
    May 27, 2005
  4. nicholas
    Replies:
    7
    Views:
    593
    John.Net
    Aug 3, 2005
  5. Mike P
    Replies:
    0
    Views:
    3,276
    Mike P
    Jun 19, 2006
Loading...

Share This Page