Results 1 to 6 of 6

Thread: [RESOLVED] Pass sql value to vb.net variable

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Resolved [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:
    1. Private Sub ArrowButton1_Click(sender As Object, e As EventArgs) Handles ArrowButton1.Click
    2.         '*****************Pas toe op gekose kultivars en klasse ***********************************
    3.         Dim konneksie As New SqlConnection
    4.         konneksie.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;Integrated Security=True"
    5.         konneksie.Open()
    6.         Dim opdragdeleteGroepperingsKode, opdragdeleteGroepperingsBeskrywing, opdragreseed, opdragskepkode, opdragskepbeskrywing, opdragdeleteGroepperingsGekies, opdraghaallaastekommauit As New SqlCommand
    7.  
    8.         Dim i As Integer = 1
    9.  
    10.         For Each row As DataGridViewRow In Me.Skedulering2DataGridView.Rows
    11.  
    12.             Dim DGVKultklas As String = row.Cells("Kultklas").Value
    13.             Dim kode As String = ""
    14.             Dim beskrywing As String = ""
    15.             Dim checked As Boolean = CType(row.Cells("checkBoxColumn").Value, Boolean)
    16.             If checked Then
    17.  
    18.                 If Me.SlaagCheckBox.Checked = True Then
    19.  
    20.                     If kode = "" Then
    21.                         kode = "GrSl"
    22.                     Else
    23.                         kode = kode & ",GrSl"
    24.                     End If
    25.  
    26.                     opdragskepkode.Connection = konneksie
    27.                     opdragskepkode.CommandText = "UPDATE skedulering2 " &
    28.                             "SET   GroeperingsKode       = CONCAT( '" & kode & "',   slaagsuiker )  " &
    29.                             "WHERE kultklas = '" & DGVKultklas & " '"
    30.                     opdragskepkode.ExecuteNonQuery()
    31.  
    32.  
    33.                 End If
    34.  
    35.  
    36.                 If Me.SnoeiCheckBox.Checked = True Then
    37.                     If kode = "" Then
    38.                         kode = "GrSn"
    39.                     Else
    40.                         kode = kode & ",GrSn"
    41.                     End If
    42.                     opdragskepkode.Connection = konneksie
    43.                     opdragskepkode.CommandText = "UPDATE skedulering2 " &
    44.                                                 "SET   GroeperingsKode       = CONCAT( '" & kode & "',   PruningMethod )  " &
    45.                                                 "WHERE kultklas = '" & DGVKultklas & " '"
    46.                     opdragskepkode.ExecuteNonQuery()
    47.  
    48.                 End If
    49.  
    50.                 If Me.OesCheckBox.Checked = True Then
    51.                     If kode = "" Then
    52.                         kode = "GrOe"
    53.                     Else
    54.                         kode = kode & ",GrOe"
    55.                     End If
    56.                     opdragskepkode.Connection = konneksie
    57.                     opdragskepkode.CommandText = "UPDATE skedulering2 " &
    58.                                                 "SET   GroeperingsKode       = CONCAT( '" & kode & "',   HarvestMethod )  " &
    59.                                                 "WHERE kultklas = '" & DGVKultklas & " '"
    60.  
    61.                     opdragskepkode.ExecuteNonQuery()
    62.  
    63.                 End If
    64.  
    65.             End If
    66.  
    67.             '  MsgBox("Finale groepperingskode (" & i & ") : " & kode)
    68.             i = i + 1
    69.         Next
    70.  
    71.     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.

  2. #2
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Re: Pass sql value to vb.net variable

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

    vb.net Code:
    1. kode = String.Concat(kode,"slaagsuiker")
    2. opdragskepkode.CommandText = "UPDATE skedulering2 " &
    3.                             "SET   GroeperingsKode       = '" & kode & "' " &
    4.                             "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.
    Last edited by kebo; Oct 21st, 2018 at 12:02 PM.
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Pass sql value to vb.net variable

    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
    Last edited by GideonE; Oct 22nd, 2018 at 07:54 AM.

  4. #4
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,757

    Re: Pass sql value to vb.net variable

    Quote Originally Posted by GideonE View Post
    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:
    1. opdragskepkode.CommandText  = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Pass sql value to vb.net variable

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

    vb.net Code:
    1. Dim resultaat As String
    2.                     opdragresultaat.CommandText = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"
    3.                     opdragresultaat.Connection = konneksie
    4.                     resultaat = CType(opdragresultaat.ExecuteScalar(), String)
    5.                     kode = resultaat

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Pass sql value to vb.net variable

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

    vb.net Code:
    1. Dim resultaat As String
    2.                     opdragresultaat.CommandText = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"
    3.                     opdragresultaat.Connection = konneksie
    4.                     resultaat = CType(opdragresultaat.ExecuteScalar(), String)
    5.                     kode = resultaat

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width