1

I am trying to collect values from a database, and if they are X value sett he background colour to green.

Basically, I have a Rota system, and if the user is working, then change the background colour. The select * will only bring back 1 row ever. 

Imports System.Data.SqlClient
 Imports System.Data.OleDb

Public Class Form4
Dim Con As SqlConnection
Dim cmd As New OleDbCommand
Dim sqlstring As String
Dim connstring As String
Dim ds As DataSet
Dim da As SqlDataAdapter
Private Sub Form4_Load(sender As Object, e As EventArgs)
    connstring = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Assignment.mdf;Integrated Security=True;Connect Timeout=30"
    Con = New SqlConnection(connstring)
    Con.Open()
    Dim strSQL As String = "SELECT * from Users"
    Dim da As New SqlDataAdapter(strSQL, Con)
    Dim ds As New DataSet
    da.Fill(ds, "Users")
    With cboname
        .DataSource = ds.Tables("Users")
        .DisplayMember = "Name"
        .ValueMember = "Id"
        .SelectedIndex = 0
    End With
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Con As SqlConnection
    Dim cmd As New OleDbCommand
    Dim sqlstring As String
    Dim connstring As String
    Dim ds As DataSet
    Dim da As SqlDataAdapter



    connstring = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Assignment.mdf;Integrated Security=True;Connect Timeout=30"

    Con = New SqlConnection(connstring)
    Con.Open()


    sqlstring = ("SELECT * FROM Rota WHERE UserId ='" & cboname.SelectedIndex & "' and ID ='" & dtp1.Value & "'")
    da = New SqlDataAdapter(sqlstring, Con)
    ds = New DataSet
    da.Fill(ds, "Rota")



End Sub

End Class

After this, I understand I need to get a few IF statements, but I am unsure on how to construct them.

JoeM
  • 93
  • 1
  • 3
  • 13
  • Background colour of _what_? what control are you using to display the data? – D Stanley Apr 28 '14 at 12:45
  • I want to have it change the colour of a textbox. It will hopefully look like this: http://imgur.com/kLhZkN6 – JoeM Apr 28 '14 at 12:48
  • you have `more than one row` sharing the `same ID AND UserId`? – DrCopyPaste Apr 28 '14 at 12:48
  • No. As a combonation, they will always be unique – JoeM Apr 28 '14 at 12:50
  • @user3343446 ah sry, I misunderstood you there I read your post as if you expected that select to retrieve you more than one row – DrCopyPaste Apr 28 '14 at 12:57
  • No, I will only have one result. So it is just how do i manipulate the data form that one record? – JoeM Apr 28 '14 at 12:58
  • @user3343446 you should include your form code as well, as this question is not so much about sql as it is about control manipulation inside forms – DrCopyPaste Apr 28 '14 at 13:12
  • @DrCopyPaste Updated the initial Post – JoeM Apr 28 '14 at 13:13
  • 2
    Personally, I'd have used a DataReader instead of a DataAdapter and DataSet and just done a While DataReader.Read with a Select Case on Datareader(0) (or whatever integer). But ideally you'd want to specify your columns instead of using SELECT * to avoid confusion. – Jonathon Cowley-Thom Apr 28 '14 at 13:16

1 Answers1

0

To check a value in a DataSet use first get a DataTable inside of it, then a DataRow, then check one of the field values:

 ds.Tables(0).Rows(0)("{field name}");

So to change the color based on some value:

 If ds.Tables(0).Rows(0)("{field name}") = "Red" Then
     textbox1.BackColor = Color.Red
 End If

Some other comments:

  • A DataSet may be a bit heavy for getting one value (unless you're binding to a control). You can just use ADO.NET objects and use ExecuteScalar)
  • It's safer to use parameters instead of concatenating SQL statements (prevents SQL Injection and errors from special characters)
  • You can refactor your code to put the connection string in a single location rather than duplicating it across methods.
D Stanley
  • 149,601
  • 11
  • 178
  • 240