0

I have this database that I created programmatically:

enter image description here

Here is the code I used to create the database:

using (SqlConnection conn = new SqlConnection(@"Data source = **** ; Database=******* ; User Id=***** ; Password=*******"))
{
    int i = 0;
    i++;

    conn.Open();

    string sqlQuery = @"INSERT INTO UXFaturas(IDNumFaturas, NumFaturas , Cliente, Valor, Data) VALUES (@idnumfaturas, @numfaturas, @client, @valor, @data)";

    SqlCommand SQLcm = new SqlCommand();
    SQLcm.Connection = conn;
    SQLcm.CommandText = sqlQuery;
    SQLcm.CommandType = CommandType.Text;

    SQLcm.Parameters.AddWithValue("@idnumfaturas", i);
    SQLcm.Parameters.AddWithValue("@numfaturas", numfatura);
    SQLcm.Parameters.AddWithValue("@client", nomecli);
    SQLcm.Parameters.AddWithValue("@valor", valorStr);
    //SQLcm.Parameters.AddWithValue("@estado", cont); no variable yet
    SQLcm.Parameters.AddWithValue("@data", data);

    SQLcm.ExecuteNonQuery();

    MessageBox.Show("inseriu");
    conn.Close();

    MessageBox.Show("gravou");
}

I need to make the IDNumFaturas an auto-increment. The way I tried didn't work. I've also tried to make it as primary key but if I do it I can't add nothing into the table. Any suggestion? I'm working with C#

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Josue Figueiredo
  • 45
  • 1
  • 8
  • Set the field type to auto increment in sql.. – BugFinder Aug 22 '17 at 09:15
  • 1
    SQL Server has 'Identity' property, that increments the value by one automatically. You can try that if it fits your scenario. – Saravanan Sachi Aug 22 '17 at 09:17
  • in sql table designer set property `Is Indentity` to `true`. Also, if this is test data delete existing rows to avoid duplication – Nino Aug 22 '17 at 09:18
  • And after you set the IDENTITY property to Yes in your management studio remember that you shouldn't reference that field and pass a value for it in your code – Steve Aug 22 '17 at 09:18
  • Try to declare "int i" as Global in C# Or In SQL, set column property as IsIdentity to True. – Shyam Vemula Aug 22 '17 at 09:21
  • You cannot make an existing column "auto-increment" - you have to specify the `IDENTITY` attribute at the time the column *is created*. – marc_s Aug 22 '17 at 09:26

2 Answers2

1

your tables should have been created with 

INT NOT NULL IDENTITY(1,1),

on the primary key field.

Or go into your table builder and set Identity to Yes and set the seed to 1, increment to 1

What you need to do is understand what an identity is in SQL server

If you want to add an identity column at this stage, it will be a table rebuild meaning you will have to turn off SQL server from restricting you from re-building the table( this should be in the option menu, and the message that pops up when you try to rebuild the table will tell you exactly what what option to turn off) . I would suggest you turn it back on when you are done.

Also you will need to remove the insert to the primary key field as creating identity will turn off inserts into that column

mahlatse
  • 1,322
  • 12
  • 24
1

You should create table with this syntax:

CREATE TABLE UXFaturas ( IDNumFaturas int IDENTITY (1, 1) PRIMARY KEY, );

Or

Getting maximum value of IDNumFaturas from current table UXFaturas and increase one value.

E.g. If its 8 is last maximum value of IDNumFaturas means you should get that value and increase one values as 9.

Example query for getting maximum value of IDNumFaturas from UXFaturas table

SqlCommand cmd = new SqlCommand ("SELECT MAX (IDNumFaturas) +1 FROM UXFaturas", conn); int newID = (int)cmd.ExecuteScalar(); SQLcm.Parameters.AddWithValue("@idnumfaturas", newID);

VIGNESH ARUNACHALAM
  • 652
  • 4
  • 24