0

I know the issue and I searched online. I know that this exception is thrown when my value length is greater than the length defined in the SQL Server table.

This is my C# code:

using (SqlCommand cmdInsertUser = new SqlCommand("INSERT INTO [Accounts] VALUES (@username, @password, @email, @fullname, @money, @active, @activationCode, @registrationDate);", con))
{
    Random random = new Random();
    const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    activationCode = (Enumerable.Repeat(chars, 6).Select(s => s[random.Next(s.Length)]).ToArray()).ToString();

    con.Open();

    cmdInsertUser.Parameters.AddWithValue("@username", txtSignUpUsername.Text);
    cmdInsertUser.Parameters.AddWithValue("@password", txtSignUpPassword.Text);
    cmdInsertUser.Parameters.AddWithValue("@email", txtSignUpEmail.Text);
    cmdInsertUser.Parameters.AddWithValue("@fullname", txtSignUpFullName.Text);
    cmdInsertUser.Parameters.AddWithValue("@money", Convert.ToInt32(txtSignUpMoney.Text));
    cmdInsertUser.Parameters.AddWithValue("@active", 0);
    cmdInsertUser.Parameters.AddWithValue("@activationCode", activationCode);
    cmdInsertUser.Parameters.AddWithValue("@registrationDate", DateTime.Now.ToShortDateString());

    cmdInsertUser.ExecuteNonQuery();
    con.Close();
}

This is my SQL Server table structure:

CREATE TABLE [dbo].[Accounts] 
(
    [Id]               INT IDENTITY (1, 1) NOT NULL,
    [Username]         VARCHAR(12) NOT NULL,
    [Password]         VARCHAR(15) NOT NULL,
    [Email]            VARCHAR(50) NOT NULL,
    [FullName]         VARCHAR(50) NOT NULL,
    [Money]            INT         NOT NULL,
    [Active]           BIT         NOT NULL,
    [ActivationCode]   VARCHAR(6)  NOT NULL,
    [RegistrationDate] VARCHAR(50) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
);

The values that I enter:

  • Username = daniel7
  • Password = 123456
  • Email = da************16@email.com (my real mail is given instead of *)
  • FullName = Daniel Ohayon
  • Money = 0
  • Active = 0
  • ActivationCode = something random with 6 chars
  • RegistrationDate = DateTime.Now.ToShortDateString()

PLEASE HELP ME!

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Daniel
  • 1
  • I think the problem is probably with your Active column. This other question may help if the link above doesn't (see the second answer) https://stackoverflow.com/questions/22155483/how-to-insert-1-or-0-in-sql-server-for-bit-type-field-if-checkbox-is-checked – hatchet - done with SOverflow Sep 27 '18 at 22:43
  • 1
    Stop the laziness. Don't use [addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). In addition, there is more laziness with your insert statement. ALWAYS specify the column list for the insert statement. Why? Because someone might change the order and somone might add columns at a later date. Let's avoid additional work that may not be necessary. Actually defining your parameters may solve your issue. – SMor Sep 27 '18 at 22:46
  • 2
    And the column RegistrationDate **should not be varchar!!!** It should be date or datetime2 (with the appropriate precision). – SMor Sep 27 '18 at 22:48
  • Are you sure Activation Code passing 6 characters. Put break point and check? – Shakeer Hussain Sep 27 '18 at 22:53
  • @hatchet thanks for the reply. I read this questions and unfortunately they didnt help me. I even tried to change the Active column from bit to int and insert 0 and it gave the same exception... :\ any other suggestions? – Daniel Sep 27 '18 at 22:56
  • @ShakeerHussain You are right! the code is giving me System.Char[] instead of a real code. Do you know why? it should work – Daniel Sep 27 '18 at 23:00
  • ToString returns the name of the type when its not overloaded. An array does not overload that method. Use string.Concat or string.Join to build a string from your array – pinkfloydx33 Sep 27 '18 at 23:33
  • What is the **exact** value of `activationCode`, from the `Watch Window`? _Please check, don't guess._ – mjwills Sep 28 '18 at 01:36
  • Some other tips, don't store passwords at all (unless you must use them to authenticate against 3rd party service), hash them with a salt (google it if you don't know what that means). Also, don't store dates as text, store them as dates, this will make it much much easier to handle. – Lasse V. Karlsen Sep 28 '18 at 05:53

1 Answers1

2

I feel like your issue stems from this line of code:

activationCode = (Enumerable.Repeat(chars, 6).Select(s => s[random.Next(s.Length)]).ToArray()).ToString();

This will return the name of the item, as this is an array object, not a six-digit character count you're probably expecting.

You can try using: 

activationCode = new string(Enumerable.Repeat(chars, 6).Select(s => s[random.Next(s.Length)]).ToArray())

instead.

Nathan
  • 1,480
  • 1
  • 12
  • 21