Inserting value into database

Question

I wanted to insert values into database but it is not working eventhough my code perfectly working when its used as stored procedure. I am trieng to use button click to store the value. Please tell whats wrong with the code. Its not showing any error or exception but data is not getting updated in the table

protected void Button1_Click(object sender, EventArgs e)
{
        SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "INSERT INTO sales(acnum, scripname, shares_bought) VALUES ('12', 'abcd', '20')";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;
        sqlConnection1.Open();

        cmd.ExecuteNonQuery();
        sqlConnection1.Close();                                
}

What do you mean by _not working_? You get any exception or error message? Is it works on database manager? What are the types of your columns? I strongly suspect you should not store your `12` and `20` as a character. Can you please be more specific? And `cmd.CommandType = CommandType.Text` is redundant. It is `.Text` by default. — Soner Gönül, Sep 10 '15 at 10:34
no exception .. . no errors but its not updating in database — , Sep 10 '15 at 10:35
my columns acnum and shares_bought are "int" and scripname is "varchar" — , Sep 10 '15 at 10:37
INSERT INTO sales(acnum, scripname, shares_bought) VALUES (12, 'abcd', 20) — Awn Ali, Sep 10 '15 at 10:39
@AwnAli: may depend on the database - MySQL will automatically convert strings to integers where necessary. — PaulF, Sep 10 '15 at 10:41
@AwnAli no errors and exception Ali.. database doesnot get updated — , Sep 10 '15 at 10:44
Strongly suggest looking at `Dapper`. Might make a lot of these complexities disappear. — code4life, Sep 10 '15 at 12:01

Soner Gönül · Accepted Answer · 2015-09-10T10:43:20.740

2

my columns acnum and shares_bought are "int" and scripname is "varchar"

Then you will not need single quotes for your integer values. Use it as;

...VALUES (12, 'abcd', 20)"

A few things more;

Use using statement to dispose your connection and command automatically instead of calling Close method manually.
You don't need cmd.CommandType = CommandType.Text line. It is .Text by default.

var conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
using(var sqlConnection1 = new SqlConnection(conStr))
using(var cmd = sqlConnection1.CreateCommand())
{
   cmd.CommandText = "INSERT INTO sales(acnum, scripname, shares_bought) VALUES (12, 'abcd', 20)";
   sqlConnection1.Open();
   cmd.ExecuteNonQuery();
}

edited Sep 10 '15 at 10:43

answered Sep 10 '15 at 10:38

Soner Gönül

97,193
102
206
364

1

tried tht also sir, but still not working and removed command type line still its not working – Sep 10 '15 at 10:39
@Darkknight _not working_ is not useful at all. What do you mean by that. You will get any error or something? Is this query works on your sql server? – Soner Gönül Sep 10 '15 at 10:40
1

yes sir this query works in sql server but its not working in my visual studio... it does not show any errors or exceptions – Sep 10 '15 at 10:43
This can be database dependant - MySQL does automatic type conversion so both 12 and '12' are acceptable values for an integer column. – PaulF Sep 10 '15 at 10:43
@PaulF I didn't know that but looks like OP using SQL Server. – Soner Gönül Sep 10 '15 at 10:44
@Darkknight Are you sure your connection string is right? Is your `sales` table belongs on default schema? Maybe you need to write `dbo.sales` or something? – Soner Gönül Sep 10 '15 at 10:47
@SonerGönül I'm sure this connection string is right and dbo.sales is not necessary I think because without "dbo." it has worked perfectly fine in previous webpage where I had to take values from textbox. – Sep 10 '15 at 10:49
@SonerGönül Do u have any idea where it can go wrong?? – Sep 10 '15 at 11:01
@Darkknight I honestly don't know, This should work. What is the size of your `varchar` column? – Soner Gönül Sep 10 '15 at 11:07
Have you checked the return value of ExecuteNonQuery - that will show how many rows were updated. – PaulF Sep 10 '15 at 11:08
@Darkknight By the way you said; _I had to take values from textbox._ But in your code, you don't. Are you sure this is _real_ code? – Soner Gönül Sep 10 '15 at 11:10
See this : http://stackoverflow.com/questions/7024109/how-can-i-get-an-error-message-that-happens-when-using-executenonquery – PaulF Sep 10 '15 at 11:13
Can you try manually running the query - for example from a command line utility like sqlcmd or workbench like MySQL Workbench - these may give better error reporting to see what the issue is. Maybe spelling mistake of field name, or entering a duplicate not allowed by a unique index for example. – PaulF Sep 10 '15 at 11:23
@PaulF I have tried to run the query in sql, it had run perfectly... it did updated the database, but while i try to use the same query in visual studio, it is producing no result. – Sep 10 '15 at 11:33
@SonerGönül no I mean in my last webpage I had to take value from textbox which worked fine.. but in this page I had to add value which is not working. – Sep 10 '15 at 11:36
See if you can use the InfoMessage event to get more info : http://www.codeproject.com/Articles/37102/Using-InfoMessage-Event-of-SqlConnection-Object – PaulF Sep 10 '15 at 11:52

score 1 · Answer 2 · answered Sep 10 '15 at 10:48

Set breakpoint in code and see if code is executing. In case you don't know how to, see here. As you are not getting any exception on insert statement and it's not reflecting in database, either you have different connection string or your code is not getting executed.

Inserting value into database

2 Answers2