none
How to compare the text in the Checkbox to the text in the database in vb ? RRS feed

  • Question

  • Hi,

    I am not expert in vb but trying to develop quiz like software. I have database in which i stored right answers

    Now I want to compare the text in the checkbox to the text in my Ans table's R_Ans column

    (Something like Checkbox1.text = ________?)

    I created database in visual studios 2010 its (.mdf)

    What should i do. Plz provide me solution.(I m using vb)

    Thanks 

    Saturday, August 3, 2013 8:19 AM

Answers

  • Hello Pratik_Bhagat,

    Thank you for posting in MSDN Forum.

    From your description,I notice that I notice the issue you are experiencing is that how to get the date in the database.

    If I have misunderstood anything, please feel free to let me know.

    As far as I know that we can use VB ADO.Net to get data from database.

    Here I made a sample.

    I create a table:

    CREATE TABLE [dbo].[Answer] (
        [AnswerID] INT           NOT NULL,
        [Answer]   NVARCHAR (50) NOT NULL,
        PRIMARY KEY CLUSTERED ([AnswerID] ASC)
    );

    And I create a project which is window form using VB.


    It has only one checkbox and I set its text to be “China”.

    In the database I made some date into it:                                                                                                                     

    .

    So I do a loop for getting data from database to compare with the text value.

    If there is the same value in database,the checkbox will be checked.

    Following is the detail code:

    Form code:
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim sqlHelper As SqlHelper = New SqlHelper()
            chk1.Checked = sqlHelper.IsAnswer(chk1.Text)
        End Sub
    End Class

    Code for getting data in database:

    Option Explicit On
    Option Strict On
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Public Class SqlHelper
        Function IsAnswer(answer As String) As Boolean
            Dim connectionString As String = "Data Source=(localdb)\v11.0;Initial Catalog=TestDataBase;Integrated Security=True"
            ' Provide the query string with a parameter placeholder.
            Dim queryString As String = "SELECT answer from answer"
            Dim flag As Boolean = False
            ' Create and open the connection in a using block. This
            ' ensures that all resources will be closed and disposed
            ' when the code exits.
            Using connection As New SqlConnection(connectionString)
                ' Create the Command and Parameter objects.
                Dim command As New SqlCommand(queryString, connection)
                ' Open the connection in a try/catch block.
                ' Create and execute the DataReader, writing the result
                ' set to the console window.
                Try
                    connection.Open()
                    Dim dataReader As SqlDataReader = _
                    command.ExecuteReader()
                    Do While dataReader.Read()
                        If answer.Equals(dataReader(0).ToString()) Then
                            flag = True
                            Exit Do
                        End If
                    Loop
                    dataReader.Close()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
                Console.ReadLine()
            End Using
            Return flag
        End Function
    End Class

    Here is the whole code:

    https://skydrive.live.com/redir?resid=16AFFEA93D6251E8!112&authkey=!AIXLDFUnElfGtHI

    You can download it.

    More information about ADO.NET:

    http://msdn.microsoft.com/en-us/library/aa286484.aspx

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 5, 2013 9:36 AM
    Moderator

All replies

  • Hello Pratik_Bhagat,

    Thank you for posting in MSDN Forum.

    From your description,I notice that I notice the issue you are experiencing is that how to get the date in the database.

    If I have misunderstood anything, please feel free to let me know.

    As far as I know that we can use VB ADO.Net to get data from database.

    Here I made a sample.

    I create a table:

    CREATE TABLE [dbo].[Answer] (
        [AnswerID] INT           NOT NULL,
        [Answer]   NVARCHAR (50) NOT NULL,
        PRIMARY KEY CLUSTERED ([AnswerID] ASC)
    );

    And I create a project which is window form using VB.


    It has only one checkbox and I set its text to be “China”.

    In the database I made some date into it:                                                                                                                     

    .

    So I do a loop for getting data from database to compare with the text value.

    If there is the same value in database,the checkbox will be checked.

    Following is the detail code:

    Form code:
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim sqlHelper As SqlHelper = New SqlHelper()
            chk1.Checked = sqlHelper.IsAnswer(chk1.Text)
        End Sub
    End Class

    Code for getting data in database:

    Option Explicit On
    Option Strict On
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Public Class SqlHelper
        Function IsAnswer(answer As String) As Boolean
            Dim connectionString As String = "Data Source=(localdb)\v11.0;Initial Catalog=TestDataBase;Integrated Security=True"
            ' Provide the query string with a parameter placeholder.
            Dim queryString As String = "SELECT answer from answer"
            Dim flag As Boolean = False
            ' Create and open the connection in a using block. This
            ' ensures that all resources will be closed and disposed
            ' when the code exits.
            Using connection As New SqlConnection(connectionString)
                ' Create the Command and Parameter objects.
                Dim command As New SqlCommand(queryString, connection)
                ' Open the connection in a try/catch block.
                ' Create and execute the DataReader, writing the result
                ' set to the console window.
                Try
                    connection.Open()
                    Dim dataReader As SqlDataReader = _
                    command.ExecuteReader()
                    Do While dataReader.Read()
                        If answer.Equals(dataReader(0).ToString()) Then
                            flag = True
                            Exit Do
                        End If
                    Loop
                    dataReader.Close()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
                Console.ReadLine()
            End Using
            Return flag
        End Function
    End Class

    Here is the whole code:

    https://skydrive.live.com/redir?resid=16AFFEA93D6251E8!112&authkey=!AIXLDFUnElfGtHI

    You can download it.

    More information about ADO.NET:

    http://msdn.microsoft.com/en-us/library/aa286484.aspx

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 5, 2013 9:36 AM
    Moderator
  • Thanks a million Fred Bao for your reply.

    But  I am not getting the expected result.

    Actually I created 2 database tables first one for questions and the other is for the right Answers.

    Your code works gives no error but the value of  

    sqlHelper.IsAnswer(chk1.Text) 

    always comes out to be false .Rest code is working fine.please Reply soon. Thank you.

    (I am not able to post any images here, so you just visualize that there is one table i.e question having ID as primary key ,Questions,Ans1,Ans2 as fields and other one is Ans having ID as primary key and R_Ans as fields. These Two tables are connected to each other by a relationship line)

    (The R_Ans column in the Ans table Provides me right answers)

    Thank you once again.

    Monday, August 5, 2013 4:16 PM
  • Hi, Pratik_Bhagat

    I am sorry for my being late to your post.

    According to your description,I think it should occur some unexpected operation in the class “SqlHelper”.

    I suggest that you can debug your code so that you can know why it will return false.

    Here is a link that will tell you how to debugging in visual studio:

    http://msdn.microsoft.com/en-us/library/vstudio/sc65sadd.aspx.

    And could you  please share your code of the “SqlHelper” class.

    With codes provided by you we can help you better.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 6, 2013 2:08 AM
    Moderator
  • Thank you for replying.This is my code.(The value of t1 and t2 is coming false always)

    Option Explicit On
    Option Strict On
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Public Class Form1
        Dim t1 As Boolean
        Dim t2 As Boolean



       
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Database1DataSet1.question' table. You can move, or remove it, as needed.
            Me.QuestionTableAdapter1.Fill(Me.Database1DataSet1.question)
            'TODO: This line of code loads data into the 'Database1DataSet.Ans' table. You can move, or remove it, as needed.
            Me.AnsTableAdapter.Fill(Me.Database1DataSet.Ans)
            'TODO: This line of code loads data into the 'Database1DataSet.question' table. You can move, or remove it, as needed.
            Me.QuestionTableAdapter.Fill(Me.Database1DataSet.question)

            
           
        End Sub

        Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        End Sub

        Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

        End Sub

        Private Sub CheckBox2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

        End Sub

        Private Sub QuestionBindingSource_CurrentChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QuestionBindingSource.CurrentChanged

        End Sub


        Private Sub BindingNavigatorPositionItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorPositionItem.Click

        End Sub

        Private Sub BindingNavigator1_RefreshItems(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigator1.RefreshItems

        End Sub

        Private Sub BindingNavigatorMoveNextItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorMoveNextItem.Click
            Dim sqlHelper As SqlHelper = New SqlHelper()
            t1 = sqlHelper.IsAnswer(CheckBox1.Text)
            MsgBox(t1)
            t2 = sqlHelper.IsAnswer(CheckBox1.Text)

            MsgBox(t2)




        End Sub
        



        Public Class SqlHelper

            Function IsAnswer(ByVal answer As String) As Boolean

                Dim connectionString As String = "Data Source=(localdb)\v11.0;Initial Catalog=TestDataBase;Integrated Security=True"

                ' Provide the query string with a parameter placeholder.
                Dim queryString As String = "SELECT answer from answer"

                Dim flag As Boolean = False

                ' Create and open the connection in a using block. This
                ' ensures that all resources will be closed and disposed
                ' when the code exits.
                Using connection As New SqlConnection(connectionString)

                    ' Create the Command and Parameter objects.
                    Dim command As New SqlCommand(queryString, connection)

                    ' Open the connection in a try/catch block.
                    ' Create and execute the DataReader, writing the result
                    ' set to the console window.
                    Try
                        connection.Open()
                        Dim dataReader As SqlDataReader = _
                        command.ExecuteReader()
                        Do While dataReader.Read()
                            If answer.Equals(dataReader(0).ToString()) Then
                                flag = True
                                Exit Do
                            End If
                        Loop
                        dataReader.Close()

                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
                    Console.ReadLine()
                End Using
                Return flag
            End Function




        End Class





    End Class

    [

    Something this I want.

    I want some code so that I can compare the text in the checkbox1,checkbox2 to the text or data which belongs to the Ans tables first row where I have already written right ans in R_Ans column .If checkbox1 is selected and that is the right answer  then msgbox will return ("1 is true").If checkbox2 is selected and that is the right answer  then msgbox will return ("2 is true").

    ]

    Thank You

    • Edited by Pratik_Bhagat Tuesday, August 6, 2013 9:06 AM Addition info
    Tuesday, August 6, 2013 8:30 AM
  • Hi Pratik_Bhagat,

    I think the problem may be that the query default.

    The connectionString should be defined for your program:

    Data Source = your database server name;

    Initial Catalog = your database name you want to connect;

    More information about connectionString:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    And queryString should also be defined for your program:

    Select column1, column2…(columns that you want to query) from tablename(which you want to query)

    And the dateReader(0).ToString() will return a string value of the column1, the dateReader(1).ToString() will return a string value of the column2.

    More information about Query Syntax:

    http://msdn.microsoft.com/en-us/library/windows/desktop/aa372021(v=vs.85).aspx.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 6, 2013 9:59 AM
    Moderator
  • Thank you Fred Bao For your replay.

    But I am very new to this visual basics thing,I know very little.

    I followed what you said.

    But I am only able to set (Initial catalog) or rather that's the only thing that I got .

    Rest things are out of my range.Sorry for the trouble.

    Can you please provide me solution.(if you provide me some code that will be ultimate help for me)

    One more thing I didn't use any mysql,ms access, oracle etc to create my database i used service based database in Visual studios 2010  

    Thank you.



    Tuesday, August 6, 2013 2:10 PM
  • Integrated Security = True is ok.

     I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 7, 2013 3:48 AM
    Moderator
  • Thank you for your reply and doing that image work for me.

    This is my Changed code as you said,

    Public Class SqlHelper

            Function IsAnswer(ByVal answer As String) As Boolean

               Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=Ans;Integrated Security=True"

                ' Provide the query string with a parameter placeholder.
               Dim queryString As String = "SELECT R_Ans FROM Ans"

                Dim flag As Boolean = False

                ' Create and open the connection in a using block. This
                ' ensures that all resources will be closed and disposed
                ' when the code exits.
                Using connection As New SqlConnection(connectionString)

                    ' Create the Command and Parameter objects.
                    Dim command As New SqlCommand(queryString, connection)

    My server explorer is like this

    Data connections

    Database1.mdf

       Database Diagrams

       Tables

            -Ans

            -question

        Views......etc

    when I click on properties of database1.mdf in connection i got this connection String

    Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\pratik\Documents\Visual Studio 2010\Projects\WindowsApplication11\WindowsApplication11\Database1.mdf";Integrated Security=True;User Instance=True

    Please tell me my changes are right or not.

    Thank you.

     

    Wednesday, August 7, 2013 4:25 AM
  • Hi Pratik_Bhagat,

    As you changed your code,does it run ok or return false yet?

    It seems ok.You can try it

    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, August 7, 2013 6:19 AM
    Moderator
  • Thank you for your replay. I am still getting false values.

    But now I want some different thing 

    Something this I want Now.

    1. As I mentioned earlier I have 2 tables question (ID,Qustions,Ans1,Ans2) and Ans (ID,R_Ans)

    2. Now I want to compare the fields belongs to the column Ans1 (from question's table)  with R_ans (from Ans's table) and column Ans2 (from question's table)  with R_ans (from Ans's table)

    3. I want to compare those columns row by row.

    (Simply I want to compare two tables row by row)

    If match found then some activity will happen.

    I ALSO SEARCHED OVER GOOGLE BUT FOUND NOTHING HELPFULL.

    Please provide me solution for this.

    Thank You.

     


    Wednesday, August 7, 2013 9:18 AM
  • You can write a sql like:

    select count(*) as matchcount from  question t1 inner join Ans t2 on (t1.Ans1 = t2.R_Ans or t1.Ans2 = t2.R_Ans)

    It will return count that two tables have same value with Ans1,Ans2 in table1 and R_Ans in table2.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 7, 2013 11:51 AM
    Moderator