[RESOLVED] Pass sql value to vb.net variable

I have a series of two checkbox groups. If a checkbox is checked in the first series of checkboxes values are updated in a sql database based on the second series of checkboxes. Here is the code :

VB.net Code:

Private Sub ArrowButton1_Click(sender As Object, e As EventArgs) Handles ArrowButton1.Click
        '*****************Pas toe op gekose kultivars en klasse ***********************************
        Dim konneksie As New SqlConnection
        konneksie.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;Integrated Security=True"
        konneksie.Open()
        Dim opdragdeleteGroepperingsKode, opdragdeleteGroepperingsBeskrywing, opdragreseed, opdragskepkode, opdragskepbeskrywing, opdragdeleteGroepperingsGekies, opdraghaallaastekommauit As New SqlCommand
 
        Dim i As Integer = 1
 
        For Each row As DataGridViewRow In Me.Skedulering2DataGridView.Rows
 
            Dim DGVKultklas As String = row.Cells("Kultklas").Value
            Dim kode As String = ""
            Dim beskrywing As String = ""
            Dim checked As Boolean = CType(row.Cells("checkBoxColumn").Value, Boolean)
            If checked Then
 
                If Me.SlaagCheckBox.Checked = True Then
 
                    If kode = "" Then
                        kode = "GrSl"
                    Else
                        kode = kode & ",GrSl"
                    End If
 
                    opdragskepkode.Connection = konneksie
                    opdragskepkode.CommandText = "UPDATE skedulering2 " &
                            "SET   GroeperingsKode       = CONCAT( '" & kode & "',   slaagsuiker )  " &
                            "WHERE kultklas = '" & DGVKultklas & " '"
                    opdragskepkode.ExecuteNonQuery()
 
 
                End If
 
 
                If Me.SnoeiCheckBox.Checked = True Then
                    If kode = "" Then
                        kode = "GrSn"
                    Else
                        kode = kode & ",GrSn"
                    End If
                    opdragskepkode.Connection = konneksie
                    opdragskepkode.CommandText = "UPDATE skedulering2 " &
                                                "SET   GroeperingsKode       = CONCAT( '" & kode & "',   PruningMethod )  " &
                                                "WHERE kultklas = '" & DGVKultklas & " '"
                    opdragskepkode.ExecuteNonQuery()
 
                End If
 
                If Me.OesCheckBox.Checked = True Then
                    If kode = "" Then
                        kode = "GrOe"
                    Else
                        kode = kode & ",GrOe"
                    End If
                    opdragskepkode.Connection = konneksie
                    opdragskepkode.CommandText = "UPDATE skedulering2 " &
                                                "SET   GroeperingsKode       = CONCAT( '" & kode & "',   HarvestMethod )  " &
                                                "WHERE kultklas = '" & DGVKultklas & " '"
 
                    opdragskepkode.ExecuteNonQuery()
 
                End If
 
            End If
 
            '  MsgBox("Finale groepperingskode (" & i & ") : " & kode)
            i = i + 1
        Next
 
    End Sub

The code works as I want except after the update the sql table only has the last concatinated value. I suspect I have to pass the concatination to my vb.net "kode". However I have no idea how. Any help would be much appreciated.

First update your kode variable by concatenating it, then send the variable's value to the server...

vb.net Code:

kode = String.Concat(kode,"slaagsuiker")
opdragskepkode.CommandText = "UPDATE skedulering2 " &
                            "SET   GroeperingsKode       = '" & kode & "' " &
                            "WHERE kultklas = '" & DGVKultklas & " '"

(freehand beware of syntax)

If you know what you will update the server with, just send it rather than telling the server to concat something.

Thank you Kebo. This is exactly what I want to do. However slaagsuiker is a value in the database. So I want the value sent back to VB from the database after the sql command. This value I then want to use to change my kode variable.

I think I have to use the ExecuteScalar method to return the GroepperingsKode value for the updated record. Alas I have no idea how to do that.


Regards

Quote:

---

Originally Posted by GideonE

Thank you Kebo. This is exactly what I want to do. However slaagsuiker is a value in the database. So I want the value sent back to VB from the database after the sql command. This value I then want to use to change my kode variable. Regards

---

If you don't know the value of the slaagsuiker field, you will either need to read it first, concatenate kode then write the new value of kode, or do what you are doing and read the value back from the DB after updating. Either way it will require 2 sql statements.

To read it afterwards you would use a select. Something like this.

vb.net Code:

opdragskepkode.CommandText  = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"

Thank you Kebo. I ended up with the following code after the update command.

vb.net Code:

Dim resultaat As String
                    opdragresultaat.CommandText = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"
                    opdragresultaat.Connection = konneksie
                    resultaat = CType(opdragresultaat.ExecuteScalar(), String)
                    kode = resultaat

Thank you Kebo. I ended up with the following code after the update command.

vb.net Code:

Dim resultaat As String
                    opdragresultaat.CommandText = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"
                    opdragresultaat.Connection = konneksie
                    resultaat = CType(opdragresultaat.ExecuteScalar(), String)
                    kode = resultaat