# Need a little Sql Assistance



## Kreij (Aug 4, 2009)

I am rolling my own Insert command for my database table. Here's the code :

```
userAdapter = new SqlDataAdapter("SELECT * FROM Users", connection);
userAdapter.Fill(userDataset = new DataSet(), "Users");
userAdapter.InsertCommand = CreateInsertCommand();
userAdapter.UpdateCommand = CreateUpdateCommand();
userBindingsource = new BindingSource(userDataset, "Users");

private SqlCommand CreateInsertCommand()
        {
            SqlCommand _Command = new SqlCommand("INSERT INTO Users (Name, Username, Password, IsAdmin) " +
                "VALUES (@Name, @Username, @Password, @IsAdmin)", connection);
            SqlParameter[] _Parameters = new SqlParameter[] {
                new SqlParameter("@UserID", SqlDbType.Int, sizeof(int), "UserID"),
                new SqlParameter("@Name", SqlDbType.VarChar, 80, "Name"),
                new SqlParameter("@Username", SqlDbType.VarChar, 20, "Username"),
                new SqlParameter("@Password", SqlDbType.VarChar, 256, "Password"),
                new SqlParameter("@IsAdmin", SqlDbType.Bit, 1, "IsAdmin")
            };
            _Command.Parameters.AddRange(_Parameters);
            _Command.Parameters["@UserID"].Direction = ParameterDirection.Output;

            return _Command;
        }

private void saveButton_Click(object sender, EventArgs e)
        {
            if (pwTextbox.Text.Trim().Length != 0)
            {
                userAdapter.InsertCommand.Parameters["@Password"].Value = 
                    Utilities.GeneratePasswordHash(pwTextbox.Text.Trim());
            }
            // MessageBox.Show(userAdapter.InsertCommand.Parameters["@Password"].Value.ToString());
            userAdapter.Update(userDataset, "Users");
        }
```

If I check the value of the Password parameter, prior to running the update ,it shows me the hash string as being contained in the paramter.

However, the update command always throws a "Can't put null in the password field" error.

Any idea what is wrong here?


----------



## W1zzard (Aug 4, 2009)

password might be a reserved keyword, try to put it into quotes or rename the field


----------



## FordGT90Concept (Aug 4, 2009)

What kind of database is it?  MSSQL?  MySQL?  Every single one has different ways of grouping syntax (some are ", some are ', some are `).

Is it a compiler error (Visual Studio) or a runtime error (database)?


All those @ symbols appear out of context.


----------



## Kreij (Aug 4, 2009)

SqlServer and it's a runtime error.

Found the problem though.

The SqlDataAdapter fills the DataSet. The BindingSource then mediates between the controls and the DataSet.

When the Update command runs (in this case doing an Insert), it fills the InsertCommand parameters from the DataSet (which is after I set the Password paramter). Since I basically did an end around the BindingSource and set the parameter manually, the Update command overwrote it with what was in the DataSet (in this case NULL, because I do not have anything bound to the Password field in the DataSet through the BindingSource). 

If I force the value into the BindingSource, like so ...

```
DataRowView view = (DataRowView)userBindingsource.Current;
view["Password"] = Utilities.GeneratePasswordHash(pwTextBox.Text.Trin());
userBindingSource.EndEdit();

userAdapter.Update(userDataset, "Users");
```

... the Update command processes correctly.

@Ford : The "@"s are required for SqlParameter variables.


----------



## FordGT90Concept (Aug 5, 2009)

Ah, I've only worked with Oracle and MySQL; hence, why I didn't recognize it.   Glad you got it sorted quick.


----------

