Database problems in ASP.NET app

[davearia]

Hi All,

 

I am writing a database ASP.NET app using SQL Server. I have a stored procesure as follows:

/*Select statement to populate textboxes in main app. Returns all fields*/

CREATE PROCEDURE dbo.SELECT_FROM_PIGSAWSUZZLE

@p1 INT

AS

SELECT 
	[PigJawSuzzleNumber], 
	[Title], 
	[Range], 
	[Artist], 
	[Manufacturer], 
	[size], 
	[NumberOfPieces], 
	[YearPainted], 
	[Price],
	[AdditionalInformation]
 FROM
	 PigJawSuzzle

WHERE
	 [PigJawSuzzleNumber] = @p1
GO

Here is the more relevant code

Private lnRowNumber As Int32 = 1
Private connectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
Private connection As New SqlConnection(connectionString)
Private selectCommand As New SqlCommand("SELECT_FROM_PIGSAWSUZZLE", connection)
Private sqlSelectAdapter As New SqlDataAdapter(selectCommand)
Private Sub refillDataset()
   sqlSelectAdapter.SelectCommand.CommandType =   CommandType.StoredProcedure
   sqlSelectAdapter.SelectCommand.Parameters.Add("@p1", System.Data.SqlDbType.Int)
   sqlSelectAdapter.SelectCommand.Parameters("@p1").Value = lnRowNumber
   Try
           ds.Clear()
           sqlSelectAdapter.Fill(ds, "PJS")
    Catch ex As Exception
           txtAdditionalInformation.Text += "Error in method refillDataset()" + ex.ToString
    End Try
End Sub

When I run this code I get this exception message

System.Data.SqlClient.SqlException: Procedure or function SELECT_FROM_PIGSAWSUZZLE has too many arguments specified.

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

at WebPigJawSuzzle.WebPigJawSuzzle.refillDataset() in e:\inetpub\wwwroot\WebPigJawSuzzle\WebPigJawSuzzle.aspx.vb:line 355System.IndexOutOfRangeException: Cannot find table 0.

at System.Data.DataTableCollection.get_Item(Int32 index)

at WebPigJawSuzzle.WebPigJawSuzzle.getData() in e:\inetpub\wwwroot\WebPigJawSuzzle\WebPigJawSuzzle.aspx.vb:line 368

When I step through with the debugger I can see that @p1 has been assigned the value 1 as required. But I cannot see what is wrong here.

 

Please help.

 

Thanks, Dave. :D

[eramgarden]

I'd say hardcode the value and see if that works. That might give u a better idea into what's happening.

 

Also, does the value work when u run the Stored Proc from the database?

[wessamzeidan]

Are you using sqlSelectAdapter.SelectCommand some where else?? If yes, clear its Parameters before adding the @p1 parameter to it.

[Optikal]

Or if you are calling refillDataSet() more than once, it will just keep adding parameters to the command, and never clear out the old ones...