How can I insert with SQL in C#

Question

1.11/5 (2 votes)

See more:

I want to know how I can do an insert in an sql database with C #

What I have tried:

string setting = ConfigurationManager.AppSettings["setting1"];
            string conn = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
            using (SqlConnection sqlConn = new SqlConnection(conn))
            {
                //string sqlQuery2 = @"UPDATE testenumserie " + "SET Estado = " + comboBox1.Text + " WHERE NumSerie = '" + textBox2.Text + "'" ;
                string sqlQuery2 = @"INSERT into dbo.testenumserie(Estado) VALUES ('" + comboBox1.Text + "')" + " WHERE NumSerie = '" + textBox2.Text + "'";
                MessageBox.Show(sqlQuery2);
                SqlCommand cmd2 = new SqlCommand(sqlQuery2, sqlConn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable table2 = new DataTable();

I tried this code but the insert did not work.

Posted 17-Aug-17 1:49am

PedroAzevedo1234

Updated 17-Aug-17 2:49am

v2

Add a Solution

Comments

Richard Deeming 17-Aug-17 9:49am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Richard Deeming 17-Aug-17 9:50am

And this is just another version of the same question you posted last week, which is marked as "solved":
https://www.codeproject.com/Questions/1201026/How-to-insert-data-into-a-SQL-table[^]

3 solutions

Solution 3

Oki first of all, consider reading up on sql injection, because you REALLY don't want to use variables directly from a user interface in a query.

Update Estado in a specific numseries

C#

string estado = CheckMyUiValues(comoBox1.Text); //Must make that method ;)
string nums = CheckMyUiValues(comoBox1.Text);

var sql = "UPDATE dbo.testenumserie SET ESTADO = '{0}' WHERE NUMSERIE = '{1}'";
using(var cn = new GetOpenConnection()){   //Could have a method to make here too
    using(var cmd = new SqlCommand(cn, string.format(sql, estado, nums)){
        cmd.ExecuteNonQuery();
    }
}

or in a somewhat more crude way

C#

string estado = comoBox1.Text; 
string nums = comoBox1.Text;
//TODO: Implement ui variable checking
var sql = "UPDATE dbo.testenumserie SET ESTADO = '{0}' WHERE NUMSERIE = '{1}'";
string conn = "put your connection string details in here";
using(var cn = new SqlConnection(conn)){   
    using(var cmd = new SqlCommand(cn, string.Format(sql, estado, nums)){
        cmd.ExecuteNonQuery();
    }
}

Posted 17-Aug-17 2:49am

Thomas Nielsen - getCore

Updated 17-Aug-17 3:35am

v4

Comments

PedroAzevedo1234 17-Aug-17 8:55am

I need to change the value of the "Estado" of a specific "NumSerie"

Thomas Nielsen - getCore 17-Aug-17 9:00am

ok, please see updated suggestion

PedroAzevedo1234 17-Aug-17 9:05am

It give me error with this "CheckMyUiValues" and this "GetOpenConnection" and this "format"

Thomas Nielsen - getCore 17-Aug-17 9:08am

:) it's not just copy paste friend. I'm implying you'll make a method CheckMyUiValues to prevent sql injection and GetOpenConnection To instantiate a connection and open it .... if you dont want to remove CheckMyUiValues method and assign directly and just new up an SqlConnection with your appropriate sql connection string ... just not something i'd write myself or incourage anybody to. Oh and string.Format should have a capital F.

PedroAzevedo1234 17-Aug-17 9:11am

the string.format give me a error is not possible to conver to system.data.SqlClient.SqlConnection

PedroAzevedo1234 17-Aug-17 9:21am

I dont understand how I can do it :C

PedroAzevedo1234 17-Aug-17 9:15am

But where is the fault of CheckMyUiValues?

Thomas Nielsen - getCore 17-Aug-17 9:21am

ok i'll make a revised one

PedroAzevedo1234 17-Aug-17 9:29am

keep giving me the error on string

Thomas Nielsen - getCore 17-Aug-17 9:34am

What error is that sorry? On the Sql connectionstring? or sql expression?? i believe i forgot an e as well in your table name, thought you'd put whatever you named it

Solution 1

To execute your sql you would do cmd2.ExecuteNonQuery();. You do not need the DataAdapter nor the DataTable.

You'll also want to change to using parameters. Your code is very insecure right now. Your database could be easily hacked. Instead do something like:

C#

string sqlQuery2 = @"INSERT into dbo.testenumserie(Estado) VALUES (@estado) WHERE NumSerie = @numSeries";            
...
cmd2.Parameters.AddWithValue("@estado", comboBox1.Text);
cmd2.Parameters.AddWithValue("@numSeries", textBox2.Text);
cmd2.ExecuteNonQuery();

Also, you should name your controls using better names. For example, your textBox2 should be named txtNumSeries. It will save you a lot of time later on to name your controls properly.

Posted 17-Aug-17 2:07am

ZurdoDev

Comments

PedroAzevedo1234 17-Aug-17 8:15am

It gives a error "System.InvalidOperationException:ExecuteNonQuery requires an open and available Connection."

ZurdoDev 17-Aug-17 8:16am

You need to open the connection.

PedroAzevedo1234 17-Aug-17 8:19am

And now it says this error "System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'WHERE'.'"

ZurdoDev 17-Aug-17 8:20am

Then fix the Syntax. Just do whatever the error tells you to do.

PedroAzevedo1234 17-Aug-17 8:21am

How can I fix this ?

ZurdoDev 17-Aug-17 8:22am

How would any of us know? I can't see the sql statement. You have a syntax error. Look at what is in sqlQuery2 and fix the syntax error.

PedroAzevedo1234 17-Aug-17 8:33am

this error appered "
System.Data.SqlClient.SqlException occurred
HResult = 0x80131904
Message = Can not insert the value NULL into column 'NumSeries', table 'testedopedro.dbo.testenumserie'; Column does not allow nulls. INSERT fails."

PedroAzevedo1234 17-Aug-17 8:33am

But my table allows nulls as you can see in this print http://prntscr.com/g9lzib

ZurdoDev 17-Aug-17 8:34am

That is not the same table. The error refers to NumSeries and your screenshot shows NumSerie without the s.

PedroAzevedo1234 17-Aug-17 8:36am

But I am not trying to insert into NumSeries

ZurdoDev 17-Aug-17 8:37am

That's what the error says. The error is not lying. Look at your code closely.

PedroAzevedo1234 17-Aug-17 8:39am

The error is this "Cannot insert the value NULL into column 'NumSerie', table 'testedopedro.dbo.testenumserie'; column does not allow nulls. INSERT fails." there is a print if you want http://prntscr.com/g9m4mq

ZurdoDev 17-Aug-17 8:42am

Then make sure your connection string is pointing to the database testedopedro because the error is not wrong. And make sure the table you are looking at matches what the code is connecting to.

PedroAzevedo1234 17-Aug-17 8:45am

its all ok with that 2 points

ZurdoDev 17-Aug-17 8:47am

All I can tell you is you have something wrong. That error only happens the way it reads. It is not wrong.

What does your connection string look like?

PedroAzevedo1234 17-Aug-17 8:48am

like this <add name="test" connectionString= "Data source = test123; Database=testedopedro ; User Id=a ; Password=12345 "/>

ZurdoDev 17-Aug-17 8:51am

That is not what you are using. You are using ConfigurationManager.AppSettings["setting1"];
so look at your AppSettings in Web.Config for setting1.

PedroAzevedo1234 17-Aug-17 8:51am

I think I need an alter not a insert

ZurdoDev 17-Aug-17 8:52am

NO!!!!!!!!!!! Look at what is in AppSettings setting1.

PedroAzevedo1234 17-Aug-17 8:52am

ZurdoDev 17-Aug-17 8:59am

That can't be. You'd get an error trying to open it. It sounds like you have changed your code a lot since you posted. Not sure how to help you anymore.

PedroAzevedo1234 17-Aug-17 9:04am

Thanks anyway

ZurdoDev 17-Aug-17 9:06am

In the screenshot below you show that the field is NOT nullable. You are not giving straight answers and making it very hard to help you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sheila Pontes · Accepted Answer · 2017-08-17T02:13:00

Solution 2

Hi,

Below is the code to solve the problem.

C#

string connectionstring = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connectionstring);
sqlConn.Open();
 
string sqlQuery2 = @"UPDATE dbo.testenumserie SET ESTADO = @estado WHERE NumSerie = @numSeries"; 

SqlCommand SQLcm = new SqlCommand();
SQLcm.Connection = sqlConn;
SQLcm.CommandText = sqlQuery2;
SQLcm.CommandType = CommandType.Text;
SQLcm.Parameters.AddWithValue("@estado", comboBox1.Text);
SQLcm.Parameters.AddWithValue("@numSeries", textBox2.Text);
SQLcm.ExecuteNonQuery();
sqlConn.Close();

Posted 17-Aug-17 2:13am

Sheila Pontes

Updated 17-Aug-17 3:59am

v8

Comments

PedroAzevedo1234 17-Aug-17 8:20am

Give this error "System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'WHERE'.'"

PedroAzevedo1234 17-Aug-17 8:27am

this error appered "
System.Data.SqlClient.SqlException occurred
HResult = 0x80131904
Message = Can not insert the value NULL into column 'NumSeries', table 'testedopedro.dbo.testenumserie'; Column does not allow nulls. INSERT fails."

PedroAzevedo1234 17-Aug-17 8:28am

But my table allows nulls as you can see in this print http://prntscr.com/g9lzib

Sheila Pontes 17-Aug-17 8:31am

Hi,
To use the "where" clause in the insert, you need to use insert with select command. I change the code above correcting the insertion.
This article can help you undestand better.
https://technet.microsoft.com/pt-br/library/ms188263(v=sql.105).aspx

Sheila Pontes 17-Aug-17 8:38am

Hi,
insert the value also in the numseries column.

 
string sqlQuery2 = @"INSERT into dbo.testenumserie(Estado, NumSerie) SELECT '" + comboBox1.Text + "', '" + textBox2.Text + '" from dbo.testenumserie WHERE NumSerie = '" + textBox2.Text + "'";

PedroAzevedo1234 17-Aug-17 8:42am

Eu só quero inserir no Estado , não quero inserir no numserie.

Sheila Pontes 17-Aug-17 8:39am

Yes. I am from Brazil

PedroAzevedo1234 17-Aug-17 8:40am

Eu sou português.

Sheila Pontes 17-Aug-17 8:48am

O número de série é chave primaria? Se não.
Verifique se o campo numserie não tem alguma constraint como not null. Use o comando sp_help dbo.testenumserie no query analyser.

The NumSerie column is primary key? if not. Verify if your numserie column has not null constraint. Use the sp_help command.

PedroAzevedo1234 17-Aug-17 8:52am

Era mas eu tirei

It was but I removed

PedroAzevedo1234 17-Aug-17 8:58am

Apareceu isto http://prntscr.com/g9mdlw

Sheila Pontes 17-Aug-17 9:05am

Verifica se não ficou nenhuma constraint no caminho proibindo você de colocar null no campo.
Uma outra opção seria excluir o campo e recriar ele de novo na tabela. Se falhar, exclui a tabela e cria de novo.
Um outro opção seria usar o identity do sql server neste campo ou colocar um valor default para o campo não ficar vazio.
Aqui eu uso oracle e fiz um teste no insert com select. Não houve problema.

Check if there is no constraint on the path forbidding you to put null in the field.
Another option would be to delete the field and recreate it again in the table. If it fails, delete the table and create it again.
Another option would be to use the identity of the sql server in this field or put a default value so the field is not empty.
Here I use oracle and did a test on insert with select. There was no problem.

Sheila Pontes 17-Aug-17 9:10am

Pedro o campo ainda é chave primaria. Ele nunca vai deixar você colocar null neste campo.
Rode este comando para remover a constraint da chave, verifique se a chave primaria deixou de existir e veja se funciona o seu insert.
ALTER TABLE dbo.testenumserie DROP CONSTRAINT pk_testenumserie.

Pedro the field is still a primary key. It will never let you put null in this field.
Rotate this command to remove the constraint from the key, make sure the primary key no longer exists and see if it works your insert.
ALTER TABLE dbo.testenumserie DROP CONSTRAINT pk_testenumserie.

PedroAzevedo1234 17-Aug-17 9:18am

Continua a dar o mesmo erro

Sheila Pontes 17-Aug-17 9:25am

Coloca o update do Thomas no código acima que ele irá funcionar.
Assim.

string sqlQuery2 = @"UPDATE dbo.testenumserie SET ESTADO = '" + comboBox1.Text + "' WHERE NumSerie = '" + textBox2.Text + "'";

PedroAzevedo1234 17-Aug-17 9:34am

Obrigado

Sheila Pontes 17-Aug-17 9:43am

Que bom que funcionou. Eu vou atualizar a solução acima para refletir a solução exata do seu problema.

Glad it worked. I'll update a solution above to reflect an exact solution to your problem.

Richard Deeming 17-Aug-17 9:51am

You have copied the SQL Injection[^] vulnerability from the question.

Sheila Pontes 17-Aug-17 10:01am

Thanks, Richard.