[RESOLVED] Gridview headache: Get column names when autogeneratecolumns=true?

[ferrethouse]

I'm getting frustrated with the Gridview control. I'm populating the Gridview like this...

dt = obj.getDataTable("SELECT * FROM tblsamplestrings")
gvTranslations.DataSource = dt
gvTranslations.DataBind()

I'm using "SELECT *" because tblsamplestrings will be variable and each table has different fields. So everything renders as expected. Now when the user clicks "Update" I need to know the column names so that I can construct an UPDATE statement. But I can't figure out how to get the column names and the column count property is ZERO (I'm guessing because the columns were autogenerated). So this is where I am stuck...

Protected Sub gvTranslations_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) Handles gvTranslations.RowUpdating
        Dim row As GridViewRow = gvTranslations.Rows(e.RowIndex)
        Dim columnCount As Integer = gvTranslations.Columns.Count
    End Sub

Any help would be much appreciated.

UDPATE: I managed to get the column names using a workaround. So now I'm stuck on the UPDATE statement.

I have this code...

Protected Sub gvTranslations_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) Handles gvTranslations.RowUpdating
        Dim y As Integer = 0
        Dim row As GridViewRow = gvTranslations.Rows(e.RowIndex)
        Dim obj As New ERSLib.Data.PublicFunc
        Dim sb As New StringBuilder
        
        sb.Append("UPDATE tblSampleStrings SET TYPE_ = '" & needNewValueHere & "'")
 
        obj.runSql(sb.ToString)
 
        obj.close()
        obj = Nothing
 
    End Sub

How do I fill the needNewValueHere variable with the appropriate value? I've tried everything I can think of. e.newvalues is empty. row.cells(e.rowindex) is empty. This is so frustrating

[ferrethouse]

Okay. I switched to using the sqldatasource rather than binding to a datatable. That seems to work better.

So I attached this UPDATE statement to my sqldatasource...

SqlDataSource1.UpdateCommand = "UPDATE tblsamplestrings SET Description = escription"

I'm connecting to Oracle from ODBC so I think the semicolon syntax is correct instead of the @ symbol. However, nothing happens. I don't get any errors but it doesn't update the description field. If I change the UPDATE statement to this...

SqlDataSource1.UpdateCommand = "UPDATE tblsamplestrings SET Description = 'HELLOWORLD'"

It works. So it must not be picking up the escription value. Is this not the correct syntax? In my sqldatasource I told it to automatically generate the fields.

Aren't there any gridview experts our there???

[zdavis]

I can post a little example of an update statement for a datagrid

[ferrethouse]

Okay. I'm providing the complete code. I am 99% there. Someone must know why escription is holding the old value and not the new one. The TIMESTAMP field is updating correctly and the WHERE clause is working. But it is updating with the old description value not the new one that I typed in the textbox.

HTML...

<asp:GridView AutoGenerateEditButton=True ID="gvTranslations" runat="server" AllowPaging=True DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Dsn=devenvdb;uid=envdb;pwd=manager" ProviderName="System.Data.Odbc"></asp:SqlDataSource>

Here is the vb...

SqlDataSource1.SelectCommand = "SELECT UNIQUEID, DESCRIPTION FROM tblsamplestrings"
SqlDataSource1.UpdateCommand = "UPDATE tblsamplestrings SET DESCRIPTION = :DESCRIPTION, TIMESTAMP = CURRENT_DATE WHERE UniqueID = :UNIQUEID"

[zdavis]

what are you setting the @description equal too???

[ferrethouse]

what are you setting the @description equal too???

I have to set it? I thought it was associated with the column and automatically populated.

How do I set it? I tried moving the UpdateCommand here...

Protected Sub gvTranslations_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) Handles gvTranslations.RowUpdating
SqlDataSource1.UpdateCommand = "UPDATE tblsamplestrings SET DESCRIPTION = :DESCRIPTION, TIMESTAMP = CURRENT_DATE WHERE UniqueID = :UNIQUEID"
gvTranslations.DataBind()
    End Sub

In the hopes that I could access e.NewValues but e.NewValues and e.OldValues are the same thing (they both have the value that was in that column before I hit edit and changed it). How do I get the new value that I typed into that textbox?

Thanks.

NOTE: I'm not setting :UNIQUEID but it is working correctly (because I don't edit it - so the old value is correct).

[ferrethouse]

I got it to work. I had to change two things...

1. I wasn't setting DateKeyNames property

2. I was calling DataBind method in page_load event. It doesn't like that.

From Microsoft...

You must set the DataKeyNames property for the automatic updating and deleting features of the GridView control to work. The values of these key fields are passed to the data source control in order to match the row to update or delete.