Question datagrid save just the first line on access table

[faracha]

Hi, it's me again,
Well now, being born again in the field of programming, I still have trouble.
Now, on my application, I created a datagridview and I linked to a Access database, but when I fill the datagridview and I click on "Save", only the first line is recorded on the table and not the other.
Here's the code:

                Using cmd As New OleDb.OleDbCommand("insert into " & TextBox1.Text & "CC (Immatriculation, NDuBon, CodeChauffeur, Date1, Heure1, LieuApprovisionnement, Kilometrage, Quantite, Total) VALUES (@Immatriculation, @NDuBon, @CodeChauffeur, @Date1, @Heure1, @LieuApprovisionnement, @Kilometrage, @Quantite, @Total)", conn)

                    For i = 2 To (Me.DataGridView1.RowCount - 1)

                        cmd.Parameters.Add("@Immatriculation", OleDbType.VarChar).Value = IIf(Len(Trim(Me.TextBox1.Text())) > 0, Me.TextBox1.Text(), Label133.Text)
                        cmd.Parameters.Add("@NDuBon", OleDbType.VarChar).Value = Me.DataGridView1.Rows(i).Cells(0).Value
                        cmd.Parameters.Add("@CodeChauffeur", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(1).Value())) > 0, Me.DataGridView1.Rows(i).Cells(1).Value(), Label133.Text)
                        cmd.Parameters.Add("@Date1", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(2).Value())) > 0, Me.DataGridView1.Rows(i).Cells(2).Value(), Label133.Text)
                        cmd.Parameters.Add("@Heure1", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(3).Value())) > 0, Me.DataGridView1.Rows(i).Cells(3).Value(), Label133.Text)
                        cmd.Parameters.Add("@LieuApprovisionnement", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(4).Value())) > 0, Me.DataGridView1.Rows(i).Cells(4).Value(), Label133.Text)
                        cmd.Parameters.Add("@Kilometrage", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(5).Value())) > 0, Me.DataGridView1.Rows(i).Cells(5).Value(), Label133.Text)
                        cmd.Parameters.Add("@Quantite", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(6).Value())) > 0, Me.DataGridView1.Rows(i).Cells(6).Value(), Label133.Text)
                        cmd.Parameters.Add("@Total", OleDbType.VarChar).Value = IIf(Len(Trim(Me.DataGridView1.Rows(i).Cells(7).Value())) > 0, Me.DataGridView1.Rows(i).Cells(7).Value(), Label133.Text)

                    Next

                    Try
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        MsgBox(ErrorToString)
                    End Try
                End Using

Thank you kindly help me to record the entire table and be able to add more lines.
thank you

 

[jmcilhinney]

You're only calling ExecuteNonQuery once so of course you're only going to save one record.

It really doesn't make sense to be using a loop and ExecuteNonQuery if you have a DataGridView anyway. At the very least you should be using a loop to populate a DataTable and then saving from that. That's not necessarily the best option though.

What exactly is in that Label133 anyway?

 

[faracha]

Thank you for telling me how I should do, because I have tried many times without success.

label133 includes the "-" character;

 

[jmcilhinney]

label133 includes the "-" character;

It looks like you are saving numeric values to the database so why are you saving dashes at all? For one thing, to be able to save a dash, your data type would have to be non-numeric, which is bad to begin with. Why not do it properly and use a numeric data with NULL saved where there is no valid value?

Even if you're determined to save a dash to the database, why are you getting it from a Label? Why don't you have a String constant?

 

[faracha]

Hello, I modified the instructions as follows:

                Using cmd As New OleDb.OleDbCommand("insert into " & TextBox1.Text & "CC (Immatriculation, NDuBon, CodeChauffeur, Date1, Heure1, LieuApprovisionnement, Kilometrage, Quantite, Total) VALUES (@Immatriculation, @NDuBon, @CodeChauffeur, @Date1, @Heure1, @LieuApprovisionnement, @Kilometrage, @Quantite, @Total)", conn)

                    For i = 2 To (Me.DataGridView1.Rows.Count - 2) Step 1

                        cmd.Parameters.AddWithValue("@Immatriculation", Me.TextBox1.Text)
                        cmd.Parameters.AddWithValue("@NDuBon", Me.DataGridView1.Rows(i).Cells("Column1").Value.ToString)
                        cmd.Parameters.AddWithValue("@CodeChauffeur", Me.DataGridView1.Rows(i).Cells("Column2").Value.ToString)
                        cmd.Parameters.AddWithValue("@Date1", Me.DataGridView1.Rows(i).Cells("Column3").Value.ToString)
                        cmd.Parameters.AddWithValue("@Heure1", Me.DataGridView1.Rows(i).Cells("Column4").Value.ToString)
                        cmd.Parameters.AddWithValue("@LieuApprovisionnement", Me.DataGridView1.Rows(i).Cells("Column5").Value.ToString)
                        cmd.Parameters.AddWithValue("@Kilometrage", Me.DataGridView1.Rows(i).Cells("Column6").Value.ToString)
                        cmd.Parameters.AddWithValue("@Quantite", Me.DataGridView1.Rows(i).Cells("Column7").Value.ToString)
                        cmd.Parameters.AddWithValue("@Total", Me.DataGridView1.Rows(i).Cells("Column8").Value.ToString)

                    Next

                    Try

                        cmd.ExecuteReader()
                    Catch ex As Exception
                        MsgBox(ErrorToString)
                    End Try
                End Using

but it saves me the last line of my datagridview.
how is what I have to do the whole datagridview Register?
Thank you again.

 

[jmcilhinney]

Firstly, I have now fixed the formatting in two of your posts. Please format your code snippets when you post them in future.

As for your issue, you're still making the same basic mistake as before. You're still only executing the command once, so of course you only save one record. If you want to do it like that then, to save multiple records, you must execute the command multiple times. If you have a loop that visits each row then it should be fairly obvious that you need to execute the command inside the loop, once for each row.

There are other issues with your code though. Firstly, why call ExecuteReader when you're not reading anything? ExecuteNonQuery is the correct method to call in that situation. You just have to call it in the rioght place. Secondly, you're using parameters incorrectly. You should create the command and add the parameters once and once only, then repeatedly set the Value properties of those parameters in your loop.

The algorithm is pretty simple. Your main issue is that you're not following an algorithm. You're trying to go straight from an idea to working code. You then have nothing to compare your code to to see whether it's doing what it's supposed to. Pick up a pen and paper and write down an algorithm. Pretend that it's not a computer that is going to do it but rather a person. What instructions would you give them. Assume that they are very stupid and you have to spell it out very clearly. Once you've got those instructions and confirmed that they work, write code that follows them.