none
Connection Method is not working - Why? RRS feed

  • Question

  • Hi folks, 

    I need your help once again, I'm doing exactly the same code, to insert users, and to insert clients at the SQL Server, 

    However the method for insert users is working, and to insert clients is not working. I've tried everything and I can't figure out where is the error.

    Please help me out you guys with your remarkable knowledge,  What am I doing wrong?

    Thanks once again for your help.

           #region Methods of Insert.
    
            private bool cadastrarUser(User users)  // Insert Uers is working perfect
            {
                
                try
                {
                    StringBuilder query = new StringBuilder();
                    query.AppendFormat("insert into Users(username, loginname, [password], [date], userlogged, datelogged)");
                    query.AppendFormat(" VALUES (@username, @loginname, @password, @Date, @userlogged, @datelogged)");
    
                    
                    connectionOpen();
    
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = query.ToString();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add(new SqlParameter("@username", users.username));
                        cmd.Parameters.Add(new SqlParameter("@loginname", users.loginname));
                        cmd.Parameters.Add(new SqlParameter("@password", users.password));
                        cmd.Parameters.Add(new SqlParameter("@Date", users.date));
                        cmd.Parameters.Add(new SqlParameter("@userlogged", users.userlogged));
                        cmd.Parameters.Add(new SqlParameter("@datelogged", users.datelogged));
                        int result = cmd.ExecuteNonQuery();
                        cmd.Dispose();
    
                        int resultado = result;
    
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    return false;
                }
                finally
                {
                    connectionClose();
                }
            }


      private bool cadastrarClient(Client client) //Is not working
            {
               
                try
                {
                    StringBuilder query = new StringBuilder();
                    query.AppendFormat("insert into ClientTB (Name, Address, City, County, State, Zip_Code, CPF, Idnumber, email, celular_phone, home-phone, date_registered, login_registered)");
                    query.AppendFormat(" VALUES (@Name, @Address, @City, @County, @State, @Zip_Code, @CPF, @Idnumber, @email, @celular_phone, @home_phone, @date_registered, @login_registered)");
    
                    connectionOpen();
    
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
                    
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = query.ToString();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add(new SqlParameter("@Name", client.Name));   // All values have been received here
                        cmd.Parameters.Add(new SqlParameter("@Address", client.Address));
                        cmd.Parameters.Add(new SqlParameter("@City", client.City));
                        cmd.Parameters.Add(new SqlParameter("@County", client.County));
                        cmd.Parameters.Add(new SqlParameter("@State", client.State));
                        cmd.Parameters.Add(new SqlParameter("@Zip_Code", client.Zip_Code ));
                        cmd.Parameters.Add(new SqlParameter("@CPF", client.CPF));
                        cmd.Parameters.Add(new SqlParameter("@Idnumber", client.Idnumber));
                        cmd.Parameters.Add(new SqlParameter("@email", client.email));
                        cmd.Parameters.Add(new SqlParameter("@celular_phone", client.celular_phone));
                        cmd.Parameters.Add(new SqlParameter("@home_phone", client.home_phone));
                        cmd.Parameters.Add(new SqlParameter("@date_registered", client.date_registered));
                        cmd.Parameters.Add(new SqlParameter("@login_registered", client.login_registered));  // last line executed
                       
                        int result = cmd.ExecuteNonQuery();
                        cmd.Dispose();
    
                        int resultado = result;
    
                        return true;
                    }
    
                }
                catch (Exception Ex)
                {
                    return false;
                }
                finally
                {
                    connectionClose();    
                }
            }
    
            #endregion Methods of Insert.

    I'm using a SQL Server 2014

    Bellow follows the table.

    ClientID	 int	       Unchecked
    Name	         varchar(50)	Checked
    Address	         varchar(50)	Checked
    City	         varchar(50)	Checked
    County	         varchar(50)	Checked
    State	         varchar(2)	Checked
    Zip_Code	 varchar(10)	Checked
    CPF	         varchar(14)	Checked
    Idnumber	 varchar(14)	Checked
    email	         varchar(50)	Checked
    celular_phone	 varchar(15)	Checked
    home_phone	 varchar(15)	Checked
    date_registered	 date	        Checked
    login_registered varchar(25)	Checked

    Thanks for all of you.

    Best Regards.

    Monday, June 22, 2020 9:45 PM

Answers

  • Hi,

    Thank you for posting here.

    There is an error in the sql statement, the column is home_phone, but in the sql statement, you wrote home-phone.

    I suggest you write some print statements in the catch block, so that you can see the error message clearly.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, June 23, 2020 2:03 AM

All replies

  • Hello,

    Rather than one connection object, create a new connection object for each operation with a using statement as shown in this random code sample. The connection string can be stored as a class variable. And note I'm thinking the connection is the problem as per the title of this post.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, June 22, 2020 10:15 PM
    Moderator
  • How do you know things are not working? Each method should go get the connectionstring individually and open the connection and close it. 
    Monday, June 22, 2020 10:30 PM
  • Hi DA924X,

    I've tried ten times, in all maners as possible, and also using the breakpoint. 

    Like happens in my last post, I had two connectionOpen in the same method, and it caused a 

    System.InvalidOperationException, and the breakpoint didn't work. Then I fixed and worked well.

    So, my guess is that I'm having the same problem, because the connectionOpen is not working.

    the cmd.ExcecuteNonQuery(),  does not execute. 

    Tuesday, June 23, 2020 1:56 AM
  • Hi,

    Thank you for posting here.

    There is an error in the sql statement, the column is home_phone, but in the sql statement, you wrote home-phone.

    I suggest you write some print statements in the catch block, so that you can see the error message clearly.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, June 23, 2020 2:03 AM
  • Karen good evening,

    It did not work, I made the changes That you have suggested, unfortunately it din't execute all way through.

    I'm posting bellow the knew code, with the new information, so if anybody can figure out what is going on, I'll appreciate that.

    Thanks for all of you.  

    Just to inform all of you, the "connectionopen" string is showing on execution the line below.

    "data source=DESKTOP-3O98051; Integrated Security= SSPI; Initial Catalog= AutocarWeb"

    I guess that the connection is OK. So, can you help me one more time?

     private bool cadastrarClient(Client client)
            {
               
                try
                {
                    StringBuilder query = new StringBuilder();
                    query.AppendFormat("insert into ClientTB (Name, Address, City, County, State, Zip_Code, CPF, Idnumber, email, celular_phone, home-phone, date_registered, login_registered)");
                    query.AppendFormat(" VALUES (@Name, @Address, @City, @County, @State, @Zip_Code, @CPF, @Idnumber, @email, @celular_phone, @home_phone, @date_registered, @login_registered)");
    
                    
    
                    using (var cn = new SqlConnection() { ConnectionString = connectionopen })
                    {
                       
                        using (var cmd = new SqlCommand() { Connection = cn })
                        {
                            cn.Open();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = query.ToString();
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add(new SqlParameter("@Name", client.Name));
                            cmd.Parameters.Add(new SqlParameter("@Address", client.Address));
                            cmd.Parameters.Add(new SqlParameter("@City", client.City));
                            cmd.Parameters.Add(new SqlParameter("@County", client.County));
                            cmd.Parameters.Add(new SqlParameter("@State", client.State));
                            cmd.Parameters.Add(new SqlParameter("@Zip_Code", client.Zip_Code ));
                            cmd.Parameters.Add(new SqlParameter("@CPF", client.CPF));
                            cmd.Parameters.Add(new SqlParameter("@Idnumber", client.Idnumber));
                            cmd.Parameters.Add(new SqlParameter("@email", client.email));
                            cmd.Parameters.Add(new SqlParameter("@celular_phone", client.celular_phone));
                            cmd.Parameters.Add(new SqlParameter("@home_phone", client.home_phone));
                            cmd.Parameters.Add(new SqlParameter("@date_registered", client.date_registered));
                            cmd.Parameters.Add(new SqlParameter("@login_registered", client.login_registered));
                       
                            int result = cmd.ExecuteNonQuery();
                            cmd.Dispose();
    
                            int resultado = result;
    
                            return true;
                        }
                    }
                }
                catch (Exception Ex)
                {
                    return false;
                }
                finally
                {
                    connectionClose();    
                }
            }

    Thanks and Best Regards.

    Tuesday, June 23, 2020 2:18 AM
  • Timon, 

    You found the problem, was exactly that home_phone.

    After hours and hours, you don't imagine that could be the only place that the problem would be.

    It is working very fine. Thanks for all of you. I really appreciated your effort.

    Best Regards.

    Tuesday, June 23, 2020 2:35 AM
  • Karen good evening,

    It did not work, I made the changes That you have suggested, unfortunately it din't execute all way through.

    I'm posting bellow the knew code, with the new information, so if anybody can figure out what is going on, I'll appreciate that.

    Thanks for all of you.  

    Just to inform all of you, the "connectionopen" string is showing on execution the line below.

    "data source=DESKTOP-3O98051; Integrated Security= SSPI; Initial Catalog= AutocarWeb"

    I guess that the connection is OK. So, can you help me one more time?

     private bool cadastrarClient(Client client)
            {
               
                try
                {
                    StringBuilder query = new StringBuilder();
                    query.AppendFormat("insert into ClientTB (Name, Address, City, County, State, Zip_Code, CPF, Idnumber, email, celular_phone, home-phone, date_registered, login_registered)");
                    query.AppendFormat(" VALUES (@Name, @Address, @City, @County, @State, @Zip_Code, @CPF, @Idnumber, @email, @celular_phone, @home_phone, @date_registered, @login_registered)");
    
                    
    
                    using (var cn = new SqlConnection() { ConnectionString = connectionopen })
                    {
                       
                        using (var cmd = new SqlCommand() { Connection = cn })
                        {
                            cn.Open();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = query.ToString();
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add(new SqlParameter("@Name", client.Name));
                            cmd.Parameters.Add(new SqlParameter("@Address", client.Address));
                            cmd.Parameters.Add(new SqlParameter("@City", client.City));
                            cmd.Parameters.Add(new SqlParameter("@County", client.County));
                            cmd.Parameters.Add(new SqlParameter("@State", client.State));
                            cmd.Parameters.Add(new SqlParameter("@Zip_Code", client.Zip_Code ));
                            cmd.Parameters.Add(new SqlParameter("@CPF", client.CPF));
                            cmd.Parameters.Add(new SqlParameter("@Idnumber", client.Idnumber));
                            cmd.Parameters.Add(new SqlParameter("@email", client.email));
                            cmd.Parameters.Add(new SqlParameter("@celular_phone", client.celular_phone));
                            cmd.Parameters.Add(new SqlParameter("@home_phone", client.home_phone));
                            cmd.Parameters.Add(new SqlParameter("@date_registered", client.date_registered));
                            cmd.Parameters.Add(new SqlParameter("@login_registered", client.login_registered));
                       
                            int result = cmd.ExecuteNonQuery();
                            cmd.Dispose();
    
                            int resultado = result;
    
                            return true;
                        }
                    }
                }
                catch (Exception Ex)
                {
                    return false;
                }
                finally
                {
                    connectionClose();    
                }
            }

    Thanks and Best Regards.

    As I see now the issue was not the connection but your insert statement. You can avoid such issues by first writing your SQL in SSMS (SQL-Server Management Studio), get that to work then copy the SQL to your C# code. Sure it's more work but in the end the code would work without waiting for a answer here.  Also, never use a catch and return a bool without checking what the actual exception is as I'm sure in this case you would have seen a syntax error in the SQL rather than thinking it was a connection issue.

    Consider using what I suggested on the connections in the future as this is a best practice to create an object and dispose of an object as needed. At some point using a connection as you are will cause an issue.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Tuesday, June 23, 2020 1:00 PM
    Moderator
  • Thank you very much for your instructions.

    I will that, it's a great idea. 

    Thanks and best regards.

    Tuesday, June 30, 2020 1:22 PM