SQL SERVER 2005 Data Insert

Apr 2, 2007

Hi,
I have 2 tables. MASTER table as M and TRAN table as T. I need to insert 1 row in table M and with the foreign key need to insert multiple rows in the table T. SQL SERVER used is 2005. Application is in .NET 1.x

I am able to insert one record in the table in M but how do i insert the multiple records in table T?


Help....



Thanks,
Rahul Jha

View 3 Replies


ADVERTISEMENT

Is It Possible To Insert JSON Data To SQL Server 2005

Dec 3, 2007



Hi,

Is it possible to insert JSON data to SQL Server 2005 directly using stored procedure?

Somthing like for XML we are using sp_xml_preparedocument and openxml. For JSON ???
Thanks,

View 6 Replies View Related

Best Way To Insert Large Amounts Of Data From A Webform To SQL Server 2005

Oct 21, 2007

HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd

View 1 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

Feb 26, 2015

I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)

I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !

Below is the code i have at the moment

declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT

set @startdate = '2015-01-01'
set @enddate = '2015-01-31'

[Code] .....

View 1 Replies View Related

Using Idb2connection Source To Insert Data Into SQL 2005

Jan 26, 2008

Hi all,I have an issue to insert data into SQL2005 from an idb2connection.I explain:We need to retrieve data from an AS400 and insert it into a SQL database. Actually the idea is to use an idb2connection (which is supposed to be much faster than a DST or SSIS package; maybe I am wrong...)Connecting to the AS400 is not an issue at all. I can retrieve data into a data reader (I try to use a data adapter but there was some issue of character conversion).So basically I have the data reader with all the data I need and I want to insert this data into SQL. My problem is that I would like to know which method is the best to do the insert: 1- Read each line of the data reader and create an insert statement that I can send to my SQL Stored procedure to perform the insert 2- Read each line of the data reader and concatenate it into an xml format in order to pass the Xml string to the stored procedure and perform the insert 3-?? Using solution 1, in case of huge amount of data it will call the stored procedure for each row and may crash the SQL server (cause of having multi call to SQL server)?Using solution 2 I have issue when I have more than 2000 lines. So please can someone tell me which method should be the more appropriate to perform this transfer of data from AS400 to SQL considering that I may have a large amount of data. All your ideas are welcome!!!! 
Many thanks,
 

View 4 Replies View Related

How Do I Call A Stored Procedure To Insert Data In SQL Server In SSIS Data Flow Task

Jan 29, 2008



I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks

View 6 Replies View Related

How Insert An Array Into Sql Server 2005 Using VB.2005

Mar 4, 2008

I have the text file parsed into an array.  How to insert that into  SQLServer Database? (VB 2005)
 Thanks,
Siby

View 3 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

Insert Data To A Table Where Select Data From Another SERVER

Oct 8, 2007

I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
May i know that how do i retrieve data from  [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
 

View 1 Replies View Related

SQL Server 2005 - Insert Problem

Apr 30, 2008

Hi All
I have a table in my database called YM_WEB_APPLICATIONS in this table I have a field called ApplicationID everytime an insert happens this increments by one.  Inserts work fine if I don't set this ApplicationID as a primary key, but I set it as a primary key inserts will not happen, there is no error so I'm a bit stumped. Any ideas?
Here is an extract of my SQL statement: -
strsqlinsert = "INSERT INTO YM_WEB_APPLICATIONS ( "strsqlinsert += "Surname, Forenames, Title, DOB, EmailAddress, Gender, EmergencyContactName, EmergencyContactNumber, "strsqlinsert += "Convictions, ConvictionsList, NationalIdentity, NationalIdentityOther, Ethnicity, EthnicityOther, FirstLanguage, FirstLanguageOther, PreferredLanguage, SpeakWelsh,"strsqlinsert += "HouseNoStreet, Locality, Town, County, Postcode, HomeTelephoneNumber, MobileTelephoneNumber,"strsqlinsert += "Qualifications, SchoolCollege1, From1, To1, SchoolCollege2, From2, To2, Employment,"strsqlinsert += "Disabilities, MedicalCondition, SpecialArrangements,"strsqlinsert += "Marketing, MarketingOther,"strsqlinsert += "InterviewSlot"strsqlinsert += ")"strsqlinsert += " VALUES ("strsqlinsert += "@Surname,@Forenames,@Title,@DOB,@EmailAddress,@Gender,@EmergencyContactName,@EmergencyContactNumber,"strsqlinsert += "@Convictions,@ConvictionList,@NationalIdentity,@NationalIdentityOther,@Ethnicity,@EthnicityOther,@FirstLanguage,@FirstLanguageOther,@PreferredLanguage,@SpeakWelsh,"strsqlinsert += "@HouseNoStreet,@Locality,@Town,@County,@Postcode,@HomeTelephoneNumber,@MobileTelephoneNumber,"strsqlinsert += "@Qualifications,@SchoolCollege1,@From1,@To1,@SchoolCollege2,@From2,@To2,@Employment,"strsqlinsert += "@Disabilities,@MedicalCondition,@SpecialArrangements,"strsqlinsert += "@Marketing,@MarketingOther,"strsqlinsert += "@InterviewSlot"strsqlinsert += ")"strsqlinsert += "; SELECT SCOPE_IDENTITY() ; "

View 3 Replies View Related

SQL Server 2005: TRIGGER AFTER INSERT

Mar 22, 2006

Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/

View 2 Replies View Related

SQL Server 2005 - BULK INSERT

Sep 4, 2006

I'm trying to import data from flat file in table and have fewproblems.1.Field Delimiter is ',' (comma). If ',' occurs in quotedstring it is still treated as field delimiter. This is BUG or ?2.In table I have datetime field that can be null, but bulkinsert reports error if in flat file is null or ''. It's OK only whenreal date is specified.Table:create table AttachmentList (Code integer not null,ClassID integer null,Description varchar(200) null,ValidUntil datetime null,constraint PK_ATTACHMENTLIST primary key (Code))flat file.1,13,'Naputak, CU 261098', ''Thanks in advanceDavor

View 3 Replies View Related

Using Bulk Insert With SQL Server 2005

Aug 29, 2006

I have a web page that prompts a user to select a csv file. Using a Bulk Insert the data is loaded into a SQL Server 2005 table.

I have been using the Bulk Insert with SQL Server 200 with no problems, but with 2005 I am getting the error "You do not have permission to use the bulk load statement".

My web.config file has the following connection string:
[code]
<add key="connectionString" value="Server=(local);Database=BroadCastOne;trusted_connection=true" />
[/code]

I've given bulkAdmin role to the ASPNET user. It's still not working. What am I doing wrong?

Any help is greatly appreciated,
Ninel

View 3 Replies View Related

Exporting/Importing Database Data (Sql Server 2005 To Sql Server 2005)

Nov 5, 2007

I'm really new to the whole database deal (as well as VB.net) - specifically with the capabilities surrounding VB and SQL Server 2005. My question is open to any recommendations...

What I have is an application that a user uses to create 'new' products. They are presented a form to enter the information regarding the product they wish to create. They enter the details of the product and also locate an image that represents the product, too. Currently, this application saves the product information (including binary image data) into a SQL Server 2005 Express Edition database. This application and database reside on a client pc. What I need to do is to be able save updated and newly created product data into a file of some sort. That file will make its way to a memory stick (USB) and then be transported to a 'field' machine. Quite simply, what is the best way to do this? Are there walk-throughs on this sort of thing? The target database is also SQL Server 2005. I thought I'd post this question on here to get the best design ideas... Any help would be greatly appreciated.

~javasource

View 1 Replies View Related

Insert Image Into Table SQL Server 2005

Jun 27, 2007

I have a table tblImage with column ImageName as varchar(50)  and picture as Image
 I am trying to Insert picture I have in C: drive into table using the following code
Insert into tblImage (ImageName, Picture)
Select 'Dog' as ImageName, Bulkcolumn from OPENROWSET(BULK N'C:dog1.jpg', SINGLE_BLOB) as picture
 I am getting error message.
Can I insert image into table through query.
Thanks in advance.
 
 

View 3 Replies View Related

How To Break Insert Query In Sql Server 2005 In To Two...

Jan 15, 2008

 I am inserting a lengthy query through the source code here it contains one big record called  description which  has 3 paragraph length.....its showing error also.Is there any way to  break the query in  to two and  execute?

View 6 Replies View Related

In SQL SERVER 2005, How Can I Get The ID Of The Record I Just Insert To Table?

Feb 12, 2006

In SQL SERVER 2005, how can I get the ID of the record I just insert to table?
I defined a table MyTable, and I insert a record into the table using the SQL below
Insert into MyTable (Name) values ("User Name")
You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.How can I know the ID of the record I just insert to table?
 
CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](10) NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

View 3 Replies View Related

SQL Server 2005, INSERT INTO, Error Message

May 8, 2007

The following function I am trying to design is to INSERT data into a SQL Server 2005 database. It is called from a different function, parse_file();, which is looping through a .CSV file and returning each record as an array to recordInsert();. Here is the function code and SQL syntax


PHP Code:




function recordInsert($rs)


    $host = 'Driver={SQL Server}; SERVER=SERVER; DATABASE=DATABASE';
    $user = 'USER';
    $pass = 'PASS';
    $conn = odbc_connect($host, $user, $pass);  
    $query = "INSERT INTO db.table VALUES ( {$rs['MODEL']}, {$rs['PID']}, {$rs['YEAR']}, {$rs['TOTAL_COST']},
                                                    {$rs['PARTS_COST']}, {$rs['LABOR_COST']}, {$rs['PROBCD']}, {$rs['FAIL_PART']},
                                                    {$rs['FAIL_PART_NAME']}, {$rs['MILES']}, {$rs['PROBLEM']}, {$rs['CAUSE']},
                                                    {$rs['REMEDY']}, {$rs['CLAIM_DATE']}, {$rs['APPRV_DATE']}, {$rs['FAIL_DATE']},
                                                    {$rs['SOLD_DATE']}, {$rs['UNIT_AGE']}, {$rs['DEALER']}, {$rs['DEALER_NAME']},
                                                    {$rs['ST']}, {$rs['ZIP']}, {$rs['CLAIM']}, {$rs['BTC']}, {$rs['PART_2']},
                                                    {$rs['PART_3']}, {$rs['PART_4']}, {$rs['PART_4']}, {$rs['PART_5']},
                                                    {$rs['PART_6']}, {$rs['JOBCD1']}, {$rs['JOBCD2']}, {$rs['JOBCD3']},
                                                    {$rs['JOBCD4']}, {$rs['PRLN']}, {$rs['RUN_DATE']} );"; 

    $exec = odbc_exec($conn, $query);

    if(!$exec)
    { 
        echo "Error: " . odbc_error($exec) . "<br />
" . odbc_errormsg() . "<br />
";
    } 
    else
    { 
        echo "Executed.<br />
";
    } 


parse_file("file.csv", csv); 






I am receiving the following error when attempting to INSERT into the database.

Code:

[Microsoft][ODBC SQL Server Driver][SQL Server]The name "YFM700RVL" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.



I have tried googling the error and since it is a long error, it has been hard trying to find an answer. One solution I found (which wasn't a solution) was to add the '@' infront of the variables. It did not work.

Kick in the right direction?

Thanks.

View 2 Replies View Related

Insert Data From One Server To Another

Sep 17, 2007

Hi,

I need to insert and update data from one server to another. I have been searching in the internet and I have found some articles about OPENQUERY, OPENDATASOURCE and OPENROWSET.

To use OPENDATASOURCE and OPENROWSET I have to set the Ad Hoc Remote Queries €œon€?, in my pc this is fine because I have sql server 2005 and I just need to go to surface area configuration and set it to €œon€?. But for the company I works for the development server have SQL Server 2000. How do I turn this on in sql server 2000?

If there is a better way of doing this please tell me.

View 3 Replies View Related

SQL Server 2012 :: How To Insert Data Into Table From Linked Server

Nov 19, 2013

I wonder if it possible to move data from tables on a linked server to a "normal database"?

Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName

Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country

This is just a test setup. I figure that if I get this working the rest will be easier.

My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')

View 8 Replies View Related

Insert Values Into Both Table At The Same Time Using Sql Server 2005

Nov 26, 2007

hi all,
In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.
table 1:
tid--> int,identity,primary key
tname-->varchar(200)
table 2:
sid-->int,identity,primary key
tid-->fk (this tid is set as foreign key for the tid in table1)
now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..
pls help me..
thanks
swapna

View 3 Replies View Related

How To Insert More Number Of Rows Into Sql Server 2005 Database At Once.

Apr 14, 2008

Hi,
Good morning to all.My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once.
Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times.
Can anyone please help me on this...
Thanks in advance...Ashok kumar.

View 3 Replies View Related

MS SQL SERVER 2005 BUG?? Cannot Insert NULL Into Column Diagram_id

Jun 4, 2006

Hi friends,

when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.

Whats with this????

View 3 Replies View Related

Queue Insert Statement What Is The Best SQL Server 2005 Feature To Use?

Apr 4, 2007

Hello everybody, I'm not completly aware of the SQL server 2005 possibilities so I'd need an hints from somebody with a wide knowledge to understand the direction to take!

This is what I have to do.



I insert into a table XML message. the messages are pushed automatically by an application I have no ""control" on and I get several messages "at the same time".

Everytime the message is inserted into the database I need to trasform the XML data into the correspondent relational value and I know already that in some cases it could take a while (1 second can be considered a while..)
My worry is that in the moment I process one message I loose the other one inserted after ,,,


There is some tool that helps me to handte the process as I would..

I was looking into SQL service broker?

It can be the right choice?



Thank you for any help!!



Marina B.

View 1 Replies View Related

Not Able To Insert Record In SQL Server 2005 Express Edition

Jul 29, 2006

Hello,

In a web application of VS.Net 2005 I am able to insert records in both the database SQL 2000 & 2005

In a windows application of VS.Net 2005 I am able to insert record in SQl 2000 but I am not able to insert record in SQL 2005 Express Edition.

Please help Me I tried very Much but not succeded yet.

Thanks in Advance

View 6 Replies View Related

Insert Date Values From SQL Server 2005 To ORacle 10g

Nov 21, 2006

I am trying to insert records with a datetime field type from a SQL Server to an Oracle table using SSIS.

Any suggestions to get it to work?

View 1 Replies View Related

How To Insert Data Into Sql Server In Bulk Using ADO.net

Dec 27, 2007

Hi!
 I'm building a web application. I need to read data from a text or excel file and process the data and then store the result records into database. The record number is big. I can store the data record into database (SQL Server 2005) one at a time. I think it's slow. Is there any way to insert the data in bulk.
 
Thanks!
ccy

View 4 Replies View Related

Sql Server 7.0 Table Insert Data

Mar 21, 2000

If you are doing an insert into a table with 10 columns, but you only have values for 2, is it better to insert 2 and let the table default the others or is it better to insert all 10 using default values for the 8 you don't know? Do you know of any benefits one way or the other? Thanks in advance.

View 1 Replies View Related

Help Pls... Insert Data From Access To SQL Server

Jan 11, 2008

hi, can someone help me.. how to insert database from access database to sqlserver... please help..

Here is my codes below but it's not work....

Insert into magpatoc.dbo.RSOTransfer
Select * FROM ('Provider=Microsoft.Jet.OLEDB.4.0;',
'Data Source=c:CopyOfRSODB.mdb;User Id=admin;Password=;',
'SELECT * FROM FinalCustItemRSO')


ed9teenMagnaza

View 2 Replies View Related

Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source

Apr 6, 2006

Environment:
 
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
 
Problem & Info:
 
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
 
Desired Help:

 
How to perform
 
1)       stripping out all undesired rows
2)       importing each column into sql table

View 1 Replies View Related

Howto Insert A Null DateTime Into A Sql Server 2005 Database

Apr 2, 2008

I'm importing data form an Excel file to a Sql Server Database. Some of the data imported represents time as a double type so i convert the times into DateTime to be inserted into the database. The time values that aren't available in the Excel file are 0.. so what i want to do is insert null into the database for all the values that are 0 in the excel file... How do i do that based on this code i have so far:protected void ButtonImport_Click(object sender, EventArgs e){PanelUpload.Visible = false;PanelView.Visible = false;PanelImport.Visible = true;LabelImport.Text = "";OleDbCommand objCommand = new OleDbCommand();objCommand = ExcelConnection(); OleDbDataReader reader;reader = objCommand.ExecuteReader(); while (reader.Read()){DateTime? in_1 = null;DateTime? out_1 = null;DateTime? in_2 = null;DateTime? out_2 = null;   int emp_id = Convert.ToInt32(reader["emp_id"]);DateTime date_entry = Convert.ToDateTime(reader["date_entry"]);if (Convert.ToDouble(reader["in_1"]) != 0)in_1 = ConvertDoubleToDateTime((double)reader["in_1"]);if (Convert.ToDouble(reader["out_1"]) != 0)out_1 = ConvertDoubleToDateTime((double)reader["out_1"]);if (Convert.ToDouble(reader["in_2"]) != 0)in_2 = ConvertDoubleToDateTime((double)reader["in_2"]);if (Convert.ToDouble(reader["out_2"]) != 0)out_2 = ConvertDoubleToDateTime((double)reader["out_2"]); ImportIntoAttendance(emp_id, date_entry, in_1, out_1, in_2, out_2);} reader.Close();}protected void ImportIntoAttendance(int emp_id, DateTime date_entry, DateTime? in_1, DateTime? out_1, DateTime? in_2, DateTime? out_2){ SqlDataSource AttendanceDataSource = new SqlDataSource();AttendanceDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SalariesConnectionString1"].ToString();AttendanceDataSource.InsertCommandType = SqlDataSourceCommandType.Text;AttendanceDataSource.InsertCommand = "INSERT INTO Attendance (emp_id, date_entry, in_1, out_1, in_2, out_2) " +"VALUES ('" + emp_id + "', '" + date_entry + "', '" + in_1 + "', '" + out_1 + "', " +"'" + in_2 + "', '" + out_2 + "')"; int rowsAffected = 0;try{rowsAffected = AttendanceDataSource.Insert();}catch(Exception ex){LabelImport.Text += "<font color=red>" + ex + "</font><br />";} }private DateTime ConvertDoubleToDateTime(double dbTime){string[] SplitTime = dbTime.ToString().Split('.');string hours = SplitTime[0];string minutes = String.Empty;string time = String.Empty; if (dbTime.ToString().IndexOf('.') != -1){if (SplitTime[1].Length >= 1){if (SplitTime[1].Length == 1)minutes = Convert.ToString(Convert.ToDouble(SplitTime[1]) * 10);else if (SplitTime[1].Length > 1)minutes = SplitTime[1];}}elseminutes = "00";time = hours + ":" + minutes;return Convert.ToDateTime(time);}

View 3 Replies View Related

Insert Statement For Junction Table On Many To Many Relationship Ms Sql Server 2005

Oct 10, 2007

Hello,

This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...

http://msdn2.microsoft.com/en-us/library/ms178043.aspx

At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).

http://msdn2.microsoft.com/en-us/library/ms189098.aspx

and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....

Here is how I set everything up...

PetitionSet table consists of:

PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls


the Petition table consists of:

PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls


the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int

And, there is a composite key made up of both the PetitionSetID and PetitionID fields.

I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.


The trigger is on the Petition table and it has the following code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID

END

I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...

The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.

Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...

Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.



David

All Rights Reserved in All Media







View 3 Replies View Related

INSERT INTO OPENROWSET Does Not Respect ORDER BY Clause On SQL Server 2005 EE

Jul 3, 2007

Hi,
I need to pass data from a SQL Server data base to an Access data base. To do this I use the OPENROWSET as followed:
FR


INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Aux.mdb'; 'Admin'; '',Test) (Id, Name, TypeId) SELECT Id,Name,TypeId
FROM Test
ORDER BY TypeId


FR

On SQL Server 2000 or MSDE the data is transfered as expected, respecting the specified order. But when I run the same clause on a SQL 2005 EE the data is transfered, but the order is not respected.
So my question is if I have to activate an option for the order to be respected or if this is a bug.

Best regards,
Ângelo Vilela

View 4 Replies View Related







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