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_SecurityUsers_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_SecurityUsers_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAdded datetime,
@lastLogin datetime,
@status bit,
@pwdExpired bit,
@lastPasswordChange datetime,
@Original_userID int,
@Original_userName varchar(15),
@IsNull_lastName varchar(50),
@Original_lastName varchar(50),
@IsNull_firstName varchar(50),
@Original_firstName varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_password varchar(50),
@Original_password varchar(50),
@IsNull_dateAdded datetime,
@Original_dateAdded datetime,
@IsNull_lastLogin datetime,
@Original_lastLogin datetime,
@IsNull_status bit,
@Original_status bit,
@IsNull_pwdExpired bit,
@Original_pwdExpired bit,
@IsNull_lastPasswordChange datetime,
@Original_lastPasswordChange datetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUsers] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordChange] = @lastPasswordChange WHERE
(([userID] = @Original_userID) AND ([userName] = @Original_userName) AND
((@IsNull_lastName = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastName)) AND ((@IsNull_firstName = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_firstName)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email)) AND ((@IsNull_password = 1 AND [password] IS NULL) OR
([password] = @Original_password)) AND ((@IsNull_dateAdded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateAdded)) AND
((@IsNull_lastLogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastLogin)) AND ((@IsNull_status = 1 AND [status] IS NULL) OR
([status] = @Original_status)) AND ((@IsNull_pwdExpired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdExpired)) AND
((@IsNull_lastPasswordChange = 1 AND [lastPasswordChange] IS NULL) OR
([lastPasswordChange] = @Original_lastPasswordChange)));
<!--end SPROC-->

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

1 public static bool SetPassword(string userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTableAdapter taUsers = new
SecurityUsersTableAdapter();
6
7 SecurityUsers.SecurityUsersDataTable dtUsers =
taUsers.GetByUserName(userName);
8
9 if (dtUsers.Rows.Count != 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.Designer.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_SecurityUsers_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_SecurityUsers_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAdded datetime,
@lastLogin datetime,
@status bit,
@pwdExpired bit,
@lastPasswordChange datetime,
@Original_userID int,
@Original_userName varchar(15),
@IsNull_lastName varchar(50),
@Original_lastName varchar(50),
@IsNull_firstName varchar(50),
@Original_firstName varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_password varchar(50),
@Original_password varchar(50),
@IsNull_dateAdded datetime,
@Original_dateAdded datetime,
@IsNull_lastLogin datetime,
@Original_lastLogin datetime,
@IsNull_status bit,
@Original_status bit,
@IsNull_pwdExpired bit,
@Original_pwdExpired bit,
@IsNull_lastPasswordChange datetime,
@Original_lastPasswordChange datetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUsers] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordChange] = @lastPasswordChange WHERE
(([userID] = @Original_userID) AND ([userName] = @Original_userName) AND
((@IsNull_lastName = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastName)) AND ((@IsNull_firstName = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_firstName)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email)) AND ((@IsNull_password = 1 AND [password] IS NULL) OR
([password] = @Original_password)) AND ((@IsNull_dateAdded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateAdded)) AND
((@IsNull_lastLogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastLogin)) AND ((@IsNull_status = 1 AND [status] IS NULL) OR
([status] = @Original_status)) AND ((@IsNull_pwdExpired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdExpired)) AND
((@IsNull_lastPasswordChange = 1 AND [lastPasswordChange] IS NULL) OR
([lastPasswordChange] = @Original_lastPasswordChange)));
<!--end SPROC-->

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

1 public static bool SetPassword(string userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTableAdapter taUsers = new
SecurityUsersTableAdapter();
6
7 SecurityUsers.SecurityUsersDataTable dtUsers =
taUsers.GetByUserName(userName);
8
9 if (dtUsers.Rows.Count != 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.Designer.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 <[e-mail]>
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:

"Failedtoconvertparametervaluefrom aInt32toDateTime"

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 userIDparameterand 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_SecurityUsers_Update' expectsparameter'@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_SecurityUsers_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAddeddatetime,
@lastLogindatetime,
@status bit,
@pwdExpired bit,
@lastPasswordChangedatetime,
@Original_userID int,
@Original_userName varchar(15),
@IsNull_lastName varchar(50),
@Original_lastName varchar(50),
@IsNull_firstName varchar(50),
@Original_firstName varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_password varchar(50),
@Original_password varchar(50),
@IsNull_dateAddeddatetime,
@Original_dateAddeddatetime,
@IsNull_lastLogindatetime,
@Original_lastLogindatetime,
@IsNull_status bit,
@Original_status bit,
@IsNull_pwdExpired bit,
@Original_pwdExpired bit,
@IsNull_lastPasswordChangedatetime,
@Original_lastPasswordChangedatetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUsers] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordChange] = @lastPasswordChange WHERE
(([userID] = @Original_userID) AND ([userName] = @Original_userName) AND
((@IsNull_lastName = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastName)) AND ((@IsNull_firstName = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_firstName)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email)) AND ((@IsNull_password = 1 AND [password] IS NULL) OR
([password] = @Original_password)) AND ((@IsNull_dateAdded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateAdded)) AND
((@IsNull_lastLogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastLogin)) AND ((@IsNull_status = 1 AND [status] IS NULL) OR
([status] = @Original_status)) AND ((@IsNull_pwdExpired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdExpired)) AND
((@IsNull_lastPasswordChange = 1 AND [lastPasswordChange] IS NULL) OR
([lastPasswordChange] = @Original_lastPasswordChange)));
<!--end SPROC-->

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

1 public static bool SetPassword(string userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTableAdapter taUsers = new
SecurityUsersTableAdapter();
6
7 SecurityUsers.SecurityUsersDataTable dtUsers =
taUsers.GetByUserName(userName);
8
9 if (dtUsers.Rows.Count != 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.Designer.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_columnname" 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_columnname" 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 <[e-mail]>
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:

"Failedtoconvertparametervaluefrom aInt32toDateTime"

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 userIDparameterand 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_SecurityUsers_Update' expectsparameter'@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_SecurityUsers_Update
(
@userName varchar(15),
@lastName varchar(50),
@firstName varchar(50),
@email varchar(50),
@password varchar(50),
@dateAddeddatetime,
@lastLogindatetime,
@status bit,
@pwdExpired bit,
@lastPasswordChangedatetime,
@Original_userID int,
@Original_userName varchar(15),
@IsNull_lastName varchar(50),
@Original_lastName varchar(50),
@IsNull_firstName varchar(50),
@Original_firstName varchar(50),
@IsNull_email varchar(50),
@Original_email varchar(50),
@IsNull_password varchar(50),
@Original_password varchar(50),
@IsNull_dateAddeddatetime,
@Original_dateAddeddatetime,
@IsNull_lastLogindatetime,
@Original_lastLogindatetime,
@IsNull_status bit,
@Original_status bit,
@IsNull_pwdExpired bit,
@Original_pwdExpired bit,
@IsNull_lastPasswordChangedatetime,
@Original_lastPasswordChangedatetime,
@userID int
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[tb_SecurityUsers] SET [userName] = @userName, [lastName] =
@lastName, [firstName] = @firstName, = @email, [password] = @password,
[dateAdded] = @dateAdded, [lastLogin] = @lastLogin, [status] = @status,
[pwdExpired] = @pwdExpired, [lastPasswordChange] = @lastPasswordChange WHERE
(([userID] = @Original_userID) AND ([userName] = @Original_userName) AND
((@IsNull_lastName = 1 AND [lastName] IS NULL) OR ([lastName] =
@Original_lastName)) AND ((@IsNull_firstName = 1 AND [firstName] IS NULL) OR
([firstName] = @Original_firstName)) AND ((@IsNull_email = 1 AND IS NULL) OR
( = @Original_email)) AND ((@IsNull_password = 1 AND [password] IS NULL) OR
([password] = @Original_password)) AND ((@IsNull_dateAdded = 1 AND
[dateAdded] IS NULL) OR ([dateAdded] = @Original_dateAdded)) AND
((@IsNull_lastLogin = 1 AND [lastLogin] IS NULL) OR ([lastLogin] =
@Original_lastLogin)) AND ((@IsNull_status = 1 AND [status] IS NULL) OR
([status] = @Original_status)) AND ((@IsNull_pwdExpired = 1 AND [pwdExpired]
IS NULL) OR ([pwdExpired] = @Original_pwdExpired)) AND
((@IsNull_lastPasswordChange = 1 AND [lastPasswordChange] IS NULL) OR
([lastPasswordChange] = @Original_lastPasswordChange)));
<!--end SPROC-->

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

1 public static bool SetPassword(string userName, string hashedPassword)
2 {
3 bool success = false;
4
5 SecurityUsersTableAdapter taUsers = new
SecurityUsersTableAdapter();
6
7 SecurityUsers.SecurityUsersDataTable dtUsers =
taUsers.GetByUserName(userName);
8
9 if (dtUsers.Rows.Count != 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.Designer.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_columnname" 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_columnname" 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.