Lookup + Insert Is Inserting Duplicated Business Keys
Apr 28, 2008
Hi,
I have set a common lookup + insert dataflow. but I'm having a problem when the business key comes duplicated in the source file. This file has transactions and each transaction has a transaction code which represents what kind of transaction it is.
So in the same file I can have 2 or more transactions with the same transaction code.
I have a dimension table wich stores kinds of transactions with a business and surrogate(identity) key.
The lookup sends through the error path every record it doesn't find its business key. but since I may have 2 or more transactions with the same business key in the same source the lookup sends them every time so I end up with duplicated business keys.
I have tried setting the max insertion commit size to 1 but it doesn't work. The look up won't find the new business keys.
I hope it's clear. thanks
View 7 Replies
ADVERTISEMENT
Aug 24, 2007
I am having data where there are empty string in the business keys which should be used for Slowly changing dimesnion type 2, how do i over come this as due to empty strings i am getting new rows even though the rows havent really changed.
example of data is name and salary are business keys
name salary age address
dev 23 klddldldlk
sdfg 24 34 kdlddlkd
when the same is given as input the row
dev 23 klddldldlk
is coming as anew row where it already exists how do i over come this
View 4 Replies
View Related
Nov 12, 2004
Hello, everyone:
There is a table MyTable,
A B
-----------------
a c
b k
mk oc
How to insert from another table, YourTable,
C D
---------------
d c
e p
mk oc
without duplicate value such as third row?
Thanks a lot
ZYT
View 1 Replies
View Related
Apr 29, 2015
I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.
I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.
CREATE TABLE dbo.CodesTable (
PartyGrpId INT NOT NULL
, CdTyp VARCHAR ( 8 ) NOT NULL
, CompId INT NOT NULL
, CdValue VARCHAR ( 8 ) NOT NULL
, CdValueDesc VARCHAR ( 255 ) NULL
, AltValueDesc VARCHAR ( 100 ) NULL
[Code] ....
I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.
ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'
View 5 Replies
View Related
Jul 20, 2005
Hi, all:I'm a newbie trying to understand the concept of referential integrity anddealing with Primary and Foreign Keys. I'm sure mine is a simple problem...I've created 3 tables as follows:MemberTable-----1 Member ID (Primary key)2 Member Name3 etc...FoodsTable-----1 Food ID (Primary key)2 Food NameMemberFoods-----1 Member ID (Foreign key)2 Food ID (Foreign key)Now, I've just learned about referential integrity and all that, and this ismy first foray into creating primary/foreign keys, linking tables, etc. (soI'm assuming the above tables are fine). What I don't understand is this:if I have a (checkbox) form from which members can choose their favoritefoods, how do I insert that data? For example:Food Form-----Which of these are your favorite foods:1) Steak2) Chicken3) Potatoes4) etc...If I want to insert, let's say, 'Steak' and 'Chicken', do I have to first doa query on my Foods table to retrieve the Food ID's for Steak and Chicken,and then do an "insert" using those ID's into my MemberFoods table? (I amusing the actual Food Names (not ID's) as values on my form.) If so, cansomeone please share the SQL for retrieving and then inserting?I don' t know...maybe I'm overcomplicating the issue, but it just seems likea lot of extra work to maintain a "linking table" of foreign keys.Thanks for the help.
View 1 Replies
View Related
Oct 12, 2006
First, I'm very new to SSIS, so forgive any stupid questions.
I'm trying to normalize some data by creating rows in a dimension table as I find new values in the original table.
My first thought is to use a Lookup to replace string values with id's to the Dimension table, and when the Lookup fails, insert the value into the Dimension table with a new key.
Does that make sense? This would be relatively easy to do with TSQL, but surely there is a way to do this in SSIS.
Thoughts?
View 6 Replies
View Related
Nov 17, 2003
I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?
thanks for your time,
Andre
View 1 Replies
View Related
Feb 12, 2008
I have a Brokers table and Trans (transactions) table. When I insert new transactions, I run a stored procedure or trigger to update the Brokers table since a transaction may have a new Broker. Is there an simpler way to do this than my query below?
INSERT INTO Brokers (Brokers.broker_id)
SELECT X.broker_id
FROM (SELECT DISTINCT broker_id FROM Trans) As X
WHERE NOT EXISTS (SELECT Brokers.broker_id FROM Brokers WHERE Brokers.broker_id = X.broker_id)
View 7 Replies
View Related
May 21, 2008
i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help? i've been trying all the permutations of queries i can think of and not getting it.
thanks.
View 5 Replies
View Related
Feb 25, 2008
Hi. i am updating a field on a table i have called tblNavproductId. when doing the insert, i get the error message that you know, it is is violation of a foreign key constraint on NavId. i see the foreign key, so i know it is there and i understand that. i read the books online and here is the article;
http://msdn2.microsoft.com/en-us/library/ms186247.aspx
does anyone have a good example of this type of query? i am putting 990k+ records in at once, so would this be a bulk insert? Thanks.
View 3 Replies
View Related
Jan 4, 2005
All,
Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.
We can certainly write this but before reinventing the wheel I figure I'd ask and see.
Many Thanks,
Isaac
View 14 Replies
View Related
Jul 5, 2006
Hi there!
I have some troubles getting done a lookup... data i get from a legacy system has to be cleaned up and split up into normalized form. i get invoice data and want to split up invoices into a separate table as customers. therefore i lookup if the customer of the current line is not already in my newly created customer-table. if it isn't there (error output of lookup transformation) i insert it into the customer-table.
The problem is that after the customer is inserted into the customer-table, it still is not found by the lookup transform because the lookup uses caching to hold the reference table (and so is the exact same customer inserted again and again and again). Is there any way to disable caching and let the lookup transformation do a select for every new row it gets? or at least refilling the cache if some event happens?
Many thanks in advance!
Wolfgang
View 2 Replies
View Related
Apr 21, 2015
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
View 14 Replies
View Related
Oct 23, 2007
Does anyone have a successful prescribed sequence for installing VS2005 and Business Intelligence Reports Projects on a Vista Business workstation to be used to create reports for a server?
I've looked through everything I can find here and I don't seem to see a clear solution without a lot of trial and error.
Fact is, I've not been successful getting just the reports to install on a plain XP box. Of course, the report creation looks fine on the server but I don't want to work directly on the server.
Thank you
View 1 Replies
View Related
Jun 10, 2006
Can anyone take me through synchronization of contacts within Business Contacts Outlook into Microsoft Small Business Accounts?
I run a stand alone PC with NO network. When SBA came SQL was also installed. Apparently you can synchronise Contacts within Business Contacts with SBA but both SBA & Outlook should work through the same SQL server.
Has anyone tried this?
Can someone walk me through the process?
Thanks
Debbie
View 1 Replies
View Related
Jun 1, 2006
Hi ,
How to INsert and Update Large Amount of Records (4 Lacs) into Destination Table Through Business Intelligence Studio Using SSIS Pacakge .How to Achieve this .i tryed with left outer join & conditional split but the problem its not able to insert & update records simultaneously . can any one give me a sample .
Thanks & Regards
Jeyakumar.M
View 3 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Jan 28, 2008
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
Sep 20, 2007
I need to insert some values into a table and after that catch the ID inserted.
I set some input parameters in stored procedure and only one to get the @id defined as output
SqlParameter paramIdPedido = new SqlParameter("@APP_IDPEDIDO", SqlDbType.Int, 4);paramIdPedido.Direction = ParameterDirection.Output;cmd.Parameters.Add(paramIdPedido);
Do I have to run cmd.ExecuteNonQuery(); to record first what I need and afterrun ExecuteReader: something like SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()... to get the ID)
From stored procedure:
INSERT table(fields) VALUES(vars)SELECT TOP 1 @id = id FROM table ORDER BY id DESC
I must do all these steps or maybe is there anything less complex to do?
Thanks!
View 1 Replies
View Related
Sep 12, 2006
Hi Andrea,
I have made a table which contain data inserted manually and also data that was inserted by using bulk insertion. I have no problems using the table with Grid View. But when I try to use a query like the following:
SELECT *
FROM dbo.last
WHERE VMake = 'Honda'
AND VType = 'sedan'
AND VColor = 'Red';
I would only get the data that were inserted manually.
When I use the same query to filter data from a table that the data was inserted by using bulk insert, I get column names but no data.
Any help please.
Juvan
View 1 Replies
View Related
May 16, 2008
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
View 2 Replies
View Related
Jul 25, 2005
m inserting some data in big-sized field
and small-sized fields, data of varchar type, int's, dateTime, and
others... i am using something called a stored procedure to add data
into a table.. now when i execute the stored procedure my data gets
truncated (although i made the sizes for my fields ridiculously big
like 1000 just in case) for example if i want to enter Jasmine it only
inserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I am
guessing it's a problem of using single vs. double quotes and stuff
like that within my insert statement...
the following is my stored procedure:
CREATE procedure addLabor
@lName varchar,
@fName varchar,
@mName varchar,
@title varchar,
@craft varchar,
@lastFour varchar,
@SSN varchar,
@dateOfHire varchar,
@currentProj varchar,
@status tinyInt,
@project_id int,
@updateBy varchar,
@updateDate varchar,
@address varchar,
@email varchar,
@phone varchar,
@zip varchar,
@myfeedBack varchar,
@ethnicity varchar,
@userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,
lastFour, SSN, dateOfHire, currentProj, status, project_id,
updateBy,
updateDate, address, email, phone, zip, ethnicity)
VALUES
(@lName , @fName , @mName , @title , @craft , @lastFour , @SSN ,@dateOfHire , @currentProj ,
@status, @project_id , @UpdateBy, @UpdateDate , @address , @email , @phone , @zip , @ethnicity )
SELECT @newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@newid ,+'
+@myfeedBack + ',
+@userID ,
@project_id )
SET NOCOUNT ON
END
GO
When i use 2 single quotes it insert the following string: "@lName" not the actual value of the variable @lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman',
'1234',
'keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==',
'6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/2005
2:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345',
'123-12-1234', 'African American', '3'
I appreciate any help or hints
thanks to all
View 1 Replies
View Related
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
May 22, 2015
I've got a piece of code that returns 53 records when using just the SELECT section.When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).
The code for creating the table is here:-
USE [CDSExtractInpatients6.2]
GO
/****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/
SET ANSI_NULLS ON
GO
[code]...
I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.Don't know if it makes any difference, but the server is running SQL2008.
View 9 Replies
View Related
Sep 16, 2009
I have a primary key named pk, name and surname fields. I need to insert to my table names and surnames.
INSERT INTO People (name,surname) VALUES ('john','black');
I'm not giving pks database gives is auto. But my problem is i need to know the pk that my database gave. Because i have lots of duplicate records. Is there any way to retrieve pk while inserting to table.
View 7 Replies
View Related
Jul 23, 2005
Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)
View 2 Replies
View Related
Oct 6, 2007
the error message I get is
{"Object reference not set to an instance of an object."}
and it points to < Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text >
this is my code":
Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click
REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text
Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text
REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand
cmd.Connection = cn
REM Build our parameterized insert statementDim sql As New StringBuilder
sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)")
cmd.CommandText = sql.ToString
REM Add parameter values to command
REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters
.Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp
.Add("Exch", SqlDbType.VarChar).Value = Exch
End With
REM Now execute the statement
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub
View 3 Replies
View Related
Feb 9, 2006
I have a database with columnsuserOwnListsuserID uniqueidentifieruserName nvarchar100userList nvrachar100createdDateI
have created successfully a gridview controller to edit these values in
database. The Gridview data is populated by SqlDataSource.I
have also created a EmptyDataTemplate and created a form into it.
There is only one textBox and submit button to create the First entry
to userOwnLists -table.Now I collect the value from EmptyDataTemplate textbox with id userList1 and create a codebehind logic for the submitbutton.protected void Button2_Click(object sender, EventArgs e) { TextBox listName = (TextBox)this.FindControl("listName1", GridView1.Controls);SqlDataSource1.InsertParameters["userId"].DefaultValue = Membership.GetUser().ProviderUserKey; SqlDataSource1.InsertParameters["userName"].DefaultValue = Membership.GetUser().UserName.ToString(); SqlDataSource1.InsertParameters["listName"].DefaultValue = listName.Text; SqlDataSource1.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString(); SqlDataSource1.Insert(); }The problem is now that I get error: Exception Details: System.Data.SqlClient.SqlException:
Implicit conversion from data type sql_variant to uniqueidentifier is
not allowed. Use the CONVERT function to run this query.OK. So I Googled a bit and found this:http://scottonwriting.net/sowblog/posts/4690.aspxMy Question is: How do I convert userId so I can insert it to database successfully?This does not work:String userId = Membership.GetUser().ProviderUserKey.ToString(); SqlDataSource1.InsertParameters["userId"].DefaultValue = Convert.ToString(userId);
View 2 Replies
View Related
Aug 7, 2015
I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''
''
, codepage = ''1252''
, keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
View 4 Replies
View Related
Jul 20, 2005
hello myself avinashi am developing on application having vb 6 as front end and sql server 7as back end.when i use insert query to insert data in table then the date value ofthat query is going as 01/01/1900my query is as followsStrSql = "Insert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"StrSql = StrSql & " Values(" & txtTransactionID.text & "," &txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),2)& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),2)& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),2)+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),0)& ")"and its output isInsert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)in above query though i used cdate to voucherdate value still it save indatabase as 01/01/1900 though here it shows right dateplz help me its a very big issue for me & i really just fed of thisproblem
View 2 Replies
View Related
Sep 29, 2007
Hi,
I have a data file in the folloing format
SubjectId1|class1
SubjectId2|class2
SubjectId3|class3
I just wanted to insert only SubjectIds into my table 'Subjects' which has the follwing schama ignoring the classes
The row delimeter is "
" and the column delimeter is ' | '
Table Subjects
{
ID (Autoincrement)
SubjectId varchar(20)
}
Can any one provide the format file for doing this or suggest anyway to do this?
Please do note that the file may contain millions of records
Thank u
~mohan
View 5 Replies
View Related
Apr 24, 2015
I am writing an Instead of Insert trigger. I would like to fire an error when inserting into an 'Identity' column. Since UPDATE([ColumnName]) always returns TRUE for insert statements, is there an easy/fast way around this? I don't want to use:
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NULL))
here is my pseudo-code...
CREATE VIEW [org].[Assets]
WITH SCHEMABINDING
[Code] .....
-- How does this statement need to be written to throw the error?
--UPDATE([AS_ID]) always returns TRUE
IF(UPDATE([AS_ID]))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Is there a faster/better method than this?
IF(EXISTS(SELECT [i].[AS_ID] FROM [inserted] [i] WHERE [i].[AS_ID] IS NOT NULL))
RAISERROR('INSERT into the anchor identity column ''AS_ID'' is not allowed.', 16, 1) WITH NOWAIT;
-- Do Stuff
END;
-- Should error for inserting into [AS_ID] field (which is an identity field)
INSERT INTO [org].[Assets]([AS_ID], [Tag], [Name])
VALUES(1, 'f451', 'Paper burns'),
(2, 'k505.928', 'Paper burns in Chemistry');
-- No error should occur
INSERT INTO [org].[Assets]([Tag], [Name])
VALUES('f451', 'Paper burns'),
('k505.928', 'Paper burns in Chemistry');
View 7 Replies
View Related
