Textbox and SQL Countdown

[newVBM3]

It doesn't "stand for" anything. It's a Using statement. It creates a variable scope and disposes the object assigned to it at the end of the block.

Note that I simply searched the web for "vb.net using" and that link I provided was the first result. You can find this information for yourself if you care to look. Ask schoemr. She'll tell you.

I did, but I did not type in the search box correctly. When I say I'm new to VB, this is the first two months I've ever worked on it. Its a learning curve for me for sure. I appreciate the team here helping and guiding.

[schoemr]

wes4dbt,

So do you think the OP is going to plug the code into his button, press it and boom! his life's mission is complete? There certainly are times where I wished someone can just shoot me a piece of code so that I can get on with it, but I am trying to teach him something here. I'm not sure what your problem is but after the OP started this thread, and obviously got some responses, he contacted me privately to ask for help as I have been of some assistance to him in another thread. Not everyone has been in this game for a zillion years.

You cannot tell me that directly accessing the database with code is a better option than doing it with a dataset. At the end of the day that is a developer's preference. Both methods have advantages as well as disadvantages.

For a dataset there are several advantages such as easier databinding to controls, easier to use, simplified CRUD operations, features for enforcing data consistency and integrity, etc, etc. while it is also true that for large and complex applications direct access may be more advisable.

[schoemr]

newVBM3,

I'm happy that you managed to add your dataset to your project. You will see that working with a dataset can simplify many things.

Can you confirm that if you expand the dataset you can see your table? (I usually when I add a dataset select all the tables not only one)

Next, on a Userfrom add a combobox from the toolbox. You will see there is a small arrow symbol

Attachment 187089

Click on it, check "Use Data Bound Items" and configure it as follows:

In the top dropdown (Data Source) Expand "Other Data Sources" >> "Project Data Sources" >> 'your database name' >> 'your table name'

Display Member: your S/N column

Value Member: your S/N column

Selected Value: you can leave blank for now.

Attachment 187090

Once you have done that and you run the application you will see the dropdown is populated with the data of your S/N column. I know you said that you will use a barcode scanner to get the values, we will get to that. Can you confirm that your combobox is populating?

You will also notice that your DataSet, BindingSource and TableAdapter was added automatically below in the component tray by the designer. It is important that you take note of that as you will need it.

Attachment 187091

[wes4dbt]

wes4dbt,

So do you think the OP is going to plug the code into his button, press it and boom! his life's mission is complete? There certainly are times where I wished someone can just shoot me a piece of code so that I can get on with it, but I am trying to teach him something here. I'm not sure what your problem is but after the OP started this thread, and obviously got some responses, he contacted me privately to ask for help as I have been of some assistance to him in another thread. Not everyone has been in this game for a zillion years.

You cannot tell me that directly accessing the database with code is a better option than doing it with a dataset. At the end of the day that is a developer's preference. Both methods have advantages as well as disadvantages.

For a dataset there are several advantages such as easier databinding to controls, easier to use, simplified CRUD operations, features for enforcing data consistency and integrity, etc, etc. while it is also true that for large and complex applications direct access may be more advisable.

For some reason lately you seem to be working very hard at being unpleasant.

I never implied that dday's post would solve all the OP's future problems. But it does answer the question that was asked and there was no response from the OP indicating if that code was sufficient for his needs or not. I have no idea what else the OP is intending to do in the future. Nor did I make any claims about if directly assessing the database is better or worse.

[schoemr]

No wes, I wasn't trying to be unpleasant. You perceive it that way. But you know I'm very observant. I picked up many things here regarding many people. I can tell you who post as multiple people etc. I also know exactly where your 'sentiments' started (in another section of vbforums) I now also know why.

I'm sorry you feel that lately I'm unpleasant...

[newVBM3]

Thank you schoemr, I appreciate all of your feedback. I do plan on using VB for the foreseeable future.

[newVBM3]

Hi all. I finished my project. Thanks schoemr and dday.

Here is example of how I did it:

Private Sub TextBox_TextChanged(sender As Object, e As EventArgs) Handles TextBox_TextChanged
Dim con As New SqlConnection("Data Source=SERVER NAME; Initial Catalog=DATABASE NAME; Integrated Security = True)
con.Open()
Dim str As String
str = "SELECT Crayon_ID FROM Crayon_Lot WHERE Crayon_SN = ' " + TextBox.Text + "'"
Dim cmd As New SqlCommand(str,con)
rdr = cmd.ExecuteReader
If rdr.Read() Then
Label.Text = rdr(Crayon_ID).ToString()
End If

If "Crayon_SN" Then
Label. Text. ToString(Crayon_ID")
End If

If Label.Text = "Crayon_ID" Then
    countdown
End If
con.Close()
End Sub

[jmcilhinney]

That is OK in principle but there are a number of things to address there. The most glaring is that you are using string concatenation to insert values into SQL code. That is an issue for various reasons but the most important is the potential for SQL injection attacks. A malicious user could potentially corrupt or delete your entire database by entering the right SQL code into your TextBox. The proper way to handle this is by using parameters. There's plenty of information around about that in various places but here is my personal take on the subject.

Secondly, you are creating objects and opening connections without closing or disposing them. That means that your app will be holding onto system resources for no reason. Any time you create an object that can be disposed, you should always dispose it when you're done with it. Any time you open a connection, you should always close it when you're done with it. The best way to do both is usually with a Using block. The Using statement will declare the variable and create the object, then the object will be closed/disposed at the end of the block. ALWAYS use a Using statement to create a disposable object unless you need it to live beyond the scope in which you create it.

A less significant issue is that you are using a control as data storage. Controls are for displaying data and user interaction. They are not for data storage. There's never a reason for you to test what the Text of a Label contains. Whatever is in a Label is there because you put it there from somewhere else. If you need to know what that value is, you should be looking at the original source.

Finally, the fact that you're querying the database on the TextChanged event is an issue. From what you posted earlier, it seems that you are expecting the user to enter 8 characters. That means that you will be querying the database 8 times and 7 of them will be pointless. You would also query the database again if the user cleared the TextBox, which be pointless again. If valid input values will always be the same length then you should add a condition to only query if the input is that length. If you don't know what length the input should be, you can start/restart a Timer on the TextChanged event and then query the database when the Timer Ticks, if and only if the TextBox contains text. You can set the Interval to say 300-500 milliseconds so that the user can type multiple characters in a row without Tick events in between but still won't have to wait long for the query to occur after they finish typing.

With all that in mind, your code might look like the following:

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    Dim serialNumber = TextBox1.Text
 
    If serialNumber.Length = 8 Then
        Dim connectionString = "Data Source=SERVER NAME; Initial Catalog=DATABASE NAME; Integrated Security = True"
        Dim sql = "SELECT Crayon_ID FROM Crayon_Lot WHERE Crayon_SN = @Crayon_SN"
 
        Using connection As New SqlConnection(connectionString),
              command As New SqlCommand(sql, connection)
            command.Parameters.Add("@Crayon_SN", SqlDbType.VarChar, 50).Value = serialNumber
 
            connection.Open()
 
            Using reader = command.ExecuteReader()
                If reader.Read() Then
                    Label1.Text = reader.GetString(0)
                Else
                    'Start countdown or whatever.
                End If
            End Using
        End Using
    End If
End Sub

or:

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    Timer1.Stop()
    Timer1.Start()
End Sub
 
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    Timer1.Stop()
 
    Dim serialNumber = TextBox1.Text
 
    If serialNumber.Length > 0 Then
        Dim connectionString = "Data Source=SERVER NAME; Initial Catalog=DATABASE NAME; Integrated Security = True"
        Dim sql = "SELECT Crayon_ID FROM Crayon_Lot WHERE Crayon_SN = @Crayon_SN"
 
        Using connection As New SqlConnection(connectionString),
              command As New SqlCommand(sql, connection)
            command.Parameters.Add("@Crayon_SN", SqlDbType.VarChar, 50).Value = serialNumber
 
            connection.Open()
 
            Using reader = command.ExecuteReader()
                If reader.Read() Then
                    Label1.Text = reader.GetString(0)
                Else
                    'Start countdown or whatever.
                End If
            End Using
        End Using
    End If
End Sub

[jmcilhinney]

If rdr.Read() Then
Label.Text = rdr(Crayon_ID).ToString()
End If

If Label.Text = "@ID" Then
    countdown
End If

Why would the Label ever contain "@ID"? Are you expecting that value to come from the database or is that the default value you put there that would be replaced if a match is found? If a match is not found, will the Label always contain "@ID" or will it possibly contain the ID you got from a previous query?

[newVBM3]

Why would the Label ever contain "@ID"? Are you expecting that value to come from the database or is that the default value you put there that would be replaced if a match is found? If a match is not found, will the Label always contain "@ID" or will it possibly contain the ID you got from a previous query?

I will update my code above. I looked and forgot to add the close connection and some other stuff to my sample code. I meant to put crayon ID. The s/n is scanned to textbox 1, search through that specific table and colum, if s/n found and correct ID, put grade ID from that column and row into the label, if label string is correct, go ahead begin countdown. I want it where I'm only pulling/view data from DB. Not updating anything.

[newVBM3]

That is OK in principle but there are a number of things to address there. The most glaring is that you are using string concatenation to insert values into SQL code. That is an issue for various reasons but the most important is the potential for SQL injection attacks. A malicious user could potentially corrupt or delete your entire database by entering the right SQL code into your TextBox. The proper way to handle this is by using parameters. There's plenty of information around about that in various places but here is my personal take on the subject.

Secondly, you are creating objects and opening connections without closing or disposing them. That means that your app will be holding onto system resources for no reason. Any time you create an object that can be disposed, you should always dispose it when you're done with it. Any time you open a connection, you should always close it when you're done with it. The best way to do both is usually with a Using block. The Using statement will declare the variable and create the object, then the object will be closed/disposed at the end of the block. ALWAYS use a Using statement to create a disposable object unless you need it to live beyond the scope in which you create it.

A less significant issue is that you are using a control as data storage. Controls are for displaying data and user interaction. They are not for data storage. There's never a reason for you to test what the Text of a Label contains. Whatever is in a Label is there because you put it there from somewhere else. If you need to know what that value is, you should be looking at the original source.

Finally, the fact that you're querying the database on the TextChanged event is an issue. From what you posted earlier, it seems that you are expecting the user to enter 8 characters. That means that you will be querying the database 8 times and 7 of them will be pointless. You would also query the database again if the user cleared the TextBox, which be pointless again. If valid input values will always be the same length then you should add a condition to only query if the input is that length. If you don't know what length the input should be, you can start/restart a Timer on the TextChanged event and then query the database when the Timer Ticks, if and only if the TextBox contains text. You can set the Interval to say 300-500 milliseconds so that the user can type multiple characters in a row without Tick events in between but still won't have to wait long for the query to occur after they finish typing.

With all that in mind, your code might look like the following:

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    Dim serialNumber = TextBox1.Text
 
    If serialNumber.Length = 8 Then
        Dim connectionString = "Data Source=SERVER NAME; Initial Catalog=DATABASE NAME; Integrated Security = True"
        Dim sql = "SELECT Crayon_ID FROM Crayon_Lot WHERE Crayon_SN = @Crayon_SN"
 
        Using connection As New SqlConnection(connectionString),
              command As New SqlCommand(sql, connection)
            command.Parameters.Add("@Crayon_SN", SqlDbType.VarChar, 50).Value = serialNumber
 
            connection.Open()
 
            Using reader = command.ExecuteReader()
                If reader.Read() Then
                    Label1.Text = reader.GetString(0)
                Else
                    'Start countdown or whatever.
                End If
            End Using
        End Using
    End If
End Sub

or:

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    Timer1.Stop()
    Timer1.Start()
End Sub
 
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    Timer1.Stop()
 
    Dim serialNumber = TextBox1.Text
 
    If serialNumber.Length > 0 Then
        Dim connectionString = "Data Source=SERVER NAME; Initial Catalog=DATABASE NAME; Integrated Security = True"
        Dim sql = "SELECT Crayon_ID FROM Crayon_Lot WHERE Crayon_SN = @Crayon_SN"
 
        Using connection As New SqlConnection(connectionString),
              command As New SqlCommand(sql, connection)
            command.Parameters.Add("@Crayon_SN", SqlDbType.VarChar, 50).Value = serialNumber
 
            connection.Open()
 
            Using reader = command.ExecuteReader()
                If reader.Read() Then
                    Label1.Text = reader.GetString(0)
                Else
                    'Start countdown or whatever.
                End If
            End Using
        End Using
    End If
End Sub

Thank you. I will update my code tomorrow and come back here to update.