Update checkboxes based on text string

[bchaney]

Is there a problem with my code? i'm trying to refresh a few form checkboxes based on text typed into machinetxt.text with what is currently stored in the database. i put this into the refresh button click event. here is my code:

Private Sub machinerefreshbutton_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles machinerefreshbutton.Click
        Dim mycon As SqlCeConnection
        Dim mycmd As New SqlCeCommand
        Dim myDA As New SqlCeDataAdapter
        Dim myDS As New DataSet
        Dim myDT As New DataTable

        If machinetxt.Text <> "" Then
            Try
                mycon = GetConnect()
                mycon.Open()
                mycmd = mycon.CreateCommand
                mycmd.CommandText = "SELECT * FROM machine WHERE Machine='[" & machinetxt.Text & "] ' "
                myDA.SelectCommand = mycmd
                myDA.Fill(myDS, "machine")
                myDT = myDS.Tables("machine")


                If (myDT.Rows.Count > 0) Then
                    line1check.CheckState = "[Line 1]"
                    line2check.CheckState = "[Line 2]"
                    line3check.CheckState = "[Line 3]"
                    line4check.CheckState = "[Line 4]"
                    line5check.CheckState = "[Line 5]"
                    line6check.CheckState = "[Line 6]"
                    line7check.CheckState = "[Line 7]"
                    line8check.CheckState = "[Line 8]"
                    line9check.CheckState = "[Line 9]"
                    line10check.CheckState = "[Line 10]"
                    line11check.CheckState = "[Line 11]"
                    utilitiescheck.CheckState = "[Utilities]"
                    misccheck.CheckState = "[Miscellaneous]"
                    smokehousecheck.CheckState = "[Smoke House]"
                Else
                    MsgBox("Machine " & Trim(machinetxt.Text) & " not found in database.", MsgBoxStyle.OkOnly)
                End If
            Catch ex As Exception
                MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error!!")
            End Try
        End If
    End Sub

Does anyone see a problem with it? i've tried tweaking the select statement but it still gives the error that the machinetxt.text string is not found in the database. Even though i type it in exactly as shown in the database.

Any help would be appreciated. Thanks

[finito]

Could you post the exact error message?

and try taking out the []

Like this

"SELECT * FROM machine WHERE Machine= '" & machinetxt.Text & "'"

make sure you don't leave space between the ' and " like you did

] ' "

Enjoy :)

[G_Waddell]

Hi,
First of all, it looks to me like youi are not passing in the database values but strings e.g. "[Line 1]"

Second, what are the values that you are storing in the database? As far as I'm aware CheckState is looking for three possible values:

Checked - displays a checkmark
Unchecked - Empty (unchecked)
Indeterminate - displays a check and box is shaded

Personally I'd use the Checked property of checkbox instead as it can only be true or false and have each of the values stored in the database as bits or True or False strings e.g.

Dim dr as datarow

If (myDT.Rows.Count > 0) Then  
	dr = myDT.rows(0)	
        line1check.Checked = CBool(dr("[Line 1]"))                    
	line2check.Checked = CBool(dr("[Line 2]"))                    
	line3check.Checked = CBool(dr("[Line 3]"))                    
	line4check.Checked = CBool(dr("[Line 4]"))                    
	line5check.Checked = CBool(dr("[Line 5]"))                    
	line6check.Checked = CBool(dr("[Line 6]"))                    
	line7check.Checked = CBool(dr("[Line 7]"))                    
	line8check.Checked = CBool(dr("[Line 8]"))                    
	line9check.Checked = CBool(dr("[Line 9]"))                    
	line10check.Checked = CBool(dr("[Line 10]"))                    
	line11check.Checked = CBool(dr("[Line 11]"))                    
	utilitiescheck.Checked = CBool(dr("[Utilities]"))                    
	misccheck.Checked = CBool(dr("[Miscellaneous]"))                    
	smokehousecheck.Checked = CBool(dr("[Smoke House]"))                
Else
....

[bchaney]

What you did in the last post worked. all i had to modify is instead of ...("[Line 1]") i put ("Line 1") and that fixed it. Can i ask why i have to do the:

dr=myDT.Rows.Count > 0

part? i thought that since i made the dt only see one row i could call the initial row by simply putting the:

line1check.checked = "Line 1"

part of it. my datatable is setup to store only true/false statements dictated by checkboxes. so i thought the data was easily interchangeable to just call the string value inside the data block.

Thanks

[G_Waddell]

Hi,

I wasn't sure if you where looping through collections or not, in which case I'd always rather type dr("") than myDt.Rows(i).Item("") 14 times.

Also I wasn't sure what you were storing in the database so I did the Cbool part.

Glad I could help

[bchaney]

oops. i totally messed up that last reply. what i meant was the dr = myDT.rows(0) part. not sure why i have to do that. i'm not looping. i have this code attached to a refresh button. the user (administrator) must type in the value exactly to bring up a successful update. i'm changing the text box to a editable combo box later today or tomorrow. i was just trying to figure out the whole refresh part. now that this is done i can make it auto refresh every index change of a combo box. I did this route to help me troubleshoot the problem areas in my code. Thanks a bunch.