Problems With Insert Query And Primary Key

Nov 27, 2007

I am trying to run an insert query off of a sql datasource and I am erroring out.  My code and stored procedure as of now are listed below.  You will notice the section of the stored procedure that is pulling the value for facility_ID (primary key).  I have also tried to pull these and pass the parameter from a label, but that does not work, giving an error that the stored procedure expects the parameter @Facility_ID which was not supplied.  One other odd thing is that stepping through the code, I watched the parameter count total 21, but when running the insert command, the insert parameter count shows 20.  With the code below (my current project), I get an error that null values can not be entered for facility_ID.  Please help.
 
CODE:

         Dim myConnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FacilitiesBuild").ConnectionString)

        'open myconnection

        myConnection.Open()



        Dim myCommand As New Data.SqlClient.SqlCommand(SqlDataSourceFac.InsertCommand, myConnection)

        myCommand.CommandType = Data.CommandType.StoredProcedure



        myCommand.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50).Value = CType(Me.DetailsView1.FindControl("Textbox5"), TextBox).Text
        myCommand.Parameters.Add("@Address1", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox3"), TextBox).Text
        myCommand.Parameters.Add("@Address2", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox4"), TextBox).Text
        myCommand.Parameters.Add("@State", Data.SqlDbType.VarChar, 2).Value = CType(Me.DetailsView1.FindControl("Textbox17"), TextBox).Text
        myCommand.Parameters.Add("@Zip", Data.SqlDbType.VarChar, 10).Value = CType(Me.DetailsView1.FindControl("Textbox6"), TextBox).Text
        myCommand.Parameters.Add("@Phone", Data.SqlDbType.VarChar, 14).Value = CType(Me.DetailsView1.FindControl("Textbox7"), TextBox).Text
        myCommand.Parameters.Add("@Admin_Name", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox8"), TextBox).Text
        myCommand.Parameters.Add("@Comments", Data.SqlDbType.VarChar, 250).Value = CType(Me.DetailsView1.FindControl("Textbox10"), TextBox).Text
        myCommand.Parameters.Add("@Owner", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeOwnerInsert"), Label).Text
        myCommand.Parameters.Add("@Beds", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("BedsInsert"), Label).Text
        myCommand.Parameters.Add("@Population", Data.SqlDbType.NText).Value = CType(Me.DetailsView1.FindControl("PopulationInsert"), Label).Text
        myCommand.Parameters.Add("@Type_Facility", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeFacilityInsert"), Label).Text
        myCommand.Parameters.Add("@Type_Other", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("TypeOtherInsert"), Label).Text
        myCommand.Parameters.Add("@Profit", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ProfitInsert"), Label).Text
        myCommand.Parameters.Add("@Religious", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ReligiousInsert"), Label).Text
        myCommand.Parameters.Add("@Licensed", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("LicensedInsert"), Label).Text
        myCommand.Parameters.Add("@Active", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ActiveInsert"), Label).Text
        myCommand.Parameters.Add("@City_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertCityLabel"), Label).Text
        myCommand.Parameters.Add("@Agency_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertAgencyLabel"), Label).Text
        myCommand.Parameters.Add("@County", Data.SqlDbType.NVarChar, 3).Value = CType(Me.DetailsView1.FindControl("InsertCountyLabel"), Label).Text
        myCommand.Parameters.Add("@Facility_ID", Data.SqlDbType.VarChar, 6).Value = CType(Me.DetailsView1.FindControl("InsertFacilityLabel"), Label).Text

        If CType(Me.DetailsView1.FindControl("DropDownList1"), DropDownList).SelectedItem.Text = "Please Select One" Then
            MsgBox("You must select an agency")
        Else : SqlDataSourceFac.Insert()
        End If
 
STORED PROCEDURE:
 
ALTER PROCEDURE [dbo].[SP_OMBFacilityAddDOTNET]

   
    @Name            varchar(50),
    @Address1        varchar(40),   
    @Address2        varchar(40),
    @State             varchar(2),
    @Zip             varchar(10),
    @Phone            varchar(14),
    @Admin_Name     varchar(40),
    @Comments         varchar(250),
    @Owner            char(1),
    @Beds            int,
    @Population        numeric(10,0),
    @Type_Facility    char(1),
    @Type_Other        varchar(40),
    @Profit            char(1),
    @Religious        char(1),
    @Licensed        char(1),
    @Active            char(1),
    @City_ID        int,
    @Agency_ID        int,
    @County            nvarchar(3)

AS
BEGIN
DECLARE @Facility_ID varchar(6)
    DECLARE @nextID varchar(3)

    /* get next facilityID */
    SELECT @nextID = MAX(RIGHT(Facility_ID, LEN(Facility_ID)-(CHARINDEX('-', Facility_ID)))) + 1
    From OMBFacility
    Where Agency_ID = @Agency_ID

    SELECT @Facility_ID = CAST(@Agency_ID AS varchar(2)) + '-' + RIGHT('000' + RTRIM(@nextID), 3)
    INSERT INTO [AIMS].[dbo].[OMBFacility]
               ([Name],
                [Address1],
                [Address2],
                [State],
                [Zip],
                [Phone],
                [Admin_Name],
                [Comments],
                [Owner],
                [Beds],
                [Population],
                [Type_Facility],
                [Type_Other],
                [Profit],
                [Religious],
                [Licensed],
                [Active],
                [City_ID],
                [Agency_ID],
                [County],
                [Facility_ID])
               
         VALUES
               (@Name
               ,@Address1
               ,@Address2
               ,@State
               ,@Zip
               ,@Phone
               ,@Admin_Name
               ,@Comments
               ,@Owner
               ,@Beds
               ,@Population
               ,@Type_Facility
               ,@Type_Other
               ,@Profit
               ,@Religious
               ,@Licensed
               ,@Active
               ,@City_ID
               ,@Agency_ID
               ,@County
               ,@Facility_ID)

END
 

View 11 Replies


ADVERTISEMENT

How To Insert A Primary Key???

Sep 8, 2006

Resolved - thank you.

View 1 Replies View Related

Getting Primary Key Id On Insert

Feb 1, 2007

i have the following code in visual studio 2005 using VB
it is running an insert query - this works fine but i want to know how can i get the primaty key value(which is auto generated) of the row that i just inserted...

Dim conn As New SqlConnection(My.Settings.connStr)

conn.Open()

Dim sql As String = "INSERT INTO tblProspect (Prspct_FirstName, Prspct_LastName, Prspct_PropIDPrimary, Prspct_PropIDSecondary, Prspct_ApplicationStatus, Prspct_DateSubmittedOn, Prspct_PrimaryRent, Prspct_SecondaryRent, Prspct_MoveInDate) VALUES ('" & Me.txtFName.Text & "','" & Me.txtLName.Text & "','" & Me.cmbPrimary.SelectedValue & "','" & Me.cmbSecondary.SelectedValue & "','Pending','" & Now & "','" & Me.txtPrimRent.Text & "','" & Me.txtSecRent.Text & "','" & Me.dtMoveIn.Value & "')"

Dim cmd As New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()



i want to get the Prspct_Id which is the primary key of the row that i just inserted..

thanks

View 5 Replies View Related

After An Insert, How Do I Get The Primary Key Of The New Row?

Jul 10, 2006

I am using C# and ADO.NET

After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @@IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @@IDENTITY'."}. does anyone know what to do?

View 8 Replies View Related

Returning Primary Key From Insert Sp?

Apr 28, 2007

Hi,I've got a stored procedure that's inserting data into a sql database fine. The only problem is that I'm not sure how to read back the value of the auto increment field that was just generated by the insert (e.g the id field). Any help appreciated.   

View 1 Replies View Related

Insert New Record - Primary Key

Jan 12, 2008

Hi
I have a table in sql server with a numeric field as Primary Key. When i insert a new record i need that primary key increments automatic (like access  auto increment) because i want avoid the possibility of duplicate Primary Keys.
Is that possible?
Thank you

View 2 Replies View Related

Insert And Return The Primary Key

Mar 3, 2005

Im trying to add a record to the DB and then get the primary key for that record. Im doing this but is obviously wrong....


Code:

// set the prepared statement
String sql="INSERT INTO Client(username, country, clientIP, browser, os) VALUES(?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setString(1, inUserName);
pstmt.setString(2, inCountry);
pstmt.setString(3, inClientIP);
pstmt.setString(4, inBrowser);
pstmt.setString(5, inOS);

// Insert the row
pstmt.executeUpdate();

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT SCOPE_IDENTITY()");

System.out.println("Result "+rs);




Any advice??

View 2 Replies View Related

Bulk Insert Does Not Allow Primary Key

Apr 25, 2014

I am using the bulk insert statement below to import data from a csv and excel files. As I am running 64bit versions of windows, and could not find any sql import statements.

However I noticed it does not allow any primary key, hence after importing to the temp table, I then import in sql to the main table.Is there anyway to fix the temp table ? ie add a PK value

BULK INSERT CSV_TESTING
FROM 'd:MAM-NAP.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
)

View 2 Replies View Related

Package Insert Primary Key

Nov 7, 2007

Hi,

I am new to SSIS and still trying to design my first package.
My package doesn't seem to want to complete when I have set an indentity field with an identity seed in my table as a primary key.

If the field is not a primary key, the package runs fine.

Any ideas how to insert records when there is a primary key in the table?

In another instance, I am actually trying to fill the primary key with a value from flat file and that also does not work.

Any ideas would be appreciated. Thanks.

View 7 Replies View Related

INSERT Violates Primary Key. Which Way Out?

Jul 23, 2005

Hi,newbie question. I have an plain INSERT INTO clause:BEGIN TRANINSERT INTO BILLSSELECTBillCode,MyUNIDFROM DPA_BILLSWHERE ErrorCode IS NULLCOMMIT TRANThe original DPA_BILLS table can hold (and actually holds) rows withnon-unique values of BillCode, which is primary key in the destinationBILLS table. An acceptable behaviour would be to update the existingrow. Given that these constraints have to be kept, which is the bestway to act? Shall I process in advance my source table, resolvingforeing key conflicts, or shall I rely on some error handling in theINSERT clause?Thanx

View 2 Replies View Related

Help With Simple Insert, How To Use Primary Key?

Sep 12, 2006

Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.

i want an unique identifier for each row.

heres my stored procedure:

 
CREATE PROCEDURE composeMessage
-- Add the parameters for the stored procedure here
@username varchar(24),
@sender varchar(24),
@date dateTime,
@subject varchar(255),
@message varchar(2500)
 
 
AS
BEGIN
 
insert into Messages(
"Username",
"Sender",
"Date",
"Subject",
"Message"
)
values (
@username,
@sender,
@date,
@subject,
@message
)
END
GO
 
 
 
 
 
 
heres my sqlcreate table:
 
USE [Messenger]
GO
/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Messages](
[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[ID] [int] NOT NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
 
 
 
 
 
 
 
 
 
As primary keycan't be null, what do i put for primary key for my insert to work?
 
hope you understand what i mean?

View 3 Replies View Related

Insert Problem With Primary Key

Oct 18, 2007

Hi there,

I wrote a script to copy data from server1(epxress) to server2(2005 standard)


SET identity_insert results on

GO

insert [server2].TEST.dbo.results

select * from [server1].TEST.dbo.results


results table are identical with primary keys.

Problem is when run this code it gives an error


Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'dbo.results' can only be specified when a column list is used and IDENTITY_INSERT is ON.


I need a help to set it so when it tries to copy the existing row (same primary key), it ignores it and moves on to the next row..

Thanks guys!

View 11 Replies View Related

Primary Key Error With INSERT INTO

Sep 10, 2007

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?


INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

View 14 Replies View Related

How To Insert Into A Table With A Uniqueidentifier As Primary Key?

Jun 28, 2006

I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId
My VB code is this.
Protected Sub btncreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btncreate.Click
'set connection string
Dim errstr As String = ""
Dim conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")
'set parameters for SP
Dim cmdcommand = New SqlCommand("sprocInsertNewReport", conn)
cmdcommand.commandtype = CommandType.StoredProcedure
cmdcommand.parameters.add("@UserName", Session("UserName"))
cmdcommand.parameters.add("@Week", vbNull)
cmdcommand.parameters.add("@Date", vbDate)
cmdcommand.parameters.add("@StartTime", vbNull)
cmdcommand.parameters.add("@EndTime", vbNull)
cmdcommand.parameters.add("@HeatTicket", vbNull)
cmdcommand.parameters.add("@Description", vbNull)
cmdcommand.parameters.add("@TakenAs", vbNull)
cmdcommand.parameters.add("@Dinner", vbNull)
cmdcommand.parameters.add("@Hours", vbNull)
cmdcommand.parameters.add("@Rate", vbNull)
cmdcommand.parameters.add("@PayPeriod", vbNull)
cmdcommand.parameters.add("@LastSave", vbNull)
cmdcommand.parameters.add("@Submitted", vbNull)
cmdcommand.parameters.add("@Approved", vbNull)
cmdcommand.parameters.add("@PagerDays", vbNull)
cmdcommand.parameters.add("@ReportEnd", vbNull)
Try
'open connection here
conn.Open()
'Execute stored proc
cmdcommand.ExecuteNonQuery()
Catch ex As Exception
errstr = ""
'An exception occured during processing.
'Print message to log file.
errstr = "Exception: " & ex.Message
Finally
'close the connection immediately
conn.Close()
End Try
If errstr = "" Then
Server.Transfer("TimeSheetEntry.aspx")
End If
My SP looks like this
ALTER PROCEDURE sprocInsertNewReport

@UserName nvarchar(256),
@Week Int,
@Date Datetime,
@StartTime Datetime,
@EndTime DateTime,
@HeatTicket int,
@Description nvarchar(max),
@TakenAs nchar(10),
@Dinner Nchar(10),
@Hours Float,
@Rate Float,
@PayPeriod int,
@LastSave Datetime,
@Submitted Datetime,
@Approved DateTime,
@PagerDays int,
@ReportEnd DateTime
AS
INSERT INTO
ReportDetails
(
rpUserName,
rpWeek,
rpDate,
rpStartTime,
rpEndTime,
rpHeatTicket,
rpTicketDescription,
rpTakenAs,
rpDinnerPremium,
rpHours,
rpRate,
rpPayPeriod,
rpLastSaveDate,
rpSubmittedDate,
rpApprovedDate,
rpPagerDays,
rpReportDueDate
)
VALUES
(
@Username,
@Week,
@Date,
@StartTime,
@EndTime,
@HeatTicket,
@Description,
@TakenAs,
@Dinner,
@Hours,
@Rate,
@PayPeriod,
@LastSave,
@Submitted,
@Approved,
@PagerDays,
@ReportEnd
)
RETURN
Any Ideas?
thx!

View 7 Replies View Related

INSERT Data Into Table That Maybe Have That Primary Key Already

May 12, 2007

Hi, I'm not user to inserting data into databases, usually I just read the data.  So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names.  I set the primary key to be the columns longitude and latitude.   I have a method that generates the user's location and the mentioned data.  So I want to only insert the new data into the database if it is new and unique.  currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inserting duplicate primary key information.  Do I need to query the database to see if the data record already exists?  or is there a way to insert the record only if it is "new"?? Thanks for the help!! 

View 2 Replies View Related

How To Insert Primary Keys Without Using Identity

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

Trigger : Insert Maximum Primary ID

Mar 15, 2005

Hi,

via VBScript, I am inserting data into one table as below:
Code:

conn.Source = "INSERT INTO img (imageDesc,imageName,imageDate,imageUser,imageIP) VALUES ('"& ni &"','"& fn &"','"& Now() &"',"& Session("MM_UserID") &",'"& Request.ServerVariables("REMOTE_HOST") &"')"

In another table, I want to insert the primary key (imageID) of this newly inserted row into a new table called "t_image_Site" along with another value in another column.

Any advice/tutorials... this can be done with a trigger if I'm not mistaken?

JJ

View 4 Replies View Related

T-SQL (SS2K8) :: How To Insert Or Update Primary Key (int)

May 22, 2014

since ten years perhpas the society has a database with some table (person, country, and so on)since last years , the project manager decides to create a new database with some new design because before some information was save in one table well, the problem is when we pass all application to the new model we've notice that some id are not the same !

sample :

table Language old model
-------------------------
id frenchDescription english description
----------------------------------------
3 Anglais English
.....
......

table Language new model

id frenchDescription english description
-----------------------------------------
5 Anglais English
.....
.....

Alright , you can understand that the new model must be the same id => 3

my question is how to modify id on the table ?

View 4 Replies View Related

INSERT A Row If Primary Key Does Not Exist But APPEND If It Does

Jan 14, 2015

I am trying to come up with one statement that will INSERT a row if the primary key does not exist but APPEND if it does

Would the following statement be correct?

INSERT INTO Products (a,b,c) VALUES (2, Cotton Socks, $12) ON DUPLICATE KEY (b=Cotton Socks), (C=$12)

where a is the primary key?

View 3 Replies View Related

Insert Records Into Gaps In Primary Key

Feb 23, 2006

Hi to all,
I'm a new member here and i would like to ask for some help regarding my problem. first i ahve an incremental primary key with format to something like this: 001-01-001, 001-01-002, 001-01-003, etc. My problem is that i want to insert (supply) the 'missing' or 'gaps' in my primary key field like for example: ..., 001-01-067, 001-01-068, 001-01-070. i want to insert the value 001-01-069 after the record 001-01-068. I have several gaps some ranging from several numbers like 005-04-007,005-04-020 which has a 13 records gap. Is there a way for stored procedure to solve this one?Thanks in advance.

View 8 Replies View Related

Insert Into Table-Primary Key Error

Jul 20, 2005

I'm trying to do multiple insert statements. The table looks likethis:CREATE TABLE $table (CNTY_CNTRY_CD char(3),ST char(2),CNTY_CNTRY_DESCR varchar(50),CNTY_CNTRY_IND char(1),HOME_CNTRY_IND char(1),CONSTRAINT cnty_key PRIMARY KEY (CNTY_CNTRY_CD, ST))I'm using 2 fields for the primary key constraintMy insert statement looks like this:INSERT INTO $table(CNTY_CNTRY_CD,ST,CNTY_CNTRY_DESCR)VALUES(?,?,?)I've been through the list of values and none have both the sameCNTY_CNTRY_CD and ST and yet, this is the error message I'm getting:DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQLServer]Violation of PRIMARY KEY constraint 'cnty_key'. Cannot insert duplicatekey in object 'event_CNTY_CNTRY_CD'. (SQL-23000)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has beenterminated.. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)Why is it looking for unique in just the one column instead ofreferencing both? What do I need to do to get this to work? Help!

View 1 Replies View Related

Return Primary Key On INSERT Statement

Jul 20, 2005

I am inserting a record into a table that automatically generatesunique ids (i.e. Primary Key). Is there anyway to return this id. As Iam using this on ASP.net page and I really need the ID to update thepage with the new details.I think on mysql there is something called LAST_INSERT_ID which doesthis.

View 10 Replies View Related

Insert Values In Primary Key Field

May 15, 2006

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

View 1 Replies View Related

Catching Primary Key Violation On Insert Error

Aug 9, 2007

I've read a few different articticles wrt how the handle this error gracefully. I am thinking of wrapping my sql insert statement in a try catch  and have the catch be something likeIF ( e.ToString() LIKE '% System.Data.SqlClient.SqlException:
Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key
in object %'){lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate" } Is there a better way?TIA Dan 

View 4 Replies View Related

Insert Into Relational Table Primary Key From Parent

Sep 22, 2007

Hello, I have a Stored Procedure which insterts into Orders table. Orders table is the parent table, with primary key OrdersID. I also have a child table, Client, with foreign key OrdersID. I want it to insert the data into the orders table, and at the same time insert the OrdersID into the FK of the child table. Any info would be appreciated. I have no idea how to do it.
My SP is as follows:ALTER PROCEDURE dbo.jobInsert
@ClientFileNumber varchar(50),@Identity int OUT
 
 
ASINSERT Orders(ClientFileNumber, DateTimeReceived) VALUES(@ClientFileNumber, GetDate())
 SET @Identity = SCOPE_IDENTITY()
 
 
 
 
RETURN

View 4 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

Hi,

I am making a program in Visual Basic .NET with SQL Server 2000.

I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...

I must do one of them, but don't know how either of them:

-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.

or

-get the command in Visual Basic that will send the Insert command with a return field ("Id")

Thanks in advance,
Sebastien Anselmo

View 6 Replies View Related

Insert Missing Primary Key Utilized By Another Table

May 6, 2015

I am converting an old Access/Forms application to a .Net/SQL intranet site. The data was imported and most CRUD features are working. But, I have some data issues to contend with.For this example I have two tables, Cases and Parties. The Parties table includes a Column ReportID, which happens to reference the primary key of some unique Case. Lets say Bob, Sally, and Mary are all associated with ReportID = 2.Looking at the Case table, I see indexes (ReportID) 1,3,4,5, such that Bob, Sally, and Mary are orphaned with out a case to reference.

1. Is it possible to bypass the auto-increment for the primary key of Cases and manually inject the missing Case (ReportID) with a specific primary key of 2? See my image to fill in some of the details.It is too early to tell but perhaps 10% of the Parties are orphan without an associated ReportID in the Case table. This fact is one of the reasons it was decided to rebuild the application, which was also never able to produce any reports.

2. What is an easy way to find all the orphan Parties?

I suppose I could export the Cases table to excel, add the missing Rows, and then overwrite the defective Cases table data with the corrected data. I would rather learn to fix the data issues using the responses from this request.

View 7 Replies View Related

Transact SQL :: Incremental Primary Key Insert From Excel To DB?

Jul 6, 2015

I have a table Employee which has following values:

Employee ID Employee Name
1                     A
2                     B
3                     D

Now I have a excel file which will have the new data and I have to write a stored procedure to insert data from Excel file to this table.

Where Employee Id should be auto calculated and the value shud insert from 4.

View 9 Replies View Related

Insert Or Update SSIS For Composite Primary Key

Sep 1, 2006

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

View 8 Replies View Related

SQL Server 2012 :: Insert Foreign Key Value Into Primary Table?

Oct 2, 2015

In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitiions:

CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[lockID] [int] NULL
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED

[code]....

The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.

Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.

I have sql that looks like the following so far:

declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber

I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables?

View 7 Replies View Related

Transact SQL :: 2012 / Insert Foreign Key Value Into Primary Table?

Oct 2, 2015

In a special request run, I need  to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitions:

CREATE TABLE [dbo].[Locker](
 [lockerID] [int] IDENTITY(1,1) NOT NULL,
 [schoolID] [int] NOT NULL,
 [number] [varchar](10) NOT NULL, 
 [lockID] [int] NULL 
 CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED

[Code] ....

The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.

Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows.  I would then like to take  value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.

I have sql that looks like the following so far:

declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
 FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID,  A.lockerNumber

I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?

View 7 Replies View Related

Stuck Between Cannot Insert The Value NULL Into Column 'ID' And Violation Of PRIMARY KEY Constraint

Jan 20, 2004

Cannot find an answer to this in previous posting, though there are similar topics.

My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:

Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.

However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:

Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.

Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.

Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.

Please help!

View 5 Replies View Related

SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?

Oct 31, 2015

Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?

For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.

View 3 Replies View Related







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