inserting data into SQL Server does not work but there is no error message to show

Question

I am using asp.net 4.5 and SQL Server 2008 Express.

I want to insert a form's data into my database, connection string is OK and works in another page but in this page it seems the insert button just refreshes the page and no insert happened!

The code :

 protected void Button1_Click(object sender, EventArgs e)
 {
        var conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        var cmdd = "insert into poroje (Pname,Pmozu,Ppayan,StartDate,EndTime,makan,tozih) values(@Pname,@Pmozu,@Ppayan,@StartDate,@EndTime,@makan,@tozih)";

        using (SqlConnection cnn = new SqlConnection(conn))
        {
            using (SqlCommand cmd = new SqlCommand(cmdd, cnn))
            {
                if (aksporoje.HasFile)
                {
                    if (CheckFileType(aksporoje.FileName))
                    {
                       PersianCalendar Pe = new PersianCalendar();

                       string s = Pe.GetYear(System.DateTime.Now).ToString();

                       if (Directory.Exists("~/poroje/"+s))
                       {
                           Directory.CreateDirectory(Server.MapPath("~/poroje/" + s+pushePoroje(System.DateTime.Today)));
                           string filePath = "~/poroje/" + s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName;
                           aksporoje.SaveAs(MapPath(filePath));

                           cmd.Parameters.AddWithValue("@aks","poroje/"+ s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName);
                       }
                       else {
                           Directory.CreateDirectory(Server.MapPath("~/poroje/"+s));
                           Directory.CreateDirectory(Server.MapPath("~/poroje/"+s+ pushePoroje(System.DateTime.Today)));
                           string filePath = "~/poroje/" + s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName;
                           aksporoje.SaveAs(MapPath(filePath));
                           cmd.Parameters.AddWithValue("@aks", "poroje/" + s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName);
                       }
                   }
                } 
                cmd.Parameters.AddWithValue("@Pname", txtnam.Text);
                cmd.Parameters.AddWithValue("@Pmozu", txtmozu.Text);
                cmd.Parameters.AddWithValue("@Ppayan",false);
                cmd.Parameters.AddWithValue("@StartDate",PersianDateTextBox1.Text);
                cmd.Parameters.AddWithValue("@EndTime",null);
                cmd.Parameters.AddWithValue("@makan", txtmakan.Text);
                cmd.Parameters.AddWithValue("@tozih", txttozih.Text);

                try
                {
                    cnn.Open();

                    int recordsAffected = cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    lblpeygham.Visible = true;
                    lblpeygham.Text = "error";
                }
                finally
                {
                    cnn.Close();
                    lblpeygham.Visible = true;
                    lblpeygham.Text = "data inserted";
                }
            }
        }

When I click on button the page refreshes amd the "data inserted" message is shown to me. But when I check the database nothing is inserted! Can you help me please?

Well, the "data inserted" always shows, because it's in the finally part of the code. — SchmitzIT
– SchmitzIT, Commented Mar 12, 2014 at 19:14
Did you debug your code and verify that recordsAffected is 1? — Rick S
– Rick S, Commented Mar 12, 2014 at 19:16
What's this @aks parameter you're adding? It's being added as a parameter to the command, but doesn't seem to be involved in the INSERT command at all. — SchmitzIT
– SchmitzIT, Commented Mar 12, 2014 at 19:18
Can you please show us the connection string you're using? — marc_s
– marc_s, Commented Mar 12, 2014 at 19:23
@marc_s <add name="ConnectionString" connectionString="workstation id=akhbarr.mssql.somee.com;packet size=4096;user id=jujuee_SQLLogin_1;pwd=u9xyfsnev6;data source=akhbarr.mssql.somee.com;persist security info=False;initial catalog=akhbarr" /> — Curious Developer
– Curious Developer, Commented Mar 12, 2014 at 20:18

Community · Accepted Answer · 2017-05-23 12:27:07Z

0

The connection needs to be opened before the SQL COMMAND object is instantiated.

I bet you the cnn.Open is wiping out any setting.

See this post for a correct working code at end. Here is the correct order of events.

1 - Open connection object
2 - Declare string
3 - Open Command object
4 - Set parameters
5 - Call Execute Non SQL
6 - Close command object
7 - Close Connection object

ASP.Net insert data from Textbox to a database

Also, put a break point in the code, check the return values.

Good luck

J

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Mar 12, 2014 at 19:27

CRAFTY DBA

15k4 gold badges28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jackson · Accepted Answer · 2014-03-12 19:47:43Z

0

Put the whole SQL command into a string a run that directly on the server. What happens? Is the value added? if so then check the code again. That should help you a lot.

answered Mar 12, 2014 at 19:47

Jackson

1498 bronze badges

Comments

marc_s · Accepted Answer · 2014-03-12 20:23:05Z

I think this is wrong..

int recordsAffected = cmd.ExecuteNonQuery();

I have never ran the command executeNonquery from an int before.. ever. It should be like

cmd.ExecuteNonQuery();

Even then, I think you want everything in the try catch.. like this..

try
{
    cnn.Open();

    var cmdd = "insert into poroje (Pname,Pmozu,Ppayan,StartDate,EndTime,makan,tozih) values(@Pname,@Pmozu,@Ppayan,@StartDate,@EndTime,@makan,@tozih)";

    using (SqlConnection cnn = new SqlConnection(conn))
    {
        using (SqlCommand cmd = new SqlCommand(cmdd, cnn))
        {
            if (aksporoje.HasFile)
            {
                if (CheckFileType(aksporoje.FileName))
                {
                   PersianCalendar Pe = new PersianCalendar();

                   string s = Pe.GetYear(System.DateTime.Now).ToString();

                   if (Directory.Exists("~/poroje/"+s))
                   {
                       Directory.CreateDirectory(Server.MapPath("~/poroje/" + s+pushePoroje(System.DateTime.Today)));
                       string filePath = "~/poroje/" + s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName;
                       aksporoje.SaveAs(MapPath(filePath));

                       cmd.Parameters.AddWithValue("@aks","poroje/"+ s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName);
                   }
                   else {
                       Directory.CreateDirectory(Server.MapPath("~/poroje/"+s));
                       Directory.CreateDirectory(Server.MapPath("~/poroje/"+s+ pushePoroje(System.DateTime.Today)));
                       string filePath = "~/poroje/" + s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName;
                       aksporoje.SaveAs(MapPath(filePath));
                       cmd.Parameters.AddWithValue("@aks", "poroje/" + s + "/" + pushePoroje(System.DateTime.Today) + "/" + aksporoje.FileName);
                   }
               }
            } 
            cmd.Parameters.AddWithValue("@Pname", txtnam.Text);
            cmd.Parameters.AddWithValue("@Pmozu", txtmozu.Text);
            cmd.Parameters.AddWithValue("@Ppayan",false);
            cmd.Parameters.AddWithValue("@StartDate",PersianDateTextBox1.Text);
            cmd.Parameters.AddWithValue("@EndTime",null);
            cmd.Parameters.AddWithValue("@makan", txtmakan.Text);
            cmd.Parameters.AddWithValue("@tozih", txttozih.Text);


            cmd.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                lblpeygham.Visible = true;
                lblpeygham.Text = "error";
            }
            finally
            {
                cnn.Close();
                lblpeygham.Visible = true;
                lblpeygham.Text = "data inserted";
            }

Something like that should work. But I think the main issue was the first mentioned above.

Hope this helps.

Curious Developer · Accepted Answer · 2014-03-13 14:56:01Z

0

thank you all! I change this part

 catch (SqlException)
            {
                lblpeygham.Visible = true;
                lblpeygham.Text = "error";
            }

to

 catch (SqlException ex)
                {
                    lblpeygham.Visible = true;
                    lblpeygham.Text = ex.message;
                }

and understand that the startDate type in databse is dateTime but i was inserting nvarchar in it! but now i hav a new problem. i want to create new folder with the Year name in host, but if I write

  Directory.CreateDirector("~/poroje/"+s);

the access deny error happen. and if i use

  Directory.CreateDirectory(Server.MapPath("~/poroje/"+s));

there is no error but directory don't create!

answered Mar 13, 2014 at 14:56

Curious Developer

1112 silver badges17 bronze badges

1 Comment

Damien_The_Unbeliever Over a year ago

If you have a new problem (and, especially, in this case, where it's not even closely related to your existing issue), ask a new question. Don't put follow up questions in an answer (They're unlikely to be answered)

Collectives™ on Stack Overflow

inserting data into SQL Server does not work but there is no error message to show

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related