Textbox and SQL Countdown

Hello.

I have a countdown timer working where you manually enter "ABC". I use a "If End If" statement. Works perfectly like this.

What I am trying to figure out now is how to connect it to SQL Databse where a Table already exists. For Example, I want to scan S/N "ABC12345", and I want my countdown to look in the database for the associated color, and start countdown.

Here is summary:
1. Enter S/N in textbox "ABC12345" entered
2. "#ADD8E6" is the color associated with this S/N
3. Therefore, a countdown starts for 1 hour.

So you're asking how to query a database with a filter?

Now that I think about it, yes. This is my first attempt at trying to connecting to SQL Database.
Here is my sample:

sqlstmt = "SELECT Color_ID FROM Crayon_LOT WHERE Color_ID = #ADD8E6"

Not sure how to use that in my If End If condition. My sql filter is probably not correct as well.

Code:

---

If Textbox1.ColorID = #ADD8E6" Then
    countdown = TimeSpan.FromSeconds(5)
            endTime = DateTime.Now.Add(countdown)
End If

---

A cursory glance at your SQL, it looks like you only want to verify that the value exists rather than use any actual value.

What you can do is get the number of records using the T-SQL function COUNT function, use ExecuteScalar to get the single value, and then use your conditional statement to do... something.

Here is an example:

Code:

---

Private Function ColorIdExists(colorId As String) As Boolean
    Dim connectionString = "Data Source=MyServerName;Initial Catalog=MyDatabaseName;User ID=MyUserName;Password=MySuperSecretPassword"
    Dim count = 0

    Try
        Using con = New SqlConnection(connectionString)
            Using cmd = New SqlCommand("SELECT Count(Color_ID) FROM Crayon_LOT WHERE Color_ID=@colorId;", con)
                cmd.Parameters.AddWithValue("@colorId", colorId)

                con.Open()
                count = Convert.ToInt32(cmd.ExecuteScalar())
                con.Close()
            End Using
        End Using
    Catch ex As Exception
        ' do something with the exception
    End Try

    Return count > 0
End Function

---

Usage:

Code:

---

If (ColorIdExists(TextBox1.Text)) Then
    ' it exists!
End If

---

I want to verify that that specific S/N is indeed that Color_ID. And if it is that Color_ID, go ahead and start counting down.

Then there you go, that example should do exactly that.

Quote:

---

Originally Posted by newVBM3

I want to verify that that specific S/N is indeed that Color_ID. And if it is that Color_ID, go ahead and start counting down.

---

In your example the "input" is "ABC12345" and the "output" is "#ADD8E6". Based on that, I would imagine you have at least two different columns in your database; one for what you call the "S/N", and one for the associated color.

From that, I would imagine that the SQL SELECT would need to be along the lines of:

Code:

---

SELECT Color_ID FROM Crayon_LOT WHERE [S/N] = UserInput

---

And you replace [S/N] with the actual column name in your database that stores the "S/N", and you replace UserInput with either the dangerous method of simple string concatenation of the input directly from the user, or the safer method of using query parameters.

Hi, are you asking how to connect to and query a database?

That is correct, I have multiple columns. I think I should look for the Color_ID, as that is when I want to start a specific countdown. The table is called Crayon_LOT. The two columns I am interested in is Crayon_SN and Color_ID.

Like this?:

Code:

---

SELECT Color_ID FROM Crayon_LOT WHERE Crayon_SN  = UserInput

---

What are parameters I can use for UserInput?

Hello. Yes.

I have a Label1. 'Scan Crayon Label'. Textbox1 to receive input.

Okay no problem, do want to query inside the database itself or are you using Visual Studio?

The first thing you have to do is to add a DataSource to your project. Did you do that already or should I show you?

Using Visual Studio.

I have.

Code:

---

Public Const CR_Connect "Data Source=MyServerName;Initial Catalog=MyDatabaseName;User ID=MyUserName;Password=MySuperSecretPassword"

---

Is this correct way?

Quote:

---

Originally Posted by newVBM3

I have.

Code:

---

Public Const CR_Connect "Data Source=MyServerName;Initial Catalog=MyDatabaseName;User ID=MyUserName;Password=MySuperSecretPassword"

---

Is this correct way?

---

Okay so it is a SQL database. But I don't think that the name of the database is "MyDatabaseName"??

In VS you have to add a datasouce. Do you have SQL Server Management Studio (SSMS) installed? That would be the easiest I think to get the database properties.

Yes it is a SQL Database. Yes I have SSMS installed. No, database is not actually "MyDatabaseName". I used that as a generic name for this thread.

Adding through Server Explorer and then right clicking on Data Connections --> Add Connection?

In VS click on View >> Other Windows >> Data Sources

You should see this as per image.

Click on Add datasource as in the image.

It will take you through the process of adding the datasouce. If you login to SSMS before you login you will see something like: MyServer\MyInstance.

Attachment 187080


You will need this to add your DataSource. This is where you specify and test the connection to the database. Once you have added the datasouce successfully, the hard part is over. Then we can work on you query.

Edit: Assuming you have already attached your database in SSMS.

Attachment 187081

Test Connect Succeeded. Yes, Database already attached to SSMS.

Quote:

---

Originally Posted by newVBM3

Attachment 187081

Test Connect Succeeded. Yes, Database already attached to SSMS.

---

The attachment thingy on vbforums is not working, you have to go down and add it though "Manage Attachments"

See new attachment.

Why are you using a textbox for this? A textbox allows people to type something in. In this case, the text may or may not match something in a database, so you are asking people to take a guess at which string is right, including the capitalization. If you are trying to frustrate the user, that seems like a good approach, but otherwise how about a listbox or drop down that includes just the options that are available?

Did you go though the rest of the prompts? It will ask you to check tables and also specify a dataset name (I usually just go with the default name). Once you have done that you should see your dataset in the datasources window.

Quote:

---

Originally Posted by Shaggy Hiker

Why are you using a textbox for this? A textbox allows people to type something in. In this case, the text may or may not match something in a database, so you are asking people to take a guess at which string is right, including the capitalization. If you are trying to frustrate the user, that seems like a good approach, but otherwise how about a listbox or drop down that includes just the options that are available?

---

Yes Shaggy you are right :) But I think the OP first need to connect his database so that he can use it in Visual Studio. When we get to that part it is definitely something to implement.

I didn't get any other prompts after I successfully tested the connection.

Quote:

---

Originally Posted by newVBM3

I didn't get any other prompts after I successfully tested the connection.

---

After clicking on test connection you should click NEXT, and NEXT and check "Tables" and Specify the dataset name..

Quote:

---

Originally Posted by schoemr

Yes Shaggy you are right :) But I think the OP first need to connect his database so that he can use it in Visual Studio. When we get to that part it is definitely something to implement.

---

The reason why I did not go with a Dropdown or Listbox because those are premade options to choose from. And users will scan a barcode, where a crayon sn is always new and saved in sql. I have the textbox, because the user scans the barcode

The only option I got was Ok. I will do some searching.

All the tables are there in the dropdown arrow under the database. And my table of interest is there.

Quote:

---

Originally Posted by newVBM3

The only option I got was Ok. I will do some searching.

---

Look at the 2nd image in post #17. You have to click "Add new datasource" - the wizard will then take you though the process of adding your dataset.

If you did that you will be prompted to select "Tables" and also to specify the name of your dataset. Once you have added your dataset you will see it in the Data Sources window.

If you select your dataset and click on "Edit DataSet with designer"

Attachment 187083


you will see your database. It will look something like this:

Attachment 187084


When you get here the rest is easy. I have to go now, but I will return in the morning and post more pictures if needed.

Quote:

---

Originally Posted by newVBM3

All the tables are there in the dropdown arrow under the database. And my table of interest is there.

---

No one is going to know what you see in front of you unless you show us....

Ok now I got the Data Source. I had to go to Views and have the option checked. Now It has the Data Sources tab next the Server Explorer and Toolbox. This time it did give the prompts, I walked through it and I selected the table from the database.

I was mixing up Server Explorer with Data Source.

Quote:

---

Originally Posted by schoemr

Look at the 2nd image in post #17. You have to click "Add new datasource" - the wizard will then take you though the process of adding your dataset.

If you did that you will be prompted to select "Tables" and also to specify the name of your dataset. Once you have added your dataset you will see it in the Data Sources window.

If you select your dataset and click on "Edit DataSet with designer"

Attachment 187083


you will see your database. It will look something like this:

Attachment 187084


When you get here the rest is easy. I have to go now, but I will return in the morning and post more pictures if needed.

---

Yes I made it this far. Ok ttyl.

There really is no need to add a datasource to your project for this. Don't get me wrong, it could help, but it seems a bit overboard for what you're wanting to do.

Quote:

---

Originally Posted by newVBM3

The reason why I did not go with a Dropdown or Listbox because those are premade options to choose from. And users will scan a barcode, where a crayon sn is always new and saved in sql. I have the textbox, because the user scans the barcode

---

Ah, so the user won't be typing this in? If so, then a label would be a better option. Of course, if the user will NORMALLY scan it, but OCCASIONALLY type it, then a textbox would be good.

As a general rule, I don't go with the Datasource wizard. I use raw SQL as DDay showed earlier. Of course, that does require that you get your connection string right, which is easy to do after you have done it once.

Quote:

---

Originally Posted by dday9

There really is no need to add a datasource to your project for this. Don't get me wrong, it could help, but it seems a bit overboard for what you're wanting to do.

---

I was wonder the same thing. Your code in post #4 does what the OP asked for. Not sure why they're going through the process of adding a datasource.

Quote:

---

Originally Posted by dday9

There really is no need to add a datasource to your project for this. Don't get me wrong, it could help, but it seems a bit overboard for what you're wanting to do.

---

Okay I thought I called the data source properly with how you guided me. All new to me.

I'm having trouble scanning a specific column.

Here is how I want the app to function:
SQL Server exists.
1. User grabs crayon, scans the barcode, which is the crayons s/n
2. If the crayon s/n is a specific crayon ID, then a countdown starts

It will be for 7 different crayon ID colors. If I can get one I can pretty copy paste the rest to the If End if.

Quote:

---

Originally Posted by Shaggy Hiker

Ah, so the user won't be typing this in? If so, then a label would be a better option. Of course, if the user will NORMALLY scan it, but OCCASIONALLY type it, then a textbox would be good.

As a general rule, I don't go with the Datasource wizard. I use raw SQL as DDay showed earlier. Of course, that does require that you get your connection string right, which is easy to do after you have done it once.

---

Correct. User will be scanning crayon sn off a barcode. It will be scanned into the textbox. With occasionally typing it in.

I am new, I am open to what is best practice for these kinds of applications.

Get my connection string right?

DDay gave you an example in post #4. Did you try it? If so, what's the problem? What exactly are you having trouble with.

Quote:

---

Originally Posted by wes4dbt

DDay gave you an example in post #4. Did you try it? If so, what's the problem? What exactly are you having trouble with.

---

What does "Using" stand for in the Try..Catch?

Quote:

---

Originally Posted by newVBM3

What does "Using" stand for in the Try..Catch?

---

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.

Quote:

---

Originally Posted by jmcilhinney

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.

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.

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

Quote:

---

Originally Posted by 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.

---

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.

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...

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

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

Here is example of how I did it:

Code:

---

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

---

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:

vb.net Code:

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:

vb.net Code:

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

Quote:

---

Originally Posted by newVBM3

Code:

---

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?

Quote:

---

Originally Posted by jmcilhinney

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.

Quote:

---

Originally Posted by 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:

vb.net Code:

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:

vb.net Code:

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.