How to Code for Inserting Data into SQL using C# [modified]
-
I am trying to insert Data in SQL Database using C#, it's not giving any error but at the same time no rows are getting updated in my database. private void Button1_Click(object sender, System.EventArgs e) { SqlConnection myConnection = new SqlConnection("server=sbs;database=bansal;Trusted_Connection=Yes"); SqlCommand myCommand = new SqlCommand("InsertCategory", myConnection); myCommand.CommandText = "InsertCategory"; myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.NChar, 15)); myCommand.Parameters["@CategoryName"].Value = "Ambarish"; myCommand.Connection.Open(); try { myCommand.ExecuteNonQuery(); } catch(SqlException e){} myCommand.Connection.Close(); } Following is the Stored Procedure: CREATE PROCEDURE InsertCategory ( @CategoryName nchar(15) ) AS INSERT INTO Categories (CategoryName) VALUES(@CategoryName) Table Name is Categories and has two Columns only: 1. CategoryName NChar 15 2. Identity Int 4 (Identity Seed is 1 starting from 1) Sachin Jain -- modified at 4:36 Saturday 4th August, 2007
-
I am trying to insert Data in SQL Database using C#, it's not giving any error but at the same time no rows are getting updated in my database. private void Button1_Click(object sender, System.EventArgs e) { SqlConnection myConnection = new SqlConnection("server=sbs;database=bansal;Trusted_Connection=Yes"); SqlCommand myCommand = new SqlCommand("InsertCategory", myConnection); myCommand.CommandText = "InsertCategory"; myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.NChar, 15)); myCommand.Parameters["@CategoryName"].Value = "Ambarish"; myCommand.Connection.Open(); try { myCommand.ExecuteNonQuery(); } catch(SqlException e){} myCommand.Connection.Close(); } Following is the Stored Procedure: CREATE PROCEDURE InsertCategory ( @CategoryName nchar(15) ) AS INSERT INTO Categories (CategoryName) VALUES(@CategoryName) Table Name is Categories and has two Columns only: 1. CategoryName NChar 15 2. Identity Int 4 (Identity Seed is 1 starting from 1) Sachin Jain -- modified at 4:36 Saturday 4th August, 2007
You are adding a parameter to the command every single time you press the button. You now have several parameters - which one does the database use? It will use the first one with the correct name and ignore the others. You most likely have a parameters set up by the wizard that created your connection with the database. This is the big problem with using these wizards - they hide what is really happening. Don't use them. They should never be used. Write your own database code, and write it in a DAL (Data Access Layer) class.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
-
You are adding a parameter to the command every single time you press the button. You now have several parameters - which one does the database use? It will use the first one with the correct name and ignore the others. You most likely have a parameters set up by the wizard that created your connection with the database. This is the big problem with using these wizards - they hide what is really happening. Don't use them. They should never be used. Write your own database code, and write it in a DAL (Data Access Layer) class.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
Dear Sir, Thank you very much for your quick reply. I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this. The problem that this code doesn't give any error, but at the same time data is not inserting in table. Please help me in this regard. Thanks, Sachin Jain
-
Dear Sir, Thank you very much for your quick reply. I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this. The problem that this code doesn't give any error, but at the same time data is not inserting in table. Please help me in this regard. Thanks, Sachin Jain
sacheesach wrote:
I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this.
Okay - it looked a bit like you were using wizard created code, but on looking again it doesn't seem to be the case anymore.
sacheesach wrote:
The problem that this code doesn't give any error
It probably does but you are using the anti-pattern known at try-catch-ignore which is summed up by this line in your code:
catch(SqlException e){}
Comment out the try and catch statements to run the code inside on its own. Does the debugger point out an error?
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
-
sacheesach wrote:
I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this.
Okay - it looked a bit like you were using wizard created code, but on looking again it doesn't seem to be the case anymore.
sacheesach wrote:
The problem that this code doesn't give any error
It probably does but you are using the anti-pattern known at try-catch-ignore which is summed up by this line in your code:
catch(SqlException e){}
Comment out the try and catch statements to run the code inside on its own. Does the debugger point out an error?
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website
Sir, Thanks for your suggestions. It was a silly mistake committed by my new programmer. In fact the Permissions (Select, Insert, Delete, Update etc.) on the Categories Table were unchecked and this was the reason the error was given. Thank you again for your kind support. Sachin Jain