adding data grid view rows to sql tables in vb.net

Question

I am trying to add data grid view rows into sql table but this error is showing although real data is saved:

The parameterized query '(@Name1 nvarchar(4000),@Dad nvarchar(4000),@Gender nvarchar(4000' expects the parameter '@Name1', which was not supplied.

I did research a lot, some say to add dbNull value and nothing happened. I also read MSN documents but can't understand enough.

cmd.CommandText = "Insert into Member values('" & txt_fserial_no.Text & "',@Name1,@DOB,@Gender,@Dad,@Relation,@NRC,@Citizen,@Job,@Race,@Country,@Religion)"
cmd.CommandType = CommandType.Text
' Dim adapFam As New SqlDataAdapter'
cmd.Parameters.Add("@Name1", SqlDbType.VarChar)
cmd.Parameters.Add("@Dad", SqlDbType.VarChar)
cmd.Parameters.Add("@Gender", SqlDbType.VarChar)
cmd.Parameters.Add("@DOB", SqlDbType.Date)
cmd.Parameters.Add("@Job", SqlDbType.VarChar)
cmd.Parameters.Add("@Race", SqlDbType.VarChar)

cmd.Parameters.Add("@Country", SqlDbType.VarChar)
cmd.Parameters.Add("@Religion", SqlDbType.VarChar)
cmd.Parameters.Add("@Relation", SqlDbType.VarChar)
cmd.Parameters.Add("@Citizen", SqlDbType.VarChar)
cmd.Parameters.Add("@NRC", SqlDbType.VarChar)

For i As Integer = 0 To datagrid_preview.Rows.Count - 1
    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@Name1", datagrid_preview.Rows(i).Cells(0).Value)
    cmd.Parameters.AddWithValue("@Dad", datagrid_preview.Rows(i).Cells(1).Value)
    cmd.Parameters.AddWithValue("@Gender", datagrid_preview.Rows(i).Cells(2).Value)
    cmd.Parameters.AddWithValue("@DOB", datagrid_preview.Rows(i).Cells(3).Value)
    cmd.Parameters.AddWithValue("@Job", datagrid_preview.Rows(i).Cells(4).Value)
    cmd.Parameters.AddWithValue("@Race", datagrid_preview.Rows(i).Cells(5).Value)
    cmd.Parameters.AddWithValue("@Country", datagrid_preview.Rows(i).Cells(6).Value)
    cmd.Parameters.AddWithValue("@Religion", datagrid_preview.Rows(i).Cells(7).Value)
    cmd.Parameters.AddWithValue("@Relation", datagrid_preview.Rows(i).Cells(8).Value)
    cmd.Parameters.AddWithValue("@Citizen", datagrid_preview.Rows(i).Cells(9).Value)
    cmd.Parameters.AddWithValue("@NRC", datagrid_preview.Rows(i).Cells(10).Value)

    cmd.Connection = conn
    cmd.ExecuteNonQuery()

    'adapFam.InsertCommand.ExecuteNonQuery()'

Next
conn.Close()`][1]

muffi · Accepted Answer · 2017-06-02 05:03:50Z

1

I think this should work:

cmd.CommandText = "Insert into Member(id,Name1,Dad,Gender,DOB,Job,Race,Country,Religion,Relation,Citizen,NRC) values(@id,@Name1,@DOB,@Gender,@Dad,@Relation,@NRC,@Citizen,@Job,@Race,@Country,@Religion)"
cmd.CommandType = CommandType.Text
cmd.Connection = conn

For i As Integer = 0 To datagrid_preview.Rows.Count - 1
    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@id", txt_fserial_no.Text)
    cmd.Parameters.AddWithValue("@Name1", datagrid_preview.Rows(i).Cells(0).Value)
    cmd.Parameters.AddWithValue("@Dad", datagrid_preview.Rows(i).Cells(1).Value)
    cmd.Parameters.AddWithValue("@Gender", datagrid_preview.Rows(i).Cells(2).Value)
    cmd.Parameters.AddWithValue("@DOB", datagrid_preview.Rows(i).Cells(3).Value)
    cmd.Parameters.AddWithValue("@Job", datagrid_preview.Rows(i).Cells(4).Value)
    cmd.Parameters.AddWithValue("@Race", datagrid_preview.Rows(i).Cells(5).Value)
    cmd.Parameters.AddWithValue("@Country", datagrid_preview.Rows(i).Cells(6).Value)
    cmd.Parameters.AddWithValue("@Religion", datagrid_preview.Rows(i).Cells(7).Value)
    cmd.Parameters.AddWithValue("@Relation", datagrid_preview.Rows(i).Cells(8).Value)
    cmd.Parameters.AddWithValue("@Citizen", datagrid_preview.Rows(i).Cells(9).Value)
    cmd.Parameters.AddWithValue("@NRC", datagrid_preview.Rows(i).Cells(10).Value)

    cmd.ExecuteNonQuery()

Next

answered Jun 2, 2017 at 5:03

muffi

3646 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kyaw Zin Thant Over a year ago

Even though I parameterized the ID , the same error still occurred.

muffi Over a year ago

I have the problem, that the link above ("this error") is for some reason not opening. Maybe the proxy of our company blocks it. I can only imagine, that there is a problem with your primary key. On my machine, my posted code runs fine. Using MySQL 5.1.

Kyaw Zin Thant Over a year ago

thanks for your effort,@muffi, but I think it is not about PK as I gave MemberID to auto increment, AddressID is a foreign key.

Kyaw Zin Thant Over a year ago

I finally found the answer. It is at the loop "For i As Integer = 0 To datagrid_preview.Rows.Count - 1", it is somehow still working after all rows in grid views. Have to substract 2(-2).

muffi Over a year ago

-1 is ok. If the error occurs on the last row, then there is something wrong with the last row. I think you should loop until Rows.Count - 1 and check (every row), if the data in the DataRows are ok. Instead, you may lose data...!

marc_s · Accepted Answer · 2017-06-02 16:30:04Z

0

Defined datatable as global which is use for insert your gridview raw into SQL Server table and that same is user for binding your gridview.

Pass dt directly to your SQL Server stored procedure.

Create a function in SQL Server:

CREATE PROCEDURE [dbo].[Insert_xyz]
      @tblxyz xyzType READONLY
AS
BEGIN
      SET NOCOUNT ON;

      INSERT INTO xyz(Id, Name, Country)
          SELECT Id, Name, Country 
          FROM @tblxyz
END

This is code for VB:

If dt.Rows.Count > 0 Then
        Dim cons As String = ConfigurationManager.ConnectionStrings("xyz").ConnectionString
        Using con As New SqlConnection(cons)
            Using cmd As New SqlCommand("Insert_xyz")
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = con
                cmd.Parameters.AddWithValue("@tblxyz", dt)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End If

Sorry for my bad English :) I tried my best to explain you. :)

edited Jun 2, 2017 at 16:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 2, 2017 at 5:25

PTank

711 silver badge8 bronze badges

4 Comments

Kyaw Zin Thant Over a year ago

thanks for your answers. But I am not familiar with "Using function in Sql and also I didn't bind my data gridview, I added from string array.

Kyaw Zin Thant Over a year ago

The data is saved into the database but that error still pop up, should I use another way?

PTank Over a year ago

if you are using string array then check string array has all required data? is you missing any parameter value? Some where you are missing values.

Kyaw Zin Thant Over a year ago

The error was at the loop, For i As Integer = 0 To datagrid_preview.Rows.Count - 1. Even all rows processed, it somehow loops again and it found nothing and so I substract (-2).

Collectives™ on Stack Overflow

adding data grid view rows to sql tables in vb.net

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related