[RESOLVED] save datagridview multiple rows

[jess21]

Hi, i want to save into my sql server 2000 database multiples rows of a datagridview. i tryed something that first was working good, but i did a code change removing a column so now i'm trying to save again but i can't do it. It's shows me a message error like this: THERE ARE FEWER COLUMNS IN THE INSERT STATEMENT THAN VALUES ESPECIFIED IN THE VALUES CLAUSE. I rewrite the code, delete and add a new table and my code has the same values in the insert than my values clause. I don't know what else i have to do.

Here is my code, thanks for your answers.

Private Sub cmdAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAceptar.Click
        Try

            Const CCon As String = "data source = DATASERVER;Initial Catalog= CATV;user id = sa;password=sa"
            Dim sqlCon As New Data.SqlClient.SqlConnection(CCon)

            Dim sqlQry As New SqlClient.SqlCommand("Insert into COMPRAS_ENC(PED_NRO, PED_FCH, PROV_ID, TIP_COMPRA) values ('" & _
            txtNumShop.Text & "', '" & dtpFecha.Text & "', '" & cboCodProv.Text & "',  '" & cboCompra.Text & "' )", sqlCon)
            sqlCon.Open()

            If grid1.Rows.Count > 0 Then
                For i As Integer = 0 To grid1.Rows.Count - 1
                    Dim sqlQry2 As New SqlClient.SqlCommand("Insert into DETALLE_COMPRA(PED_NRO, PED_LIN, MED_ID, CANT_COM, PRE_UNI) values ('" & _
                    txtNumShop.Text & "', " & Me.grid1.Rows(i).Cells(0).Value.ToString & ", '" & _
                    Me.grid1.Rows(i).Cells(1).Value.ToString & "', " & _
                    Me.grid1.Rows(i).Cells(2).Value.ToString & ", " & _
                    Me.grid1.Rows(i).Cells(3).Value.ToString & ")", sqlCon)
                    sqlQry2.ExecuteNonQuery()
                Next
            End If
            sqlQry.ExecuteNonQuery()
            sqlCon.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
        'Me.Close()

    End Sub

[danasegarane]

THERE ARE FEWER COLUMNS IN THE INSERT STATEMENT THAN VALUES ESPECIFIED IN THE VALUES CLAUSE.

Means that you are passing values less than the required.. For example your insert statement requires 3 values and you are supplying only 2 values. Check the InsertQuery....

[cicatrix]

Is your datagridview unbound?

Concatenating an SQL statement is a poor way from the security point of view and I would check whether your values in the textboxes and the datagridview contain commas, semicolons, quotes or double quotes.

Use parametrized queries instead. (Search this forum for parametrized queries if you don't know how).

[GaryMazzone]

I count 5 files in the columns to insert into list and only 4 values in the value list.

[jess21]

i did it, i used a breakpoint to see what values are passing to each field. But nothing, i could see that values of the datagrid are no passing, but i don't know how to resolve it 'cause this code worked good first.

[cicatrix]

Is your datagridview bound or unbound?

[jess21]

i have five values in the insert statement and five in the values clause.
PED_NRO
PED_LIN
MED_ID
CANT_COM
PRE_UNI

Values especified:
'" & txtNumShop.Text & "',
" & Me.grid1.Rows(i).Cells(0).Value.ToString & ",
'" & me.grid1.Rows(i).Cells(1).Value.ToString & "',
" & Me.grid1.Rows(i).Cells(2).Value.ToString & ",
" & Me.grid1.Rows(i).Cells(3).Value.ToString & ")", sqlCon)

[jess21]

what do you mean with bound or unbound, i don't know well english so this word is not familiar to me

[jess21]

i think that the problem is in the datagridview values, but i don't know how to resolve it

[cicatrix]

When you specify a DataSource for your datagridview it becomes bound to that datasource or if you fill the values manually it's unbound. With data bound controls you have to change the datasource rather than the controls themselves while with unbound control you change the contents of such controls directly.
Also, since you're concatenating your sql query I asked you to check whether values contain characters "," ";", quote (') or double quote (").
And I still suggest that you parametrize your query (use named parameters)

[jess21]

well i write the values directly in the datagrid and enable add, update and delete rows.

Now i'm trying with parametrized values, the purchase header works fine but i'm trying wth the purchase detail table, but i don't know how to indicate to save each row of te datagrid. with my old code i tryed this:

If grid1.Rows.Count > 0 Then
For i As Integer = 0 To grid1.Rows.Count - 1
Dim sqlQry2 As New SqlClient.SqlCommand("Insert into DETALLE_COMPRA(PED_NRO, PED_LIN, MED_ID, CANT_COM, PRE_UNI) values ('" & _
txtNumShop.Text & "', " & Me.grid1.Rows(i).Cells(0).Value.ToString & ", '" & _
Me.grid1.Rows(i).Cells(1).Value.ToString & "', " & _
Me.grid1.Rows(i).Cells(2).Value.ToString & ", " & _
Me.grid1.Rows(i).Cells(3).Value.ToString & ")", sqlCon)

how could i do it with parameters?

[jess21]

and the value "PRE_UNI" (unit price) contains a (,) 'cause i use decimals numbers

[jess21]

when i assign the value taked for the textbox to the parameter i do this

rsProveedor = Me.cboCodProv.Text

cmd.Parameters("@proveedor").Value = rsProveedor

but how could i get each value of each datagrid row?

[cicatrix]

and the value "PRE_UNI" (unit price) contains a (,) 'cause i use decimals numbers

THIS IS THE SOURCE OF YOUR PROBLEM!

There's more, you won't be able to add "2,23" value because db engine understands only "2.23"

You should specify data types for your datagridview columns.

To get the value of the row(4) column (5) you should use:

DataGridView.Rows(4).Items(5)

If you substitute a variable instead of 4 you can iterate through all the rows.

[jess21]

but sometimes i will use 20, 10, 3, 5, 15 rows.

[GaryMazzone]

I would absoultley say it is IMPERITIVE that you use parameters for doing this. If you don't want to change the , to a . (10,23 to 10.23) then you need to use parameters

[jess21]

i have this code to define the unit price of each medicine 'cause in the purchase order the unit price is based on boxes no unit prices for example, if i buy a medicine like amoxicillin the supplier write in the purchase order 20 boxes of amoxicillin x 100 caps = $15 each one. So in my win form i have to write 2000 caps of amoxicillin and $30(total price) so the program will calculate the unit price. This is my code to calculate the unit price (i tryed to use a (.) instead a (,) but i couldn't

Dim calculo As Double
Dim cantidad As Double = CDbl((Me.grid1.Rows(e.RowIndex).Cells(2).Value))
Dim precio As Double = CDbl((Me.grid1.Rows(e.RowIndex).Cells(4).Value))
Dim columna As Integer = 4

'subtotal de cada fila
calculo = Val(precio) / Val(cantidad)

Me.grid1.Rows(e.RowIndex).Cells(3).Value = calculo

[GaryMazzone]

I couldn't mean the left front tire fell off right?

[cicatrix]

Try it

This code can give you errors if the datatype in the database table does not match with datagridview columns data types.
If there are errors, tell me what are the datatypes of fields in the data table.

Private Sub cmdAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAceptar.Click
        Try
 
            Const CCon As String = "data source = DATASERVER;Initial Catalog= CATV;user id = sa;password=sa"
            Dim sqlCon As New Data.SqlClient.SqlConnection(CCon)
 
            Dim Sql As String = "INSERT INTO COMPRAS_ENC(PED_NRO, PED_FCH, PROV_ID, TIP_COMPRA) Values " & _
                                    "(@ped_nro, @ped_fch, @prov_id, @tip_compra)"
 
            Dim sqlQry As New SqlClient.SqlCommand(Sql, sqlCon)
 
            With sqlQry.Parameters
                .AddWithValue("@ped_nro", txtNumShop.Text)
                .AddWithValue("@ped_fch", dtpFecha.Text)
                .AddWithValue("@prov_id", cboCodProv.Text)
                .AddWithValue("@tip_compra", cboCompra.Text)
            End With
 
            sqlCon.Open()
            Dim sql2 As String = "INSERT INTO COMPRAS_ENC(PED_NRO, PED_LIN, MED_ID, CANT_COM, PRE_UNI) values" & _
                                    "(@ped_nro, @ped_lin, @med_id, @cant_com, @pre_uni)"
 
            
            For Each row As DataGridViewRow In grid1.Rows
 
                Dim sqlQry2 As New SqlClient.SqlCommand(sql2, sqlCon)
                With sqlQry2.Parameters
                    .AddWithValue("@ped_nro", txtNumShop.Text)
                    .AddWithValue("@ped_lin", row.Cells(0).Value)
                    .AddWithValue("@med_id", row.Cells(1).Value)
                    .AddWithValue("@cant_com", row.Cells(2).Value)
                    .AddWithValue("@pre_uni", row.Cells(3).Value)
                End With
 
                sqlQry2.ExecuteNonQuery()
            Next
 
            sqlQry.ExecuteNonQuery()
            sqlCon.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
 
        End Try
        'Me.Close()
 
    End Sub

[jess21]

it shows me an error but when i go to my database the values were saved.

Prepared statement’(@ped_nro nvarchar(5), @ped_lin nvarchar(4000),@med_id nvarchar(4’ expects parameter @ped_lin, which was not supplied

[jess21]

the datatypes are:
PED_NRO = VARCHAR 10
PED_FCH = VARCHAR 12
PROV_ID = VARCHAR 10
TIP_COMPRA = CHAR 10

PURCHASE DETAILS:
PED_NRO = VARCHAR 10
PED_LIN = NUMERIC 9
MED_ID = VARCHAR 10
CANT_COM = NUMERIC 9
PRE_UNI = NUMERIC 9

[cicatrix]

Try this:

Private Sub cmdAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAceptar.Click
        Try
 
            Const CCon As String = "data source = DATASERVER;Initial Catalog= CATV;user id = sa;password=sa"
            Dim sqlCon As New Data.SqlClient.SqlConnection(CCon)
 
            Dim Sql As String = "INSERT INTO COMPRAS_ENC(PED_NRO, PED_FCH, PROV_ID, TIP_COMPRA) Values " & _
                                    "(@ped_nro, @ped_fch, @prov_id, @tip_compra)"
 
            Dim sqlQry As New SqlClient.SqlCommand(Sql, sqlCon)
 
 
            With sqlQry.Parameters
                .AddWithValue("@ped_nro", txtNumShop.Text)
                .AddWithValue("@ped_fch", dtpFecha.Text)
                .AddWithValue("@prov_id", cboCodProv.Text)
                .AddWithValue("@tip_compra", cboCompra.Text)
            End With
 
            sqlCon.Open()
            Dim sql2 As String = "INSERT INTO COMPRAS_ENC(PED_NRO, PED_LIN, MED_ID, CANT_COM, PRE_UNI) values" & _
                                    "(@ped_nro, @ped_lin, @med_id, @cant_com, @pre_uni)"
 
            
            For Each row As DataGridViewRow In grid1.Rows
 
                Dim sqlQry2 As New SqlClient.SqlCommand(sql2, sqlCon)
                With sqlQry2.Parameters
                    .AddWithValue("@ped_nro", txtNumShop.Text)
                    .AddWithValue("@ped_lin", ToDecimal(row.Cells(0).Value.ToString))
                    .AddWithValue("@med_id", row.Cells(1).Value.ToString)
                    .AddWithValue("@cant_com", ToDecimal(row.Cells(2).Value.ToString))
                    .AddWithValue("@pre_uni", ToDecimal(row.Cells(3).Value.ToString))
                End With
 
                sqlQry2.ExecuteNonQuery()
            Next
 
            sqlQry.ExecuteNonQuery()
            sqlCon.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
 
        End Try
        'Me.Close()
 
    End Sub
 
    Private Function ToDecimal(ByVal value As String) As Decimal
        Dim fp As IFormatProvider = Globalization.NumberFormatInfo.CurrentInfo
        ' Will throw an exception if conversion fails. Catch it in the main sub
        Dim ret As Decimal = Decimal.Parse(value, fp)
        Return ret
    End Function

[jess21]

object reference no set to an instance of an object. this error it's now showing

[cicatrix]

object reference no set to an instance of an object. this error it's now showing

On what?

[jess21]

when i clicked the save button

[cicatrix]

What line of code did it highlight?

[jess21]

noneone, but when i go to my database the values were saved

[jess21]

i comment the try, catch and end try to see in what line is the error. it's in the line of the first row of the datagrid

.AddWithValue("@ped_lin", ToDecimal(row.Cells(0).Value.ToString))

[cicatrix]

This is probably because your datagridview contains empty cells.

Change the inner cycle code to this one:

For Each row As DataGridViewRow In grid1.Rows
 
    Dim sqlQry2 As New SqlClient.SqlCommand(sql2, sqlCon)
 
    Dim values() As String = {"0", "", "0", "0"}
 
    For i As Integer = 0 To 3
        If Not (IsNothing(row.Cells(i).Value) OrElse IsDBNull(row.Cells(i).Value)) Then
            values(i) = row.Cells(0).Value.ToString
        End If
    Next
 
    With sqlQry2.Parameters
        .AddWithValue("@ped_nro", txtNumShop.Text)
        .AddWithValue("@ped_lin", ToDecimal(values(0)))
        .AddWithValue("@med_id", values(1))
        .AddWithValue("@cant_com", ToDecimal(values(2)))
        .AddWithValue("@pre_uni", ToDecimal(values(3)))
    End With
 
    sqlQry2.ExecuteNonQuery()
Next

[jess21]

i have problems with the foreign key med_id, i changed to the previous value (.AddWithValue("@med_id", row.Cells(1).Value)) and i delete a value form values array. but i have the same error message. I don't know if it's not taking the datagridcomboxcolumn (med_id). But remember it save them into my db, it's just only the message

[cicatrix]

To be perfectly honest, I don't understand your last post, particularly this part: 'i delete a value form values array'.

The code in the post #29 should work. I think I have tied all loose ends. I simply have no idea why you keep receiving this error message.

Have you tried tracing your code? Do you know how to do it? If not, have a look at this:
http://www.homeandlearn.co.uk/net/nets5p6.html

Try to step through the code line by line and perhaps you will be able to isolate the problem.

[jess21]

i used breakpoints and it's taking the last row of the datagrid that it's empty

[cicatrix]

Then use this

Dim r As Integer, row As DataGridViewRow

For r = 0 To grid1.Rows.Count - 2 ' Normally it would be - 1 but if the last row is empty then we use one row less
    row = grid1.Rows(r)
    
    Dim sqlQry2 As New SqlClient.SqlCommand(sql2, sqlCon)

    Dim values() As String = {"0", "", "0", "0"}

    For i As Integer = 0 To 3
        If Not (IsNothing(row.Cells(i).Value) OrElse IsDBNull(row.Cells(i).Value)) Then
            values(i) = row.Cells(0).Value.ToString
        End If
    Next

    With sqlQry2.Parameters
        .AddWithValue("@ped_nro", txtNumShop.Text)
        .AddWithValue("@ped_lin", ToDecimal(values(0)))
        .AddWithValue("@med_id", values(1))
        .AddWithValue("@cant_com", ToDecimal(values(2)))
        .AddWithValue("@pre_uni", ToDecimal(values(3)))
    End With

    sqlQry2.ExecuteNonQuery()
Next

[jess21]

that's work good to me. If you remember i used a variable to go to each row of my datagrid but i count the rows - 1, i tryed with -2 before (like post 1) 'cause i see a tread with teh same problem.
Anyway, Thanks again