generate transaction number in C#

Question

I have a C# application with a Transaction No. in windows form. I need to generate my transno. whenever i press the button to generate the transaction no. it will promp me a messagebox that says

I try to change the query from like '%" + sdate + "'" to like '" + sdate + "%'"

near 1 transno from tblcar where transno like '20200605%' at line 1

private void GetTransNo()
    {
        try
        {
            string sdate = DateTime.Now.ToString("yyyyMMdd");
            string transno;
            int count;
            con.conDB.Open();
            cmd = new MySqlCommand("SELECT TOP 1 transno from tblcart where transno like '" + sdate + "%'",con.conDB);
            dr = cmd.ExecuteReader();
            dr.Read();
            if (dr.HasRows)
            {
                transno = dr[0].ToString();
                count = int.Parse( transno.Substring(8, 4));
                lblTransaction.Text = sdate + (count + 1);
            }
            else
            {
                transno = sdate + "1001";
                lblTransaction.Text = transno;
            }
            dr.Close();
            con.conDB.Close();

        }
        catch(Exception ex)
        {
            con.conDB.Close();
            MessageBox.Show(ex.Message, stitle, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
    }

[link] https://www.youtube.com/watch?v=F9LIzZAox1U&list=PL0VHAkRIxGQ7RLUtxdA6CBlVLT_K3m4SC&index=27 By the way. im following this tutorial — KennyTan, Jun 05 '20 at 08:25
TOP 1 is for Sql Server. MySql uses LIMIT 1 at the end of the query — Steve, Jun 05 '20 at 08:25
There are also other errors in your code. a) You are using a global connection object, not good for scalability and performances of your server and your application. b) You are calling Read and then HasRows. Just looking at the return value of Read is enough. c) But the most dangerous error is the string concatenation instead of using a parameterized query — Steve, Jun 05 '20 at 08:30
`cmd = new MySqlCommand("SELECT LIMIT 1 transno from tblcart where transno like '" + sdate + "%'",con.conDB);` — KennyTan, Jun 05 '20 at 08:40
LIMIT 1 goes at the end of the query after the WHERE or ORDER BY statement. Did you look at the duplicate question? — Steve, Jun 05 '20 at 08:52
bit confusing where to put LIMIT 1 `("SELECT transno FROM tblcart where transno like '" + sdate + "%',LIMIT 1", con.conDB);` — KennyTan, Jun 05 '20 at 09:01
@KennyTan then checkout the accepted answer in the duplicate question, it shows you the syntax. — Shadow, Jun 05 '20 at 11:55

generate transaction number in C#

0 Answers0