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.
Is there a way to get asp.net to return the primary key in SQLServer after INSERT as I need to re-name an uploaded file with the primary key. Thanks.
View 10 Replies View RelatedI would like to return the the Primary Key of the row altered from this query - what changes do I need to make?
UPDATE Members SET LastLog = @time WHERE UserName=@user AND Password=@pass;
Thanks in advance,
I'm using SQL-MSDE and have a table defined with a 'identity seed' column that automatically gets assigned when a record is added (I do not load this value). This column is also my KEY to this table. I'm using INSERT to add a record. Is there a way to return this KEY value after doing the INSERT?
View 4 Replies View Relatedso I know how to use tsql to print a value in just the query, but when I insert a column, I want it to return the value of the ID that was just created. I don't know how to do this and send the value back to asp.net so that I can move on with that value. Can any body help me with this? this is what I have so far. thanks for the help!@title varchar(40) = null,
@intro varchar(max) = null
AS
if @title is null
begin
print 'Title Cannot Be Null'
return 0
end
if @intro is null
begin
print 'Intro Cannot Be Null'
return -1
end
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Issue
(Title, Intro)
VALUES
(@title, @intro)
return SCOPE_IDENTITY()
Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it.
The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false".
When the page is viewed, I insert a record into the dB:
Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;")
If Not IsPostBack Then
AddTrack()
End If
End Sub
Sub AddTrack()
Dim myCommand As SqlCommand
Dim insertTrack As String
insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn)
myCommand.Connection.Open()
Try
myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack
Catch ex As SqlException
tempTxt.Text = ex.Number.ToString()
End Try
myCommand.Connection.Close()
End Sub
And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1".
The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
I am trying to insert a record to based on the source below, however the GUID of GiftOcc_ID is being returned as zero's so the first record can be added but as it is defined as the primary Key and uniqueidentifier the next record fails with a duplicate entry. Basically how do I ensure that the GUID is created and not nulls. As you can see I am trying to use Newid() which I have inserted as a default value but that does not work. Also as it is a unique identifier the "is identity" is not available
Protected Sub CreateGiftOccasion(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim null As New Nullable(Of Integer)
Dim da As New DataSet2TableAdapters.Gift_OccasionTableAdapter
Dim GiftOcc_ID As Guid
da.Insert(newid(), Occ_Type_Text.Text, Occ_Desc_Text.Text, Calendar1.SelectedDate, Calendar2.SelectedDate, 1)
Catch ex As Exception
Response.Write("Ooops")
End Try
End Sub
The code from the table adapter regarding inserts is:
<InsertCommand>
<DbCommand CommandType="Text" ModifiedByUser="False">
<CommandText>INSERT INTO [dbo].[Gift_Occasion] ([GO_ID], [Go_Type], [GO_Description], [GO_DateOn], [GO_DateOff], [GO_Active]) VALUES (@GO_ID, @Go_Type, @GO_Description, @GO_DateOn, @GO_DateOff, @GO_Active);
SELECT GO_ID, Go_Type, GO_Description, GO_DateOn, GO_DateOff, GO_Active FROM Gift_Occasion WHERE (GO_ID = @GO_ID)</CommandText>
<Parameters>
<Parameter AllowDbNull="False" AutogeneratedName="" DataSourceName="" DbType="Guid" Direction="Input" ParameterName="@GO_ID" Precision="0" ProviderType="UniqueIdentifier" Scale="0" Size="0" SourceColumn="GO_ID" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@Go_Type" Precision="0" ProviderType="Text" Scale="0" Size="0" SourceColumn="Go_Type" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@GO_Description" Precision="0" ProviderType="Text" Scale="0" Size="0" SourceColumn="GO_Description" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@GO_DateOn" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="GO_DateOn" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="DateTime" Direction="Input" ParameterName="@GO_DateOff" Precision="0" ProviderType="DateTime" Scale="0" Size="0" SourceColumn="GO_DateOff" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
<Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@GO_Active" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="GO_Active" SourceColumnNullMapping="False" SourceVersion="Current">
</Parameter>
</Parameters>
</DbCommand>
</InsertCommand>
Many thanks in advance for any assistance
Help! I am importing a large comma delimited text file into an existing table useing the BULK INSERT command. The table is 4 colums (char16, char16, varchar50, char1). The first 100 or so lines go in without an error. then, I recieve an error stateing that an entry is too long for the field in the database, and kicks me out. The entry is 50 characters, which is allowed. Any ideas why this would happen?
View 1 Replies View RelatedI have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration. Thank you in advnace for any help on this! INSERTrequest
(
emp_id,
request_submit_date,
request_start_date,
request_end_date,
request_duration,
request_notes,
time_off_id
)
Select@emp_id,
GETDATE(),
@request_start_date,
@request_end_date,
1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date),
@request_notes,
@time_off_id
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob
INsert into b values(1,1,2,2)
select 0
(this is a cut up version of the sp just to show about the way it is formated)
Please help.
SQL Version: SQL2014
PROBLEM: The SQL insert trigger code below is returning incorrect results. In some cases the results returned are from entirely different fields than those specified as the source field in the SET statement. For instance the value returne for the Price_BeforeAdj field does not = 20000000? It returns a NULL. See code below.
OFFENDING CODE:
ALTER TRIGGER [dbo].[xcti_WIPAdjustments_I]
ON [dbo].[budxcWIPAdjustments]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
UPDATE budxcWIPAdjustments
[Code] ....
Resolved - thank you.
View 1 Replies View Relatedi 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
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?
I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_db
GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST
USE dv_wrp_test
[Code] ....
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4)
drop procedure dbo.spdv_AVAUpdate
[Code] ....
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
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
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??
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 = '
)
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.
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 RelatedIve 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?
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!
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];
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!
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 RelatedI 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)ASBEGINDECLARE @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 View RelatedI 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
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
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 ?
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?
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.
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 RelatedI 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