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
ADVERTISEMENT
Jan 11, 2007
Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
View 1 Replies
View Related
Jul 23, 2004
Hi,
Im really new to MS SQL - actually I just have a schema that I need to convert to Postgresql format. The MS SQL database already exists - I need to use a Postgresql web database to hold part of the master database's information.
So, I've converted most of the "CREATE TABLE" schema, but right at the end it says:
Code:
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have NO idea what the ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] bit means. Can someone please explain to me? And what part this would play in my Postgresql conversion?
Ive been reading up a lot and find that the ON [PRIMARY] bit is pretty much standard with all tables, and TEXTIMAGE_ON [PRIMARY] is used a lot as well. But I'd just like to find out the meaning.
So far I'm presuming I'd just have to delete that line since it doesn't apply to Postgresql. Am I right?
Also, there was no primary key in the schema.. how does MS SQL identify the primary key for a table? I thought that every table needs at least one primary key ?
Thanks a lot in advance!
View 1 Replies
View Related
Dec 15, 2014
What is the simple way to remember Primary and foreign key?
Why is the ID column in a table declared as a integer datatype?
View 2 Replies
View Related
Mar 7, 2006
Hi,
how do I perform a simple insert command?
I dont need the SQL syntax, just how do i do it using VS2005.
using SqlDataSource, for some reason, after writing the insert sentence, the "next" button remains gray.
i am using VS2005 and sql server.
Thank you.
View 2 Replies
View Related
Mar 30, 2006
I am trying to do a simple insert 3 items into 3 colms the table has 4 the 4th is an identity with a seed of 1
I am getting this error that i need an explicit val for the key
TABLE SCRIPT :
GO
/****** Object: Table [dbo].[Company] Script Date: 03/30/2006 17:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Company](
[idCompany] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Abreviation] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Sol] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[idCompany] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--SHOULDNT NEED THIS :
--SET IDENTITY_INSERT dbo.Company ON
INSERT INTO COMPANY
VALUES('AmericanExpressCheck','Amex','ax')
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
View 1 Replies
View Related
Feb 13, 2007
Hello everyone:
From the help files in sql 2000:
INSERT INTO archivetitles
(title_id, title, type, pub_id)
SELECT title_id, title, type, pub_id
FROM titles
WHERE (pub_id = '0766')
How would I modify this if the table archivetitles had columns:
titleid, title, newcolumn, type, pub_id
and I wanted the newcolumn value to be null, but I still wanted to select the values from titles?
View 1 Replies
View Related
Sep 8, 2006
Resolved - thank you.
View 1 Replies
View Related
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
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
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
Mar 21, 2006
Hello,I just started with ms sql. I need to insert data from some textboxes into ms sql 2000 database.I can't get it work.I'm trying this:"INSERT INTO tblUplate (ID_Uplata, JMB, IDOsnovaUplate, Iznos, Datum) Values('', ' + txtJMBUplate.Text + ', ' + ddVrstaPrihoda.SelectedItem + ', ' + txtIznos.Text + ', ' + DateTimePicker1.Value + ')"Is this wrong? Should I use + or & (like I used to use with MS Access)?Thanks
View 1 Replies
View Related
Apr 27, 2006
I have a textbox with the id of txtName it is a name text box.
and I have a submit button.
How do i get the value of the text in the text box to popluate a new row of data in my Sql Server database.
View 13 Replies
View Related
Sep 22, 2004
I got a Problem with a simple insert!
insert into processo_reembolso (NUB) Values ('22') where ID_Processo = '1' and Nome_Processo = 'Reembolso' and Incidente = '1' and Versao = '1'
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'.
Can anyone give me a light on this one...
Thanks
View 1 Replies
View Related
Apr 19, 2008
Here are my tables:
-------------------------------
Events
-------------------------------
ID | E_EventName
-------------------------------
-------------------------------
Photos
-------------------------------
ID | P_EventID | P_Filename
-------------------------------
Given an event like "2005 cookout", and a file like "bob.jpg", I'd like to insert the event id based on the event name and the filename into Photos. How can I do that?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
Aug 1, 2006
I'm trying to wrap my head around inserting values into a table w/ SQLDataSource. What I'm trying to do is have a form where users can submit their feedback and their responses would get put into a table on the backend. It would store not only their comments, but their userid, the url that they were on last, and which area they're from. The trouble is, I have no idea if I'm coding this correctly. On the submit Here is the form: <form id="form1" runat="server"> <asp:SqlDataSource runat="server" ID="feedbackSQL" InsertCommand="insertfeedback_sp" InsertCommandType="StoredProcedure" connectionstring="<%$ ConnectionStrings:Test%>"> <InsertParameters> <asp:Parameter Name="ID" Type="Int16" /> <asp:Parameter Name="Region" Type="String" /> <asp:Parameter Name="Querystring" Type="String" /> <asp:FormParameter Name="Comments" FormField="Comment" /> </InsertParameters> </asp:SqlDataSource> <div> <asp:TextBox ID="Comment" runat="server" Height="200px" Width="350px"></asp:TextBox><br /> <asp:Button ID="Submit" OnClientClick="btn_Submit" runat="server" BackColor="White" Font-Names="Arial" Font-Size="X-Small" Text="Submit" /> <asp:Button ID="Cancel" runat="server" BackColor="White" Font-Names="Arial" Font-Size="X-Small" Text="Cancel" OnClientClick='javascript: window.close()' /> <asp:Button ID="Clear" runat="server" BackColor="White" Font-Names="Arial" Font-Size="X-Small" Text="Clear" OnClientClick='javascript: ClearBox(Comment)' /> </div> </form>Here is the backend: protected void btn_Submit(object sender, EventArgs e) { feedbackSQL.InsertParameters["ID"].DefaultValue = UserId ; feedbackSQL.InsertParameters["Region"].DefaultValue = Region; feedbackSQL.InsertParameters["Comments"].DefaultValue = Comment.Text.ToString(); feedbackSQL.InsertParameters["Querystring"].DefaultValue = URL.ToString(); }When I test it out. Nothing shows up in my database table. I also have another problem, ID is stored in my table as an int, but when I try to set it's value in btn_Submit and try to compile it, I get a "cannont convert 'int' to 'string'" error. Am I missing something here? Any help would be much appreciated, thanks :)
View 6 Replies
View Related
Mar 30, 2007
Hi all, I really can't figure out what i'm doing wrong here, and I'd appreciate some help if possible please! SqlDataSource5.InsertParameters("ntuser").DefaultValue = ntuser SqlDataSource5.InsertParameters("datetime").DefaultValue = DateTime.Now SqlDataSource5.Insert()Above is my code to insert into my SQL database a varchar NT user name, and the current date. The Parameters are defined in the HTML as : <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:Test EnvironmentConnectionString %>" InsertCommand="usp_insert_hit" InsertCommandType="StoredProcedure" SelectCommand="usp_select_hits" SelectCommandType="StoredProcedure" UpdateCommand="usp_insert_hit" UpdateCommandType="StoredProcedure"> <InsertParameters> <asp:Parameter Name="ntuser" Type="String" /> <asp:Parameter Name="datetime" Type="DateTime" /> </InsertParameters></asp:SqlDataSource> My SQL Database is set up to have a table called "Hits" which has three colums, a hit_id (Autocounter, primary key), ntuser (varchar), datetime (datetime) When the application runs it stops at runtime and highlights the SQLDataSource5.Insert() line, and I can't figure out why - can anyone else tell why please? thanks.
View 1 Replies
View Related
Mar 28, 2008
I am trying to figure out what SQL script on how can I do this........I want to insert a record in a table by using the text fieds instead of using a primary key to recognize where it relates to. Fro example, I have two tables:
table Make---------------Make ID- Interger (PK) 1 description - text Honda
table model----------------Model ID - integer (PK) 1desc.--text AccordMake ID - interger (FK) 1
I am trying to figure out A way to do something like this......... INSERT into model (Model ID which is an auto #, description, Make ID) VALUES (txtmodelID.text, txtdescripton.text, instead of doing MakeID here; I want to use the descripton from the Make table to insert thiis MakeID field) I badly need some help with this logic or syntax as it relates to most of the way I need to insert through out my site. Thanks in advance.
In other words I want the user to select a Make from a drop down list not a Make ID# such as Honda that will insert under that Make ID number related to that description..... such as a type of car ; Accord's description into the model table.
View 3 Replies
View Related
Mar 7, 2005
Hello,
I don't know what to do anymore ;o(
I've got 2 servers, with sql server 2000 sp3 and ms windows 2003 server.
I've written a very simple stored procedure to insert 20,000 rows into a very simple table TEST (id int, msg varchar_50)
On the first server (P-IV 2 GHz), it takes 700 ms / 1000 insertions
and on the second (2x Xeon 2,6 GHz), it takes 13 s / 1000 insertions...
(insertion is : INSERT INTO TEST (id, msg) VALUES (@id, 'dummy text'))
...
SQL Server was installed exactly in the same way...
what could I do see where the problem is ? With profiler, I see no difference while logging all events....
please help or give ideas
View 1 Replies
View Related
Oct 31, 2006
I have just one table but need to create a trigger that takes place after an update on the Orders table. I need it to multiply two columns and populate the 3rd column (total cost) with the result as so:
Orders
---------
ProductPrice ProductQuantity TotalCost
-------------- ------------------ -----------
£2.50 2
£1.75 3
£12.99 2
Can anyone please help me?
View 3 Replies
View Related
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
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
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)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 Related
Nov 17, 2003
I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?
thanks for your time,
Andre
View 1 Replies
View Related
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