Typed Dataset with Update SPROC

[=?Utf-8?B?UlBhcmtlcg==?=]

I used the wizard to generate a typed dataset for my table and let it create
my SPROCs. It created everything, and the GetData() method and the custom
GetByUserName query works great, but when I try to call the Update() method
of my TableAdapter, I get the following:

"Procedure 'stp_SecurityUs ers_Update' expects parameter '@userName', which
was not supplied."

@userName happens to be the first param in the SPROC. Here is the SPROC
that VS2005 generated for me:

<-- start SPROC -->

CREATE PROCEDURE stp_SecurityUse rs_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAdded datetime,
@lastLogin datetime,
@status bit,
@pwdExpired bit,
@lastPasswordCh ange datetime,
@Original_userI D int,
@Original_userN ame varchar(15),
@IsNull_lastNam e varchar(50),
@Original_lastN ame varchar(50),
@IsNull_firstNa me varchar(50),
@Original_first Name varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_passwor d varchar(50),
@Original_passw ord varchar(50),
@IsNull_dateAdd ed datetime,
@Original_dateA dded datetime,
@IsNull_lastLog in datetime,
@Original_lastL ogin datetime,
@IsNull_status bit,
@Original_statu s bit,
@IsNull_pwdExpi red bit,
@Original_pwdEx pired bit,
@IsNull_lastPas swordChange datetime,
@Original_lastP asswordChange datetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUser s] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordCha nge] = @lastPasswordCh ange WHERE
(([userID] = @Original_userI D) AND ([userName] = @Original_userN ame) AND
((@IsNull_lastN ame = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastN ame)) AND ((@IsNull_first Name = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_first Name)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email )) AND ((@IsNull_passw ord = 1 AND [password] IS NULL) OR
([password] = @Original_passw ord)) AND ((@IsNull_dateA dded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateA dded)) AND
((@IsNull_lastL ogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastL ogin)) AND ((@IsNull_statu s = 1 AND [status] IS NULL) OR
([status] = @Original_statu s)) AND ((@IsNull_pwdEx pired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdEx pired)) AND
((@IsNull_lastP asswordChange = 1 AND [lastPasswordCha nge] IS NULL) OR
([lastPasswordCha nge] = @Original_lastP asswordChange)) );
<!--end SPROC-->

Here is the code used to try to update a user's record with a new password:

1 public static bool SetPassword(str ing userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTa bleAdapter taUsers = new
SecurityUsersTa bleAdapter();
6
7 SecurityUsers.S ecurityUsersDat aTable dtUsers =
taUsers.GetByUs erName(userName );
8
9 if (dtUsers.Rows.C ount != 0)
10 {
11 dtUsers[0].password = hashedPassword;
12
13 try
14 {
15 taUsers.Update( dtUsers);
16
17 success = true;
18 }
19 catch
20 {
21 //
22 }
23 }
24
25 return success;
26 }

The exception occurs on line 15 above. I have looked through the
SecurityUsers.D esigner.cs file created by VS, and I can find the methods;
I'm not a C# expert, but nothing jumped out at me as to why this would be
happening.

I thought that VS kindly generated what was needed to map the parameters and
pass them with the method(s) it creates. If I add "= NULL" to all params in
the SPROC, the error goes away, but nothing gets updated. The Update method
is not passing the variables via the datatable, and I don't know where to
look to try to troubleshoot this. Can anyone help?

[Nick]

SPROC is expecting a datarow parameter. The sample is sending a datatable.

[=?Utf-8?B?UlBhcmtlcg==?=]

Thanks for the reply.

I figured this out recently, just haven't been back to post the solution.
The table adapter wizard did not map my source columns to the sproc
parameters. This is why I hate doing things visually.

(Actually, the Update() method can take a datatable or datarow parameter)


"Nick" wrote:

SPROC is expecting a datarow parameter. The sample is sending a datatable.

[=?Utf-8?B?UlBhcmtlcg==?=]

So now my new problem. The error (below in original post) is resolved, but
using the same code, I am now getting the following when calling the Update()
method:

"Failed to convert parameter value from a Int32 to DateTime"

I'm researching this, but if anyone has any ideas, I'm all ears. I've
looked at this for so long, I have no objectivity. The only variable that is
Int32 is the userID parameter and column. The SPROC and method stub for
Update() is all referencing the correct data types.

Thanks,
Rparker

"RParker" wrote:

I used the wizard to generate a typed dataset for my table and let it create
my SPROCs. It created everything, and the GetData() method and the custom
GetByUserName query works great, but when I try to call the Update() method
of my TableAdapter, I get the following:

"Procedure 'stp_SecurityUs ers_Update' expects parameter '@userName', which
was not supplied."

@userName happens to be the first param in the SPROC. Here is the SPROC
that VS2005 generated for me:

<-- start SPROC -->

CREATE PROCEDURE stp_SecurityUse rs_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAdded datetime,
@lastLogin datetime,
@status bit,
@pwdExpired bit,
@lastPasswordCh ange datetime,
@Original_userI D int,
@Original_userN ame varchar(15),
@IsNull_lastNam e varchar(50),
@Original_lastN ame varchar(50),
@IsNull_firstNa me varchar(50),
@Original_first Name varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_passwor d varchar(50),
@Original_passw ord varchar(50),
@IsNull_dateAdd ed datetime,
@Original_dateA dded datetime,
@IsNull_lastLog in datetime,
@Original_lastL ogin datetime,
@IsNull_status bit,
@Original_statu s bit,
@IsNull_pwdExpi red bit,
@Original_pwdEx pired bit,
@IsNull_lastPas swordChange datetime,
@Original_lastP asswordChange datetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUser s] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordCha nge] = @lastPasswordCh ange WHERE
(([userID] = @Original_userI D) AND ([userName] = @Original_userN ame) AND
((@IsNull_lastN ame = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastN ame)) AND ((@IsNull_first Name = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_first Name)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email )) AND ((@IsNull_passw ord = 1 AND [password] IS NULL) OR
([password] = @Original_passw ord)) AND ((@IsNull_dateA dded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateA dded)) AND
((@IsNull_lastL ogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastL ogin)) AND ((@IsNull_statu s = 1 AND [status] IS NULL) OR
([status] = @Original_statu s)) AND ((@IsNull_pwdEx pired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdEx pired)) AND
((@IsNull_lastP asswordChange = 1 AND [lastPasswordCha nge] IS NULL) OR
([lastPasswordCha nge] = @Original_lastP asswordChange)) );
<!--end SPROC-->

Here is the code used to try to update a user's record with a new password:

1 public static bool SetPassword(str ing userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTa bleAdapter taUsers = new
SecurityUsersTa bleAdapter();
6
7 SecurityUsers.S ecurityUsersDat aTable dtUsers =
taUsers.GetByUs erName(userName );
8
9 if (dtUsers.Rows.C ount != 0)
10 {
11 dtUsers[0].password = hashedPassword;
12
13 try
14 {
15 taUsers.Update( dtUsers);
16
17 success = true;
18 }
19 catch
20 {
21 //
22 }
23 }
24
25 return success;
26 }

The exception occurs on line 15 above. I have looked through the
SecurityUsers.D esigner.cs file created by VS, and I can find the methods;
I'm not a C# expert, but nothing jumped out at me as to why this would be
happening.

I thought that VS kindly generated what was needed to map the parameters and
pass them with the method(s) it creates. If I add "= NULL" to all params in
the SPROC, the error goes away, but nothing gets updated. The Update method
is not passing the variables via the datatable, and I don't know where to
look to try to troubleshoot this. Can anyone help?

[faberk]

On Jul 13, 11:24 am, RParker <RPar...@discus sions.microsoft .com>
wrote:

So now my new problem. The error (below in original post) is resolved, but
using the same code, I am now getting the following when calling the Update()
method:

"Failedtoconver tparametervalue from aInt32toDateTim e"

I'm researching this, but if anyone has any ideas, I'm all ears. I've
looked at this for so long, I have no objectivity. The only variable that isInt32is the userIDparameter and column. The SPROC and method stub for
Update() is all referencing the correct data types.

Thanks,
Rparker

"RParker" wrote:

I used the wizard to generate a typed dataset for my table and let it create
my SPROCs. It created everything, and the GetData() method and the custom
GetByUserName query works great, but when I try to call the Update() method
of my TableAdapter, I get the following:

"Procedure 'stp_SecurityUs ers_Update' expectsparamete r'@userName', which
was not supplied."

@userName happens to be the first param in the SPROC. Here is the SPROC
that VS2005 generated for me:

<-- start SPROC -->

CREATE PROCEDURE stp_SecurityUse rs_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAddeddatet ime,
@lastLogindatet ime,
@status bit,
@pwdExpired bit,
@lastPasswordCh angedatetime,
@Original_userI D int,
@Original_userN ame varchar(15),
@IsNull_lastNam e varchar(50),
@Original_lastN ame varchar(50),
@IsNull_firstNa me varchar(50),
@Original_first Name varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_passwor d varchar(50),
@Original_passw ord varchar(50),
@IsNull_dateAdd eddatetime,
@Original_dateA ddeddatetime,
@IsNull_lastLog indatetime,
@Original_lastL ogindatetime,
@IsNull_status bit,
@Original_statu s bit,
@IsNull_pwdExpi red bit,
@Original_pwdEx pired bit,
@IsNull_lastPas swordChangedate time,
@Original_lastP asswordChangeda tetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUser s] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordCha nge] = @lastPasswordCh ange WHERE
(([userID] = @Original_userI D) AND ([userName] = @Original_userN ame) AND
((@IsNull_lastN ame = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastN ame)) AND ((@IsNull_first Name = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_first Name)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email )) AND ((@IsNull_passw ord = 1 AND [password] IS NULL) OR
([password] = @Original_passw ord)) AND ((@IsNull_dateA dded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateA dded)) AND
((@IsNull_lastL ogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastL ogin)) AND ((@IsNull_statu s = 1 AND [status] IS NULL) OR
([status] = @Original_statu s)) AND ((@IsNull_pwdEx pired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdEx pired)) AND
((@IsNull_lastP asswordChange = 1 AND [lastPasswordCha nge] IS NULL) OR
([lastPasswordCha nge] = @Original_lastP asswordChange)) );
<!--end SPROC-->

Here is the code used to try to update a user's record with a new password:

1 public static bool SetPassword(str ing userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTa bleAdapter taUsers = new
SecurityUsersTa bleAdapter();
6
7 SecurityUsers.S ecurityUsersDat aTable dtUsers =
taUsers.GetByUs erName(userName );
8
9 if (dtUsers.Rows.C ount != 0)
10 {
11 dtUsers[0].password = hashedPassword;
12
13 try
14 {
15 taUsers.Update( dtUsers);
16
17 success = true;
18 }
19 catch
20 {
21 //
22 }
23 }
24
25 return success;
26 }

The exception occurs on line 15 above. I have looked through the
SecurityUsers.D esigner.cs file created by VS, and I can find the methods;
I'm not a C# expert, but nothing jumped out at me as to why this would be
happening.

I thought that VS kindly generated what was needed to map the parameters and
pass them with the method(s) it creates. If I add "= NULL" to all params in
the SPROC, the error goes away, but nothing gets updated. The Update method
is not passing the variables via the datatable, and I don't know where to
look to try to troubleshoot this. Can anyone help?- Hide quoted text -

- Show quoted text -

I am having the same problem withthe update() method. This has occured
once before and I basically had to delete the table adapter from the
record set designer and recreate all the objects on the form that were
limked to it. This did work, but not sure why.

Now I am seeing the same problem come up again and am less will to
duplicate my work again. I did some browsing on the web and found some
information that indicated there was a problem with how vs has
assigned the "@isnull_column name" parameter a datatype when the table
adapter was created.

Here is my solution: If you select the table adapter in question on
the recordset designer and expand the "update command" under
properties, yu will be able to edit the parameters collection. Scroll
through these and make sure all of your datetime fields have thier
corresponding "@isnull_column name" parameter set to an int32 datatype.

After looking at the stored procedure (vs generated) behind this
command i am still unsure why this fix works. the Stored Procedure is
expecting a datetime type for these variables. Any who has any more
info about this would be appreciated.

[=?Utf-8?B?UlBhcmtlcg==?=]

Thank you, thank you, thank you. Your solution does "fix" the problem. I
don't understand how this is working with the SPROC, but at least I can move
on. Thanks again for taking the time to post this.

"fa****@comcast .net" wrote:

On Jul 13, 11:24 am, RParker <RPar...@discus sions.microsoft .com>
wrote:

So now my new problem. The error (below in original post) is resolved, but
using the same code, I am now getting the following when calling the Update()
method:

"Failedtoconver tparametervalue from aInt32toDateTim e"

I'm researching this, but if anyone has any ideas, I'm all ears. I've
looked at this for so long, I have no objectivity. The only variable that isInt32is the userIDparameter and column. The SPROC and method stub for
Update() is all referencing the correct data types.

Thanks,
Rparker

"RParker" wrote:

I used the wizard to generate a typed dataset for my table and let it create
my SPROCs. It created everything, and the GetData() method and the custom
GetByUserName query works great, but when I try to call the Update() method
of my TableAdapter, I get the following:

"Procedure 'stp_SecurityUs ers_Update' expectsparamete r'@userName', which
was not supplied."

@userName happens to be the first param in the SPROC. Here is the SPROC
that VS2005 generated for me:

<-- start SPROC -->

CREATE PROCEDURE stp_SecurityUse rs_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAddeddatet ime,
@lastLogindatet ime,
@status bit,
@pwdExpired bit,
@lastPasswordCh angedatetime,
@Original_userI D int,
@Original_userN ame varchar(15),
@IsNull_lastNam e varchar(50),
@Original_lastN ame varchar(50),
@IsNull_firstNa me varchar(50),
@Original_first Name varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_passwor d varchar(50),
@Original_passw ord varchar(50),
@IsNull_dateAdd eddatetime,
@Original_dateA ddeddatetime,
@IsNull_lastLog indatetime,
@Original_lastL ogindatetime,
@IsNull_status bit,
@Original_statu s bit,
@IsNull_pwdExpi red bit,
@Original_pwdEx pired bit,
@IsNull_lastPas swordChangedate time,
@Original_lastP asswordChangeda tetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUser s] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordCha nge] = @lastPasswordCh ange WHERE
(([userID] = @Original_userI D) AND ([userName] = @Original_userN ame) AND
((@IsNull_lastN ame = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastN ame)) AND ((@IsNull_first Name = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_first Name)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email )) AND ((@IsNull_passw ord = 1 AND [password] IS NULL) OR
([password] = @Original_passw ord)) AND ((@IsNull_dateA dded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateA dded)) AND
((@IsNull_lastL ogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastL ogin)) AND ((@IsNull_statu s = 1 AND [status] IS NULL) OR
([status] = @Original_statu s)) AND ((@IsNull_pwdEx pired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdEx pired)) AND
((@IsNull_lastP asswordChange = 1 AND [lastPasswordCha nge] IS NULL) OR
([lastPasswordCha nge] = @Original_lastP asswordChange)) );
<!--end SPROC-->

Here is the code used to try to update a user's record with a new password:

1 public static bool SetPassword(str ing userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTa bleAdapter taUsers = new
SecurityUsersTa bleAdapter();
6
7 SecurityUsers.S ecurityUsersDat aTable dtUsers =
taUsers.GetByUs erName(userName );
8
9 if (dtUsers.Rows.C ount != 0)
10 {
11 dtUsers[0].password = hashedPassword;
12
13 try
14 {
15 taUsers.Update( dtUsers);
16
17 success = true;
18 }
19 catch
20 {
21 //
22 }
23 }
24
25 return success;
26 }

The exception occurs on line 15 above. I have looked through the
SecurityUsers.D esigner.cs file created by VS, and I can find the methods;
I'm not a C# expert, but nothing jumped out at me as to why this would be
happening.

I thought that VS kindly generated what was needed to map the parameters and
pass them with the method(s) it creates. If I add "= NULL" to all params in
the SPROC, the error goes away, but nothing gets updated. The Update method
is not passing the variables via the datatable, and I don't know where to
look to try to troubleshoot this. Can anyone help?- Hide quoted text -

- Show quoted text -

I am having the same problem withthe update() method. This has occured
once before and I basically had to delete the table adapter from the
record set designer and recreate all the objects on the form that were
limked to it. This did work, but not sure why.

Now I am seeing the same problem come up again and am less will to
duplicate my work again. I did some browsing on the web and found some
information that indicated there was a problem with how vs has
assigned the "@isnull_column name" parameter a datatype when the table
adapter was created.

Here is my solution: If you select the table adapter in question on
the recordset designer and expand the "update command" under
properties, yu will be able to edit the parameters collection. Scroll
through these and make sure all of your datetime fields have thier
corresponding "@isnull_column name" parameter set to an int32 datatype.

After looking at the stored procedure (vs generated) behind this
command i am still unsure why this fix works. the Stored Procedure is
expecting a datetime type for these variables. Any who has any more
info about this would be appreciated.