Trying to get table to update with table adapter or stored prodecure

Question

User-1767698477 posted

I thought this would work, but it is giving Nothing as a result for processoruswername.  I'm trying to lookup in the membership table if an email address already exists. I don't know of any way to do this without first getting the username by email and then plugging the username into Membership.GetUser(processorusername). Why doesn't this Membership.GetUserNameByEmail(txtProcessoremail.Text return the username of the email address? The email address exists in the db.

   If txtProcessoremail.Text <> "" Then 'nothing to do since it is blank
            If txtProcessoremail.Text <> Session("processoremail") Then 'only if what is in the text box is different from the session
                Dim processorusername As String = Membership.GetUserNameByEmail(txtProcessoremail.Text)
                Dim processoruser As MembershipUser = Membership.GetUser(processorusername)
                If processorusername <> "" Then 'continuing means we found a username.
                    'update the LOs profile with the processor's id
                    Dim updatequery1 As String = "UPDATE [UserProfiles] SET [ProcessorID=@ProcessorID] Where UserId=@UserID"
                    Dim cmdupdate1 As New SqlCommand(updatequery1, cn)
                    cmdupdate1.Parameters.Add(New SqlParameter("ProcessorID", processoruser.ProviderUserKey))
                    cmdupdate1.Parameters.Add(New SqlParameter("UserID", Session("UserID")))
                    cmdupdate1.Connection = cn
                    cn.Open()
                    cmdupdate.ExecuteNonQuery()
                    cn.Close()
                    'send a different email
                    'Emailprocessor(False)
                Else
                    'otherwise they are a new processor not yet registered.
                    'Emailprocessor(True)
                End If
            End If
        End If

Answer 1

User-943250815 posted

Membership.GetUserNameByMail, works as expected, be sure you searching for an existing mail, when not found it return Nothing.

Answer 2

User-1330468790 posted

Hi sking,

 

It looks like the membership cannot find an username which matches the input email address so that it will give you a null as you could see the details in the document: Return value of Membership.GetUserNameByEmail(String) Method

 

Reason:

One of the reasons could be that you don't configure the membership provider correctly so that it will connect to a wrong database. 

Could you please share the configuration for membership provider and more relevant details?  

I assume you using SqlMembershipProvider so that I check the source code and try to provide you with a direction to target the problem.

You will see that the method "GetUserNameByEmail" will use a store procedure to execute the username retrieving.

 SqlCommand    cmd         = new SqlCommand("dbo.aspnet_Membership_GetUserByEmail", holder.Connection);
 cmd.CommandType = CommandType.StoredProcedure;

Therefore, I think you could check the SP "dbo.aspnet_Membership_GetUserByEmail" from your side firstly and try to see if there is anything wrong.

If the email address does not exist in the database which is targeted by the SP, you might need to consider either modify the configuration or change the membership design.

 

Hope this can help you.

Best regards,

Sean

Answer 3

User-1767698477 posted

In debugging this code, I am getting the values now as I go through line by line, but the table is not being updated with the guid.  The ProcessorID is a guid as well as the UserID. I was missing the @ sign in front of the parameter, but after fixing that, it still isn't working. I can see the correct guids when I hover over the provideruserkey & userid. 

 If txtProcessoremail.Text <> "" Then 'nothing to do since it is blank
            If txtProcessoremail.Text <> Session("processoremail") Then 'only if what is in the text box is different from the session
                Dim processorusername As String = Membership.GetUserNameByEmail(txtProcessoremail.Text)
                Dim processoruser As MembershipUser = Membership.GetUser(processorusername)
                If processorusername <> "" Then 'continuing means we found a username.
                    'update the LOs profile with the processor's id
                    Dim updatequery1 As String = "UPDATE [UserProfiles] SET [ProcessorID] = @ProcessorID WHERE (([UserID]=@UserID))"
                    Dim cmdupdate1 As New SqlCommand(updatequery1, cn)
                    Dim Guid As Guid = processoruser.ProviderUserKey
                    cmdupdate1.Parameters.Add(New SqlParameter("@ProcessorID", Guid))
                    cmdupdate1.Parameters.Add(New SqlParameter("@UserID", Session("UserID")))
                    cmdupdate1.Connection = cn
                    cn.Open()
                    cmdupdate.ExecuteNonQuery()
                    cn.Close()
                    'send a different email
                    'Emailprocessor(False)
                Else
                    'otherwise they are a new processor not yet registered.
                    'Emailprocessor(True)
                End If
            End If
        End If
        Session.Abandon()
        Session.Clear()
        lblresult.Text = "Your profile was updated. Please login again. "

I created a tableadapter and even a stored procedure which do the same thing. Update a guid in a row in the UserProfiles table.  Both of them work as I have tested them. I don't understand. When I test them, when I execute the stored procedure I just paste in the 2 guids from the windows clipboard. That is string data right?  They are 2 32 digit long guids, but it's a string being pasted into the parameter fields.  I click on execute and it does its job. Why can't I call either one of these in the code behind and get it to work?

                    Dim ProcessorGuid As Guid = processoruser.ProviderUserKey
                    Dim OriginatorGuid As Guid = Session("UserID")
                    Dim tblUserProfilesadapeter As New UserProfilesTableAdapter
                    Dim tbluserProfiles As New DataSet1.UserProfilesDataTable
                    tblUserProfilesadapeter.UpdateProcessorIDinUserProfiles(ProcessorGuid, OriginatorGuid)
                    'update the LOs profile with the processor's id
                    ' Dim updatequery1 As String = "UPDATE [UserProfiles] SET [ProcessorID] = @ProcessorID WHERE (([UserID]=@UserID))"
                    ' Dim cmdupdate1 As New SqlCommand(updatequery1, cn)

                    ' cmdupdate1.Parameters.Add(New SqlParameter("@ProcessorID", Guid))
                    'cmdupdate1.Parameters.Add(New SqlParameter("@UserID", Session("UserID")))
                    'cmdupdate1.CommandType = System.Data.CommandType.Text
                    'cmdupdate1.Connection = cn
                    'cn.Open()
                    'cmdupdate.ExecuteNonQuery()
                    'cn.Close()
                    'send a different email
                    'Emailprocessor(False)
                Else
                    'otherwise they are a new processor not yet registered.
                    'Emailprocessor(True)
                End If
            End If

UPDATE UserProfiles
SET ProcessorID = @ProcessorID
WHERE (UserID = @UserID)

If txtProcessoremail.Text <> "" Then 'nothing to do since it is blank
If txtProcessoremail.Text <> Session("processoremail") Then 'only if what is in the text box is different from the session
Dim processorusername As String = Membership.GetUserNameByEmail(txtProcessoremail.Text)
Dim processoruser As MembershipUser = Membership.GetUser(processorusername)
If processorusername <> "" Then 'continuing means we found a username.
Dim ProcessorGuid As Guid = processoruser.ProviderUserKey
Dim OriginatorGuid As Guid = Session("UserID")  <--specificied cast is invalid
Dim tblUserProfilesadapeter As New UserProfilesTableAdapter
tblUserProfilesadapeter.UpdateProcessorIDinUserProfiles(ProcessorGuid, OriginatorGuid)

Answer 4

User-943250815 posted

Dim OriginatorGuid As Guid = Session("UserID")  <--specificied cast is invalid

The value of Session variable is not type of Guid, as soon you store correct type it should work.

Answer 5

User-1767698477 posted

At login time I store Session variables. I read that session variables can be of any type. So my question is when it stores the variables, why doesn't it recognize the type? The UserID is a uniqueIdentifier type so why isn't this a guid when I retrieve it?  Session("userID") How do I convert it to a guid so I can use it as a parameter in the tableadapter?

Answer 6

User-943250815 posted

It recognize the type. But the point is, what type is stored in Session variable?
Your session variable can have any value but it is not a GUID type, right now it can be a string, integer or any other type. You have to review where you set its value.
In code bellow, I´m storing in session ProviderKey as guid object and as string, then try to get back both as guid, but got an Cast Exception when try to get back a string to guid

Dim zUser = Membership.GetUser
    Dim zProviderUserKey = zUser.ProviderUserKey
    Session("UserIDok") = zProviderUserKey            ' Store value as guid object
    Session("UserIDfail") = zProviderUserKey.ToString ' Store value as string
    Dim zOriginatorGuidobj As Guid
    Dim zOriginatorGuidstr As Guid
    Dim zError As String
    Try
      zOriginatorGuidobj = Session("UserIDok")        ' Session return guid object
    Catch ex As Exception
      zError = "Session UserIDok : " & ex.Message
    End Try
    Try
      zOriginatorGuidstr = Session("UserIDfail")      ' Session return a string and causes an exception
    Catch ex As Exception
      zError = "Session UserIDfail : " & ex.Message   ' Specified cast is not valid
    End Try

Answer 7

User-1767698477 posted

Yes thanks. I dug into the login page and I found that when I was storing the session vars at login time, I had set the type parameter of the UserID to a varchar I believe. I have changed that to a uniqueidentifier and now when I go on another page and request Session("UserId") it is a guid. So I don't have to fumble around anymore trying to convert it to a guid! Also I was passing it into the sub as an integer and I changed it to a guid. The table is getting the update now. :P

Protected Function Gettsessionvars(ByVal userid As Guid, ByVal username As String, ByVal password As String, ByVal email As String) As Boolean
Session("Username") = username
Session("Email") = email
Session("Userid") = userid
Session("Password") = password

Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)
Using command As New SqlCommand("GetUserProfileDetails", con)
command.Parameters.Add(New SqlParameter("@UserID", SqlDbType.UniqueIdentifier)).Value = userid
command.CommandType = CommandType.StoredProcedure