Inserting Record Problem

  • Thread starter Thread starter thebison
  • Start date Start date
T

thebison

Hi all,

I hope someone can help with this relatively simple problem.
I am building a timesheet application using ASP.NET C# with Visual
Studio 2003.As it is only a protoype application, my database has been
made in MSDE.

I have a 'Create Resource' page where new employees are added to the
database. On my data-entry form I capture all the standard details for
the Resource table. I have two drop down boxes which are data bound and
populated with department IDs and manager IDs from other tables
(datasets). I have a stored procedure for inserting a new resource, and
when I click the 'Submit' button everything seems to work. However when
I look at the values inserted to the Resource table, everything is
correct apart from the two drop-down values which have defaulted to
'1', even when I select '3', or '4' or any other option in the
drop-down list. Whatever I do, when the record is written, it defaults
to the value of '1'.

The code for writing these two elements in the insert is:

sqlParam = cmd.Parameters.Add("@ManagerID", SqlDbType.Int, 4);

sqlParam.Value = ddManager.SelectedValue;


sqlParam = cmd.Parameters.Add("@DeptID", SqlDbType.Int, 4);

sqlParam.Value = ddDept.SelectedValue;

The weird thing is that when I use the same code on another form for a
drop-down which has 'hard-coded' values in the drop down list this
works fine.

Any help would be much appreciated! I'm sure it's something simple!

Thanks

Al
 
First, just so I understand. If you hard code the value in then it behaves
as expected, is that right?

Double Check the Values of the DataValueField and make sure it's what you
expect.

Also, the SqlDbType is an Integer but SelectedValue is a String. This might
be causing a problem. Verify the parameter in the DB (the lenght and type)
and then try casting the SelectedValue to an Int32. I forget the exact rule
on how SqlClient handles it, but there may be type conversion going on with
the String and int that's causing the weirdness.

Lemme know how it goes ;-)
 
Hi thebison,

Any ddl.selectedValue is string type value. Hence it's better to explicitly
cast to integer:

sqlParam.Value = Convert.ToInt32(ddManager.SelectedValue);

HTH

Elton Wang
 
Thanks for replying!

Firstly, Ryan, what is happening on the other form is that I am using a
dropdown list populated with the hard coded values "1, 2, 3,4". When I
select one of these and update the database with

sqlParam.Value = ddProjStatus.SelectedValue

it works fine?... yet when I try this with the other form (where the
drop down is databound and populated by another table) it doesn't seem
to work.
Have checked the datavalue fields, they are ManagerID and DeptID as
expected. I have also tried wha Elton suggested, however this does not
seem to have worked either. I am going to post the main parts of the
code to see if either of you can spot where the problem might be, if
that's ok?


private void StoreAccountDetails
( string FirstName,
string LastName,
string userName,
string passwordHash,
string salt,
string StartDate,
string EndDate,
int ManagerID,
int DeptId,
string ResourceActive,
string PayRate)
{

SqlCommand cmd = new SqlCommand("NewRegisterUser", sqlConnection1 );
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = null;

sqlParam = cmd.Parameters.Add("@FirstName",SqlDbType.VarChar,10);
sqlParam.Value = txtFirstName.Text;

sqlParam = cmd.Parameters.Add("@LastName",SqlDbType.VarChar,50);
sqlParam.Value = txtSurname.Text;

sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar,255);
sqlParam.Value = txtUserName.Text;

sqlParam = cmd.Parameters.Add("@passwordHash ",
SqlDbType.VarChar,40);
sqlParam.Value = passwordHash;

sqlParam = cmd.Parameters.Add("@salt", SqlDbType.VarChar, 10);
sqlParam.Value = salt;

sqlParam = cmd.Parameters.Add("@StartDate",SqlDbType.DateTime,8);
sqlParam.Value = txtStartDate.Text;

sqlParam = cmd.Parameters.Add("@EndDate", SqlDbType.DateTime,8);
sqlParam.Value = txtEndDate.Text;

sqlParam = cmd.Parameters.Add("@ManagerID", SqlDbType.Int, 4);
sqlParam.Value = Convert.ToInt32(ddManager.SelectedValue);

sqlParam = cmd.Parameters.Add("@DeptID", SqlDbType.Int, 4);
sqlParam.Value = Convert.ToInt32(ddDept.SelectedValue);

sqlParam = cmd.Parameters.Add("@ResourceActive", SqlDbType.VarChar,
50);
sqlParam.Value = rdoResActive.SelectedValue;

sqlParam = cmd.Parameters.Add("@PayRate", SqlDbType.Int, 4);
sqlParam.Value = txtPayRate.Text;
try........etc

(ddManager and ddDept are the problem fields here, in case I hadn't
made that clear!)

The 'Submit' Button click event:

private void btnSubmit_Click(object sender, System.EventArgs e)
{
string salt = CreateSalt(5);
string passwordHash = CreatePasswordHash(txtPassword.Text,salt);
try
{

StoreAccountDetails( txtFirstName.Text,
txtSurname.Text,
txtUserName.Text,
passwordHash,
salt,
txtStartDate.Text,
txtEndDate.Text,
Convert.ToInt32(ddManager.SelectedValue),
Convert.ToInt32(ddDept.SelectedValue),
rdoResActive.SelectedValue,
txtPayRate.Text);

Label16.Text = "New Account Created";
txtUserName.Text = "";
}
catch(Exception ex)
{
Label16.Text = ex.Message;
}

I have tried a number of things to sort this, it must be something
really simple if it works with hard-coded values. I believe it defaults
to '1' because this is the first value in the table the dropdown is
feeding from?... So just to clarify, with the code above, if i select
ManagerID to be '4', the Insert statement is still writing it to the
table a value of '1'.

Any suggestions?

Thanks!

Al
 
Hey,

Thanks for your help...it was a simple problem! It was because (being a
careless newbie) i had forgotten the

if(!IsPostBack)

in my page_load...so my dd values were defaulting to the first value.

It now works!

Thanks!
 
The first thing to do is put a breakpoint or a Debug.writeline statement on
the line where you're setting @ManagerID when you select 4 or some other
number other than 1. It's see first if we're passing in 1 instead of what we
expect. Otherwise it might be in the stored proc.
 
Back
Top