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

[MdWebtress]

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

#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:

        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).

        '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:

CREATE PROCEDURE sp_multiSelect 
(
@Lower int,
@Upper int
)
AS

	SELECT *
	FROM MyLinks
	WHERE dbID >= @Lower and dbID <= @Upper

GO

[MdWebtress]

ok, I sort of got it working.

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:

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.

[Ribeyed]

Hi,
when using store procedures you also need to name the paramaters you want to out put.
Try this:

CREATE PROCEDURE sp_multiSelect 
(
@Lower int,
@Upper int
)
AS

	SELECT *
	FROM MyLinks
	WHERE dbID >= @Lower and dbID <= @Upper

RETURN @@Lower
RETURN @@Upper

[MdWebtress]

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.

[Ribeyed]

sorry,

CREATE PROCEDURE sp_multiSelect 
(
@Lower int,
@Upper int
)
AS

	SELECT *
	FROM MyLinks
	WHERE dbID >= @Lower and dbID <= @Upper

RETURN @@Lower int,
RETURN @@Upper int

[CardboardHammer]

Originally posted by Ribeyed
sorry,

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.

[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:

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

[CardboardHammer]

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:

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...

[Ribeyed]

Hi,
sorry

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

[CardboardHammer]

That doesn't do anything about her problem, which is that this

SqlDataAdapter1.Fill(DataSet11, SqlSelectCommand1.ExecuteNonQuery())

should be changed to this

SqlDataAdapter1.Fill(DataSet11)