www.webdeveloper.com
Results 1 to 10 of 10

Thread: What am I doing wrong? (ASP.NET question)

  1. #1
    Join Date
    Jul 2004
    Posts
    43

    What am I doing wrong? (ASP.NET question)

    I'm using an SQLDataAdapter that uses a stored procedure. In my aspx.vb code, this was generated in the region:

    Code:
    #Region " Web Form Designer Generated Code "
    
        'This call is required by the Web Form Designer.
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
            Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
            Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
            Me.DataSet11 = New ReadFromStoredProcedure.DataSet1
            CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
            '
            'SqlDataAdapter1
            '
            Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
            Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "sp_multiSelect", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("dbID", "dbID"), New System.Data.Common.DataColumnMapping("Title", "Title"), New System.Data.Common.DataColumnMapping("URL", "URL")})})
            '
            'SqlSelectCommand1
            '
            Me.SqlSelectCommand1.CommandText = "[sp_multiSelect]"
            Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
            Me.SqlSelectCommand1.Connection = Me.SqlConnection1
            Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Lower", System.Data.SqlDbType.Int, 4))
            Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Upper", System.Data.SqlDbType.Int, 4))
    .
    .
    .
    I have this in my page load:

    Code:
            Dim cmd As New SqlCommand(SqlSelectCommand1.CommandText, SqlConnection1)
            SqlConnection1.Open()
    
    
            'SqlSelectCommand1.Parameters.Add("@Lower", 2)
            'SqlSelectCommand1.Parameters.Add(New SqlParameter("@Lower", 2))
            SqlSelectCommand1.Parameters("@Lower").Value = 2
    
            SqlSelectCommand1.Parameters.Add("@Upper", 5)
            SqlDataAdapter1.Fill(DataSet11, cmd.ExecuteNonQuery)
            SqlConnection1.Close()
    
            ' txtSearch.Text = DataSet11.Tables("MyLinks").Rows(2).Item("dbID")
            ' txtGetWebpage.Text = DataSet11.Tables("MyLinks").Rows(2).Item("Title")
            ' txtGetURL.Text = DataSet11.Tables("MyLinks").Rows(2).Item("URL")
    The problem is that each time I run it, it gives this error:
    Procedure 'sp_multiSelect' expects parameter '@Lower', which was not supplied.
    I tried each of the following 3 ways to enter a parameter (I've read these on other sites).

    Code:
            'SqlSelectCommand1.Parameters.Add("@Lower", 2)
            'SqlSelectCommand1.Parameters.Add(New SqlParameter("@Lower", 2))
            SqlSelectCommand1.Parameters("@Lower").Value = 2
    None seem to be working.

    I also tried this:

    cmd.Parameters.Add("@Lower", 3)
    cmd.Parameters.Add("@Upper", 5)

    and I get this error: Line 1: Incorrect syntax near 'sp_multiSelect'.

    and this...

    SqlSelectCommand1.Parameters.Add("@Lower", 3, "@Upper", 6)
    and got: Input string was not in a correct format.


    This is my stored procedure that I use as a select statement when I ran the sqlDataAdapter wizard:

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper int
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    
    GO
    Last edited by MdWebtress; 11-01-2004 at 02:16 PM.

  2. #2
    Join Date
    Jul 2004
    Posts
    43
    ok, I sort of got it working.

    Code:
    SqlSelectCommand1.Connection = Me.SqlConnection1
    
            SqlConnection1.Open()
    
            SqlSelectCommand1.Parameters("@Lower").Value = 3
            SqlSelectCommand1.Parameters("@Upper").Value = 5
            SqlDataAdapter1.Fill(DataSet11, SqlSelectCommand1.ExecuteNonQuery())
    
            SqlConnection1.Close()
    This seems to run, but when I use this code:

    Code:
    txtSearch.Text = DataSet11.Tables("MyLinks").Rows(0).Item("dbID")
            txtGetWebpage.Text = DataSet11.Tables("MyLinks").Rows(0).Item("Title")
            txtGetURL.Text = DataSet11.Tables("MyLinks").Rows(0).Item("URL")
    I get this error message: Object reference not set to an instance of an object.

  3. #3
    Join Date
    Jan 2003
    Location
    Dundee, Scotland
    Posts
    1,367
    Hi,
    when using store procedures you also need to name the paramaters you want to out put.
    Try this:

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper int
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    
    RETURN @@Lower
    RETURN @@Upper
    ----------------------------------

  4. #4
    Join Date
    Jul 2004
    Posts
    43
    When I put in @@Lower and @@Upper, it says I must declare the variables @@Lower and @@Upper. If I just use @Lower and @Upper, I still get the "Object reference not set to an instance of an object." error.

  5. #5
    Join Date
    Jan 2003
    Location
    Dundee, Scotland
    Posts
    1,367
    sorry,

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper int
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    
    RETURN @@Lower int,
    RETURN @@Upper int
    ----------------------------------

  6. #6
    Join Date
    Nov 2003
    Posts
    655
    Originally posted by Ribeyed
    sorry,

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper int
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    
    RETURN @@Lower int,
    RETURN @@Upper int
    Ummm... what version of SQL Server are you using that lets you RETURN more than 1 value...? Beyond that, why would anyone want to RETURN the exact same value that they passed in in the first place?

    The actual problem: (from MSDN) "Although the ExecuteNonQuery does not return any rows..." The data set is empty. Row 0 does not exist.

  7. #7
    Join Date
    Jan 2003
    Location
    Dundee, Scotland
    Posts
    1,367
    Hi,
    You are trying to retrieve records between lower and upper values. You have to declare the input and output parameters in your SQL Procedure. You have 2 input parameters:

    @Lower,
    @Upper

    You want to out put the fields from the record set so you need the parameters:




    So your SQl Procedure should look like this:

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper intí
    @dbID int,
    @Title varchar,
    @URL varchar
    
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    
    RETURN @@dbID int,
    RETURN @@Title varchar,
    RETURN @@URL varchar
    ----------------------------------

  8. #8
    Join Date
    Nov 2003
    Posts
    655
    Originally posted by Ribeyed
    Hi,
    You are trying to retrieve records between lower and upper values. You have to declare the input and output parameters in your SQL Procedure. You have 2 input parameters:

    @Lower,
    @Upper

    You want to out put the fields from the record set so you need the parameters:




    So your SQl Procedure should look like this:

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper intí
    @dbID int,
    @Title varchar,
    @URL varchar
    
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    
    RETURN @@dbID int,
    RETURN @@Title varchar,
    RETURN @@URL varchar
    WTF? That is wrong in so many ways...

  9. #9
    Join Date
    Jan 2003
    Location
    Dundee, Scotland
    Posts
    1,367
    Hi,
    sorry

    Code:
    CREATE PROCEDURE sp_multiSelect 
    (
    @Lower int,
    @Upper int,
    @dbID int OUTPUT,
    @Title varchar OUTPUT,
    @URL varchar OUTPUT
    
    )
    AS
    
    	SELECT *
    	FROM MyLinks
    	WHERE dbID >= @Lower and dbID <= @Upper
    ----------------------------------

  10. #10
    Join Date
    Nov 2003
    Posts
    655
    That doesn't do anything about her problem, which is that this
    Code:
    SqlDataAdapter1.Fill(DataSet11, SqlSelectCommand1.ExecuteNonQuery())
    should be changed to this
    Code:
    SqlDataAdapter1.Fill(DataSet11)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles