Compare User.Identity.Name with inserting value from other DataSource

illuminati · December 11th, 2009, 11:41 AM

I have the ASPNETDB.MDF (with users and roles and so on) and another db i made.
my db is like this:

Code:

24 CREATE TABLE Sales
25 (
26 salID INT PRIMARY KEY IDENTITY(1,1),
27 salResID INT NOT NULL,
28 salCD INT NOT NULL,
29 salSum INT NOT NULL,
30 salDate datetime NOT NULL,
31 CONSTRAINT FKsalResID FOREIGN KEY (salResID) REFERENCES Resellers(resID)
32 )

I want to insert into this table as such i have:

Code:

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" 
    AutoGenerateRows="False" DataKeyNames="salID" DataSourceID="dvDS" 
    DefaultMode="Insert">
    <Fields>
      <asp:BoundField DataField="salID" HeaderText="salID" InsertVisible="False" ReadOnly="True" SortExpression="salID" />
      
      <asp:TemplateField HeaderText="Forhandler:" SortExpression="salResID">
        <InsertItemTemplate>
          <asp:TextBox ID="tbxUserName" runat="server" ReadOnly="true" Text='<%# Bind("salResID") %>' oninit="tbxUserName_Init"></asp:TextBox>
        
          <asp:SqlDataSource ID="resDS" runat="server" 
            ConnectionString="<%$ ConnectionStrings:BlackestCD %>" 
            ></asp:SqlDataSource><%--SelectCommand="SELECT [resID], [resName] FROM [Resellers]"--%>
            
        </InsertItemTemplate>
      </asp:TemplateField>
      
      <asp:TemplateField HeaderText="Ant solgte CD:" SortExpression="salCD">
        <InsertItemTemplate>
          <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("salCD") %>'></asp:TextBox>
        </InsertItemTemplate>
      </asp:TemplateField>
      
      <asp:TemplateField HeaderText="Salgs sum:" SortExpression="salSum">
        <InsertItemTemplate>
          <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("salSum") %>'></asp:TextBox>
        </InsertItemTemplate>
      </asp:TemplateField>
      
      <%--<asp:TemplateField HeaderText="Rapport dato:" SortExpression="salDate">
        <InsertItemTemplate>
          <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("salDate") %>'></asp:TextBox>
        </InsertItemTemplate>
      </asp:TemplateField>--%>
      
      <asp:CommandField ShowInsertButton="True" CancelText="Avbryt" 
        InsertText="Lever rapport!" />
    </Fields>
  </asp:DetailsView>
  <asp:SqlDataSource ID="dvDS" runat="server" 
    ConnectionString="<%$ ConnectionStrings:BlackestCD %>" 
    InsertCommand="INSERT INTO [Sales] ([salResID], [salCD], [salSum], [salDate]) VALUES (@salResID, @salCD, @salSum, getdate())">
    <InsertParameters>
      <asp:Parameter Name="salResID" Type="Int32" />
      <asp:Parameter Name="salCD" Type="Int32" />
      <asp:Parameter Name="salSum" Type="Int32" />
      <asp:Parameter Name="salDate" Type="DateTime" />
    </InsertParameters>
  </asp:SqlDataSource>

In codebehind i have the following:

Code:

Protected Sub tbxUserName_Init(ByVal sender As Object, ByVal e As System.EventArgs)
        CType(DetailsView1.Controls(0).FindControl("resDS"), SqlDataSource).SelectCommand() = "SELECT resID FROM Resellers WHERE resName = '" & User.Identity.Name & "'"
        'Dim value As String
        'CType(DetailsView1.Controls(0).FindControl("tbxUserName"), TextBox).Text = DataBinder.Eval(TextBox

    End Sub

What i want to do is when choose insert with detailsview the tbxUserName should insert the value from datasource resDS where resName equals User.Identity.Name as i have shown in VB code select command?
So i guess im wondering how to in vb code get the right value from resDS inserted in dvDS?
I dont even have to see the username or salResID in the detailsview - could it be hidden - as the logged in person only can report sales for him self?

Code example would be greatly appreciated

?

illuminati

illuminati · December 11th, 2009, 06:55 PM

What do you know i managed to solve this with this code:

Code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'set resDS SelectCommand to choose resID like UserName:
        resDS.SelectCommand() = "SELECT resID FROM Resellers WHERE resName = '" & User.Identity.Name & "'"

        'henter DataView fra SqlDataSource:
        Dim dview As DataView = CType(resDS.Select(DataSourceSelectArguments.Empty), DataView)
        Dim str1 As String = String.Empty
        For Each drow As DataRow In dview.Table.Rows
            str1 &= drow("resID").ToString()
        Next
        'skriv til textbox:
        CType(DetailsView1.Controls(0).FindControl("tbxUserName"), TextBox).Text = str1
        
    End Sub