Help "Matching text box with database field"

[Maha Sh.]

Hi every one,

please could you help me in my project!!

I'm trying to design a words game that's give you the first two letters from the word and you insert the rest to make a word (for now I'm just using "co" as example and I have just one table in the database for it). The word will be written in textbox to be compared to a database field. If the word exist in the field the word will be displayed in a label with blue color and the score will increased by one if not the word will be displayed in the label with red color. This should happen three times with three words and I have different label for each one.

the name of my database is database1 and it have one table "A" wich contain "Comp" and "word"

I tried this game with array of word before and it was working. Now I'm trying to use database instead of arrays but its not working.

This is my code

Public Class Form1
    Dim score = 0
    Dim t = 0
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connection As New SqlClient.SqlConnection
        Dim command As New SqlClient.SqlCommand
        Dim adaptor As New SqlClient.SqlDataAdapter
        Dim dataset As New DataSet

Dim Word(2) As String
If t < 3 Then
            Word(t) = TextBox1.Text
            TextBox1.Text = ""
            'For i = 0 To 9
            connection.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Acer\Documents\Visual Studio 2005\Projects\word\word\database\Database1.mdf;Integrated Security=True;User Instance=True")
            command.CommandText = " SELECT * FROM (A)WHERE (word= '" + TextBox1.Text + "')"
            connection.Open()

            command.Connection = connection

            adaptor.SelectCommand = command
            adaptor.Fill(dataset, "0")

            Dim count = dataset.Tables(0).Rows.Count

            If count > 0 Then
                Select Case t
                    Case 0

                        Label2.Text = Word(t)
                        Label2.ForeColor = Color.Blue
                    Case 1
                        Label3.Text = Word(t)
                        Label3.ForeColor = Color.Blue
                    Case 2
                        Label4.Text = Word(t)
                        Label4.ForeColor = Color.Blue
                End Select
                score += 1
            Else
                Select Case t

                    Case 0
                        Label2.Text = Word(t)
                        Label2.ForeColor = Color.Red
                    Case 1
                        Label3.Text = Word(t)
                        Label3.ForeColor = Color.Red
                    Case 2
                        Label4.Text = Word(t)
                        Label4.ForeColor = Color.Red
                End Select
            End If
 

TextBox2.Text = score
            t += 1
            Label6.Text = t
        End If 

End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class

[kvprajapati]

Why not SELECT COUNT(*)?

...
 command.CommandText = " SELECT count(*) FROM  [A] WHERE [word]=@word"
 command.Parameters.AddWithValue("@word",TextBox1.Text)
 command.Connection = connection
 connection.Open()
 
 Dim count = command.ExecuteScalar()
 connection.Close()
 ....

[Maha Sh.]

Hi adatapost thanks for your help :)
what you post its way better than what I wrote before.

I changed the code, and its working. But if I wrote a word from the table its appears as false.

So the problem in the "count" its always =0 ??? How i could fix it??

[Maha Sh.]

Please could anybody help me????

[kvprajapati]

Note that the column type of word column must be varchar/nvarchar. Try out with like pattern.

...
 command.CommandText = " SELECT count(*) FROM  [A] WHERE [word] like @word"
 command.Parameters.AddWithValue("@word","%" & TextBox1.Text & "%")
   'OR
' command.Parameters.AddWithValue("@word",TextBox1.Text & "%") 
 command.Connection = connection
 connection.Open()
 
 Dim count = command.ExecuteScalar()
 connection.Close()
 ....

[Maha Sh.]

Yes, I already changed the word column to nvarchar its working but the count always =0????

[Maha Sh.]

Thanks a lot adatapost for your help the game is working :)
I discover that I was clearing the textbox before comparing it to the DB:$