How do I insert data into an SQL table

Question

1.00/5 (2 votes)

See more:

I need to save data inside an sqlserver

What I have tried:

C#

using (SqlConnection conn = new SqlConnection(@"Data source = ******* ; Database=********* ; User Id=sa ; Password=********"))
                    {

                        SqlCommand command = new SqlCommand("IF OBJECT_ID('UXFaturas', 'U') IS NULL CREATE TABLE UXFaturas(NumFaturas char(250) NOT NULL, Cont1 char(250) NOT NULL, Cont2 char(250) NOT NULL)" +
                            "INSERT INTO UXFaturas (NumFaturas, Cont, Cont2)", conn);
                        
                        
                        command.Parameters.AddWithValue("@NumFaturas", numfatura);
                        command.Parameters.AddWithValue("@Cont1", cont);
                        command.Parameters.AddWithValue("@Cont2", cont2);
                    conn.Open();
                    command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    conn.Close();
                }

I've tried this but is not inserting. There are no errors but the table stills empty.

Posted 17-Aug-17 2:02am

Member 13356973

Updated 21-Aug-17 7:55am

Slacker007

v5

Add a Solution

3 solutions

Solution 2

You are calling command.ExecuteNonQuery(); before you have set any of the parameters in your command object. You are also specifying to use a stored procedure when you do not need one.

Posted 17-Aug-17 2:10am

Richard MacCutchan

Updated 17-Aug-17 2:12am

v2

Comments

Member 13356973 17-Aug-17 8:19am

I'm calling ExecuteNonQuery before because if I do i after i'll get an error

Richard MacCutchan 17-Aug-17 8:53am

As ridiculous ideas go that has to be near the top of the list. I suggest you use Google to find some tutorials on SQL (and maybe even programming in general) before trying this again.

Solution 4

I'd suggest splitting out the table creation part to a separate command. If the table doesn't exist, SQL might struggle to prepare the query to insert data into it.

Your INSERT command is currently missing a VALUES clause, so you should be getting an exception telling you the syntax is not correct.

C#

using (SqlConnection conn = new SqlConnection(@"Data source = 2c4138928627\Sage ; Database=ARMINDOData ; User Id=sa ; Password=sage2008+"))
{
    conn.Open();
    
    using (SqlCommand command = new SqlCommand("IF OBJECT_ID('UXFaturas', 'U') IS NULL CREATE TABLE UXFaturas(NumFaturas char(250) NOT NULL, Cont1 char(250) NOT NULL, Cont2 char(250) NOT NULL)", conn))
    {
        command.CommandType = CommandType.Text;
        command.ExecuteNonQuery();
    }
    
    using (SqlCommand command = new SqlCommand("INSERT INTO UXFaturas (NumFaturas, Cont, Cont2) VALUES (@NumFaturas, @Cont, @Cont2)", conn))
    {
        command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("@NumFaturas", numfatura);
        command.Parameters.AddWithValue("@Cont1", cont);
        command.Parameters.AddWithValue("@Cont2", cont2);
        command.ExecuteNonQuery();
    }
}

INSERT (Transact-SQL) | Microsoft Docs[^]

NB: Ideally, you should avoid connecting as sa. That user has unrestricted access to the entire server, which your application doesn't need. Instead, you should use an account which has only the permissions required by your application.

Posted 18-Aug-17 5:50am

Richard Deeming

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)

ZurdoDev · Accepted Answer · 2017-08-17T02:09:00

Solution 1

Look at your code carefully. You are calling ExecuteNonQuery before you even add your parameters. You also need to change your sql statement to use parameters.

SqlCommand insertCommand = new SqlCommand("INSERT INTO UXFaturas(NumFaturas, Cont1, Cont2) VALUES (@numfatura, @cont, @cont2)", conn);

Add the @ symbol in the VALUES clause.

Posted 17-Aug-17 2:09am

ZurdoDev

Comments

Member 13356973 17-Aug-17 8:19am

I'm calling ExecuteNonQuery before because if I do i after i'll get an error

ZurdoDev 17-Aug-17 8:20am

Then fix the error. When you call ExecuteNonQuery is when it calls into sql so if you haven't added in your parameters it does no good.

Member 13356973 17-Aug-17 8:20am

This is de error I get:

System.Data.SqlClient.SqlException: 'Could not find stored procedure 'IF OBJECT_ID('UXFaturas', 'U') IS NULL CREATE TABLE UXFaturas(NumFaturas char(250), Cont1 char(250), Cont2 char(250))'.'

ZurdoDev 17-Aug-17 8:22am

Think about what the error is telling you and then look at your code. It think you are trying to call a stored procedure because you told it you were going to with this line:
command.CommandType = System.Data.CommandType.StoredProcedure;

Change the commandType to Text.

Member 13356973 17-Aug-17 8:25am

Now I got no error, it should be working now right?

ZurdoDev 17-Aug-17 8:26am

I can't see your code so I have no idea if you have it all right. But try it and see if it works.

Member 13356973 17-Aug-17 8:27am

I can provide you more code if you need

ZurdoDev 17-Aug-17 8:28am

It'd be faster for you to just try it.

Member 13356973 17-Aug-17 8:36am

I created a select to make sure it is working and it returns me this "System.Data.SqlClient.SqlCommand" any clue on what i'm doing wrong?

ZurdoDev 17-Aug-17 8:38am

Ya, it looks like you called .ToString() on the SqlCommand.

I suggest you google for some examples using c# and sqlcommands because this is pretty basic stuff and I think you need some good tutorials to get you started.

Member 13356973 17-Aug-17 8:40am

I searched on google and I got the example I used

ZurdoDev 17-Aug-17 8:42am

The example did not call ExecuteNonQuery before adding parameters.

Member 13356973 17-Aug-17 8:44am

I did this:

SqlCommand comando = new SqlCommand();
string myConnectionString = @"Data source=xxx ; Database=xxx; User Id=xxx ; Password=xxx";
SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand command = new SqlCommand("SELECT * FROM UXFaturas", conn);
conn.Open();
textBox1.Text = command.ToString();
conn.Close();

ZurdoDev 17-Aug-17 8:45am

What do you want to know from the SELECT statement? Because all this code does is print the words "command" in the textbox.

Member 13356973 17-Aug-17 8:46am

I want to check if it saved correctly inside the table

ZurdoDev 17-Aug-17 8:47am

Then open up sql management studio and look.

Member 13356973 17-Aug-17 8:50am

It didnt write anything there

ZurdoDev 17-Aug-17 8:51am

Post the whole function then.

Member 13356973 17-Aug-17 8:52am

I'll add it into the question

Member 13356973 17-Aug-17 8:59am

Done

ZurdoDev 17-Aug-17 9:01am

You have 2 different commands. One to to create the table and that is the one you are executing and adding your parameters to.

Member 13356973 17-Aug-17 9:03am

but its not adding the parameters

ZurdoDev 17-Aug-17 9:04am

Because you are putting them on the wrong command. You have command and insertCommand.

You ought to make them all one command.

Member 13356973 17-Aug-17 9:06am

I have to add the insertCommand into the command?

ZurdoDev 17-Aug-17 9:09am

No. I strongly suggest you go through some tutorials. This is very basic stuff.

Use a single command.

Command Text will be "
IF OBJECT_ID ..... IS NOT NULL CREATE TABLE...
INSERT INTO table (field1, field2)
VALUES (@field1, @field2)
...

cmd.Parameters.AddWithValue("@field1", value)
...

You really, really, need to understand what you are doing. If you did, this would be pretty easy. I am not trying to insult you but you are asking very simple questions and you'd be better off taking some time to learn this process.

Member 13356973 17-Aug-17 9:17am

Did this and it stil doesnt work

ZurdoDev 17-Aug-17 9:18am

What does that mean?

Member 13356973 17-Aug-17 9:19am

Still no errors but doesnt saves into the sql

ZurdoDev 17-Aug-17 9:23am

Remember, we cannot see your code so update your question with the full sql code, not all the rest, just the sql code.

Member 13356973 17-Aug-17 9:24am

Done

ZurdoDev 17-Aug-17 9:36am

OK, now look at your sql statement and tell me why it is not inserting any data.

Member 13356973 17-Aug-17 9:37am

If I knew I would tell you

ZurdoDev 17-Aug-17 9:40am

Just slow down and read your sql statement. You don't have an insert statement so how would it insert data?

Member 13356973 17-Aug-17 9:42am

So how do I do it?

ZurdoDev 17-Aug-17 9:44am

I already answered above when I told you to use a single command. Put both statements into the same command. Look at my above comment.

Member 13356973 17-Aug-17 9:46am

You dont have any insert on your comment

ZurdoDev 17-Aug-17 9:52am

Yes, I do. This is the exact comment from above:
-----------------------------------------------

No. I strongly suggest you go through some tutorials. This is very basic stuff.

Use a single command.

Command Text will be "
IF OBJECT_ID ..... IS NOT NULL CREATE TABLE...
INSERT INTO table (field1, field2)
VALUES (@field1, @field2)
...

cmd.Parameters.AddWithValue("@field1", value)
...

You really, really, need to understand what you are doing. If you did, this would be pretty easy. I am not trying to insult you but you are asking very simple questions and you'd be better off taking some time to learn this process.

Member 13356973 17-Aug-17 10:08am

got this error doing as you said

System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

I updated the question code for what i've now

ZurdoDev 17-Aug-17 10:10am

THen fix the syntax. I'm not sure what you want me to do.

Member 13356973 17-Aug-17 10:11am

Which syntax? The error stops the program and happears here:

command.ExecuteNonQuery();

ZurdoDev 17-Aug-17 10:12am

It's a sql error. Notice that it is a SqlException. That means your Sql statement is wrong.

Member 13356973 17-Aug-17 10:14am

which statement?

ZurdoDev 17-Aug-17 10:17am

The CommandText property on the SqlCommand stores the sql statement that will be executed. In your case it has both sql statements. But CommandText is what will be sent to the sql server. Look at it, find the syntax error, and fix it.

Member 13356973 17-Aug-17 9:08am

What if I do a try{} catch{}?

ZurdoDev 17-Aug-17 9:10am

You should have one but that won't fix the problem. Again, you need to understand what you are doing. Stop trying to copy other people's code and have it work in your environment. Take the time to learn.