After reader read wrong value insert new row...????

Goran Bibic

8y901Learn iOS Programming

I need to reader do NOTHING

private void prijavaAction() //testna
{
{
SqlConnection con = new SqlConnection(cs);
if (textBox1.Text.All(char.IsDigit))
{
string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
using (SqlConnection connection = new SqlConnection(cs))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
if (reader.Read()) //radi
//while (reader.Read()) //radi
{
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
readerresultTextbox.Text = (reader["ime_prezime"].ToString());
}
else
{
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
messageLabel.Text = "Radnik ne postoji u bazi! \nProverite vašu karticu ili probajte ponovo!";
textBox1.Text = "";
readerresultTextbox.Clear();
}
}
finally
{
string prijava = "SELECT count(*) from dbo.prijava_radnika where vrijemeprijave is not null and vrijemeodjave is null and radnik = '" + readerresultTextbox.Text + "' and redni_broj is NULL";
SqlCommand provjeraprijaveradnika = new SqlCommand(prijava, con);
con.Open();
int result = Convert.ToInt32(provjeraprijaveradnika.ExecuteScalar());
//con.Open();
con.Close();
if ((result == 0)) //radi
//if ((prijava !))
{
String saveStaff = "INSERT INTO dbo.prijava_radnika (vrijemeprijave, status, radnik,idfakture) VALUES(@vrijemeprijave,'prijavljen', '" + readerresultTextbox.Text + "',@idfakture)";
using (SqlConnection openCon2 = new SqlConnection(cs))
{
using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
{
querySaveStaff.Connection = openCon2;
querySaveStaff.Parameters.Add("@vrijemeprijave", SqlDbType.DateTime).Value = DateTime.Now;
querySaveStaff.Parameters.Add("@idfakture", SqlDbType.Int).Value = id_fakture;
openCon2.Open();
querySaveStaff.ExecuteNonQuery();
openCon2.Close();
//notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFE11A");
// messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
}
}
}
else
{
String updateStaff = " declare @maxNo integer = 0 select @maxNo = isnull(max(redni_broj), 0) from[dbo].[prijava_radnika] UPDATE[dbo].[prijava_radnika] SET"
+ " [vrijemeodjave] = @vrijemeodjave,[redni_broj] = (@maxNo+1),[status] = 'odjavljen' WHERE radnik = '" + readerresultTextbox.Text + "'and[status] = 'prijavljen'";
//string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
using (SqlConnection connection1 = new SqlConnection(cs))
{
SqlCommand command1 = new SqlCommand(queryString, connection1);
connection1.Open();
SqlDataReader reader1 = command1.ExecuteReader();
connection.Close();
try
{
if (reader1.Read())
{
using (SqlConnection openCon3 = new SqlConnection(cs))
{
using (SqlCommand queryupdateStaff = new SqlCommand(updateStaff))
{
queryupdateStaff.Connection = openCon3;
queryupdateStaff.Parameters.Add("@vrijemeodjave", SqlDbType.DateTime).Value = DateTime.Now;
openCon3.Open();
queryupdateStaff.ExecuteNonQuery();
openCon3.Close();
}
}
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFE11A");
messageLabel.Text = (reader1["ime_prezime"].ToString()) + " je odjavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
}
}
catch { }
}
// Always call Close when done reading.
reader.Close();
}
}
}
}
else
{
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
messageLabel.Text = "Bar kod nije važeci ili nije bar kod";
textBox1.Text = "";
}
}
}

19 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
Accepted answer
oh k,
cut line no 92,93,94,95 ie below code
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
                   messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
readerresultTextbox.Text = (reader["ime_prezime"].ToString());
and paste it after if(reader.read()) that is after lin no 20
Goran Bibic8y ago
Congratulation! Thank you....
Goran Bibic8y ago
Maybe to close reader somwhere?
Goran Bibic8y ago
Have one error...when couple times put wrong code...readerresulttextbox is empty and when first time put insisde right code...insert empty readerresultvalue...Undestand?

Bar code put inside ok insert comand ok (Name, status, dattime)
Bar code put inside ok update command ok (Name, status, dattime)

work fine...'

when couple time put wrong code...readerresulttextbox is empty...

When put right code going insert comand fine BUT (Name(emptycell), status, dattime)
Rafnas T P8y ago
line no 50,51 check in update statement you have to put one space as in red
String updateStaff = " declare @maxNo integer = 0 select @maxNo = isnull(max(redni_broj), 0) from[dbo].[prijava_radnika] UPDATE[dbo].[prijava_radnika] SET"
+ " [vrijemeodjave] = @vrijemeodjave,[redni_broj] = (@maxNo+1),[status] = 'odjavljen' WHERE radnik = '" + readerresultTextbox.Text + "'and [status] = 'prijavljen'";
Rafnas T P8y ago
now the functionality is, when the reader is correct then only inserts or updates, but here every time when reader is correct it inserts, it will not go to update because your select statement always return 1. pls tell me when do you wnted to update and when you wanted to insert if remain all ok.
Rafnas T P8y ago
ya thats right, then how do you want
Goran Bibic8y ago
No errors...but when I hawe wrong value couple times...reader result is empty...and when I put right value insert comand insert empty value from readerresult text box?
Rafnas T P8y ago
remove line no 91 reader.Close();
it will solve your error I think.
Goran Bibic8y ago
Now just work insert comand...two times...and then error meta data reader close...else (update command) not use...somesthing wrong
Rafnas T P8y ago
i have modifide your code just check
private void prijavaAction()  //testna
{

    {
        SqlConnection con = new SqlConnection(cs);

        if (textBox1.Text.All(char.IsDigit))

        {
            string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
            using (SqlConnection connection = new SqlConnection(cs))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    if (reader.Read())  //radi
                                        //while (reader.Read())  //radi
                    {
                        string prijava = "SELECT count(*) from dbo.prijava_radnika where vrijemeprijave is not null and vrijemeodjave is null and radnik = '" + readerresultTextbox.Text + "' and redni_broj is NULL";
                        SqlCommand provjeraprijaveradnika = new SqlCommand(prijava, con);
                        con.Open();
                        int result = Convert.ToInt32(provjeraprijaveradnika.ExecuteScalar());
                        //con.Open();
                        con.Close();
                        if ((result == 0)) //radi
                                           //if ((prijava !))
                        {
                            String saveStaff = "INSERT INTO dbo.prijava_radnika (vrijemeprijave, status, radnik,idfakture) VALUES(@vrijemeprijave,'prijavljen', '" + readerresultTextbox.Text + "',@idfakture)";
                            using (SqlConnection openCon2 = new SqlConnection(cs))
                            {
                                using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
                                {
                                    querySaveStaff.Connection = openCon2;
                                    querySaveStaff.Parameters.Add("@vrijemeprijave", SqlDbType.DateTime).Value = DateTime.Now;
                                    querySaveStaff.Parameters.Add("@idfakture", SqlDbType.Int).Value = id_fakture;
                                    openCon2.Open();
                                    querySaveStaff.ExecuteNonQuery();
                                    openCon2.Close();

                                    //notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFE11A");
                                    // messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                }
                            }
                        }
                        else
                        {

                            String updateStaff = " declare @maxNo integer = 0 select @maxNo = isnull(max(redni_broj), 0) from[dbo].[prijava_radnika] UPDATE[dbo].[prijava_radnika] SET"
                                + " [vrijemeodjave] = @vrijemeodjave,[redni_broj] = (@maxNo+1),[status] = 'odjavljen' WHERE radnik = '" + readerresultTextbox.Text + "'and[status] = 'prijavljen'";



                            //string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
                            using (SqlConnection connection1 = new SqlConnection(cs))
                            {
                                SqlCommand command1 = new SqlCommand(queryString, connection1);
                                connection1.Open();
                                SqlDataReader reader1 = command1.ExecuteReader();
                                connection.Close();
                                try
                                {
                                    if (reader1.Read())
                                    {


                                        using (SqlConnection openCon3 = new SqlConnection(cs))
                                        {
                                            using (SqlCommand queryupdateStaff = new SqlCommand(updateStaff))
                                            {
                                                queryupdateStaff.Connection = openCon3;
                                                queryupdateStaff.Parameters.Add("@vrijemeodjave", SqlDbType.DateTime).Value = DateTime.Now;
                                                openCon3.Open();
                                                queryupdateStaff.ExecuteNonQuery();
                                                openCon3.Close();

                                            }
                                        }

                                        notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFE11A");
                                        messageLabel.Text = (reader1["ime_prezime"].ToString()) + " je odjavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                    }
                                }
                                catch { }


                            }
                            // Always call Close when done reading.

                            reader.Close();
                        }
                        notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
                        messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                        readerresultTextbox.Text = (reader["ime_prezime"].ToString());
                    }
                    else
                    {
                        notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                        messageLabel.Text = "Radnik ne postoji u bazi! \nProverite vašu karticu ili probajte ponovo!";
                        textBox1.Text = "";
                        readerresultTextbox.Clear();

                    }
                }
                finally
                {

                }
            }
        }
        else
        {
            notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
            messageLabel.Text = "Bar kod nije važeci ili nije bar kod";
            textBox1.Text = "";
        }
    }
}
I have moved all final block into inside of if(reader.Read())
Goran Bibic8y ago
I dont have errors...just that problem
Goran Bibic8y ago
Yes, when is true insert or update...app going right...if reader false just message label

this
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
messageLabel.Text = "Radnik ne postoji u bazi! \nProverite vašu karticu ili probajte ponovo!";
textBox1.Text = "";
readerresultTextbox.Clear();

Thats it...some return...or somesthing
Rafnas T P8y ago
you should not write insert code into final block, this block every time will execute,if you get error also
Rafnas T P8y ago
what do you want to do exactly,
do you want when reader true then insert else nothing
Goran Bibic8y ago
Right what to do? Some return?
Rafnas T P8y ago
ya this code will insert only one because every time when you click button line 36 select statement will execute, that result is when condition met it will return 1 so that code will not run
Goran Bibic8y ago
Or just hit ENTER key...insert new row empty...but interesting just only one...you can hit 10 times...app insert jus only one empty row
Goran Bibic8y ago
Line 27,28,29,30 is error...app must return...not to work insert into