Find rate in the SQL table

Question

0.00/5 (No votes)

See more:

i use sql server 2008 and visul studio 2017

sql database name is RATEDB AND Table Name is RATE_TABLE
sql table contain 4 columun (1) CENTER (2) DOCTYPE (3) WEIGHT (4) RATE

userform contain

center = combobox 1
doctype = combobox 2
weight(gram) = textbox 1
rate = textbox 2

this project is find out rate of cover or parcel from the sql table when select center from combobox 1 and doctype from combobobox 2 and enter value in weight(gram) textbox 1 and rate show in textbox 2

but i need show OUTPUT as below condition when enter weight in textbox 1 suppose in the combobox 1 select "GUJARAT" and in the combobox 2 select is " COVER " and ener weight textbox as below value

BTWEEN 0 TO 100 then rate show is 30
BTWEEN 101 TO 250 then rate show is 40
BTWEEN 251 TO 500 then rate show is 50
BTWEEN 500 TO 1000 then rate show is 50

and also every 250 garam addition in above 1000 gram ADD Rs. 30 in rate then rate show is (50 + 30) = 80 e.g
if 1250 gram enter in weight textbox then rate show is 80

What I have tried:

my code is

VB.NET

Private Sub SHOW_RATE_BTN_Click(sender As Object, e As EventArgs) Handles SHOW_RATE_BTN.Click
        Try
            cmd = New SqlCommand("select CENTER,DOCTYPE,WEIGHT,RATE FROM RATE_TABLE where CENTER= '" & ComboBox1.Text & "' and DOCTYPE = '" & ComboBox2.Text & "' and WEIGHT = '" & TextBox1.Text & "' ", connection)
            connection.Open()
            myreader = cmd.ExecuteReader
            myreader.Read()
            TextBox2.Text = myreader(3)
            connection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

pls correct the code

Posted 12-Apr-22 19:34pm

kalpeshdelta

Updated 14-Apr-22 8:35am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

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

OriginalGriff · Answer 1 · 2022-04-12T19:55:00

I suspect I know what your problem is, but lets leave that for a moment, and fix a much more important problem first: 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?

FIx that throughout your whole app right now, and make sure you don't miss a single case - or your best mate will destroy your DB just to see the look on your face ...

Then change your DB design. I'm guessing that all the fields in your table are VARCHAR or NVARCHAR from the passing of a textbox content as the Rate to check - that's bad and causes a problem for you because all string based comparisons are made based solely on the first different pair of characters encountered.#
Which means that "1000.0" is smaller that "9.7" because the first different character pair is '1' and '9', and '1' is less than '9'.

I'd also expand your table by replacing the Weight column by two columns: MinWeight and MaxWeight - which means that the comparison doesn't need anything more than

SQL

... WHERE @Weight BETWEEN MinWeight AND MaxWeight ...

Which makes life a whole load easier!

Find rate in the SQL table

1 solution

Solution 1

Add your solution here

Preview 0

Find rate in the SQL table

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us