DataGrid - Edit, Update, Delete

[drakuu]

Hello there,

I have DataGrid with some records and I would like to edit it right in
the datagrid using the built in commands.
I can't figure out a way to pass to the SQL query the record ID which
I'm editing.
SELECT:
SelectCommand="SELECT ProviderID, AddressID, Address, Address2, City,
State, County, Zip FROM ProviderAddress WHERE (ProviderID =
@ProviderID)"
<SelectParameters>
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</SelectParameters>
the above works fine.

DELETE:
DeleteCommand="DELETE FROM [ProviderAddress] WHERE ([AddressID] =
@AddressID) AND ([ProviderID] = @ProviderID)"
<DeleteParameters>
<asp:Parameter Name="AddressID" Type="Int32" />
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</DeleteParameters>
Doesn't do anything. Posts the page back to itself but no record is
effected.
When I add DefaultValue="xxx" to the parameter then it works so I know
that the AddressID parameter is incorrect.

UPDATE:
UpdateCommand="UPDATE [ProviderAddress] SET [Address] = @Address,
[Address2] = @Address2, [City] = @City, [State] = @State, [County] =
@County, [Zip] = @Zip WHERE ([AddressID] = @AddressID AND [ProviderID]
= @ProviderID)">
<UpdateParameters>
<asp:Parameter Name="Address" />
<asp:Parameter Name="Address2" />
<asp:Parameter Name="City" />
<asp:Parameter Name="State" />
<asp:Parameter Name="County" />
<asp:Parameter Name="Zip" />
<asp:Parameter Name="AddressID" />
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</UpdateParameters>
Doesn't work at all and returns SQL error: Must declare the variable
'@AddressID'.
Again, if I define the default value for AddressID then works fine.

QUESTION: How can I define the @Values (@AddressID) for the edited
record? I have the AddressID value displayed in the DataGrid to I
should be able to access it.

Thanks,
draku

[Phil]

On your gridview you need an onrowcommand event handler (ie.
onrowcommand="GridView1_RowCommand" for declaritve assignment). This will
fire when a command button (such as "Delete") is clicked. Then on you
"Delete" button you need to specify the commandargument attribute (ie.
commandargument='<%# Eval("SomeRecId") %>') and commandname (ie.
commandname="Delete"). This will be the value passed to the eventhandler.
In you code behind add a funcation for the event handler:

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
switch (e.CommandName)
{
case "Delete":
this.GridView1.DeleteRow(Convert.ToInt32(e.Command Argument));
break;
}
}

That should take care of your Delete row, I usually upate using a FormView,
so I'll have to dig up some code for that (unless someelse posts). It should
be possible to put the Update Routine (or call to it) in the
GridView1_RowCommand function and look for a different
commandname/commandargument.

Hope this helps.

-Phil

"dr****@gmail.com" wrote:

Hello there,

I have DataGrid with some records and I would like to edit it right in
the datagrid using the built in commands.
I can't figure out a way to pass to the SQL query the record ID which
I'm editing.
SELECT:
SelectCommand="SELECT ProviderID, AddressID, Address, Address2, City,
State, County, Zip FROM ProviderAddress WHERE (ProviderID =
@ProviderID)"
<SelectParameters>
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</SelectParameters>
the above works fine.

DELETE:
DeleteCommand="DELETE FROM [ProviderAddress] WHERE ([AddressID] =
@AddressID) AND ([ProviderID] = @ProviderID)"
<DeleteParameters>
<asp:Parameter Name="AddressID" Type="Int32" />
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</DeleteParameters>
Doesn't do anything. Posts the page back to itself but no record is
effected.
When I add DefaultValue="xxx" to the parameter then it works so I know
that the AddressID parameter is incorrect.

UPDATE:
UpdateCommand="UPDATE [ProviderAddress] SET [Address] = @Address,
[Address2] = @Address2, [City] = @City, [State] = @State, [County] =
@County, [Zip] = @Zip WHERE ([AddressID] = @AddressID AND [ProviderID]
= @ProviderID)">
<UpdateParameters>
<asp:Parameter Name="Address" />
<asp:Parameter Name="Address2" />
<asp:Parameter Name="City" />
<asp:Parameter Name="State" />
<asp:Parameter Name="County" />
<asp:Parameter Name="Zip" />
<asp:Parameter Name="AddressID" />
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</UpdateParameters>
Doesn't work at all and returns SQL error: Must declare the variable
'@AddressID'.
Again, if I define the default value for AddressID then works fine.

QUESTION: How can I define the @Values (@AddressID) for the edited
record? I have the AddressID value displayed in the DataGrid to I
should be able to access it.

Thanks,
draku

[Phil]

In your GridView you need to specify a couple of attributes on the Delete
button:
commandname="Delete"
commandargument='<%# Eval("SomeRecId")%>'

Also you need to declare an event handler for row commands on you GridView:
onrowcommand="GridView1_RowCommand"

Then create the handler in your codebehind:
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
switch (e.CommandName)
{
case "Delete":
this.GridView1.DeleteRow(Convert.ToInt32(e.Command Argument));
break;
}
}

Thus, when a command button is click on your GridView (Delete, Update
etc...) it will fire the GridView1_RowCommand routine, passsing in your
commandname and commandargument (holding your recid).

I'll have to dig up some Update code, as I usually use a FormView for my
updates. You should be able to follow the same principle and add an Update
routine and catch it in the RowCommand routine as you do the delete.

Hope this helps.

-Phil

"dr****@gmail.com" wrote:

Hello there,

I have DataGrid with some records and I would like to edit it right in
the datagrid using the built in commands.
I can't figure out a way to pass to the SQL query the record ID which
I'm editing.
SELECT:
SelectCommand="SELECT ProviderID, AddressID, Address, Address2, City,
State, County, Zip FROM ProviderAddress WHERE (ProviderID =
@ProviderID)"
<SelectParameters>
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</SelectParameters>
the above works fine.

DELETE:
DeleteCommand="DELETE FROM [ProviderAddress] WHERE ([AddressID] =
@AddressID) AND ([ProviderID] = @ProviderID)"
<DeleteParameters>
<asp:Parameter Name="AddressID" Type="Int32" />
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</DeleteParameters>
Doesn't do anything. Posts the page back to itself but no record is
effected.
When I add DefaultValue="xxx" to the parameter then it works so I know
that the AddressID parameter is incorrect.

UPDATE:
UpdateCommand="UPDATE [ProviderAddress] SET [Address] = @Address,
[Address2] = @Address2, [City] = @City, [State] = @State, [County] =
@County, [Zip] = @Zip WHERE ([AddressID] = @AddressID AND [ProviderID]
= @ProviderID)">
<UpdateParameters>
<asp:Parameter Name="Address" />
<asp:Parameter Name="Address2" />
<asp:Parameter Name="City" />
<asp:Parameter Name="State" />
<asp:Parameter Name="County" />
<asp:Parameter Name="Zip" />
<asp:Parameter Name="AddressID" />
<asp:SessionParameter Name="ProviderID" SessionField="PId" Type="Int32"
/>
</UpdateParameters>
Doesn't work at all and returns SQL error: Must declare the variable
'@AddressID'.
Again, if I define the default value for AddressID then works fine.

QUESTION: How can I define the @Values (@AddressID) for the edited
record? I have the AddressID value displayed in the DataGrid to I
should be able to access it.

Thanks,
draku

[drakuu]

Thanks for the replay Phil,
I believe that there is an easier method and I don't need to take it
into code behind. I just need to figure out a way to define the @values
for the datagrid parameters.
But if I won't find it soon I'l do it in code behind.

draku

[drakuu]

It was a BUG in VS2005 Beta 2 version. My code is perfectly fine.

draku