SQL Error Regarding apostrophe from datagridview using parameter in query-CodePudding

Dim sc = datagridview.rows(0).cells(0).value
cmd = New SqlCommand("insert into Schedule (Name) values(@sc)", con)
with cmd
                .Connection = con
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@sc", sc.cells(0))
end with

The value from the datagrid view contains an apostrophe where I get an error in ('s). Any work around with this?

EDIT: This is the error:

SqlException was unhandled. Incorrect syntax near 's'.

EDIT 2: i've revised the code to this:

for 1=0 to datagridview.rows.count -1 
Dim tname = datagridview.Rows(i)
cmd = New SqlCommand("insert into Schedule (Name) values(@sc)", con)
with cmd
                 .Connection = con
                 .CommandType = CommandType.Text
                 .Parameters.AddWithValue("@sc", sc.cells(0).value) 
 end with
 con.Close() 
 con.Open()  
 If Not cmd.ExecuteNonQuery() > 0 Then
    con.Close()
    Exit For
 End If
 next

Error is still the same.

CodePudding user response：

Try to replace the " ' " by " '' "



Dim sc = datagridview.rows(0).cells(0).value
cmd = New SqlCommand("insert into Schedule (Name) values(@sc)", con)
with cmd
                .Connection = con
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@sc", replace(sc.cells(0), "'", "''")
end with

/**** EDIT ****/ I will consider by the way that your code have a problem with the affectation of the value. You get the value into SC then try to get again the value from cell

Dim sc = datagridview.rows(0).cells(0).value
cmd = New SqlCommand("insert into Schedule (Name) values(@sc)", con)
with cmd
                .Connection = con
                .CommandType = CommandType.Text
                .Parameters("@sc").value = sc.replace("'", "''")
end with

CodePudding user response：

As it goes through all the values quickly in one go, I would make the connection once and change the value of the parameter for each iteration, like this:

Dim sql = "INSERT INTO [Schedule] ([Name]) VALUES(@sc)"

Using conn As New SqlConnection("your connection string"),
       cmd As New SqlCommand(sql, conn)

    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@sc",
                       .SqlDbType = SqlDbType.NVarChar,
                       .Size = -1})

    conn.Open()

    For i = 0 To dgv.Rows.Count - 1
        Dim tname = dgv.Rows(i).Cells(0).Value.ToString()
        cmd.Parameters("@sc").Value = tname
        cmd.ExecuteNonQuery()
    Next

End Using

Including the size of the parameter in its declaration helps SQL Server keep things tidy (it only needs one entry in the query cache).

The Using statement makes sure that the connection and command are disposed of properly when they are finished with.

The purpose of checking If Not cmd.ExecuteNonQuery() > 0 was not clear to me, so I didn't put that in. (I used "dgv" as the name of the DataGridView.)