Entering each row of datagrid into SQL database

Question

I'm attempting to take a column from each row of my datagrid and enter it into an SQL database. Having all sorts of problems/errors making it work.

This is the most current version:

For i = 0 to agentGridView.Rows.Count - 1
    varAgt = agentGridView.Rows(i).Cells(1).Text

    strSQL = "INSERT INTO tblAgentVisitAgents (VisitID, AgtID)" & _
        " Values (" & _
        VisitID.Text & "," & _
        varAgt & ")"
    strSQL = Utils.replaceChars(strSQL)
    DAL.ExecNonQuery(strSQL, CommandType.Text)
Next

EDIT: The issue is that my cell(1) is a hidden field. When I make it visible, the entry form works. When it's hidden, it won't enter anything and thus gives me a syntax error. Is there a way I can use a hidden field for entry purposes?

Arindam Nayak · Answer 1 · 2014-10-21T17:30:48.877

1

You need to use Text instead of Value . Like following.

varAgt = agentGridView.Rows(i).Cells(1).Text instead of varAgt = agentGridView.Rows(i).Cells(1).Value

But if you have that control in a Label, then you need to typecast to label and then use Text. E.g.

varAgt = CType(agentGridView.Rows(i).Cells(1).FindControl("controlID"),Label).Text -- replace controllID with required label id.

Source - http://www.aspsnippets.com/Articles/How-to-get-Selected-Row-cell-value-from-GridView-in-ASPNet.aspx

edited Oct 21 '14 at 17:30

answered Oct 21 '14 at 16:35

Arindam Nayak

7,346
4
32
48

This worked. However, the issue is now that my cell(1) is a hidden field that I want to use. Showing it makes it work. Hiding it throws a syntax error. – niclake Oct 21 '14 at 17:27
Checked your updated question, my reply = when you make it hidden , by `visible = false` , there will not be any element added to HTML, so you won't be able to get by `cells(1)`, my suggestion would be to make it just hidden by setting attribute `display:none`, then only you will be able to get it using code.\ – Arindam Nayak Oct 21 '14 at 17:34
1

Added my own answer, but yours helped me get there. Thank you very much. – niclake Oct 21 '14 at 17:43

score 0 · Accepted Answer · edited May 23 '17 at 11:49

Here's what I went with:

Front:

<asp:GridView ID="agentGridView" DataKeyNames="agentName,agentValue" ... />

Back:

For i = 0 to agentGridView.Rows.Count - 1
    varAgt = agentGridView.DataKeys(i).Values("agentValue").ToString

    strSQL = "INSERT INTO tblAgentVisitAgents (VisitID, AgtID)" & _
        " Values (" & _
        VisitID.Text & "," & _
        varAgt & ")"
    strSQL = Utils.replaceChars(strSQL)
    DAL.ExecNonQuery(strSQL, CommandType.Text)
Next

Source (for C#): Hide BoundField's but still be able to get values with C#

Entering each row of datagrid into SQL database

2 Answers2