Read a datagridview cell

[atr232000]

Hi

I am having trouble when I am running the below code. I a datagridview (DG) and it has one column that is a DataGridViewComboBoxColumn and this gets populated from a list in a database.

I want to go through all the lines in the DG and add them to a SQL insert statement

        
Dim RN As String = "Show Cell Address"
        Try
            Dim tempsql As String = ""
            Dim sqli As SqlCommand = sqlconn.CreateCommand
            Dim Counter As Integer = 0
            For Each item In DG.Rows

Dim temp As String = "Insert into ConvLookup (JobID,RecordID,FieldName,FieldData) values '" & ConvertID.ToString & "','" & RecordID.ToString & "','" & Counter.ToString & "','" & DG.Item(0, item).Value & "'"

                tempsql = +temp
                Counter = +1
                Console.WriteLine(DG.Item(0, item).Value.ToString)
            Next
            sqli.CommandText = tempsql
            sqli.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error " & vbCrLf & RN & ex.Message)
        End Try

Im not quite sure if I am going about this right but any help would be great.

thanks

Alan

[jmcilhinney]

I think that it's safe to say that you're not going about it the right way. You say that your data is in multiple rows and one column and then you want to save it to multiple columns in one row. That doesn't sound right for a start. Can you provide a full description of what you're doing?

[kaliman79912]

Yes, you have the right idea but some errors I can detect. I am sure more expert users would do this more elegant but this is how I see it.

The first error I see is that you create a loop that goes through the dataGridView and declares a variable each time, filling it with the SQL statement, but only after the last iteration you issue the ExecuteNonQuery() thus only sending the last one.

Second, there is no need to declare the variable each time, you can Dim the variable on the first part of the sub and use the loop only to assign it values.

Third, after the 'VALUES' keyword on the SQL statement you need to start with a '(' as you did in naming the fields. And , of course, end with ')'

Fourth, on the part where you are trying to retrieve the DG value you put 'item' as the row index and you use another counter named Counter for the recordId you are trying to pass, this is redundant. Not only that but item is of type row and you are using it as an integer. Either use the counter for the row number or use your loop variable (which I dont think you should name item) as the row you are accessing.

Sixth, on your field list you have 'JobID, RecordID, FieldName, FieldData' which you are pairing with 'ConvertID, RecordID, Counter, Value'. The first two sound like variables declared anywhere else in your code, that would be OK, even though they would be the same for all the records inserted in this table. But the third data is the counter which you are storing in th fieldName, that does not make sense.

Fifth, if you are trying to increment values like the counter set them the other way, it is Counter += 1, not Counter = +1, because this sets the counter to positive one each time. And if that is what you were trying to do with the tempsql, then it would be tampsql &= temp.

[atr232000]

Hi

Thanks for your replies
I have amended the code as below

        Dim RN As String = "Show Cell Address "
        Try
            Dim tempsql As String = ""
            Dim temp As String = ""
            Dim sqli As SqlCommand = sqlconn.CreateCommand
            Dim Counter As Integer = 0
            For Each item In DG.Rows

                temp = "Insert into ConvLookup (JobID,RecordID,FieldName,FieldData) values '" & ConvertID.ToString & "','" & RecordID.ToString & "','" & item & "','" & DG.Item(0, item).Value("Target Field").ToString & "';"

                tempsql += temp
                Counter += 1
                Console.WriteLine(DG.Item(0, item).Value.ToString)
            Next
            sqli.CommandText = tempsql
            sqli.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error " & vbCrLf & RN & vbCrLf & ex.Message)
        End Try

Answer to number 1, The current sqlstatement is added to the string "temp" inside the for each loop I add each sql statement to a "TempSQL" then outside the loop when all rows have been added to the "tempSQL" I do the

sqli.CommandText = tempsql
sqli.ExecuteNonQuery()

to then execute the full sqlstatement (I hope this makes sence)

Answer to number 2, I have now declared my temp outside my loop.

Answer to number 3, I have changed the value lookup to.

DG.Item(0, item).Value("Target Field").ToString

Not sure if this is correct!! This is the code to build the datagridviewcombox

          Dim targetlist As New DataGridViewComboBoxColumn
            With targetlist
                .DataPropertyName = "Target List"
                .HeaderText = "Target Field"
                .DropDownWidth = 180
                .Width = 150
                .FlatStyle = FlatStyle.Flat
                For Each item As String In TargetArray
                    .Items.Add(item.ToString)
                Next
            End With

Answer to number 4, No sure what you mean!!!!

Answer to number 5, Code changed!!

Answer to number 6, 'JobID, RecordID' are declare outside as another part of the code. I have changed the code so instead of "Counter" its looking at the "item" to try and return the item row number.

thanks for your help

Alan

[tamil.veera]

pls..........post your project

[kaliman79912]

I hope this makes things a little clearer.
1) If you are trying to concatenate the strings to only call the sql once you should use tempsql &= temp istead of += as they are not numbers.
3) what you need to enclose in parenthesis are the values, the statement should read something like this
INSERT INTO datatable (ID, Name, Phone) VALUES ('1','Chuck','555-5454')

4) item is a keyword in VB.Net, I suggest you do not use it as the counter of your loop "For each item in
DG.Rows"