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.
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.
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.
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
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.
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.
Last edited by schoemr; Feb 28th, 2023 at 02:35 PM.
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.
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.
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.
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"
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.
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"
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.
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.
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.
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.
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.
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.
Reply With Quote
Originally Posted by newVBM3
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.
