locked
Trying to get table to update with table adapter or stored prodecure RRS feed

  • 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

    Wednesday, May 13, 2020 10:34 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 14, 2020 5:35 PM

All replies

  • User-943250815 posted

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

    Thursday, May 14, 2020 2:09 AM
  • 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 documentReturn 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

    Thursday, May 14, 2020 3:16 AM
  • 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)

    Thursday, May 14, 2020 5:38 AM
  • 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.

    Thursday, May 14, 2020 2:17 PM
  • 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?

    Thursday, May 14, 2020 4:47 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 14, 2020 5:35 PM
  • 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

    Thursday, May 14, 2020 10:01 PM