Inserting Values And Get The Last ID Recorded To Use In Another INSERT
Sep 20, 2007
I need to insert some values into a table and after that catch the ID inserted.
I set some input parameters in stored procedure and only one to get the @id defined as output
SqlParameter paramIdPedido = new SqlParameter("@APP_IDPEDIDO", SqlDbType.Int, 4);
paramIdPedido.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramIdPedido);
Do I have to run cmd.ExecuteNonQuery(); to record first what I need and after
run ExecuteReader: something like SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()... to get the ID)
From stored procedure:
INSERT table(fields) VALUES(vars)
SELECT TOP 1 @id = id FROM table ORDER BY id DESC
I must do all these steps or maybe is there anything less complex to do?
Thanks!
View 1 Replies
ADVERTISEMENT
Nov 18, 2015
I have a fundamental problem with how CDC works for bulk updates.When CDC enabled table is updated for single row - My CDC system tables its recording it as update (3 & 4) which is perfect and what it should be. No Complains!But when I do a bulk update in the same CDC enabled tables for the same columns - My CDC system tables its recording as delete and then insert (1 & 2). This is not correct and this is what my problem is. We used triggers before CDC we did not face this problem with triggers every thing was fine with triggers other than performance.The way how the CDC is handling the bulk update is a big problem for me because based on the output of CDC system tables we are doing some migration work to legacy system.
It will be impossible for me to go and change my migration logic scripts because we have 100's or procedures in it.Is it a know problem with CDC? Is there any solution in CDC when a bulk update happens on a table the CDC system tables record it as updates. I don't think CDC 'net changes' in this situation because the net change would show as single inserted row.If this can't be done with CDC then I have to completely abandon CDC and go back to triggers..
View 5 Replies
View Related
Oct 6, 2007
the error message I get is
{"Object reference not set to an instance of an object."}
and it points to < Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text >
this is my code":
Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click
REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text
Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text
REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand
cmd.Connection = cn
REM Build our parameterized insert statementDim sql As New StringBuilder
sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)")
cmd.CommandText = sql.ToString
REM Add parameter values to command
REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters
.Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp
.Add("Exch", SqlDbType.VarChar).Value = Exch
End With
REM Now execute the statement
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub
View 3 Replies
View Related
Oct 25, 2006
Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply. I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from address http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:http://forums.asp.net/thread/1437716.aspx I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:Without masterpage:<input name="NazevBox" type="text" id="NazevBox" /><span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="CodeBox" type="text" id="CodeBox" /><span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" /> With masterpage:<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" /><span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" /><span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.
View 2 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Jun 9, 2008
Gurus,
I have two list boxes, user can move items back n forth, from second listbox I am inserting values into a table. So far everything is working fine.
Now I want to delete all the existing values from the table before inserting evertime..Please help me in this I dont know what to do.
thanks
kalloo
View 8 Replies
View Related
Jun 22, 2005
I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )If anyone has this answer please let me know.Thanks,RB
View 3 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Jun 26, 2004
Dear All,
In sql-server2000
In a table i am having a column of datatype varchar(8000).
While inserting the record through executenonquery, i am insert only
255 characters rest of the characters are getting trucated.
My question in how i will able to insert the row of that particular
column more than 255 characters
Thanx in advance.
Regards
View 2 Replies
View Related
Sep 20, 2006
Hi all,I have a table called Jobs, with fields PK auto increment job_id, job_name, date. I'm using the visual web developer, .net 2.0, sql server 2005.I'm trying to have an option in one of my forms that allows me to add Jobs for a specific time frame. Say I want to add a job called "JobOne" and that I expect this job to last 2 months, so I would like to add this "JobOne" from October 1st 2006 to December 1st 2006. Than in the table "jobs" I would see JobOne in October 1st, 2nd, 3rd... all the way to December 1st. I'm familiar how to insert single values from formviews, using sqldatasources but I have no idea how to insert something like this, so I was wondering if anyone out there could help.Thanks!
View 1 Replies
View Related
Jan 22, 2008
Hi there
I have an exel spreadsheet with a very long list of towns. How can I import/insert that into my "Towns" table in sql express? I can't seem to find any way to import it and I'm not sure how to do multiple inserts.
Thanks
View 1 Replies
View Related
Apr 24, 2001
What would be the best way to insert unique values into a table/unique column ?
I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to
final table.
Suggestions are much appreciated.
Ivan
View 2 Replies
View Related
May 11, 2004
Hi,
I am trying to insert null values into sql server from my access from. I am using sql statement. But it says 'Syntex error in Insert statement'. When i remove null values it works fine? How can I insert null values into a table?
Any help will be highly appreciated.
View 3 Replies
View Related
Oct 2, 2007
Hi,
I am trying to insert multiple values from another table as well as an addition value defined by me. Here's my code which is obviously incorrect at the AND statement:
Insert Into table_1 (ID, Col_1, Col_2)Select ID, Col_A, Col_B From table_A AND table1.Col_3 = 'XYZ'
Where table_A.Col_A IS NOT NULL
Pls advise on the correct way of constructing this statement.
Many Thanks
View 8 Replies
View Related
Oct 17, 2007
Hi all,
I am having issue with a set of radiobuttons. I have four radiobuttons associated by a groupname (answer). I am attempting to store the text of the selected radiobutton when the user make a selection and clicks submit. For reasons unknown... each of the radiobutton.checked values are remaining false hence I cannot interrogate for a checked equal true to insert associated text of check response.
Here is how I am interrogating who is checked:
If rb1.Checked = True Then
useranswer = rb1.Text.ToString
ElseIf rb2.Checked = True Then
useranswer = rb2.Text.ToString
ElseIf rb3.Checked = True Then
useranswer = rb3.Text.ToString
ElseIf rb4.Checked = True Then
useranswer = rb4.Text.ToString
End If
I then attempt to insert this text to sql but none of the rb values are true hence a null wont insert.
Cmd1.Parameters.Add(New SqlParameter("@answer", useranswer))
How can I evaluate grouped radiobutton checked values?
Many thanks in advance...
Scott
View 2 Replies
View Related
Jan 25, 2008
Hi All, am new to sql server in my application I am having one Asp.net web page, the user has to enter values for 5 fields(Empno,Empname,salary,deptno,deptname) in that web page and for that i created two tables in sql serverEmp Table- empno(primary key),empname,salaryDept Table- Deptno(primary key),empno(Foreign key ),Deptname(with some check constraint.) After the user enter all the values in the Asp.net web page now i want to store data into database for that i wrote the following stored procedure...create procedure usp_EmpDept @empno integer, @empname varchar(15), @salary money, @deptno integer, @deptname varchar(10) As Insert into emp(empno,empname,salary)values(@empno,@empname,@salary) Insert into dept(deptno,Empno,deptname)values(@deptno,@empno,@deptname) but the problem is whenever some constaint violation for eg. if some check constraint violation in Dept table its inserting the values in the Emp table only, but my requirement is, It must enter into both tables if there is no constaraint violation otherwise it has to ignore both the tables. And also please suggest is there any better way to insert values into two tables other than using the stored procedure Any help will be greatly appreciated..Thanks,Vision..
View 6 Replies
View Related
Apr 10, 2008
i have a table whose Primary Key is "UserID". the sample "UserID" are M1,M2,M3,M4,B1,B2,B3 .
i want that when i insert a valuse "M4" in the table ,by pressing Submit Button.
it should not be at the end or at the start of table.
Rather it should be next to M3. like the following
M1
M2
M3
M4
M5
B1
B2
B3
i need the C# code of how to do that !!!!
Thanks
View 7 Replies
View Related
May 1, 2001
Is there a way to insert multiple values into a single column based on various "tests".
For example, I want to check a sales_order table and flag all new orders coming in against previous orders placed that were determined to be fraudulent. If I were to set up i.e. five different tests(i.e. check email, credit_card number etc. against previous fraud orders), then there would be the possibility that any given order can be flagged 1 to 5 times. I want to record all of these tests within the same column if possible. Therefore the output may look something like the following:
order_number fraud_score
1234567890 a,b,d
5432109876 e
2345678901 null
3455607983 a,b,c,d,e
I was considering adding five additional columns to the table and running five different update steps, but this doesn't appear very scalable. Any suggestions would be greatly appreciated!
thanks in advance-
trevorb
View 3 Replies
View Related
May 9, 2000
Can some one please tell me how to update a field in a table with multiple
values for each of the values in the other fields?
Thanks in advance.
View 1 Replies
View Related
Dec 14, 2000
Hi,
We are using an SQL Server database and seem to be having some problems with inserting null values into numeric fields. The field is set to accept nulls, but when we try and write a record into the database and that field is null, the application craps out on us. Are there any issues that we should be aware of when inserting null values into numeric fields? What might the problem be? Thanks.
View 1 Replies
View Related
Jan 21, 2006
I am fetching values from a table containing non english characters and inserting the values in another table. Both the tables have the same structure and the column datatype is nvarchar. I am using jdbc jtdc.jar with default parameters. When I query my inserted values, they are junk characters. when I insert values using Query Analyzer the characters are proper. But when i insert using jdbc junk values are stored.
any help would be greatly received.
View 1 Replies
View Related
May 3, 2006
i have attached XML and XSD file
i bulkload xml file into 2 tables .ManifestID is the Relationship between 2 tables
CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Detail] (
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_num] [bigint] NULL ,
[track_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[scan_time] [datetime] NULL ,
[scac_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Header] (
[trailer_id] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trailer_close_date] [datetime] NULL ,
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manifest_qty] [int] NULL ,
[origin_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[destination_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
When the first TrackCodeid is NULL
all remaining NULL TrackCodeid is entered as null in the database table
but if the first/prev trackcodeID is not NULL then following null trackCODEID is populated with the prev trackcodeID and not as null in database
<TrackCodeId>ABCDEFG</TrackCodeId>
All Null TrackCOdeID is populated as ABCDEFG
if we remove ABCDEFG and then bulkload all null values are populated
if a null trackCODEID is to be inserted the prev trackCODEID must be null and must not contain any value
View 1 Replies
View Related
Feb 14, 2004
hi
how can we insert null into a small datetime field (from client application VB)
View 2 Replies
View Related
Mar 17, 2014
I try to do all insert-actions in one SP. But it doesn't work. I couldn't insert any values into the DB. Is this possible or the wrong way?
use env
go
create proc [SP$insert](
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output,
@debug bit = 0
[Code] ....
View 9 Replies
View Related
Aug 11, 2014
I have a trigger that searches for duplicates before inserting values.
I have written the trigger however its not inputting values into the column at all.
see my trigger:
ALTER TRIGGER DUPLICATES
ONAMGR_User_Fields_Tbl
AFTERINSERT,
UPDATE
AS
Declare @AlphanumericCol varchar (750)
Declare @Counter integer
[code]....
View 5 Replies
View Related
Dec 20, 2013
I am having Test table with (id int , DataDescription xml) . I need to read the values from DataDescription column and insert into one table.
View 2 Replies
View Related
Feb 15, 2006
can we insert multiple values into the same field. as we do for the mailing list. that is can we use commas to enter multiple values into the same field
View 10 Replies
View Related
Apr 25, 2007
SELECTIndustry,
100.0 * SUM(CASE when ceoischairman = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [YesPercent],
100.0 * SUM(CASE when ceoischairman = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [NoPercent]
FROMTCompanies
GROUP BYIndustry
ORDER BYIndustry
This code above is working as I need it but I need to insert some additional functionality. Thanks
I need to add something like this:
IF YesPercent > NoPercent
UPDATE tableX SET CEOIsChairman='Yes' WHERE Industry='<the industry value being evaluated>'
Else If NoPercent > YesPercent
UPDATE tableX SET CEOIsChairman='No' WHERE Industry='<the industry value being evaluated>'
Else
UPDATE tableX SET CEOIsChairman='Equal' WHERE Industry='<the industry value being evaluated>'
End
View 1 Replies
View Related
Apr 14, 2006
Hi,
I have developed an application where i am inserting all the records from the dataset into sql mobile 2005. Dataset contains a primary key which is an uniqueidentifier datatype. While inserting the data it is inserting properly into the database but it is changing the value of primary key which is in the dataset.
I am using the below syntax, please suggest me so that to avoid creating a different uniqueidentier key into the database.
conAdap = new SqlCeDataAdapter(strQuery, conSqlceConnection);
SqlCeCommandBuilder cmdBuilder = new SqlCeCommandBuilder(conAdap);
conAdap.AcceptChangesDuringFill = false;
conAdap.Fill(dsData);
conAdap.MissingMappingAction = MissingMappingAction.Passthrough;
conAdap.InsertCommand = cmdBuilder.GetInsertCommand();
conAdap.InsertCommand.Connection = conSqlceConnection;
int r =conAdap.Update(dsData.Tables[0]);
Thank you,
Prashant Mulay
View 1 Replies
View Related
Nov 27, 2007
Hi,
I want to be able to spot when the same employee name gets added to my grid. This is to ensure that I cannot not have the same firstname and lastname (i.e. cannot have 2 John Smiths).
It is kind of like spoting for duplicates but they are not PKs. I was hoping if there was a way you could identify the the feild values on the "inserting" event of the datasource so I could put some logic in. The reason for placing it there is because we have the e.cancel = true command.
Thanks in advance,
Jon
View 5 Replies
View Related
May 5, 2004
Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
------------
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..
View 3 Replies
View Related
Aug 12, 2015
I am trying to insert a value in the table. Sometimes the value has this symbol at the end. I just want insert the entire value except this symbol. The values can be like this that I am insert in the table
Insert into Table1(col1)
Values (12345§ )
The values can be, below is just an example
12345§
3456DER§
5678D
FGR564
I want to insert only these values from the above data
12345
3456DER
5678
FGR564
View 6 Replies
View Related
Aug 2, 2006
Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
View 1 Replies
View Related