Passing a Parameter to an SQL query

[ras2k2]

Hi I am trying a pass a parameter to a query so that when the page loads the information that is loaded is dependant on the passed parameter.

This is the code that is supposed to pass the parameter ID to the SQl query :


If Not Page.IsPostBack Then
If Request.QueryString("Id") Is Nothing Then Response.Redirect("addsections.aspx")

Else
Dim Id As Integer Id = Request.QueryString("Id")
PageIdLabel.Text = Id

SqlSelectCommand1.Parameters.Add("@id", Id)
SqlDataAdapter1.Fill(DataSet11) This is where the error kicks out

This is then the query that is supposed to use the parameter :

SELECT Sections.*
FROM Sections
WHERE (Id =@id)

The Error message I recieve is :
The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.

I am guessing this is because I have used wrong Syntax somewhere, but i am new to vb.net as well as asp.net and I have to clue whats wrong or whats missing.

Thanks for anyhelp

R Scott

 

[hadinatayp]

try this,

dim parameter as sqlParameter

Parameter = Command.Parameters.Add("@variable_name", variable_type,length)
Parameter.Value = value

 

[TechGnome]

You'fe getting that error because you're adding @ID more than once..... can you post the entire sub? And/Or how you are calling it? And also (if it's in a different area) how you are setting up the SQL command?

-tg

 

[ras2k2]

Before I post code I am going to explain how I have the commands, the sql commands are managed within a data adapter and the parameters are added within that data adapter, i simply have to assign values to those paramters.

I am now going to post a subroutine, this routine is called when the delete button is clicked on the datagrtid.

*********************************************************

PublicSub Grid_Command(ByVal source AsObject, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
If e.CommandName = "DeleteCommand" Then
Dim index AsInteger
'get the id of the selected row in order to delete the correct row from the datagrid
index = e.Item.Cells(2).Text
'pass parameterts to the sql delete command
PreEditDocAdapter.DeleteCommand.Parameters("@Id").Value = index
PreEditDocAdapter.DeleteCommand.Parameters("@deleted").Value = True
'open sql connection
SqlConnection1.Open()
'execute the delete command
PreEditDocAdapter.DeleteCommand.ExecuteNonQuery()
'close the connection
SqlConnection1.Close()
'Clear Dataset
PreEditDocDataSet1.Clear()
'Fill dataset with updated values
PreEditDocAdapter.Fill(PreEditDocDataSet1)
'Bind datagrid
DataGrid1.CurrentPageIndex = 0
Label1.Text = DataGrid1.PageCount
DataGrid1.DataBind()
EndIf


EndSub
*********************************************************
I also have this subroutine which only executes when the page is changed on the datagrid.


Sub PageChanger(ByVal Source AsObject, ByVal E As DataGridPageChangedEventArgs)
' Set the CurrentPageIndex before binding the grid
PreEditDocAdapter.Fill(PreEditDocDataSet1)
DataGrid1.CurrentPageIndex = E.NewPageIndex
pagecount = E.NewPageIndex
DataGrid1.DataBind()
EndSub
*********************************************************

As far as I can tell the problem only occurs when I delete the last row of data of the last page of the datagrid, if I set the datagrid page index to 0 like in the subroutine I have posted the error does not occur, however whenever the user deletes data they are they are always redirected to the first page of the datagrid.