Insert Statement Which Uses A Return Value From An SP As An Insert Value

Jul 20, 2005

I'm quite stuck with this:

I have an import table called ReferenceMatchingImport which contains
data that has been sucked from a data submission. The contents of
this table have to be imported into another table ExternalReference
which has various foreign keys.

This is simple but one of these keys says that the value in
ExternalReference.CompanyRef must be in the CompanyReference table.
Of course if this is an initial import then it will not be so as part
of my script I must insert a new row into CompanyReference and
populate ExternalReference.CompanyRef with the identity column of this
table.

I thought a good idea would be to use an SP which inserts a new row
and returns @@Identity as the value to insert. However this doesn't
work as far as I can tell. Is there a approved way to perform this
sort of opperation? My code is below.

Thanks.

ALTER PROCEDURE SP00ReferenceMatchingImport
AS

/*
Just some integrity checking going on here
*/

INSERT ExternalReference
(
ExternalSourceRef,
AssetGroupRef,
CompanyUnitRef,
EntityTypeCode,
CompanyRef, --this is the unknown ref which is returned by the sp
ExternalReferenceTypeCode,
ExternalReferenceCompanyReferenceMapTypeCode,
StartDate,
EndDate,
LastUpdateBy,
LastUpdateDate
)
SELECT rmi.ExternalDataSourcePropertyRef,
rmi.AssetGroup,
rmi.CompanyUnit,
rmi.EntityType,
SP01InsertIPDReference rmi.EntityType, --here I'm trying to run the
sp so that I can use the return value as the insert value
1,
1,
GETDATE(),
GETDATE(),
'RefMatch',
GETDATE()
FROM ReferenceMatchingImport rmi
WHERE rmi.ExternalDataSourcePropertyRef NOT IN (
SELECT ExternalSourceRef
FROM ExternalReference
)

View 3 Replies


ADVERTISEMENT

RETURN Of IF NOT EXISTS INSERT Statement

Feb 20, 2006

Hi @ all

I'm using MSSQL and PHP.
I've got the following sql statement:

$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')

The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused:
mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not.
I tried:

$result = mssql_query($msquery);
$succeed = mssql_rows_affected ($result);

And:

$result = mssql_query($msquery);
$succeed = mssql_num_rows($result);

to get the rows, affected by the statement, but both return:

supplied argument is not a valid MS SQL-Link resource

Anyone an idea?

View 2 Replies View Related

Return Primary Key On INSERT Statement

Jul 20, 2005

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

View 10 Replies View Related

Can INSERT Statement Also Return All Columns Inserted As Result Set

Jun 2, 2014

Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?

View 3 Replies View Related

SQL 2012 :: Can INSERT Statement Also Return All Columns Inserted As Result Set

Jun 2, 2014

Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?

View 2 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

Jan 17, 2007

Greeting.

I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.

I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.

Have this problem solution? May be, I must use another method to get my data?



P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.

P.S.S. Sorry for bad english.









View 1 Replies View Related

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage

Apr 21, 2008

An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
 
thanks

View 7 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement

Jan 14, 2008


This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1


/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id bigint )

declare @forename VARCHAR(100)
declare @surname VARCHAR(50)

set @forename = N'john'
set @surname = N'smith'

INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

View 5 Replies View Related

What Does DataSource.Insert() Return?

Jun 19, 2007

i seem to get a 1 if the values are inserted successfully and -1 if not. but is that right? any other values that can be returned? I can't find it in MSDN2 Library. like i can with other classes, properties & methods 

View 1 Replies View Related

Return A Value After Insert The Query

Oct 8, 2007

Hi!   create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value.          Dim objConn As SqlConnection        Dim SQLCmd As SqlClient.SqlCommand        Dim ds As New DataSet        Dim strsql As String        Try            objConn = New SqlConnection            objConn.ConnectionString = _                "Network Library=DBMSSOCN;" & _                "Data Source=localhost;" & _                "Initial Catalog=mydb;" & _                "User ID=userid;" & _                "Password=pass"            objConn.Open()            strsql = "insert into testReturn values ('a')"            SQLCmd = New SqlClient.SqlCommand(strsql, objConn)            Dim rowsAffected As Integer = 0            rowsAffected = SQLCmd.ExecuteNonQuery            Dim rv As String            rv = SQLCmd.Parameters(0).Value.ToString()            Response.Write(rv)                    Catch ex As Exception            Response.Write(ex.ToString)        End Try  

View 5 Replies View Related

Return GUID On Insert

Oct 24, 2007

once upon a time someone told me how to do this, but i forget, and it was for classic asp..
 so what im trying to do is create a new record (a workstation) and get back the auto generated ID (its not actually a GUID, its an int which is set to the identity) and then once i have that create other records (for external devices, different table) that will have a link to which machine they are attached to (that Id im looking to get returned)
I know this can be done in a two step process, create the record, query it for its id, and then slap that in my next insert statement for the external devices... but... i know there was a way to do it before and i assume there is a way to do it now...
anyone know how?
Thanks
Justin

View 8 Replies View Related

How Do You Use A SP To Do An Insert And Return The Identity To ADO

Apr 12, 1999

I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.

Thanks

View 1 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

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

Get A Return Value From An Insert Without Using A Stored Proc.

Oct 31, 2006

hi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log
SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn);
cmdStoreTrans.Parameters.Add("@ImportID",importId);
cmdStoreTrans.Parameters.Add("@ProfileID",profileId);
cmdStoreTrans.Parameters.Add("@RowID",i);
try
{
conn.Open();
cmdStoreTrans.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
throw(ex);
}I need the new Identity number of that record added.  how can i get that within THIS Sqlcommand.  Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm

View 2 Replies View Related

Accessing The Return Variable Of An Insert

May 4, 2008

When I create a tableadapter to do an insert it added
SELECT EventDate, EventID, org_id, ROMEventID FROM ROMEvents WHERE (ROMEventID = SCOPE_IDENTITY())
at the end. This looks useful to me since it implies that I can access ROMEventID which is the auto-incremented identity field.
 How would I reference the variable in my .cs file?
My insert is working as ROMEventsTableAdapter ROMEventInsert = new ROMEventsTableAdapter();
ROMEventInsert.InsertIntoROMEvents(theDate, EventID, org_id);
Thanks for any help

View 2 Replies View Related

Insert :) I Have Different Insert Code Lines (2 Insert Codelines) Which One Best ?

Jun 4, 2008

hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
 
 
cheers

View 2 Replies View Related

How To Return Identity Value When Insert Into Slq Command Run Successful ?

Feb 10, 2006

hello,
        I'm new to store procedure in slq 2000.
       I want to create  a sproc in  my sql 2000 sevrer,I have a table named "Supplier",
now I want to return  my identiy field value when  I use insert into sql command (insert into "Supplier")  successful.
   So how to create this store procedure ?
much thanks

View 6 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

Hi,

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

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

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

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

or

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

Thanks in advance,
Sebastien Anselmo

View 6 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

Problem With @@identity Return In Stored Procedure Insert.

Nov 24, 2003

I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.




CREATE PROCEDURE addMagicRecTest
(
@theSequence int,
@theSubject int,
@theFirstName nvarchar(50)=null
@theLastName nvarchar(75)=null
)



AS

INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@theSequence,@theSubject,@theFirstName,@theLastName)
SELECT @@identity AS finalNum



magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure

' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"

' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"


' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50))
magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName

' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName



DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")

If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If





I need finalMagicNum2

View 2 Replies View Related

Insert Trigger And Xp_cmdshell With Carriage Return Text

Jul 20, 2005

I have created the following trigger:CREATE TRIGGER [CreateFile] ON OutputTableFOR INSERTASDeclare @filename nvarchar(35)Declare @filecontents nvarchar(2000)Declare @strcmdshell varchar(150)SELECT @filecontents = OutputText FROM INSERTEDSELECT @filename = 'c:' + OutputFileName FROM INSERTEDSELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellIt works fine as long as the column OutputText has no carriagereturns.I have used my Access2K front end to dump about ten lines of text intoOutputText, each line broken by vbcrlf so that OutputText looks like:line1testline2textline3textetc.The trigger won't fire with this text.Is there something I can do to remedy this?lq

View 4 Replies View Related

Transact SQL :: Insert Or Update Stored Procedure Return ID

Nov 1, 2015

I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?

ALTER PROCEDURE [dbo].[AddNode]
@Name VARCHAR(15),
@Thumbprint VARCHAR(40),
@new_identity [uniqueidentifier] = NULL OUTPUT
AS
BEGIN
UPDATE dbo.NODES

[Code] ....

View 7 Replies View Related

How To Excute Stored Procedure That Insert Record And Return Last Inserted Value

Jul 18, 2007

Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values      
Please help 
thanks 
 

View 3 Replies View Related

INSERT Statement

May 1, 2007

I was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say. word 

View 3 Replies View Related

Need Help With INSERT INTO Statement...

Jan 4, 2008

I have put together the following code, but am getting a syntax error on the Insert Into statement. Could someone kindly tell me what I am doing wrong? Thanks  1 Protected Sub SubmitButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton2.Click2 authAndSaveToDB()3 End Sub4 Private Sub authAndSaveToDB()5 On Error GoTo errhand6 If connectToDB() Then 'connection to database was successful7 'insert candidate in the database
8 insertCandidate(ddlPositions.Text, txtName.Text, txtBio.Text, conn)9 Response.Redirect("FileUpload.aspx")10
11 Else
12 Response.Write("<font color = red><strong>Database connection failure. Please try again in a few minutes or contact system administrator.</strong></font>")13 End If
14 closeDB() 'end the connection to the database
15 Exit Sub
16 errhand:17 Response.Write("<br>ERROR: " & Err.Description & "<br>")18 End Sub19 Private Sub insertCandidate(ByVal strPositions As String, ByVal strCandidate As String, ByVal strBiography As String, ByVal myconn As OleDb.OleDbConnection)20 Dim cmd As OleDb.OleDbCommand21 Dim strSQL As String
22
23 strSQL = "INSERT INTO Position (Positions,Candidate,Biography) VALUES ('" & _
24 strPositions & "'," & _
25 "'" & strCandidate & "'," & _
26 "'" & strBiography & "')"
27 cmd = New OleDb.OleDbCommand(strSQL, myconn)28 cmd.ExecuteNonQuery()29 End Sub30 End Class
  

View 16 Replies View Related

Insert SQL Statement

Jan 31, 2008

Hi, I am retriving the value time from my database and i have the convert function on it CONVERT (varchar, Time, 8)
When i edit this and try and insert it back to the database i am losing the information. I am not sure how the insert statement shoulld read in order to do this.
Thanks Mike

View 8 Replies View Related

SQL Insert Statement

Feb 14, 2008

Hi forum, currently I insert a mobile phone number into colomn OK but what I need to do is add +61 as a prefix.Users provide 055 555 555, I want to store number as +61 55 555 555 (note this also drops the 0) my SQL skills a little lacking so appreciate all good info, cheers P

View 2 Replies View Related

Insert Statement

Apr 3, 2004

Hi Everyone

I am having difficulties with my first hand coded insert statement. The record inserts BUT the first item VALUE is selected for all the drop down lists ( 3 of them are optional) I have Prerenders on the page to insert a null value at the top of the list. For the dropdown that is mandatory it only enter first item. Thats without specifying type. As soon as I specify type I get - Input string not in correct format - doesn't actually tell me which one!!!!!! All char types match the database.

Here is my insert:



Sub Add_Click(sender as object, e as EventArgs)

Dim conBooks As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand

conBooks = New SqlConnection( System.Configuration.ConfigurationSettings.AppSettings("conFantasy") )
strInsert = "INSERT INTO dbo.Books (ISBN, BookTitle, AuthorID, AuthorType, author2, author3, Series, VolNo, Synopsis, PubYear, PubDate) VALUES (@ISBN, @BookTitle, @AuthorID, @AuthorType, @author2, @author3, @Series, @VolNo, @Synopsis, @PubYear, @PubDate )"

cmdInsert = New SqlCommand( strInsert, conBooks )

' Add Parameters
cmdInsert.Parameters.Add( "@ISBN", SqlDbType.nvarchar).Value = txtISBN.Text
cmdInsert.Parameters.Add( "@BookTitle", SqlDbType.nvarchar).Value = title.Text
cmdInsert.Parameters.Add( "@AuthorID", SqlDbType.int).Value = AuthorID.SelectedItem.Value
cmdInsert.Parameters.Add( "@AuthorType", SqlDbType.char).Value = AuthorType.SelectedItem.Value
cmdInsert.Parameters.Add( "@author2", SqlDbType.int).Value = author2.SelectedItem.Value
cmdInsert.Parameters.Add( "@author3", SqlDbType.int).Value = Author3.SelectedItem.Value
cmdInsert.Parameters.Add( "@Series", SqlDbType.nvarchar).Value = Series.SelectedItem.Value
cmdInsert.Parameters.Add( "@VolNo", SqlDbType.int).Value = volumeno.Text
cmdInsert.Parameters.Add( "@Synopsis", SqlDbType.ntext).Value = txtSynopsis.Text
cmdInsert.Parameters.Add( "@PubYear", SqlDbType.bigint).Value = pubyear.Text
cmdInsert.Parameters.Add( "@PubDate", SqlDbType.nvarchar).Value = PubDate.Text


conBooks.Open()
cmdInsert.ExecuteNonQuery()
conBooks.Close()

Response.Redirect("/admin/default.aspx")
End Sub

View 4 Replies View Related

Insert Statement...

Feb 13, 2006

Hi All,
I am trying to insert data into table1 by getting data from table2, table3 and looking whether the data is already not exist in table1.
Here is my code...
insert into table1 (first_name, last_name, user_login, email, organization_name) values (select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3  wepsUsers where ru.id = wepsUsers.user_id and not exists  (select user_login from table1  where user_login = ru.UserID))
 
When I excute the code, I am getting the following error.....
Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.Server: Msg 170, Level 15, State 1, Line 7Line 7: Incorrect syntax near ')'.
But, If execute the following code....
select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2 ru, table3  wepsUsers where ru.id = wepsUsers.user_id and not exists  (select user_login from table1  where user_login = ru.UserID).... I am able to see the values.
 
Can any one shade on my code? and please let me know, where I am doing wrong. I am appreciate your help.
 
Thanks.
Srinivas.

View 3 Replies View Related







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