Getting And Inserting Logged In Username To Related Table
Nov 18, 2005
Hi,I've got VS 05 web dev express installed and i'm trying the walkthroughs for login admin. I've succeeded and noticed the tables VS05 produces in the database ASPNETDB.MDF. I've created a new table "Customers" also with a UserID and also configured it as "UniqueIdentifyer" as VS05 has done in the table aspnet_users. The Customers table has two other fields: CustID (autoint) and CustomerName. Now i'm setting up a detailsview control that should insert a CustomerName but I also want it to insert the current logged in userID to the Customers.UserID field so that the aspnet_users and customers tables can be related. My question is how would my Sql insert statement look like to incorporate the parameter of the current logged in UserID and insert it into the Customers.UserID field?thanks.
View 5 Replies
ADVERTISEMENT
Apr 7, 2006
Hi there
I have a book and I'm learning now, but I really want to get this working as soon as possible as it will convince the boss to give me more time to study as it will blow him away. (I started this 2 hours ago and it's all working except for this bit). He's used to me building apps in weeks not hours.
I have an online application form that inserts data to a sql 2k5 db and then displays it in another administrators web form. I have used login views which work great with Active Directory (Tokens) to display correct information. (It's not a highly secure app so relax).
All the simple stuff works a treat, but I am trying to write the Domain/Username to the database with an update command.
I can display this information on the form using Page.User.Identity.Name, but how can I add it to the update command to insert it to the DB?
Here is the code for the entire page, and code behind at bottom. I want the Domain/Username to be inserted into the @ByUSername field.
Many thanks
=======aspx code====================
<%@ Page Language="VB" Debug="true" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>NURF</title>
</head>
<body bgcolor="#f3f5fa">
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlWinUserRequests" runat="server" ConnectionString="<%$ ConnectionStrings:IM&TSystems on BOHWEB1 %>"
InsertCommand="INSERT INTO WinUsers (ByFullName, ByPosition, ByInternalTel, ByEmail, NewTitle, NewForename, NewSurname, NewPositionHeld, NewLocation, AccDateOfRequest, AccDateRequired, AccAccountToCopy, NewInternalTel, ByUsername) VALUES (@ByFullName,@ByPosition,@ByInternalTel,@ByEmail,@NewTitle,@NewForename,@NewSurname,@NewPositionHeld,@NewLocation, { fn NOW() },@AccDateRequired,@AccAccountToCopy,@NewInternalTel, <%Page.USER.IDENTITY.Name%>)"
ProviderName="<%$ ConnectionStrings:IM&TSystems on BOHWEB1.ProviderName %>" SelectCommand="SELECT ID, ByFullName, ByPosition, ByInternalTel, ByEmail, ByUsername, NewTitle, NewForename, NewSurname, NewPositionHeld, NewLocation, AccDateOfRequest, AccDateRequired, AccAccountToCopy, NewInternalTel FROM WinUsers">
<InsertParameters>
<asp:Parameter Name="ByFullName" />
<asp:Parameter Name="ByPosition" />
<asp:Parameter Name="ByInternalTel" />
<asp:Parameter Name="ByEmail" />
<asp:Parameter Name="NewTitle" />
<asp:Parameter Name="NewForename" />
<asp:Parameter Name="NewSurname" />
<asp:Parameter Name="NewPositionHeld" />
<asp:Parameter Name="NewLocation" />
<asp:Parameter Name="AccDateRequired" Type="DateTime" />
<asp:Parameter Name="AccAccountToCopy" />
<asp:Parameter Name="NewInternalTel" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlLocations" runat="server" ConnectionString="<%$ ConnectionStrings:IM&TSystems on BOHWEB1 %>"
SelectCommand="SELECT [Location] FROM [Locations] ORDER BY [Location]"></asp:SqlDataSource>
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlWinUserRequests">
<EditItemTemplate>
<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
Font-Bold="True" Font-Names="Verdana" Font-Size="14pt" ForeColor="#000000" Text="Request a new windows user account"></asp:LinkButton>
</EditItemTemplate>
<EmptyDataTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="New"
Font-Bold="True" Font-Names="Verdana" Font-Size="14pt" ForeColor="#000000" Text="Request a new windows user account"></asp:LinkButton>
</EmptyDataTemplate>
<InsertItemTemplate>
<span style="font-size: 14pt; font-family: Verdana"><strong>Request a new windows user
account</strong></span><br />
<table style="font-size: small; font-family: Verdana" width="750">
<tr>
<td colspan="3" style="height: 21px">
</td>
<td style="height: 21px; width: 10px;">
</td>
<td style="height: 21px">
</td>
</tr>
<tr>
<td colspan="3">
<strong><span style="font-size: 12pt; color: gray">Your Details (Line Managers Only)</span></strong></td>
<td style="width: 10px">
</td>
<td style="height: 21px">
</td>
</tr>
<tr>
<td width="150" style="text-align: right">
Full Name</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="ByFullNameTextBox"
ErrorMessage="Please supply your name">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:TextBox ID="ByFullNameTextBox" runat="server" Text='<%# Bind("ByFullName") %>'
Width="235px"></asp:TextBox></td>
<td style="width: 10px">
</td>
<td rowspan="14" valign="top">
<asp:ValidationSummary ID="ValidationSummary1" runat="server" Font-Size="12pt" />
</td>
</tr>
<tr>
<td width="150" style="text-align: right">
Position</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="ByPositionTextBox"
ErrorMessage="Please supply your position">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:TextBox ID="ByPositionTextBox" runat="server" Text='<%# Bind("ByPosition") %>'
Width="235px"></asp:TextBox></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td width="150" style="text-align: right">
Email</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="ByEmailTextBox"
ErrorMessage="Please supply your email address">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:TextBox ID="ByEmailTextBox" runat="server" Text='<%# Bind("ByEmail") %>' Width="235px"></asp:TextBox></td>
<td style="width: 10px">
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="ByEmailTextBox"
ErrorMessage="Please enter a valid HSSD email address" ValidationExpression="w+([-+.']w+)*@w+([-.]w+)*.gov.gg">*</asp:RegularExpressionValidator></td>
</tr>
<tr>
<td width="150" style="text-align: right; height: 26px;">
Internal Tel</td>
<td style="height: 26px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="ByInternalTelTextBox"
ErrorMessage="Please supply your internal telephone number">*</asp:RequiredFieldValidator></td>
<td style="width: 244px; height: 26px;">
<asp:TextBox ID="ByInternalTelTextBox" runat="server" Text='<%# Bind("ByInternalTel") %>'
Width="60px"></asp:TextBox></td>
<td style="width: 10px; height: 26px;">
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ControlToValidate="ByInternalTelTextBox"
ErrorMessage="Please enter a valid HSSD internal telephone" ValidationExpression="d{4}">*</asp:RegularExpressionValidator></td>
</tr>
<tr>
<td width="150" style="height: 26px; text-align: right">
Username</td>
<td style="height: 26px">
</td>
<td style="width: 244px; height: 26px">
<asp:Label ID="Label1" runat="server" Text="<%# page.user.identity.name %>"></asp:Label></td>
<td style="width: 10px; height: 26px">
</td>
</tr>
<tr>
<td colspan="3" style="height: 20px">
</td>
<td style="width: 10px; height: 20px;">
</td>
</tr>
<tr>
<td colspan="3">
<span style="font-size: 12pt; color: gray"><strong>New Account Details</strong></span></td>
<td style="width: 10px;">
</td>
</tr>
<tr>
<td style="text-align: right" width="150">
Title</td>
<td style="width: 13px; text-align: right">
</td>
<td style="width: 244px">
<asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Bind("NewTitle") %>' Width="60px">
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Miss"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Sr"></asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td style="text-align: right" width="150">
Forename</td>
<td style="width: 13px; text-align: right">
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="NewForenameTextBox"
ErrorMessage="Please supply new user's forename">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:TextBox ID="NewForenameTextBox" runat="server" Text='<%# Bind("NewForename") %>'
Width="235px"></asp:TextBox></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td style="text-align: right" width="150">
Surname</td>
<td style="width: 13px; text-align: right">
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="NewSurnameTextBox"
ErrorMessage="Please supply new user's surname">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:TextBox ID="NewSurnameTextBox" runat="server" Text='<%# Bind("NewSurname") %>'
Width="235px"></asp:TextBox></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td style="text-align: right" width="150">
Position</td>
<td style="width: 13px; text-align: right">
<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ControlToValidate="NewPositionHeldTextBox"
ErrorMessage="Please supply new user's position">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:TextBox ID="NewPositionHeldTextBox" runat="server" Text='<%# Bind("NewPositionHeld") %>'
Width="235px"></asp:TextBox></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td style="text-align: right" width="150">
Location</td>
<td style="width: 13px; text-align: right">
<asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" ControlToValidate="DropDownList2"
ErrorMessage="Please supply new user's location">*</asp:RequiredFieldValidator></td>
<td style="width: 244px">
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlLocations" DataTextField="Location"
DataValueField="Location" SelectedValue='<%# Bind("NewLocation") %>' Width="241px">
</asp:DropDownList></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td style="text-align: right" width="150">
Date
Required</td>
<td style="width: 13px; text-align: right">
</td>
<td style="width: 244px">
<asp:TextBox ID="AccDateRequiredTextBox" runat="server" Text='<%# Bind("AccDateRequired", "{0:d}") %>'
Width="60px"></asp:TextBox></td>
<td style="width: 10px">
</td>
</tr>
<tr>
<td style="text-align: right; height: 26px;" width="150">
Similar Account</td>
<td style="width: 13px; text-align: right; height: 26px;">
<asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server" ControlToValidate="AccAccountToCopyTextBox"
ErrorMessage="Please supply the Name of a user with the same account priveledges">*</asp:RequiredFieldValidator></td>
<td style="width: 244px; height: 26px;">
<asp:TextBox ID="AccAccountToCopyTextBox" runat="server" Text='<%# Bind("AccAccountToCopy") %>'
Width="235px"></asp:TextBox></td>
<td style="width: 10px; height: 26px;">
</td>
</tr>
<tr postbackurl="ThankYou.htm">
<td width="150" style="text-align: right">
Contact Tel</td>
<td style="width: 13px">
</td>
<td style="width: 244px; text-align: left">
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("NewInternalTel") %>' Width="100px"></asp:TextBox></td>
<td style="width: 10px">
</td>
<td>
</td>
</tr>
<tr>
<td width="150" style="height: 26px">
</td>
<td style="width: 13px; height: 26px;">
</td>
<td style="width: 244px; text-align: right; height: 26px;">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click"
Text="Send Request" />
</td>
<td style="width: 10px; height: 26px;">
</td>
<td style="height: 26px">
</td>
</tr>
</table>
</InsertItemTemplate>
<ItemTemplate>
<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
Font-Bold="True" Font-Names="Verdana" Font-Size="11pt" ForeColor="#000000" Text="Request a new Windows user account including MS Office, email and Internet access"></asp:LinkButton>
</ItemTemplate>
<EmptyDataRowStyle BackColor="#F3F5FA" />
</asp:FormView>
<span style="font-size: 10pt; font-family: Verdana">Please note that this form may only
be completed by a line manager from the applicant's department.</span> </div>
</form>
</body>
</html>
=================end aspx code=============
=================code behind==============
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
FormView1.InsertItem(True)
End Sub
Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted
Dim Message As String
Message = ", your request has been recieved by IM&T."
System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">" & vbCrLf)
System.Web.HttpContext.Current.Response.Write("alert (""" & Page.User.Identity.Name & Message & """)" & vbCrLf)
System.Web.HttpContext.Current.Response.Write("</SCRIPT>")
End Sub
End Class
=================end code behind==========
View 3 Replies
View Related
Jun 18, 2007
Hi Guys,
I have an Intranet web application and I have to use windows authentication. They are 4 main users group to access the application, each with different set of permission on my store procedures.
I have created a windows group for each of those group in my windows environemnt. My DB Connection String in web.config is set to Integrated Windows Authentication ( Integrated Security=SSPI)
I have done all configuration based on this article: http://msdn2.microsoft.com/en-us/library/ms998292.aspx
I have 4 different database Roles in my database and 4 different database users. I also add NT AUTHORITYNETWORK SERVICE as a member of each of those database Roles.
Now I get connected, fine and set permission on the database roles.
1 thing I want to know, How Can I get the Windows Group name in the database, which has the same name as my SQL Login and Database Roles or User
I tried all SUSER_SNAME() and ... they all return NT AUTHORITYNETWORK SERVICE
I want to get AppPowerUser Role or windows group name. I think I should be able to because I am using windows authentication.
Any help is appreciated.
Mehdi
View 1 Replies
View Related
Feb 11, 2008
How do I insert the UserName of the Logged in user into the DB field UserID. I created a web form page, and added a form view control set the page default to Insert Mode. The Page is used to insert data. I have a hidden field called UserID that I would like to capture the Logged in user. I have logged in to the default page with userID and password, added a new record, and the db table field UserID is empty. I have been trying to figure out what I am doing worng, but no luck, Please suggest the best way to do this. I have listed what I have done thus far?
I have added the login Control from Login and added it to the page as a hidden field thinking that I needed to have this field on the page to get this to work where you copy the value in the Loginname field to the useridTextbox on the formview1 insert template. I read on line where the method below is better. Please tell me what I am doint wrong.
System.aspx---------------------------------------------------------------------------------------------------------------1) <asp:FormView ID="FormView1" runat="server" AllowPaging="True" DataKeyNames="SystemID" DataSourceID="SystemSqlDataSource1" DefaultMode="Insert" Width="583px">2) <asp:TextBox ID="UserIDTextBox" runat="server" Text='<%# Bind("UserID") %>' Visible="False"></asp:TextBox><br />3) <asp:SqlDataSource ID="SystemSqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RCMISConnectionString %>" DeleteCommand="DELETE FROM [tblSystem] WHERE [SystemID] = @SystemID" InsertCommand="INSERT INTO [tblSystem] ([SystemID], [SystemDesc], [Inactive], [TimeStampEntry], [TimeStampUpdate], [UserID]) VALUES (@SystemID, @SystemDesc, @Inactive, GETDATE(), @TimeStampUpdate, @UserID)"4) <InsertParameters> <asp:Parameter Name="SystemID" Type="String" /> <asp:Parameter Name="SystemDesc" Type="String" /> <asp:Parameter Name="Inactive" Type="Boolean" /> <asp:Parameter Name="TimeStampEntry" Type="DateTime" /> <asp:Parameter Name="TimeStampUpdate" Type="DateTime" /> <asp:Parameter Name="UserID" Type="String" /> </InsertParameters> </asp:SqlDataSource>System.aspx.vb---------------------------------------------------------------------------------------------------------5) Partial Class MemberPages_RCM_frmSystem Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'UserIDTextBox.Text = Membership.GetUser().ProviderUserKey.ToString() End Sub Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted 'Set the UserID Value to the currently logged on user's ID e.Values("UserID") = Membership.GetUser().ProviderUserKey End SubEnd ClassThank you in advance,
View 7 Replies
View Related
Dec 31, 2006
I have a simple insert statement that takes values from textboxes and inserts them into a sql server database. I would like to add a value that is selected from a dropdown list and find its corresponding unique "Id" value and insert into the database as well. My sql statement looks like this:string strSQL = "INSERT INTO aspnet_Expenses (ExpenseDate,RentalCar,ect..) VALUES (@ExpenseUserId,@ExpenseDate,@RentalCar,ect..)";I would like to add this to it: SELECT @ExpenseUserId = UserId FROM aspnet_users WHERE Username = ExpenseUserName1) How do I assign the value from the dropdown list to save as the "ExpenseUserName"and2) Am I on the right path with this at all?
View 3 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €śEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €śBranch€? entry, basically each entry in €śbranch€? and €śEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €śON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Mar 24, 2004
Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
View 2 Replies
View Related
Jun 2, 2005
I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug
View 1 Replies
View Related
Jun 18, 2007
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1
------
tbl1_id
tbl1_data1
tbl1_data2
table2
------
tbl2_id
tbl2_data1
tbl2_data2
table3
------
tbl3_id
tbl3_data1
tbl3_data2
table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2
View 3 Replies
View Related
Oct 10, 2007
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
View 3 Replies
View Related
Jun 29, 2015
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
View 11 Replies
View Related
Nov 23, 2007
Hi All,
I have 2 tables People & PeopleCosts.
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Kindly help. thanks in advance.
Myl
View 3 Replies
View Related
Sep 16, 2004
Hello -
I dont know something weired happened on our MSSQL server today. We are able to connect to any Databases from Enterprise Manager from a Remote server.
Once we logged in and connected to a database on my database server, it keep prompting for the user id and password as we browsing through different tables in that database?
(Right click on the table then select Open Table -> Return all rows -> and it prompts for password )
Now when we enter password the contents of Table are displayed and now when we try to see contents of another table in the database it again prompts for password.
Any Idea whats wrong? How this can be resolved as it was not happening before .. :(
View 1 Replies
View Related
Jun 2, 2014
I put this together to export the user name /password to a csv file to test my SP to output the user name/password.
DECLARE @user_name varchar(50)
DECLARE @psswrd varchar(10)
SELECT @user_name ,@psswrd
FROM ngweb_bulk_enrollments
EXEC master.dbo.xp_cmdshell 'bcp NGDevl.dbo.ngweb_bulk_enrollments out C: est.csv -Sserver1 -T -t, -r
-c'
This works but I don't get the headers in the file. How can I include the headers?
View 7 Replies
View Related
Jan 5, 2008
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
View 16 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Dec 29, 2007
I have two tables. In tblUsers is all userdata. In table tblMoreInfo is some info on a user (0-15 records).Now I want to select tblUsers.username and tblUsers.usercode and the matching top 1 row tblMoreInfo.schoolname (top 1 based on tblMoreInfo.createdate) IF IT EVENT EXISTS from tblMoreInfo. If no matching record exists I want the value NULL to be returned for the schoolname...In all cases tblUsers.username must be unique (and I cant use the distinct keyword as im already using the row_number() function.Any help?Thanks!
View 1 Replies
View Related
Jan 4, 2008
I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...
View 6 Replies
View Related
Dec 9, 2006
Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain...
I set up a table called relationships that looks like this:
ID
type: int
RELID1
type: int, is the user id that initiated the relationship request.
RELID2
type: int, is the user id of the second person in the relationship.
Story
type: varchar(255), quick blerb on how they are related.
Type
type: int, a number 1-20 based on the relationship they have
Status
type: int, 1 = confirmed by second person, 0 = not confirmed
What i'd like to have pulled is a list of the people that person (for example: 70) is related to. I am having the two following problems:
1. unqid of "70" could be in RELID1 or RELID2 as they could have initiated the request or been the second person.
2. I don't want to display them selves in their own relationship listing
Example Data:
ID
1
2
3
4
RELID1
25
15
70
12
RELID2
54
70
13
8
Story
Met on the east coast
Met at walmart
Met walking
Met outside
Type
14
11
3
8
Status
1
1
1
1
Example Output:
Again assuming the current user is "70", the sql should pull: 15, 13.. but i'd like to pull their names from another table called "Users" where "15" and "13" are the UnqID's in a column called "ID".
So:
15 = Bob Smith
13 = Jane Doe
The following code works great that I got with the help on another form:
SELECT Relationship.RELID1
, Relationship.RELID2
, ReMembers.FirstName
, ReMembers.LastName
FROM Relationship
INNER
JOIN ReMembers
ON ReMembers.AccountID = Relationship.RELID1
WHERE Relationship.RELID2 = 70
UNION ALL
SELECT Relationship.RELID1
, Relationship.RELID2
, ReMembers.FirstName
, ReMembers.LastName
FROM Relationship
INNER
JOIN ReMembers
ON ReMembers.AccountID = Relationship.RELID2
WHERE Relationship.RELID2 = 70
My question is: What is the best way to set this table up? I'm not committed to any any design as of right now, but want to be sure I set it up in the most efficient manner.
Any feedback / opinions are welcome! :)
View 5 Replies
View Related
Nov 1, 2007
Hi,
If I have 2 tables:
TableA
aID
aCount
TableB
bID
bCount
And I need to create a 3rd table that will look like:
aID
bID
What should I call this table?
I've seen:
relAB
AXB
Any other naming conventions?
View 7 Replies
View Related
Sep 8, 2006
how can i find all the names of tables that a specific table is related to in tsql?
View 3 Replies
View Related
Jul 20, 2006
I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?
My logical approach to this is:
1. Build a handler for the OnInsert event of the first table
2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.
What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
View 4 Replies
View Related
Aug 23, 2007
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
View 3 Replies
View Related
Jan 11, 2006
Hey guys,
I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items.
I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId)
I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable.
Any help or suggestions would be great.
Thanks,
Matt
View 1 Replies
View Related
Jan 22, 2008
I have the following:
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5
I need to insert a dummy row as the first row in this AuditRecord table for every occurrance of a given TicketID so that I get the following:
Dummy_Tick1 Dummy_AuditRec
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
Dummy_Tick2 Dummy_AuditRec
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5
The AuditRec table is huge. How can I accomplish this using SQL?
View 1 Replies
View Related
Dec 28, 2007
Hi All,
Can anyone help me find out which system table/s are out there that hold SQL Server Job/Agent information?
Thanks!
View 3 Replies
View Related
Dec 27, 2007
I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
View 4 Replies
View Related
Dec 4, 2007
Hi everyone!
I'm on my way to learn SSIS by myself and it's a little complicated!
I'd like to ask you one thing:
I have two tables at my data source, one is "Clients" and the other one is ClientsAddress. That is, a client can have more than one address. Both tables are related by a one to many relationship and the tables description is:
CLIENTS CLIENTSADDRESS
#PK_Client #PK_Client
other fields.. #ID_address
... other fields
What i intend to do is to obtain one table with approximately 3 fields, each one for a possible client address; something like this:
CLIENTS
PK_Client
ID_Address1
ID_Address2
ID_Address3
My question is what transformation can i use? an how ?
Thanks very much in advance!!
Emilio Leyes
Salta, Argentina
View 1 Replies
View Related
Apr 2, 2014
I am working on a query that seems very simple, but I just cannot seem to get it correct.
I want to be able to select Only 1 MemberAddress with the MIN(MoveDate) for each of the 3 Members. The results would be this:
Joe Smith, 2000-03-10, 1034 Sturgis Road, 115, Portland, Or, 77665
Sally Jones, 2001-01-02, 8970 Pierce Road, 25, Clear Bay, Washington, 96547
Beth Moore, 2006-05-30, 456 W. Blane Ave, NULL, Charleston, West Virgina, 56897
DECLARE @Members TABLE
(
MemberRowID INT IDENTITY(1,1) NOT NULL
,FirstName VARCHAR(20)
, LastName VARCHAR(20)
[Code]....
I am not opposed to using CTE, I really like them, but I cannot figure this one out. But I will try most anything.
View 4 Replies
View Related
May 12, 2015
Here is the data:
ProjectId
Stage
StateStatus
ProjectId
ProjectName
StartDate
P1
S1
Completed
P1
P1Name
31/12/2015
[Code] ...
I want with PowerPivot to tell what stage is in progress for project. I looked at RElatedTable, LOOKUPVALUES, but I can't find a way to associate this to get working. I got however the one telling me which projects are Completed.
Result shall be:
ProjectName StageinProgess
P1Name S2
P2Name S3
P3Name None
View 4 Replies
View Related
May 14, 2008
Hi,
Not too long ago I was looking to change a primary key in a table from one column to another, the standard accepted procedure to so procedure to do so (I was told) was:
1) drop primary key constraint from old_key_column
2)add primary key constraint to new_key_column
3) drop old_key_column (optional).
In order to carry out the first step, I needed the name of the primary key constraint, at which point I asked around for a command that would return that name (and would quite probably take as a parameter the name of the column that key is related to). The answer was :
Try this:
select name 'constraintName' from sys.indexes where object_id=object_id('<tableName>') and is_primary_key=1
Unfortunately this command never worked for me. Probably because I don't have a table sys.indexes in my DB, but I can see a table sysindexes in the system tables folder, then again that table doesn't have an object_id column, but it has an id column, finally this column only contain obscur numbers.
So I am asking you all (again) for help, do you know about a command that returns every single constraint related to a column (or a table, or both).
One more thing, I am running SQL SERVER 2000, I do know that there are graphic ways to do those very operations (in the enterprise manager or the query analyzer) and I do know those ways (that's how i got past the problem mentioned earlier), I just think that knowing how to do it programmatically would be a plus.
PS: I am a total newbie to T-SQL (and a still a beginner in SQL, so please take it easy with me )
View 6 Replies
View Related
Mar 4, 2005
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
View 3 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
