Database problems in ASP.NET app

D

davearia

Well-known member
Joined
Jan 4, 2005
Messages
184
Hi All,

I am writing a database ASP.NET app using SQL Server. I have a stored procesure as follows:
Code: 
/*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
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
Click to expand...
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
 
Id say hardcode the value and see if that works. That might give u a better idea into whats happening.

Also, does the value work when u run the Stored Proc from the database?
 
Are you using sqlSelectAdapter.SelectCommand some where else?? If yes, clear its Parameters before adding the @p1 parameter to it.
 
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...
 
Back
Top