NULL Value

[dr223]

Hi,

I have the follwoing code;

query = "INSERT INTO dbo.TblPracExclude (prac_no, Goldpracid) VALUES (" & _
                                                      chk_prac_no & ", '" & _
                                                      DgvPracExcl.Rows(RowID).Cells(1).Value  & "')"

                                cmd = New SqlCommand(query, conn1)
                                cmd.ExecuteNonQuery()

I am trying to add these values to a table in SQL database.

The problem am facing is that DgvPracExcl.Rows(RowID).Cells(1).Value is empty so this inserts an empty string to the database.
Instead I want it to insert a NULL value.

Is this possisble?

Thanks

[Mur16]

Why put ' ' around it then?

[dr223]

If I exclude the '' I receive the error message - Incorrect syntax near ','.

Thanks

[Mur16]

maybe test to see if the value is Null. If it is not (has a value) then include the ' '. If it is Null don't use them.

[GremlinSA]

while i prefer not to use NULLS in a db as it raises problems later when reading the data, the answer here is to not include it in the query if you want it to be null...

IE:

Tmpstring = DgvPracExcl.Rows(RowID).Cells(1).Value
query = "INSERT INTO dbo.TblPracExclude (prac_no " & _
        IIF(Tmpstring="" ,")" ,",Goldpracid)").tostring & " VALUES (" & chk_prac_no & _
        IIF(Tmpstring="" ,")" ,",'" & Tmpstring & "')").tostring 
cmd = New SqlCommand(query, conn1)
cmd.ExecuteNonQuery()

[dr223]

Ok - Changed it to

     Dim conn As SqlConnection = GetDbConnection()

     Dim sql As String = "INSERT INTO dbo.TblPracExclude (prac_no, Goldpracid, prac_name, prac_status, prac_enabled, Datetime) " & _
                                                    "VALUES (@prac_no, @Goldpracid, @prac_name, @prac_status, @prac_enabled, @Datetime)"
                                Dim myCommand As New SqlCommand(sql, conn)

                                With myCommand.Parameters
                                    .AddWithValue("@prac_no", DgvPracExcl.Rows(RowID).Cells(0).Value)
                                    .AddWithValue("@Goldpracid", DgvPracExcl.Rows(RowID).Cells(1).Value)
                                    .AddWithValue("@prac_status", Me.DgvPracExcl.Rows(RowID).Cells(2).Value)
                                    .AddWithValue("@prac_enabled", Me.DgvPracExcl.Rows(RowID).Cells(3).Value)
                                    .AddWithValue("@Datetime", DateTime.Now)
                                End With

Works, but doesnt update the table why?

[dglienna]

Don't see your .Execute() statement in there (for myCommand)