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.