INSERT INTO Comments_tbl (FullName_tsf, Email_tsf, NTuser_tsf,
name_tsf, type_tsf, comments_tsf) VALUES ('Test Tester' ,
't*****@test.com', 'ttest', 'test', 'help', 'need better help')
I am trying to use the following sql to insert from a script but there
is a primary key column - 'com_no' - on the table.
How do I write the sql insert so that the 'com_no' value gets a value
inserted into and increases each time.
Thanks for all help. 5 1501
"kieran" <ki********@hotmail.com> wrote in message
news:b3**************************@posting.google.c om... INSERT INTO Comments_tbl (FullName_tsf, Email_tsf, NTuser_tsf, name_tsf, type_tsf, comments_tsf) VALUES ('Test Tester' , 't*****@test.com', 'ttest', 'test', 'help', 'need better help')
I am trying to use the following sql to insert from a script but there is a primary key column - 'com_no' - on the table.
How do I write the sql insert so that the 'com_no' value gets a value inserted into and increases each time.
Thanks for all help.
I'm not completely sure from your description, but I guess you're looking
for an IDENTITY column, which will automatically generate a new value for
each INSERT? Unfortunately, you can't add the IDENTITY property to an
existing table, so you would need to create the table again:
create table dbo.Comments (
CommentID int identity(1,1) not null,
FullName ...,
...,
constraint PK_Comments primary key nonclustered (CommentID)
)
After the INSERT, you can use the function SCOPE_IDENTITY() to get the value
that was just inserted.
Simon
Thanks Simon.
That clears up everything for me.
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>... "kieran" <ki********@hotmail.com> wrote in message news:b3**************************@posting.google.c om... INSERT INTO Comments_tbl (FullName_tsf, Email_tsf, NTuser_tsf, name_tsf, type_tsf, comments_tsf) VALUES ('Test Tester' , 't*****@test.com', 'ttest', 'test', 'help', 'need better help')
I am trying to use the following sql to insert from a script but there is a primary key column - 'com_no' - on the table.
How do I write the sql insert so that the 'com_no' value gets a value inserted into and increases each time.
Thanks for all help.
I'm not completely sure from your description, but I guess you're looking for an IDENTITY column, which will automatically generate a new value for each INSERT? Unfortunately, you can't add the IDENTITY property to an existing table, so you would need to create the table again:
create table dbo.Comments ( CommentID int identity(1,1) not null, FullName ..., ..., constraint PK_Comments primary key nonclustered (CommentID) )
After the INSERT, you can use the function SCOPE_IDENTITY() to get the value that was just inserted.
Simon
Hello Simon,
Suppose, unlike Kieran's case, I have a table with one column set as
primary key, which is not supposed to be duplicated. This key has a
corresponding textbox on the form which the user enters manually.
I use .ExecuteNonQuery() after setting up the parameters of a
SQLCommand object to insert a row. The SP does a simple insert
routine.
Using one single button for updating the table (new row/existing row),
is there a simple / automatic method to modify an existing row in the
table (using Triggers, etc.) ?
Or, do I have to write the code in the button that will -
first, fire an sp that returns back a boolean indicating whether a row
with the user-entered primary key already exists, then
second, conditionally set the .commandtext value to either of the two
SPs (INSERTSP / UPDATESP) depending on whether the boolean was
row-exists/does-not-exist.
finally, fire the .executenonquery() ?
Any help will be highly appreciated.
Regards,
Sanjay.
sa wrote: Hello Simon,
Suppose, unlike Kieran's case, I have a table with one column set as primary key, which is not supposed to be duplicated. This key has a corresponding textbox on the form which the user enters manually.
Then you'd have a bad design. :) You really shouldn't allow primary keys
to be changed, especially by the user. If you find yourself changing
your primary keys so often that you need a data entry field, then
rethink your primary key. That said...
What you can do is test for the row's existance in the stored procedure.
Basically, your stored procedure can do one or the other depending on
the case, no need for multiple procedures:
IF EXISTS(SELECT statement to check for the row)
UPDATE Statement
ELSE
INSERT statement
Zach
try this...
declare @com_no int,
@cnt int,
@rowcnt int
select @com_no =max(com_no) from Comments_tbl
select @cnt=0,
@rowcnt=count(*) from <table with rows to insert, or hard code a
number>
while @cnt < @rowcnt
begin
INSERT INTO Comments_tbl (com_no, FullName_tsf, Email_tsf,
NTuser_tsf,
name_tsf, type_tsf, comments_tsf) VALUES (@com_no, 'Test Tester' ,
't*****@test.com', 'ttest', 'test', 'help', 'need better help')
select @com_no-@comno+1
if @cnt>@rowcnt break else continue
end
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: newbie_mw |
last post by:
Hi, I need urgent help with a novice problem. I would appreciate any
advice, suggestions... Thanks a lot in advance! Here it is:
I created a sign-up sheet (reg.html) where people fill in their...
|
by: serge |
last post by:
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.
I need to...
|
by: jtocci |
last post by:
I'm having a big problem with CREATE RULE...ON INSERT...INSERT
INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50)
records based on a single INSERT to a view. Either I get a 'too...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with...
| |
by: alexmaster_2004 |
last post by:
hi
i have made an application using C# that access sql2000.
this application is just used to insert data to the database.
i use something like this in my code:
//
string colmnA = TextBox1.Text;...
|
by: Lorenzino |
last post by:
Hi, I have a problem with bindings in a formview.
I have a formview; in the insert template i've created a wizard control and
inside
it i have an HTML table with some textboxes bound to the...
|
by: =?Utf-8?B?UmljaA==?= |
last post by:
On a form - I have a datagridview which is docked to the entire form. The
datagridview allows users to Delete and/or Add Rows. On the Form_Load event
I Fill the datagridview source table with a...
|
by: sgulciny |
last post by:
hi friends;
I have problem about sql server insert and update in client side.
I am coding windows application with c#.When I run my code in database
server computer all is fine.I can see data,...
|
by: Red |
last post by:
If auto-format is turned off in VS2008, there is apparently no way to
indent a line. Under Tools->Options->Text Editor->C#->Formatting,
there are three checkboxes. Unchecking those seems to cause...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |