How Can I Populate Data In A One-to-one Relationship

Jul 20, 2006

Here is what I have and (somewhat understand).
 
I’m using Visual Web
Developer 2005 Express Edition and have setup my application to use form authentication
(which automatically creates the ASPNETDB.MDF file with several default tables
and views).
  I’m using the CreateUserWizard
which is fine…but I need to collect additional information like (firstname,
lastname, address…and on..).
  What I’ve
done.
  I’ve created a tabled named
UserProfile and set UserId as the primary key (uniqueidentifier).
 

 I then setup a 1-to-1 relationship
between aspnet_Users and UserProfile (which I think is correct).
  On my UpdateContactInfo.aspx page (where
users go to update their personal information) I use a hidden label control
(UserValue) to receive the UserId during the page_load event as below:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        UserValue.Text = Membership.GetUser().ProviderUserKey().ToString()
    End Sub
Now with the UserID available I need to populate the
UserProfile table with the UserId, firstname, lastname, address of the
currently logged in user.
   How can I do this and am I on the
right track..?

View 6 Replies


ADVERTISEMENT

Master Data Services :: User Friendly Way To Manage Data With Many-to-many Relationship

Sep 28, 2015

I've been quite excited about SQL Server MDS that should allow non-IT staff to easily maintain data. However maintaining data that have many-to-many relationships seems to be quite a pain. I believe the best way is:

Open up your MDS web interfaceGo to entities > product (for example)Add a new member and fill the details Click "product parts" in the bottom right "Related Entities" partAdd a new memberTry and find the product you just created from the dropdownlistSelect the first part and click OKAgain try and find the product you have created from the dropdownlistSelect the second part and click OKRepeat...Close the tab on your browser and finish your product entity.How I wish it worked:

Open up your MDS web interfaceGo to entities > product (for example)Add a new member and fill the detailsCheck a checkbox for each part visible under "product parts" in the bottom right "Related Entities" partFinish your product entity.

View 3 Replies View Related

How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?

Mar 25, 2006

How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?

For example:
I have 2 tables, tbl1 and tbl2.

tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}

tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id


If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?

mradlmaier

View 3 Replies View Related

Power Pivot :: Use Relationship Not Overriding Conflicting Active Relationship?

Oct 14, 2015

I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.

   Operation (Commodity, OperationKey)   ==========> 
Commodity (Commodity)
                      /                                                                        
/
                       |                                                                         
|
   Advice (OperationKey)       ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)

I have the following measure:

Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)

However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.

If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.

View 9 Replies View Related

Populate Binay Data

Oct 16, 2006

We have stored large text data in "Image" data type in SQL Server. Can someone tell me how to get that vlaue from image datafield. Thanks

View 3 Replies View Related

Best Way To Populate A Page With Lots Of SQL Data

Sep 8, 2006

I have a page that has about 8 dropdown boxes that need to be populated from sql tables.  What is the best way to populate these boxes. Here is how I have it nowconn = New SqlConnection(ConfigurationManager.AppSettings("SQLString")) ''''''''''''' Fill in DropDownList Status '''''''''''''''''''strSelect = "SELECT * FROM Requests_Status"cmdSelect = New SqlCommand(strSelect, conn) conn.Open()dtrSearch = cmdSelect.ExecuteReader()ddlRequestStatus.DataSource = dtrSearchddlRequestStatus.DataTextField = "RequestStatusName"ddlRequestStatus.DataValueField = "RequestStatus"ddlRequestStatus.DataBind()ddlRequestStatus.Items.Insert(0, New ListItem("-- Select Below --", -1)) cmdSelect.Cancel()dtrSearch.Close()conn.Close()''''''''''''' Fill in DropDownList Container '''''''''''''''''''strSelect = "SELECT * FROM Containers"cmdSelect = New SqlCommand(strSelect, conn) conn.Open()dtrSearch = cmdSelect.ExecuteReader()ddlContainer.DataSource = dtrSearchddlContainer.DataTextField = "ContainerName"ddlContainer.DataValueField = "ContainerID"ddlContainer.DataBind()ddlContainer.Items.Insert(0, New ListItem("-- Select Below --", -1)) cmdSelect.Cancel()dtrSearch.Close()conn.Close()'''''''''''''''''''''''''''''I then repeat the same commands as above for the other 6 dropdowns.  This seems like a bad way to have to do all this.ThanksCraig

View 3 Replies View Related

How To Populate A Dropdown Box Based On SQL Data?

Dec 16, 2003

How can I populate options in a dropdown (combo?) box based on data pulled from a SQL database compared against certain paramaters (if it is not equal to x, equal to y, etc)?

Thanks.

View 2 Replies View Related

How To Populate Checkboxes Based On SQL Data?

Dec 16, 2003

I apologize about the newbie-ness of these questions, but I am a total newbie (I have used VB for a while but this is my first 5 hours with ASP ever).

I just asked how to populate dropdowns, which I have found out how to do, but this question is quite a bit more difficult for me.

I have a SQL database, and in that database is a table, with a few columns. The first columnis a list of user id's, and the second is list of "options".

I want to make it on my webpage so that if the userid matches the column1 data, then it will display a checkbox for the column 2 option. Ie, if its "USERID1,OPTION1" in the SQL table, then if userID = "USERID1" then it will make an checkbox with caption/name "OPTION1". It has to loop through all the rows in the SQL table and make all the checkboxes for these options that match.

I have no idea how to do this, any help would be great.

View 1 Replies View Related

How Do I Populate A Table With Data On Setup

Jan 7, 2004

Hi Guys,

My ASP.Net app is multilingual and all my translations are stored in seperate MSDE tables, for example, tblEN for English, tblES for Spanish and tblTH for Thai.

When I send the installation files to my clients, I get them to double click on 4 MS DOS batch files that use OSQL to run 4 scripts.....

1. DataBase&Tables.sql - creates the database and tables
2. Logins&Users.sql
3. StoredProcedures.sql
4. Permissions.sql

This worked great before my language table came along to spoil the party and I now need some way of getting the data stored in the language on my server into the table on the client.

If I export the data to a text file and then send it out with the rest of the installation files - what are my options for transferring the data into the table ?

Any suggestions appreciated.

Steve.

View 4 Replies View Related

Populate Data By Hour Or By Minutes?

Sep 8, 2014

i have a table that populates the data every second.

ex.
10:40:10
10:40:09
10:40:08
10:40:07
... so on

the problem is i want to gather the data like

Every Hour

10:00:00
09:00:00
08:00:00
07:00:00
..and so on

and Every Minutes like (1 Min, 5 mins, or 10 Mins, 30 Mins)

10:40:00
10:30:00
10:20:00
10:10:00
.. so on

by this, it will make my reporting not over populated.

Im using SQL server 2008 R2

View 3 Replies View Related

Compare Data And Populate In Same Table

Feb 10, 2015

I have the data as below,in which owner is assigning some task to another user.

INPUT
#########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 11user2
user112user3
user1 13user4

PRIVILEGE table
#########

USER_DETAIL PRIVILEGE_ID

user110
user111
user112
user28
user35
user46

OWNER has one set of privilege details in privilege table. I need to check privilege for user2,user3 and user4 in privilege table, if privilege not matches with the user1 then i want to populate the data output as below

NEEDED OUTPUT
###########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 11user2
user112user3
user1 13user4
user211user2
user312user3
user413user4

I am populating this data in the view.

View 5 Replies View Related

How To Add Data Into A PK To FK Relationship?

Jan 7, 2008

HiI have 2 tables one is called QuickLinks and another called QuickLinkItemsQuickLinksQuickLinkID<PK>        UserID                 QuickLinkName--------------------------       -----------                ------------------------1                         111-111-111-111            QuickLink12                        111-111-111-111             QuickLink23                        222-222-222-222             QuickLink1 QuickLinkItemsQuickLinkID <FK>          CharacterName           CharacterImagePath---------------------- ------------------------------- ---------------------------------------- 1                                a                            path11                                b                            path22                               d                              path32                               e                              path43                               a                              path14                               b                             path 2 So thats how I see the data will look like in both of these tables the problem however I don't know how to write this into Ado.net code. Like I have made a PK to FK relationship on QuickLinkID(through the relationship builder thing).  I am just not sure how to I do this. For the sake of an example say you have 5 text boxes 2 text boxes for inserting the character names and 2 text boxes for inserting the CharacterImagePath. Then one for the Name of the QuickLink. So how would this be done? Like if it was one table it would be just some Insert commands. Like if this was all one table I would first get the userID from the asp membership table then grab all the values from the textBoxes and insert them where they go to and the QuickLinkID would get filled automatically.  The thing that throws me off is the QuickLinkID since it needs to be the same ID as in the QuickLink table. So do I have to first insert some values into the QuickLink table then do a select on it and grab the ID then finally continue with the inserts Into the QuickLinkItems table? Or do I have to join these tables together?Thanks  

View 18 Replies View Related

Trying To Populate Dropdown List From SQL Data Source

Apr 8, 2008

I am trying to populate a dropdownlist from a sql data source in my codebehind using c#.  I thought I had the code correct but I keep getting the following error and I am stuck:  "Only assignment, call, increment, decrement,  and new object expressions can be used as a statement"
 Here is the code:protected void populateOppNameList(){
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);SqlCommand cmd = new SqlCommand("SELECT empname FROM opportunity WHERE (username = " + userName + ")", conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
try
{
conn.Open();DataSet ds = new DataSet();DropDownList ddl_OppNames = (DropDownList)FormView1.FindControl("dropdownlist1");
ddl_OppNames.DataSource = ds;
ddl_OppNames.DataBind;}catch (Exception e)
{
}
finally
{if (conn != null) { conn.Close(); }
}
 
}

View 11 Replies View Related

Data Manipulation With One-to-many-to-one Relationship

Dec 4, 2006

Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.
Thanks

View 7 Replies View Related

Extract Data And Populate Values Into The Respective Controls

Apr 16, 2008

Hi!

I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:

Dim insertSQLDatasource As New SqlDataSource()
insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString

insertSQLDatasource.InsertCommandType = SqlDataSourceCommandType.Text
insertSQLDatasource.InsertCommand = "INSERT INTO Demo_Theatre_DB (StartTime, EndTime, CompanyName, Purpose, AccountManager, Presenter, ColorCode, Status, Comments) VALUES (@StartTime, @EndTime, @CompanyName, @Purpose, @AccountManager, @Presenter, @ColorCode, @Status, @Comments)"

insertSQLDatasource.InsertParameters.Add("StartTime", StartTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("EndTime", EndTimeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("CompanyName", CompanyNameTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Purpose", PurposeTextBox.Text)
insertSQLDatasource.InsertParameters.Add("AccountManager", AccountManagerTextBox.Text)
insertSQLDatasource.InsertParameters.Add("Presenter", PresenterTextBox.Text)
insertSQLDatasource.InsertParameters.Add("ColorCode", ColorDDL.SelectedValue.ToString)
insertSQLDatasource.InsertParameters.Add("Status", StatusRadioButtonList.SelectedValue)
insertSQLDatasource.InsertParameters.Add("Comments", CommentTextBox.Text)

Try
insertSQLDatasource.Insert()
Catch ex As Exception
Panel1.Visible = True
InsertMsgLabel.Text = ex.Message.ToString
Finally
insertSQLDatasource = Nothing
End Try

Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.

Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString")
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn)
sqlConn.Open()
Dim r As SqlDataReader = com.ExecuteReader()
While r.Read()
StartTimeTextBox.Text = r("StartTime")
EndTimeTextBox.Text = r("EndTime")
CompanyNameTextBox.Text = r("CompanyName")
PurposeTextBox.Text = r("Purpose")
AccountManagerTextBox.Text = r("AccountManager")
PresenterTextBox.Text = r("Presenter")
ColorDDL.SelectedValue = r("ColorCode")
StatusRadioButtonList.SelectedValue = r("Status")
CommentTextBox.Text = r("Comments")
End While

I hope someone can help me with this.

Your help will be appreciated.

Thanks!

View 8 Replies View Related

Parse XML Attributes And Populate Columns Within DB With Stripped Data

Nov 16, 2011

I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.

Example XML file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd">
<Asset_Collection>
<Collection_Metadata
Name="Asset Collection"
Description="Random XML Feed Test"

[Code] ....

Table/Columns which need to be inserting into:
Table:
TABLE_A

Columns:
ProdID
CustomerID
AreaCode

View 4 Replies View Related

How To Generate / Populate Already Existing Column With Sequential Data

May 8, 2012

I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.

what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.

I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.

View 3 Replies View Related

Data Flow Multiple Sources To Populate A Destination

Nov 28, 2007

I have 5 or more tables to join to get a particular output which has to be sent to a destination table. In the 5 tables some are inner joins and some are left outer join. I am opting for stored procedure at this point. But I would like to know how can this be done in data flow transformations having multiple souce and merge joins or any other alternates. I tried using merge join, but this does not accept more than two tables.

I saw this simple post which kick started me to use ssis transformations to stored procedures. But I encounter issue.
http://www.mssqltips.com/tip.asp?tip=1322
error
"The destination component does not have any available inputs for use in creating a path".

Please advice alternates

View 3 Replies View Related

Integration Services :: How To Populate Data Length To Excel

Jul 1, 2015

I use a ole db to get data from database as source data, and use ole db destination to put data into excel, destination component connect to an excel file . and got below warning:

Warning 10 Validation warning. {9FA859ED-E4C7-4EA1-AE32-11F21CFDC23D} OLE DB Destination [136]: Truncation may occur due to inserting data from data flow column "sMessage" with a length of 2000 to database column "sMessage" with a length of 255. how to populate data length >255 to excel

View 4 Replies View Related

The Best SQL Query Tool To Visualize Data Relationship

Nov 29, 2005

Another marketing campaign :eek:

View 1 Replies View Related

SQL Server 2014 :: One To Many Relationship In Data Processing

Jun 24, 2014

I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.

I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.

What would be the best option to select this kind of records.

View 9 Replies View Related

Database Table Data Modifications With PK/FK Relationship

Mar 21, 2007

I have two tables (T1 and T2). In T1 I have a field FT1 that is aprimary key in T2 I have a field FT2 that is a foreign key linked toFT1. These fields have been populated with data. Lets say that in onerow of data I have in T1 under FT1 "my cell" as the data entry,similarly with T2 under FT2 I have 2 rows of data that also have "mycell" as the data entry. What is the best line of action is I wantedto change "my cell" to "my data"?

View 3 Replies View Related

Inserting 1:M Relationship Data Via One Stored Procedure

Sep 2, 2006

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

View 3 Replies View Related

SSIS: Populate Data From Space Delimited File To SQL Server 2005

Aug 9, 2007

The problem is that I have (for example) following data

AA AA LH
BB BB ML
CC CC QA
DD DD PS

in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.

View 7 Replies View Related

Inserting Data Into Two Tables With Parent-child Relationship

Nov 13, 2006

I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?

If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.

The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.

View 1 Replies View Related

Integration Services :: Loading Data To Destination With Foreign Key Relationship

Apr 22, 2015

I have to load data into destination table, it has foreign key relation to two different tables called person table and organization table . sample data to be loaded is like

person_id organization_id
1                   Null
2                    NULL
Null                1
null               null

person table and organization table doesn't have null values in them, when I try to load this data none of them are laoded, I know either person_id or organization id having null value is failing foreign key constraint. But I want to transfer all the rows except  the ones having both nulls. how this can be achieved ?

View 7 Replies View Related

Data Warehousing :: Foreign Key Relationship Between Two Slowly Changing Dimensions?

Apr 20, 2015

Should we have foreign key relationship between two slowly changing dimensions?

Most of the cases we talked about are about how to build an SLC, any example of trying to have two SLCs with FK relationship in a snow flake schema?

View 3 Replies View Related

I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir

Nov 28, 2006

Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.

When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.


I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)


I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.


Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦

View 2 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

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

INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table

Jul 20, 2005

Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO

View 2 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

Populate Database Tables With Data From Another Database

May 2, 2008



I have two Databases with the same name that reside in different servers. The two servers are linked servers. Each database consists of 5 Tables. The tables have identical names in each database.

The database in ServerA has 5 tables with data, the database in ServerB also has 5 tables with the same schema as ServerA, however the 5 identical tables in ServerB contain no data.

I need to populate the 5 tables in ServerB with the Data from the 5 tables in ServerA.

What SQL code or script can i use to populate the 5 tables in ServerB

Below are the names of the 5 tables:

TABLE1: [ServerB].[ProdDB].dbo.[Orders]
TABLE2: [ServerB].[ProdDB].dbo.[Sales]
TABLE3: [ServerB].[ProdDB].dbo.[Employee]
TABLE4: [ServerB].[ProdDB].dbo.[Customer]
TABLE5: [ServerB].[ProdDB].dbo.[Region]


Does any one have a script that could help me with this task. Thanks

I am using SQL Server 2005

View 2 Replies View Related

Ddl Doesn't Populate

Feb 6, 2008

Anybody see a reason why this list won't populate?
<asp:DropDownList ID="ddlState" DataSourceID="srcStates"    DataTextField="StateName" DataValueField="StateName" runat="server">    <asp:ListItem>Select State</asp:ListItem></asp:DropDownList><asp:SqlDataSource ID="srcStates" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>"    SelectCommand="sp_States" SelectCommandType="StoredProcedure">    <SelectParameters>        <asp:ControlParameter ControlID="ddlState" DefaultValue="Select State" Name="State"            PropertyName="SelectedValue" Type="String" />    </SelectParameters></asp:SqlDataSource>

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved