Delete row in GridView problem

William LaMartin · Jun 10, 2008

On webform, I am populating a GridView from a SQLDatasource based on a MySQL
table named PIB.

There is no vb code involved. Everything is done in the source for the aspx
page, provided below. The update works fine, but a delete of a row produces
the following error:

Exception Details: System.Data.Odbc.OdbcException: ERROR [07001]
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27]SQLBindParameter not used for all
parameters

The table, PIB, involved has a primary key , Id, and I have worked on this
for some time with no solution. Does anyone know the answer?

Thanks

<body>
<form id="form1" runat="server">

<asp:TextBox ID="txtUid" runat="server" Style="z-index: 100; left: 14px;
position: absolute; top: 96px">JoeTestor</asp:TextBox>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:MysoftwareConnectionString %>"

ProviderName="<%$ ConnectionStrings:MysoftwareConnectionString.ProviderName
%>"

SelectCommand="Select * from PIB where Uid=?"

UpdateCommand="UPDATE PIB SET Description = ?, Lat = ?, Link = ?, Lon = ?,
Map = ?, Title = ?, Uid = ? WHERE Id = ?"

DeleteCommand ="DELETE FROM PIB WHERE Id=?">

<SelectParameters>

<asp:ControlParameter ControlID="txtUid" Name="?" PropertyName="Text" />

</SelectParameters>

<UpdateParameters>

<asp

arameter Name="Description" Type="String"/>

<asp

arameter Name="Lat" Type="String" />

<asp

arameter Name="Link" Type="String" />

<asp

arameter Name="Lon" Type="String" />

<asp

arameter Name="Map" Type="String" />

<asp

arameter Name="Title" Type="String" />

<asp

arameter Name="Uid" Type="String" />

</UpdateParameters>

<DeleteParameters>

<asp

arameter Name="Id" Type="Int32"/>

</DeleteParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateDeleteButton="True"

AutoGenerateEditButton="True" DataSourceID="SqlDataSource1" Style="z-index:
102;

left: 5px; position: absolute; top: 149px">

</asp:GridView>

</form>

</body>

Manish · Jun 11, 2008

Hi William,

I tried the following code and it works fine. Please make sure your Primary
key is set as property to DataKeyNames property of GridView control.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
DeleteCommand="DELETE FROM [MasterTable] WHERE [num] = @original_num AND
[fname] = @original_fname"
InsertCommand="INSERT INTO [MasterTable] ([fname]) VALUES
(@fname)" OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [MasterTable]"
UpdateCommand="UPDATE [MasterTable] SET [fname] = @fname WHERE [num] =
@original_num AND [fname] = @original_fname">
<DeleteParameters>
<asp

arameter Name="original_num" Type="Int32" />
<asp

arameter Name="original_fname" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp

arameter Name="fname" Type="String" />
<asp

arameter Name="original_num" Type="Int32" />
<asp

arameter Name="original_fname" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp

arameter Name="fname" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

</div>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="num"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="num" HeaderText="num"
InsertVisible="False" ReadOnly="True"
SortExpression="num" />
<asp:BoundField DataField="fname" HeaderText="fname"
SortExpression="fname" />
<asp:CommandField ShowDeleteButton="True"
ShowEditButton="True" />
</Columns>
</asp:GridView>

Hope this helps.

Regards,
Manish
www.ComponentOne.com

William LaMartin said:
On webform, I am populating a GridView from a SQLDatasource based on a MySQL
table named PIB.

There is no vb code involved. Everything is done in the source for the aspx
page, provided below. The update works fine, but a delete of a row produces
the following error:

Exception Details: System.Data.Odbc.OdbcException: ERROR [07001]
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27]SQLBindParameter not used for all
parameters

The table, PIB, involved has a primary key , Id, and I have worked on this
for some time with no solution. Does anyone know the answer?

Thanks

<body>
<form id="form1" runat="server">

<asp:TextBox ID="txtUid" runat="server" Style="z-index: 100; left: 14px;
position: absolute; top: 96px">JoeTestor</asp:TextBox>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:MysoftwareConnectionString %>"

ProviderName="<%$ ConnectionStrings:MysoftwareConnectionString.ProviderName
%>"

SelectCommand="Select * from PIB where Uid=?"

UpdateCommand="UPDATE PIB SET Description = ?, Lat = ?, Link = ?, Lon = ?,
Map = ?, Title = ?, Uid = ? WHERE Id = ?"

DeleteCommand ="DELETE FROM PIB WHERE Id=?">

<SelectParameters>

<asp:ControlParameter ControlID="txtUid" Name="?" PropertyName="Text" />

</SelectParameters>

<UpdateParameters>

<asparameter Name="Description" Type="String"/>

<asparameter Name="Lat" Type="String" />

<asparameter Name="Link" Type="String" />

<asparameter Name="Lon" Type="String" />

<asparameter Name="Map" Type="String" />

<asparameter Name="Title" Type="String" />

<asparameter Name="Uid" Type="String" />

</UpdateParameters>

<DeleteParameters>

<asparameter Name="Id" Type="Int32"/>

</DeleteParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateDeleteButton="True"

AutoGenerateEditButton="True" DataSourceID="SqlDataSource1" Style="z-index:
102;

left: 5px; position: absolute; top: 149px">

</asp:GridView>

</form>

</body>

William LaMartin · Jun 12, 2008

Thanks for the reply. I tried to reply to this posting yesterday, but the
message was bounced back to me, so I will try again.

I actually got my code to work by removing all reference to the
deleteparameters section:

<DeleteParameters>
<asp

arameter Name="Id" Type="Int32"/>
</DeleteParameters>

Manish said:
Hi William,

I tried the following code and it works fine. Please make sure your
Primary
key is set as property to DataKeyNames property of GridView control.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:TestConnectionString
%>"
DeleteCommand="DELETE FROM [MasterTable] WHERE [num] = @original_num AND
[fname] = @original_fname"
InsertCommand="INSERT INTO [MasterTable] ([fname]) VALUES
(@fname)" OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [MasterTable]"
UpdateCommand="UPDATE [MasterTable] SET [fname] = @fname WHERE [num] =
@original_num AND [fname] = @original_fname">
<DeleteParameters>
<asparameter Name="original_num" Type="Int32" />
<asparameter Name="original_fname" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="fname" Type="String" />
<asparameter Name="original_num" Type="Int32" />
<asparameter Name="original_fname" Type="String" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="fname" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

</div>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="num"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="num" HeaderText="num"
InsertVisible="False" ReadOnly="True"
SortExpression="num" />
<asp:BoundField DataField="fname" HeaderText="fname"
SortExpression="fname" />
<asp:CommandField ShowDeleteButton="True"
ShowEditButton="True" />
</Columns>
</asp:GridView>

Hope this helps.

Regards,
Manish
www.ComponentOne.com

William LaMartin said:

On webform, I am populating a GridView from a SQLDatasource based on a
MySQL
table named PIB.

There is no vb code involved. Everything is done in the source for the
aspx
page, provided below. The update works fine, but a delete of a row
produces
the following error:

Exception Details: System.Data.Odbc.OdbcException: ERROR [07001]
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27]SQLBindParameter not used for all
parameters

The table, PIB, involved has a primary key , Id, and I have worked on
this
for some time with no solution. Does anyone know the answer?

Thanks

<body>
<form id="form1" runat="server">

<asp:TextBox ID="txtUid" runat="server" Style="z-index: 100; left: 14px;
position: absolute; top: 96px">JoeTestor</asp:TextBox>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:MysoftwareConnectionString %>"

ProviderName="<%$
ConnectionStrings:MysoftwareConnectionString.ProviderName
%>"

SelectCommand="Select * from PIB where Uid=?"

UpdateCommand="UPDATE PIB SET Description = ?, Lat = ?, Link = ?, Lon =
?,
Map = ?, Title = ?, Uid = ? WHERE Id = ?"

DeleteCommand ="DELETE FROM PIB WHERE Id=?">

<SelectParameters>

<asp:ControlParameter ControlID="txtUid" Name="?" PropertyName="Text" />

</SelectParameters>

<UpdateParameters>

<asparameter Name="Description" Type="String"/>

<asparameter Name="Lat" Type="String" />

<asparameter Name="Link" Type="String" />

<asparameter Name="Lon" Type="String" />

<asparameter Name="Map" Type="String" />

<asparameter Name="Title" Type="String" />

<asparameter Name="Uid" Type="String" />

</UpdateParameters>

<DeleteParameters>

<asparameter Name="Id" Type="Int32"/>

</DeleteParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateDeleteButton="True"

AutoGenerateEditButton="True" DataSourceID="SqlDataSource1"
Style="z-index:
102;

left: 5px; position: absolute; top: 149px">

</asp:GridView>

</form>

</body>

Click to expand...

Anetta Hayat · Oct 4, 2010

Thanks a Lot. It was helpful.

On webform, I am populating a GridView from a SQLDatasource based on a MySQL
table named PIB.

There is no vb code involved. Everything is done in the source for the aspx
page, provided below. The update works fine, but a delete of a row produces
the following error:

Exception Details: System.Data.Odbc.OdbcException: ERROR [07001]
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27]SQLBindParameter not used for all
parameters

The table, PIB, involved has a primary key , Id, and I have worked on this
for some time with no solution. Does anyone know the answer?

Thanks

<body>
<form id="form1" runat="server">

<asp:TextBox ID="txtUid" runat="server" Style="z-index: 100; left: 14px;
position: absolute; top: 96px">JoeTestor</asp:TextBox>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:MysoftwareConnectionString %>"

ProviderName="<%$ ConnectionStrings:MysoftwareConnectionString.ProviderName
%>"

SelectCommand="Select * from PIB where Uid=?"

UpdateCommand="UPDATE PIB SET Description = ?, Lat = ?, Link = ?, Lon = ?,
Map = ?, Title = ?, Uid = ? WHERE Id = ?"

DeleteCommand ="DELETE FROM PIB WHERE Id=?">

<SelectParameters>

<asp:ControlParameter ControlID="txtUid" Name="?" PropertyName="Text" />

</SelectParameters>

<UpdateParameters>

<asparameter Name="Description" Type="String"/>

<asparameter Name="Lat" Type="String" />

<asparameter Name="Link" Type="String" />

<asparameter Name="Lon" Type="String" />

<asparameter Name="Map" Type="String" />

<asparameter Name="Title" Type="String" />

<asparameter Name="Uid" Type="String" />

</UpdateParameters>

<DeleteParameters>

<asparameter Name="Id" Type="Int32"/>

</DeleteParameters>

</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateDeleteButton="True"

AutoGenerateEditButton="True" DataSourceID="SqlDataSource1" Style="z-index:
102;

left: 5px; position: absolute; top: 149px">

</asp:GridView>

</form>

</body>

On Wednesday, June 11, 2008 1:56 AM Manis wrote:
Hi William,

I tried the following code and it works fine. Please make sure your Primary
key is set as property to DataKeyNames property of GridView control.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
DeleteCommand="DELETE FROM [MasterTable] WHERE [num] = @original_num AND
[fname] = @original_fname"
InsertCommand="INSERT INTO [MasterTable] ([fname]) VALUES
(@fname)" OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [MasterTable]"
UpdateCommand="UPDATE [MasterTable] SET [fname] = @fname WHERE [num] =
@original_num AND [fname] = @original_fname">
<DeleteParameters>
<asparameter Name="original_num" Type="Int32" />
<asparameter Name="original_fname" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asparameter Name="fname" Type="String" />
<asparameter Name="original_num" Type="Int32" />
<asparameter Name="original_fname" Type="String" />
</UpdateParameters>
<InsertParameters>
<asparameter Name="fname" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

</div>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="num"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="num" HeaderText="num"
InsertVisible="False" ReadOnly="True"
SortExpression="num" />
<asp:BoundField DataField="fname" HeaderText="fname"
SortExpression="fname" />
<asp:CommandField ShowDeleteButton="True"
ShowEditButton="True" />
</Columns>
</asp:GridView>

Hope this helps.

Regards,
Manish
www.ComponentOne.com

"William LaMartin" wrote:

Click to expand...

Delete row in GridView problem

William LaMartin

Manish

William LaMartin

Anetta Hayat