Click here to Skip to main content
15,072,627 members
Please Sign up or sign in to vote.
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
Updated 21-Aug-17 7:55am
v5

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.
   
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.
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.
   
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.
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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900