none
Must declare the scalar variable @id

    Question

  • I am getting must declare the scalar variable but it is declared below

    Can someone tell me what's wrong with this code. I need to be able to delete from the Gridview.

    thanks

     

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim s As New SqlDataSource("datasourceinfo", "select * from table")

    s.DeleteCommand = "DELETE FROM [table] WHERE [id] = @id "

    s.DeleteParameters.Add("id", "TypeCode.Int16")

    s.ID = "sqldtsource"

    Page.Form.Controls.Add(s)

    Dim g As New GridView

    g.AllowPaging = True

    g.AllowSorting = True

    g.AutoGenerateDeleteButton = True

    g.DataSourceID = "sqldtsource"

    Page.Form.Controls.Add(g)

    End Sub

    Friday, February 15, 2008 6:22 AM

Answers

  • I am sorry if you are confused but here are two fixes one with the strange reason for the error, web gridview is not simple.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=263853&SiteID=1


    http://fabdata.wordpress.com/2007/03/23/must-declare-the-scalar-variable-id/

     

     

     

    Sunday, February 17, 2008 7:49 PM
  • I don't see it. Also, is your datakeynames set to ID on the gridview?

     

    Adam

    Sunday, February 17, 2008 9:30 PM
  • The second link explained that the DataKeyNames property was required to be set in order to have this working.

    I did that and the code is working fine now. I can delete rows and sort and page as well.

    The key is to make sure you define DataKeyNames in the gridview. It does take and array of strings as the parameter.

    Below is the sample code that worked for me.

    Thanks for your help.

     

     

    Dim s As New SqlDataSource("Data Source=servername;Initial Catalog=dbname;Persist Security Info=False;User ID=userid;Password=password", "select * from table")

    s.DeleteCommand = "DELETE FROM [table] WHERE [ObjectID] = @id"

    Dim p As New Parameter("id", TypeCode.Int16)

    s.DeleteParameters.Add(p)

    Dim param(0) As String

    param(0) = "id"

    s.ID = "sqldatasource1"

    Dim g As New GridView

    g.DataKeyNames = param

    g.AllowPaging = True

    g.AllowSorting = True

    g.AutoGenerateDeleteButton = True

    g.DataSourceID = "sqldatasource1"

    Page.Form.Controls.Add(s)

    Page.Form.Controls.Add(g)

     

    Sunday, February 17, 2008 10:15 PM

All replies

  • Hello,

     

    the name of the parameter is @id and not id which is the name of the column in the table

    It will be better with :

    s.DeleteParameters.Add("@id", "TypeCode.Int16")

     

    I am not a specialist for web but i have not seen link between the deleteparameter name and from where is coming the value of this parameter

     

    Have a nice day

    Friday, February 15, 2008 7:37 AM
  • I tried that and it's not working. Same error message.

     

     

     

    Friday, February 15, 2008 10:54 PM
  • Try this for working code pay attention to the code because somethings as you know it are changed to make the code work for the web.

     

    http://www.asp.net/learn/data-access/tutorial-50-vb.aspx

     

    Saturday, February 16, 2008 12:44 AM
  • The tutorial is no help.

    I am trying to do this in a dynamic approach.

    It's very simple but it's not working.

     

     

    Sunday, February 17, 2008 5:21 AM
  • (g.AllowPaging = True

    g.AllowSorting = True)

     

    I knew you will come back to say this  but  the above code with delete in gridview means what you are doing is not simple, you have to be Scott who wrote that article to do something like that.  Here is one doing sorting and paging without data source.  If the code below is not what you want then you need to change to DataList.

     

    http://forums.asp.net/t/956540.aspx?PageIndex=1

     

    Sunday, February 17, 2008 3:47 PM
  • My example is using a sqldatasource which is what I want to accomplish. The update on the datasource is working when I set the UpdateCommand to a sql statement. It should also work for the delete method. The only defined requirement for a delete to work is to add a parameter which I am doing in the example. So my question again why is this not working?

    I am using this in a dynamic approach which is what I want to accomplish and nothing less.

     

    Sunday, February 17, 2008 6:24 PM
  • (The update on the datasource is working when I set the UpdateCommand to a sql statement. It should also work for the delete method.)

     

    The answer is no because you are using Asp.net gridview which does not work as expected you use existing code from experts like Scott.  If you are doing update, delete, sorting and paging you need to use Scott's code because few people knows how to get all of the above to work and definitely not with few lines of code.  It can be dynamic but it takes many line of code.  Why is your code not working? you are writing Winform datagrid code for webform both are not related.

     

    Sunday, February 17, 2008 6:34 PM
  • I don't understand your response. You are not making any sense. I am not using code from Scott and I don't want to use his code period. I am using this in a simple approach. If you can't explain why it's not working then that's fine.

    I am getting the error which is the variable must be declared. It is declared and added to the sqldatasource in my code.

    There must be someone who has done this before. This is really a simple question.

     

    Sunday, February 17, 2008 6:46 PM
  • Your code is fine. The parameter declaration is incorrect:

     

    Code Snippet

    s.DeleteCommand = "DELETE FROM [table] WHERE [id] = @id "

    s.DeleteParameters.Add("@id", "id")

     

     

     

    Adam

    Sunday, February 17, 2008 6:52 PM
  • I am sorry if you are confused but here are two fixes one with the strange reason for the error, web gridview is not simple.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=263853&SiteID=1


    http://fabdata.wordpress.com/2007/03/23/must-declare-the-scalar-variable-id/

     

     

     

    Sunday, February 17, 2008 7:49 PM
  • Tried that already and it's not working.

    Loop at the top of the discussion.

    Sunday, February 17, 2008 9:26 PM
  • I don't see it. Also, is your datakeynames set to ID on the gridview?

     

    Adam

    Sunday, February 17, 2008 9:30 PM
  • The second link explained that the DataKeyNames property was required to be set in order to have this working.

    I did that and the code is working fine now. I can delete rows and sort and page as well.

    The key is to make sure you define DataKeyNames in the gridview. It does take and array of strings as the parameter.

    Below is the sample code that worked for me.

    Thanks for your help.

     

     

    Dim s As New SqlDataSource("Data Source=servername;Initial Catalog=dbname;Persist Security Info=False;User ID=userid;Password=password", "select * from table")

    s.DeleteCommand = "DELETE FROM [table] WHERE [ObjectID] = @id"

    Dim p As New Parameter("id", TypeCode.Int16)

    s.DeleteParameters.Add(p)

    Dim param(0) As String

    param(0) = "id"

    s.ID = "sqldatasource1"

    Dim g As New GridView

    g.DataKeyNames = param

    g.AllowPaging = True

    g.AllowSorting = True

    g.AutoGenerateDeleteButton = True

    g.DataSourceID = "sqldatasource1"

    Page.Form.Controls.Add(s)

    Page.Form.Controls.Add(g)

     

    Sunday, February 17, 2008 10:15 PM
  • I found this problem also.

    Search in here.

    I like to share what I just know.
    Even I am not so sure it's the same problem or not (ofcourse same error message)

    For me the problem is I create the one Insert into query by using the Querytableadaptor.
    And I use select to get data from other table filter with where cluse.
    And try to put the parameter.

    But the parameter is not show in my Insert query.
    So when I write the code it's not show the parameter.

    And I try to make the parameter in that query by using the properties of this query.
    There are parameter Collection.
    I just put the parameter what I use in query.
    The error is occure in that step.

    So I make the same name for all Parameter Name(in Properties parameter Collection), Veriable name I want to send (Inside my vb code),Parameter inside Qury (SQL Statement). And the problem is solve.

    Please take care to make the same data type in Propterties Parameter Collection.

    I hope you will under stand these code.
    Tuesday, July 22, 2008 5:35 PM
  • please check if your gridview DataKeyNames is assigned

    if not go to your page .aspx

    and add DataKeyNames="ID"

    ID meens your primary key to the GridView

     

     

    <asp:GridView ID="GridView3" runat="server" AllowPaging="True" AutoGenerateColumns="False"

    CellPadding="4" DataSourceID="SqlDataSource1" DataKeyNames="ID" ...........

     

    Hope this will be helpfull........

     

     

    Thursday, October 30, 2008 8:46 AM
  • There are two reasons:

    1-Do not forget to declare @id
    2-Inside for example ExecuteNonQuery() do not forget to add parameter variable inside.
    Take a look at my sample:


                    Dim dtNow As Date
                    Dim params(7) As SqlClient.SqlParameter

                    dtNow = Now()

                  1-  sSQL = "Insert Object (ObjectTypeID, Name, Description, Category, ModifiedBy, ModifiedDate,sData1,sData2)   Values (@TypeID, @Name, @Description, @Category, @ModifiedBy, @ModifiedDate,@Data1,@Data2)"

                  2-  params(0) = New SqlClient.SqlParameter("@TypeID", lngTypeID)
                    params(1) = New SqlClient.SqlParameter("@Name", strName)
                    params(2) = New SqlClient.SqlParameter("@Description", strDesc)
                    params(3) = New SqlClient.SqlParameter("@Category", strCategory)
                    params(4) = New SqlClient.SqlParameter("@ModifiedBy", GetUserName())
                    params(5) = New SqlClient.SqlParameter("@ModifiedDate", dtNow)
                    params(6) = New SqlClient.SqlParameter("@Data1", strData1)
                    params(7) = New SqlClient.SqlParameter("@Data2", strData2)

                  3-  SQLHelper.ExecuteNonQuery(Config.AppSettings("SQLCONN"), CommandType.Text, sSQL, params)

    -If you forgot to add params inside ExecuteNonQuery you will get error " must declare scalar variable ..."
    -If put sSQL(1) and put it after 2 you will get the same error.

    Just add to response collections.

    Talley Ouro
    Friday, August 07, 2009 3:42 AM
  • Hi all,

    I had the same problem as you (must declare scalar) and found the solution by Roudi_H to be working (and really simple). No difficult parameters required, just added  DataKeyNames="ID" to the gridview and used the deletecommand as follows : DeleteCommand="DELETE birthday Where birthdayID = @birthdayID"

    Thanks,

    Jan
    Wednesday, October 21, 2009 8:59 AM
  • I came across something that stated the ADO.net no longer uses parameter names with the @ sign in them and that they have been replaces by the ?.  I use something more like this now:

    "DELETE FROM [table] WHERE [id] = ?"

    s.DeleteParameters.Add("id", "TypeCode.Int16")

    s.value = 4567

    s.delete

    This seems to work for me.

    Friday, June 01, 2012 11:49 PM