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

Convert Composite Primary Key Into Simple Primary Key

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

Simple Question: ON PRIMARY?

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

SQL 2012 :: Simple Way To Remember Primary And Foreign Key?

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

A Simple Insert

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

Simple Insert Err

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

Help With Simple Insert

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

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

Can We Pause Log Shipping, Bring Primary Db To Simple Recovery Model And Then Back To Full R Model?

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

Simple Insert Into... MS SQL 2000

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

Simple Insert Statement

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

Problem With A Simple Insert!

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

Simple Insert Statement

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

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

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

Simple Form/DB Insert Question

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" />&nbsp;&nbsp;        <asp:Button ID="Cancel" runat="server" BackColor="White"  Font-Names="Arial" Font-Size="X-Small"            Text="Cancel" OnClientClick='javascript: window.close()' />&nbsp; &nbsp;        <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

SQLDatasource.Insert() Should Be Simple, But I'm Stuck!

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

Need Help On How To Do A Simple Insert SQL Script That Is 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

Simple Insert Query VERY Slow !

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

How To Create Simple Insert Trigger

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

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

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)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

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







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