locked
Problem while inserting data in DB in C#, .NET 4.0 RRS feed

  • Question

  • I created a new project with just one table in database having 3 cloumns(all varchar(50) and nullable). And used this code, but still database is empty and there isn't a single row in Table1. By the way I'm using Visual Studio 2010.

    string constr = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
        string sql = "INSERT INTO Table1 VALUES('110','Mohsin Ali','BS(CS)')";
        SqlConnection conn = new SqlConnection(constr);
        SqlCommand com = new SqlCommand(sql, conn);
        conn.Open();
        com.ExecuteNonQuery();
        conn.Close();
    
    Sunday, November 21, 2010 10:31 AM

Answers

  • Hi Mohsin,

    I tried your code in VS 2008 and worked fine means the data is inserting to the Table1.

    Please check again and as URI said check your Database connection string (Database Name, Server Name and User and Password)

    I can say the C# code is working fine and Sql Command is also Fine. Only   you need to check your database details.

    As you said only one table in the database so I don't think any dependency or May not be any trigger but please check once again...

    And yes, if you are using try catch in C# code. then remove it and try the code..if any issue you can easily trap it..

    Hope this help you...

    Thanks,


    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Monday, November 22, 2010 5:21 AM
  • Dima

    INSTEAD OF trigger means that he/she has created a view... I do not think he/she is trying to insert into the view based on provided example....

    I agree with you that probably the best advise is turning on SQL Server Profiler and see what is going on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 23, 2010 6:16 AM
  • Dima

    INSTEAD OF trigger means that he/she has created a view... I do not think he/she is trying to insert into the view based on provided example....

    I agree with you that probably the best advise is turning on SQL Server Profiler and see what is going on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Uri, you can create INSTEAD OF trigger on the table. See below:

    create table dbo.A(Id int)
    go
    
    create trigger trg_InsteadOfA on dbo.A
    instead of insert
    as
    begin
    	return
    end
    go
    
    insert into dbo.A(Id) values(1)
    go
    
    select * from dbo.A
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, November 23, 2010 2:26 PM
  • >@Dima: Yes I have created a view to monitor that wheather the >data is being inserted or not. 

    And what do you see? Have you checked for triggers?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 24, 2010 8:32 AM

All replies

  • Do you get errors? Check out that  you connected to right database...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 21, 2010 10:37 AM
  • There are no errors at all. And there is only one database with only one table so there is no question to connect to some wrong db. Actually i was having the same problem with a project therefore created a new just to make sure that what the problem is, but unfortunatly i'm still unable to find it.
    Sunday, November 21, 2010 11:10 AM
  • There is no miracles,  you know :-)

    In SSMS open new query and run the below

    INSERT INTO Table1 VALUES('110','Mohsin Ali','BS(CS)')

    Any errors? I would  also specify columns such as

    INSERT INTO Table1(col1,col2,col3) VALUES('110','Mohsin Ali','BS(CS)')

    And finally check out that table does not have triggers


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 21, 2010 11:36 AM
  • Hi Mohsin,

    I tried your code in VS 2008 and worked fine means the data is inserting to the Table1.

    Please check again and as URI said check your Database connection string (Database Name, Server Name and User and Password)

    I can say the C# code is working fine and Sql Command is also Fine. Only   you need to check your database details.

    As you said only one table in the database so I don't think any dependency or May not be any trigger but please check once again...

    And yes, if you are using try catch in C# code. then remove it and try the code..if any issue you can easily trap it..

    Hope this help you...

    Thanks,


    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Monday, November 22, 2010 5:21 AM
  • Make sure that there are no triggers (especially INSTEAD OF) as Uri suggested.

    Also it could make sense to capture the actual call with SQL Profiler (using one of T-SQL traces) and run it in the management studio. It could give you better understanding if something is wrong there.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, November 22, 2010 9:48 PM
  • Dima

    INSTEAD OF trigger means that he/she has created a view... I do not think he/she is trying to insert into the view based on provided example....

    I agree with you that probably the best advise is turning on SQL Server Profiler and see what is going on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 23, 2010 6:16 AM
  • Hi mohsinLangrial,

     

    Have you solved this issue?

    If you have solved this issue, could you please share us the solution?

    If this issue still exists, before further investigation, please provide us the connection string you are using.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Tuesday, November 23, 2010 10:01 AM
  • Dima

    INSTEAD OF trigger means that he/she has created a view... I do not think he/she is trying to insert into the view based on provided example....

    I agree with you that probably the best advise is turning on SQL Server Profiler and see what is going on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Uri, you can create INSTEAD OF trigger on the table. See below:

    create table dbo.A(Id int)
    go
    
    create trigger trg_InsteadOfA on dbo.A
    instead of insert
    as
    begin
    	return
    end
    go
    
    insert into dbo.A(Id) values(1)
    go
    
    select * from dbo.A
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, November 23, 2010 2:26 PM
  • Sorry guys I was busy with some other stuff. And I'm impressed with your concerns. The issue is still unresolved.

    @Dima: Yes I have created a view to monitor that wheather the data is being inserted or not. 

    @Tom Li: Here is the connection string: connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDB.mdf;Integrated Security=True;User Instance=True"

    There is one more thing I have noticed that when i run the project; the connection to database goes from connected state to crossed state.

    Tuesday, November 23, 2010 5:53 PM
  • Thanks

    Never used INSTEAD OF... on table


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 24, 2010 8:31 AM
  • Thanks

    Never used INSTEAD OF... on table


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 24, 2010 8:31 AM
  • >@Dima: Yes I have created a view to monitor that wheather the >data is being inserted or not. 

    And what do you see? Have you checked for triggers?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 24, 2010 8:32 AM