locked
How to retrieve data from the table for specific user RRS feed

  • Question

  • User1557098506 posted

    Hi,

    I have created a quote form on which login member has to fill the form and send to me which is working fine.

    I wanted to know, how to show that specific result to the user who has submit the form in another page.

    I am using membership and the submit form is custom form.

    Currently this is the code:

     Guid id = Guid.NewGuid();
    
                    SqlConnection con = new SqlConnection(strConnString);
    
                    string strQuery = "insert into test (Id, Quote_Name, PO)" +
    
                        " values ( @Id, @Quote_Name, @PO)";
    
                    SqlCommand cmd = new SqlCommand(strQuery);
    
                    cmd.Parameters.AddWithValue("@Id", id.ToString());
                    cmd.Parameters.AddWithValue("@Quote_Name", QuoteName.Text);

    But the id is that is create is random id, Is there a way I can add something like membership id so that I can retrieve the data using the membership id for this form?

    Thanks

    Tuesday, July 8, 2014 8:37 AM

All replies

  • User1728944201 posted

    I'm not sure if I fully understand what you are asking but you can always store that GUID in a session variable and it will be available on any page within the site as long as the session is active.

    You should also have an inserted_date field in your table.  Then you can query your table and order by inserted_date desc  in order to get the latest posted quote.

    Tuesday, July 8, 2014 10:27 AM
  • User475983607 posted

    But the id is that is create is random id, Is there a way I can add something like membership id so that I can retrieve the data using the membership id for this form?

    Sure, use the membership username in place of the GUID.

    Tuesday, July 8, 2014 2:00 PM
  • User1557098506 posted

    I'm not sure if I fully understand what you are asking but you can always store that GUID in a session variable and it will be available on any page within the site as long as the session is active.

    You should also have an inserted_date field in your table.  Then you can query your table and order by inserted_date desc  in order to get the latest posted quote.

    Can you please give me an example? I have created a form, I login and submit the form , now I want that submit form information that I have submit on another page.

    I have these in table : id, quote name,  etc

    I am trying to have it session by id but it's not showing anything.

    I am using membership so I was thinking if there is a way to have this by membership username or it's userid ?

    Any example of coding would be helpful thanks.

    Tuesday, July 8, 2014 4:59 PM
  • User475983607 posted

    I am using membership so I was thinking if there is a way to have this by membership username or it's userid ?

    Any example of coding would be helpful thanks.

    There's plenty of information on MSDN.

    Membership.GetUser()

    Tuesday, July 8, 2014 5:42 PM
  • User1557098506 posted

    No It didn't work for me yet.

    Here's the full code

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    using System.IO;
    using System.Net.Mail;
    using System.Net;
    
    public partial class asp : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string UserID = User.Identity.Name;
    
        }
    
        protected void Upload_Click(object sender, EventArgs e)
        {
    
           
                    String strConnString = System.Configuration.ConfigurationManager
                        .ConnectionStrings["userConnectionString"].ConnectionString;
    
                    Guid id = Guid.NewGuid();
    
                    SqlConnection con = new SqlConnection(strConnString);
    
                    string strQuery = "insert into Quotes (Id, Quote_Name, PO)" +
    
                        " values ( @Id, @Quote_Name, @PO)";
    
                    SqlCommand cmd = new SqlCommand(strQuery);
    
                    cmd.Parameters.AddWithValue("@Id", id.ToString());
                    cmd.Parameters.AddWithValue("@Quote_Name", QuoteName.Text);
                    cmd.Parameters.AddWithValue("@PO", PO.Text);
                    
    
                    cmd.CommandType = CommandType.Text;
    
                    cmd.Connection = con;
    
    
    
    
                    try
                    {
    
                        con.Open();
    
                        cmd.ExecuteNonQuery();
    
                    }
    
                    catch (Exception ex)
                    {
    
                        Response.Write(ex.Message);
    
                    }
    
                    finally
                    {
    
                        con.Close();
    
                        
                        con.Dispose();
                        Message.Text = "Your Quote has been sent";
                    }
    
                }
    
            
            
           
    
          
    
    }
      

    Here is the page :

          <table class="style1">
                <tr>
                    <td class="style4" 
                        style="font-family: Verdana; font-size: small; text-align: right; color: #000000">
                        Quote Name:</td>
                    <td>
                        <asp:TextBox ID="QuoteName" runat="server"></asp:TextBox>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                            ControlToValidate="QuoteName" ErrorMessage="*" ForeColor="#CC0000"></asp:RequiredFieldValidator>
                    </td>
                </tr>
                <tr>
                    <td class="style4" 
                        style="font-family: Verdana; font-size: small; text-align: right; color: #000000">
                        PO#:</td>
                    <td>
                        <asp:TextBox ID="PO" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td class="style2" colspan="2" 
                        style="font-family: Verdana; font-size: small; text-align: center; color: #000000">
                        <b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<asp:Button ID="Upload" runat="server" 
                            onclick="Upload_Click" onclientclick="return validate();" 
                            style="font-family: Verdana; font-size: small" Text="Submit" />
                        <span style="font-family: Verdana; font-size: small; color: #000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><input 
                            id="Reset1" class="style3" style="font-family: Verdana; font-size: small" 
                            type="reset" value="Reset" /><span 
                            style="font-family: Verdana; font-size: small; color: #000000">&nbsp;&nbsp;<br />
                        &nbsp;<asp:Label ID="Message" runat="server" style="text-align: left"></asp:Label>
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; </span></b>
                    </td>
                </tr>
            </table>
        
        </div>
          
      </div> 
       <div id="head_right">
             	
             <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                 ConnectionString="<%$ ConnectionStrings:userConnectionString %>" 
                 SelectCommand="SELECT * FROM 
    WHERE ([Id] = @Id)"> <SelectParameters> <asp:SessionParameter Name="Id" SessionField="Session" Type="Object" /> </SelectParameters> </asp:SqlDataSource> </div> </div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" /> <asp:BoundField DataField="Quote_Name" HeaderText="Quote_Name" SortExpression="Quote_Name" /> <asp:BoundField DataField="PO" HeaderText="PO" SortExpression="PO" /> </Columns> </asp:GridView> </form>

    When I select * it's shows all the data but nothing shows for id or using string UserID = User.Identity.Name;

    Wednesday, July 9, 2014 9:29 AM
  • User475983607 posted

    When I select * it's shows all the data but nothing shows for id or using string UserID = User.Identity.Name;

    The code is placing a new GUID in the Quotes.ID field not the membership UserId.  

    Take a moment to think about the design.  If the intent is to find quotes by UserId, then the UserId must be included in the Quote record.  Another option is to create a cross reference table which contains both the UserId and Quote Guid.  

    First, design the table scheme to support the business logic.  Then create the C# code.

    Wednesday, July 9, 2014 9:52 AM
  • User1557098506 posted

    new2world

    When I select * it's shows all the data but nothing shows for id or using string UserID = User.Identity.Name;

    The code is placing a new GUID in the Quotes.ID field not the membership UserId.  

    Take a moment to think about the design.  If the intent is to find quotes by UserId, then the UserId must be included in the Quote record.  Another option is to create a cross reference table which contains both the UserId and Quote Guid.  

    First, design the table scheme to support the business logic.  Then create the C# code.

    Here's what I am trying to achieve, kindly suggest me what is the best way to this.

    I have already made login system using membership database.

    I have created another table in which I have assign id, quotename

    Now what I want is, when someone go to mywebsite and login in the register system and fill the customize form ( Quote name).

    I want that information to be shown to that user that he has submitted form with the this quote name.

    Usually it's should work, in past I have done it but that was using the session username and that was custom build membership.

    Since I am using build in membership. I don't know how to do this, I tried to create relationship between custom table id with the asp_net userid but it's giving me error.

    So what the way around for this? any example?

    Wednesday, July 9, 2014 11:36 AM
  • User475983607 posted

    So what the way around for this? any example?

    Session and a custom built membership are irreverent to the current situation.   The way around this is good design.

    When the quote is created also insert the membership UserId.  This might require a new column in the Quotes table to hold the UserId or a change to the Quotes.Id datatype.  

    Wednesday, July 9, 2014 3:27 PM
  • User1557098506 posted

    mgebhard

    new2world

    So what the way around for this? any example?

    Session and a custom built membership are irreverent to the current situation.   The way around this is good design.

    When the quote is created also insert the membership UserId.  This might require a new column in the Quotes table to hold the UserId or a change to the Quotes.Id datatype.  

    Okay, I have tried that may be there is somewhere I am doing wrong in the coding,

    I am getting this error now : Violation of PRIMARY KEY constraint 'PK_Quotes_1'. Cannot insert duplicate key in object 'dbo.Quotes'. The statement has been terminated.

    This is the cs :

    protected void Upload_Click(object sender, EventArgs e)
        {
    
            String strConnString = System.Configuration.ConfigurationManager
    
                               .ConnectionStrings["userConnectionString"].ConnectionString;
    
           
    
            SqlConnection con = new SqlConnection(strConnString);
    
            string strQuery = "insert into Quotes (userName, Id, Quote_Name)" +
    
                " values ( @UserName, @Id, @Quote_Name)";
    
            SqlCommand cmd = new SqlCommand(strQuery);
    
    
     cmd.Parameters.AddWithValue("@Username", HttpContext.Current.User.Identity.Name);
    cmd.Parameters.AddWithValue("@Id", Membership.GetUser().ProviderUserKey); cmd.Parameters.AddWithValue("@Quote_Name", QuoteName.Text); cmd.CommandType = CommandType.Text; cmd.Connection = con; try { con.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { Response.Write(ex.Message); } finally { con.Close(); con.Dispose(); Message.Text = "Your Quote has been sent"; } }

    Edited : What I have found that It's work fine for first try like it submits the userid of membership in the table then, when I try to submit again it's give me an error because that id is already present(submitted before) in the quote table.

    So, what I wan to know here is, how can I allow table to accept duplicate information?

    Edited: Okay sorted out, I just un select the primary key in quote table for ID and it's entering now.

    Still having error for retrieving the table but will create new thread for that thanks

    Thursday, July 10, 2014 6:14 AM