populate datagrid with stored procedure w/parameter

Discussion in 'ASP .Net Datagrid Control' started by Machelle Chandler, Oct 16, 2003.

  1. All,

    I'm trying to populate a datagrid with a data adapter that uses a stored
    procedure with a parameter. I get the below error when I run my code
    (as seen below). Any hints?

    If I delete the .value = "Business Acumen" at the end of the add
    parameters statement & put the below code on a different row, the error
    goes away, but I get back an empty dataset.

    Me.cmdUYP.Parameters("@web_competency_name").Value = "Business Acumen"

    Error Msg:
    The SqlParameterCollection only accepts non-null SqlParameter type
    objects, not Boolean objects.


    My Code:

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text.RegularExpressions
    Imports System.Text


    Public Class UYP_Class
    Inherits System.Web.UI.Page
    Protected WithEvents btnFLMClose As System.Web.UI.WebControls.Button
    Dim previousCat As String
    Dim previousComp As String
    Protected WithEvents linkIntelU As
    System.Web.UI.WebControls.HyperLink
    Protected WithEvents Image1 As System.Web.UI.WebControls.Image
    Protected WithEvents btnClose As System.Web.UI.WebControls.Button
    Protected WithEvents lnkIntelLibrary As
    System.Web.UI.WebControls.HyperLink
    Protected WithEvents lnkBuyOnline As
    System.Web.UI.WebControls.HyperLink
    Protected WithEvents conUYP As System.Data.SqlClient.SqlConnection
    Protected WithEvents lblError As System.Web.UI.WebControls.Label
    Protected WithEvents daUYP As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents cmdUYP As System.Data.SqlClient.SqlCommand
    Protected WithEvents DsUYP1 As FDO.dsUYP
    Protected WithEvents lnkAllCurric As
    System.Web.UI.WebControls.HyperLink
    Protected WithEvents lnkHelp As System.Web.UI.WebControls.HyperLink
    Protected WithEvents dgUYP As System.Web.UI.WebControls.DataGrid
    Protected WithEvents btnExportExcel As
    System.Web.UI.WebControls.Button

    #Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
    InitializeComponent()
    Me.conUYP = New System.Data.SqlClient.SqlConnection()
    Me.daUYP = New System.Data.SqlClient.SqlDataAdapter()
    Me.cmdUYP = New System.Data.SqlClient.SqlCommand()
    Me.DsUYP1 = New FDO.dsUYP()
    CType(Me.DsUYP1,
    System.ComponentModel.ISupportInitialize).BeginInit()
    '
    'conUYP
    '
    Me.conUYP.ConnectionString = "data source=OREA2SQL017;initial
    catalog=Fin_Trng_DB;password=abcd$1234;persist se" & _
    "curity info=True;user id=FinTrngUserGrp"
    '
    'daUYP
    '
    Me.daUYP.SelectCommand = Me.cmdUYP
    '
    'cmdUYP
    '
    Me.cmdUYP.CommandText = "dbo.[prc_uyp_curriculum]"
    Me.cmdUYP.CommandType = System.Data.CommandType.StoredProcedure
    Me.cmdUYP.Connection = Me.conUYP
    Me.cmdUYP.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("@web_competency_name",
    System.Data.SqlDbType.NVarChar, 50).Value = "Business Acumen")

    '
    'DsUYP1
    '
    Me.DsUYP1.DataSetName = "dsUYP"
    Me.DsUYP1.Locale = New System.Globalization.CultureInfo("en-US")
    Me.DsUYP1.Namespace = "http://www.tempuri.org/dsUYP.xsd"
    CType(Me.DsUYP1,
    System.ComponentModel.ISupportInitialize).EndInit()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
    End Sub

    #End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Try
    'Me.cmdUYP.Parameters("web_competency_name").Value =
    "Business Acumen"
    daUYP.Fill(DsUYP1, "prc_uyp_curriculum")
    If Not IsPostBack Then
    dgUYP.DataSource =
    DsUYP1._dbo_prc_uyp_curriculum.DefaultView()
    dgUYP.DataBind()
    End If
    End Sub
    End Class

    Machelle Chandler
    Intel Corporation
    Beginning .NET developer
    Thanks in advance for the help!

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Machelle Chandler, Oct 16, 2003
    #1
    1. Advertising

  2. Machelle Chandler

    William Ryan Guest

    You have a typo in the parameter where you reference value...you declare it
    as "@web_company_name" but reference it as "web_company_name", I think this
    is probably the problem.

    HTH,

    Bill
    "Machelle Chandler" <> wrote in message
    news:uPr%...
    > All,
    >
    > I'm trying to populate a datagrid with a data adapter that uses a stored
    > procedure with a parameter. I get the below error when I run my code
    > (as seen below). Any hints?
    >
    > If I delete the .value = "Business Acumen" at the end of the add
    > parameters statement & put the below code on a different row, the error
    > goes away, but I get back an empty dataset.
    >
    > Me.cmdUYP.Parameters("@web_competency_name").Value = "Business Acumen"
    >
    > Error Msg:
    > The SqlParameterCollection only accepts non-null SqlParameter type
    > objects, not Boolean objects.
    >
    >
    > My Code:
    >
    > Imports System
    > Imports System.Data
    > Imports System.Data.SqlClient
    > Imports System.Text.RegularExpressions
    > Imports System.Text
    >
    >
    > Public Class UYP_Class
    > Inherits System.Web.UI.Page
    > Protected WithEvents btnFLMClose As System.Web.UI.WebControls.Button
    > Dim previousCat As String
    > Dim previousComp As String
    > Protected WithEvents linkIntelU As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents Image1 As System.Web.UI.WebControls.Image
    > Protected WithEvents btnClose As System.Web.UI.WebControls.Button
    > Protected WithEvents lnkIntelLibrary As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents lnkBuyOnline As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents conUYP As System.Data.SqlClient.SqlConnection
    > Protected WithEvents lblError As System.Web.UI.WebControls.Label
    > Protected WithEvents daUYP As System.Data.SqlClient.SqlDataAdapter
    > Protected WithEvents cmdUYP As System.Data.SqlClient.SqlCommand
    > Protected WithEvents DsUYP1 As FDO.dsUYP
    > Protected WithEvents lnkAllCurric As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents lnkHelp As System.Web.UI.WebControls.HyperLink
    > Protected WithEvents dgUYP As System.Web.UI.WebControls.DataGrid
    > Protected WithEvents btnExportExcel As
    > System.Web.UI.WebControls.Button
    >
    > #Region " Web Form Designer Generated Code "
    >
    > 'This call is required by the Web Form Designer.
    > <System.Diagnostics.DebuggerStepThrough()> Private Sub
    > InitializeComponent()
    > Me.conUYP = New System.Data.SqlClient.SqlConnection()
    > Me.daUYP = New System.Data.SqlClient.SqlDataAdapter()
    > Me.cmdUYP = New System.Data.SqlClient.SqlCommand()
    > Me.DsUYP1 = New FDO.dsUYP()
    > CType(Me.DsUYP1,
    > System.ComponentModel.ISupportInitialize).BeginInit()
    > '
    > 'conUYP
    > '
    > Me.conUYP.ConnectionString = "data source=OREA2SQL017;initial
    > catalog=Fin_Trng_DB;password=abcd$1234;persist se" & _
    > "curity info=True;user id=FinTrngUserGrp"
    > '
    > 'daUYP
    > '
    > Me.daUYP.SelectCommand = Me.cmdUYP
    > '
    > 'cmdUYP
    > '
    > Me.cmdUYP.CommandText = "dbo.[prc_uyp_curriculum]"
    > Me.cmdUYP.CommandType = System.Data.CommandType.StoredProcedure
    > Me.cmdUYP.Connection = Me.conUYP
    > Me.cmdUYP.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("@web_competency_name",
    > System.Data.SqlDbType.NVarChar, 50).Value = "Business Acumen")
    >
    > '
    > 'DsUYP1
    > '
    > Me.DsUYP1.DataSetName = "dsUYP"
    > Me.DsUYP1.Locale = New System.Globalization.CultureInfo("en-US")
    > Me.DsUYP1.Namespace = "http://www.tempuri.org/dsUYP.xsd"
    > CType(Me.DsUYP1,
    > System.ComponentModel.ISupportInitialize).EndInit()
    >
    > End Sub
    >
    > Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Init
    > 'CODEGEN: This method call is required by the Web Form Designer
    > 'Do not modify it using the code editor.
    > InitializeComponent()
    > End Sub
    >
    > #End Region
    >
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Load
    > 'Put user code to initialize the page here
    > Try
    > 'Me.cmdUYP.Parameters("web_competency_name").Value =
    > "Business Acumen"
    > daUYP.Fill(DsUYP1, "prc_uyp_curriculum")
    > If Not IsPostBack Then
    > dgUYP.DataSource =
    > DsUYP1._dbo_prc_uyp_curriculum.DefaultView()
    > dgUYP.DataBind()
    > End If
    > End Sub
    > End Class
    >
    > Machelle Chandler
    > Intel Corporation
    > Beginning .NET developer
    > Thanks in advance for the help!
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    William Ryan, Oct 16, 2003
    #2
    1. Advertising

  3. Hi William,

    Good catch - thanks. It still has the same problem, but I'm sure that
    helped isolate it to one problem instead of two. Thanks!

    MC

    Machelle Chandler
    Intel Corporation
    Beginning .NET developer
    Thanks in advance for the help!

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Machelle Chandler, Oct 16, 2003
    #3
  4. Machelle Chandler

    William Ryan Guest

    I've run almost the exact same code and I'm ok. I don't mean to ask
    something stupid, but the code is commented out as presented here...I'm
    assuming that's not the case when you run this? Also, if you put a
    breakpoint on the line where you set the Param value to "Business Acumen",
    does it take it ok? Better yet, with the code posted below, where exactly
    do you get the error, on Fill or on the DataBind?


    "Machelle Chandler" <> wrote in message
    news:uPr%...
    > All,
    >
    > I'm trying to populate a datagrid with a data adapter that uses a stored
    > procedure with a parameter. I get the below error when I run my code
    > (as seen below). Any hints?
    >
    > If I delete the .value = "Business Acumen" at the end of the add
    > parameters statement & put the below code on a different row, the error
    > goes away, but I get back an empty dataset.
    >
    > Me.cmdUYP.Parameters("@web_competency_name").Value = "Business Acumen"
    >
    > Error Msg:
    > The SqlParameterCollection only accepts non-null SqlParameter type
    > objects, not Boolean objects.
    >
    >
    > My Code:
    >
    > Imports System
    > Imports System.Data
    > Imports System.Data.SqlClient
    > Imports System.Text.RegularExpressions
    > Imports System.Text
    >
    >
    > Public Class UYP_Class
    > Inherits System.Web.UI.Page
    > Protected WithEvents btnFLMClose As System.Web.UI.WebControls.Button
    > Dim previousCat As String
    > Dim previousComp As String
    > Protected WithEvents linkIntelU As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents Image1 As System.Web.UI.WebControls.Image
    > Protected WithEvents btnClose As System.Web.UI.WebControls.Button
    > Protected WithEvents lnkIntelLibrary As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents lnkBuyOnline As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents conUYP As System.Data.SqlClient.SqlConnection
    > Protected WithEvents lblError As System.Web.UI.WebControls.Label
    > Protected WithEvents daUYP As System.Data.SqlClient.SqlDataAdapter
    > Protected WithEvents cmdUYP As System.Data.SqlClient.SqlCommand
    > Protected WithEvents DsUYP1 As FDO.dsUYP
    > Protected WithEvents lnkAllCurric As
    > System.Web.UI.WebControls.HyperLink
    > Protected WithEvents lnkHelp As System.Web.UI.WebControls.HyperLink
    > Protected WithEvents dgUYP As System.Web.UI.WebControls.DataGrid
    > Protected WithEvents btnExportExcel As
    > System.Web.UI.WebControls.Button
    >
    > #Region " Web Form Designer Generated Code "
    >
    > 'This call is required by the Web Form Designer.
    > <System.Diagnostics.DebuggerStepThrough()> Private Sub
    > InitializeComponent()
    > Me.conUYP = New System.Data.SqlClient.SqlConnection()
    > Me.daUYP = New System.Data.SqlClient.SqlDataAdapter()
    > Me.cmdUYP = New System.Data.SqlClient.SqlCommand()
    > Me.DsUYP1 = New FDO.dsUYP()
    > CType(Me.DsUYP1,
    > System.ComponentModel.ISupportInitialize).BeginInit()
    > '
    > 'conUYP
    > '
    > Me.conUYP.ConnectionString = "data source=OREA2SQL017;initial
    > catalog=Fin_Trng_DB;password=abcd$1234;persist se" & _
    > "curity info=True;user id=FinTrngUserGrp"
    > '
    > 'daUYP
    > '
    > Me.daUYP.SelectCommand = Me.cmdUYP
    > '
    > 'cmdUYP
    > '
    > Me.cmdUYP.CommandText = "dbo.[prc_uyp_curriculum]"
    > Me.cmdUYP.CommandType = System.Data.CommandType.StoredProcedure
    > Me.cmdUYP.Connection = Me.conUYP
    > Me.cmdUYP.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("@web_competency_name",
    > System.Data.SqlDbType.NVarChar, 50).Value = "Business Acumen")
    >
    > '
    > 'DsUYP1
    > '
    > Me.DsUYP1.DataSetName = "dsUYP"
    > Me.DsUYP1.Locale = New System.Globalization.CultureInfo("en-US")
    > Me.DsUYP1.Namespace = "http://www.tempuri.org/dsUYP.xsd"
    > CType(Me.DsUYP1,
    > System.ComponentModel.ISupportInitialize).EndInit()
    >
    > End Sub
    >
    > Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Init
    > 'CODEGEN: This method call is required by the Web Form Designer
    > 'Do not modify it using the code editor.
    > InitializeComponent()
    > End Sub
    >
    > #End Region
    >
    > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles MyBase.Load
    > 'Put user code to initialize the page here
    > Try
    > 'Me.cmdUYP.Parameters("web_competency_name").Value =
    > "Business Acumen"
    > daUYP.Fill(DsUYP1, "prc_uyp_curriculum")
    > If Not IsPostBack Then
    > dgUYP.DataSource =
    > DsUYP1._dbo_prc_uyp_curriculum.DefaultView()
    > dgUYP.DataBind()
    > End If
    > End Sub
    > End Class
    >
    > Machelle Chandler
    > Intel Corporation
    > Beginning .NET developer
    > Thanks in advance for the help!
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    William Ryan, Oct 17, 2003
    #4
  5. Hi Guys,

    Thanks for your help. It was 2 problems, the missing @ sign that HTH
    caught (thank you!) and that I had my table name wrong in the below
    statement. I took off the _dbo and now it works:

    dgUYP.DataSource =
    > DsUYP1._dbo_prc_uyp_curriculum.DefaultView()


    thanks everyone!
    Machelle Chandler
    Intel Corporation
    Beginning .NET developer
    Thanks in advance for the help!

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Machelle Chandler, Oct 17, 2003
    #5
    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. raymond chiu
    Replies:
    1
    Views:
    5,684
    Tushar Agarwal
    Jan 21, 2006
  2. Mona
    Replies:
    2
    Views:
    664
    Rajesh Tiwari
    Jun 27, 2003
  3. =?Utf-8?B?SklNLkgu?=

    Q: populate ddl from stored procedure

    =?Utf-8?B?SklNLkgu?=, Apr 11, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    398
    Craig Deelsnyder
    Apr 11, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,295
    Mike P
    Jun 19, 2006
  5. Scott D

    Populate Datagrid from a Stored Procedure

    Scott D, Jul 1, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    161
    Scott D
    Jul 6, 2004
Loading...

Share This Page