I want a code that does not repeat the number

Question

1.00/5 (2 votes)

See more:

I want code that does not repeat the number in the database. I wrote the code and it works for me. There is a drawback, for example, if the number is eating breakfast and needs to buy lunch, it appears that it is already in the database and cannot buy lunch because it is there. I need a code so that the person can buy every meal on the dropdownlist page, including breakfast, lunch, and dinner.

What I have tried:

VB

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    con.ConnectionString = "Data Source=DESKTOP-NO2IM8B\SQLEXPRESS;Initial Catalog=Barcode;
                            User ID=sa;Password=P@ssw0rd"
    con.Open()
    cmd.CommandText = "SELECT * FROM Table_ERROR WHERE sale_id = '" _
                      + txtUserName.Text + "'"

    cmd.Connection = con
    If cmd.ExecuteScalar >= 1 Then
        Label12.text = "The student already exists+الطالب موجود بالفعل"
        Exit Sub

        con.Close()
    End If

    'Dim ID_STUDENT As Integer = txtUserName.Text
    'Dim con As New SqlConnection
    'Dim cmd As New SqlCommand
    con.ConnectionString = "Data Source=DESKTOP-NO2IM8B\SQLEXPRESS;Initial Catalog=Barcode;
                            User ID=sa;Password=P@ssw0rd"
    con.Open()
    Dim sql As String = "INSERT INTO Table_ERROR (sale_id,building,Meal_time,date)
                         VALUES (@sale_id,@building,@Meal_time,@date) "
    cmd = New SqlCommand(sql, con)
    cmd.Parameters.AddWithValue("sale_id", txtUserName.Text)
    cmd.Parameters.AddWithValue("building", DropDownList1.Text)
    cmd.Parameters.AddWithValue("Meal_time", DropDownList2.Text)
    cmd.Parameters.AddWithValue("date", Now)
    cmd.ExecuteNonQuery()
    Label12.text = "The student has been entered+تم ادخال رقم الطالب:-" _
                   + txtUserName.Text

End Sub

Posted 25-Oct-23 19:01pm

yossef2023

Updated 27-Oct-23 7:30am

Deeksha Shenoy

v3

Add a Solution

Comments

Member 15627495 26-Oct-23 2:24am

as you use String Type ' UserName for sale_id in your DB,
it looks like you have 'Db errors design', it's weak structure.

you can show your Database structure please, I'll try to understand the logical, and bring fix on the purposes.
thank you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-10-25T20:25:00

First off, don;t do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Second, don't use ExecuteScalar with a SELECT query that returns multiple columns, and potentially multiple rows - what you get isn't particularly useful as it's content of the first column of the first row of the SELECTed data - which is subject to change if your DB design changes.
If you want to know how many matching rows there are, you need to ask that specific question with COUNT:

SQL

SELECT COUNT(sale-id) FROM Table_ERROR WHERE ...

which returns a single number.

Third, don't use SQL keywords as column names: DATE is a type in SQL, so when you use it as a column name, you need to surround it with "[" and "]" to indicate it's a literal name rather than the datatype you are using.

Fourth, don't hardcode connection strings into your apps : it means you have to change every single instance when you release code of change your system and it's very easy to get one wrong. Hold connections strings in a settings file so that it can be edited without changing your code!

And if you want to allow students to have three meals a day, you need to extend your query to check that the date matches, and that the appropriate meal has not been taken already: these are probably the date and meal_time what what the values you dump into them are, I have no idea so I can't directly help you with that.