-
Oct 21st, 2018, 09:57 AM
#1
Thread Starter
Addicted Member
[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.
-
Oct 21st, 2018, 11:05 AM
#2
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:
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.
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
-
Oct 22nd, 2018, 06:23 AM
#3
Thread Starter
Addicted Member
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.
-
Oct 22nd, 2018, 07:22 AM
#4
Re: Pass sql value to vb.net variable
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 & " '"
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
-
Oct 23rd, 2018, 01:48 AM
#5
Thread Starter
Addicted Member
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:
Dim resultaat As String opdragresultaat.CommandText = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '" opdragresultaat.Connection = konneksie resultaat = CType(opdragresultaat.ExecuteScalar(), String) kode = resultaat
-
Oct 23rd, 2018, 01:49 AM
#6
Thread Starter
Addicted Member
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:
Dim resultaat As String
opdragresultaat.CommandText = "SELECT GroeperingsKode FROM skedulering2 WHERE kultklas = '" & DGVKultklas & " '"
opdragresultaat.Connection = konneksie
resultaat = CType(opdragresultaat.ExecuteScalar(), String)
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|