How To Read Just Inserted Auto Incremented Primary Key To Use It As Parameter?
Hi. After inserting data (new row) by using DetailsView control, how to read auto incremented primary key (identity) of this new row from sql database to use it as parameter passed to stored procedure?
View Complete Forum Thread with Replies
Related Forum Messages:
Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record. And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View Replies !
Auto Incremented Integer Primary Keys Vs Varchar Primary Keys
Hi, I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key. For example: id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null] isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime. Regards Mike
View Replies !
Auto-incremented Column
Hello, I have a column that is not auto-incremented. I would like to make it auto-incremented. For the moment, the only solution I found is to delete my column and to add a new auto-incremented column. The problem is I cannot delete it without deleting dependencies on it... So is there a solution to make an existing column auto-incremented or is it necessary to a create a new column ? Thank you in advance for your help.
View Replies !
Auto-incremented Fields Error
I am trying to insert into a SQL Server table from an Oracle database. This table has an auto-incremented field, and when I try to insert into this table I get the following error: Code Snippet [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column '<column_name>', table '<my_table>'; column does not allow nulls. UPDATE fails.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (SQL State: 23000; SQL Code: 515) When I turn off all the triggers in the database, I don't get this error. But that is just a test environment, the production environment will need to have those triggers activated. Any ideas on what is going on here? Thanks.
View Replies !
Padding An Auto-incremented Int Column To Ten Zeros
Good day gentleman, Is it possible to pad columns of Int data type with zeros? I currently have a primary key column of data type Int which is auto-incremented by 1. My problem is I need to pad the auto-incremented ID's with ten zeros so, 0000000009 instead of 9. I can pad the ID's in the c# dataaccess layer, but i need to generate/store them padded in the database to allow direct reporting. I am using Enterprise Manager and i can see a Formula field when the table is in design view, can i use this? i am just guessing here. Any comments or pointers would be a big help.
View Replies !
Cannot Read A Record Just Inserted
Hi, I have a small app which has the following statements: Insert into tableA (ID, Name) Values (iID, vName) Select * from tableA where ID = iID where iID and vName are variables. If I run 100 instances of this app, each inseting mutually exclusive IDs, the second statement may occasionally return no record. Why ? By the way, I use OLEDB command object and didn't specify any transaction isolation level (use the default in OLEDB). Any feedback would be greatly appreciated. Thanks. Ray
View Replies !
Cannot Read A Record Just Inserted
Hi, I have a small app which has the following statements: Insert into tableA (ID, Name) Values (iID, vName) Select * from tableA where ID = iID where iID and vName are variables. If I run 100 instances of this app, each inseting mutually exclusive IDs, the second statement may occasionally return no record. Why ? By the way, I use OLEDB command object and didn't specify any transaction isolation level (use the default in OLEDB). Any feedback would be greatly appreciated. Thanks. Ray
View Replies !
Get Primary Key Of Last Inserted Record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
View Replies !
Setting Primary Key Of New Row Being Inserted
Not sure if this is the right place for this post or not, but I'll give it a try... If I am inserting a record into a table that uses a Primary Key, but not an Identity column, how do I insert the new primary key value? I started out with SQL just letting the Identity column increment itself for primary keys, but I cannot do that for this particular situation. If 'AddressID' is the primary key in the 'Address' table, could I do something like this: INSERT INTO Address (AddressID, Field1, Field2, ...) VALUES (**max value of primary key in Address table, plus 1**, 'field1data', 'field2data', ...); If that works, how would I make this part of the insert query: **max value of primary key in Address table, plus 1** ? I hope this question is clear and makes sense. If it's not, please let me know.
View Replies !
How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database? My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e) 2 { 3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text; 4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue; 5 try 6 { 7 srcAddSkill.Insert(); 8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score."; 9 lblErrorMessage.Visible = true; 10 txtSkillName.Text = ""; 11 } 12 catch (Exception ex) 13 { 14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message; 15 lblErrorMessage.Visible = true; 16 }
View Replies !
Auto Increment Primary Key
Dear all, I am using SQL express 2005 in visual studio 2008. I would like to use Form View to insert new record in the table. There is a column call "id". I think it is too stupid to let user enter a unique identifier instead of generating by the system. I know how to generate a random integer number in a normal TextBox. However, when I edit the InsertItemTemplate, I cannot modified the value of that textbox. For example, i couldn't use "TextBox1.Text = temp;" here. How can I achieve to change this TextBox's value by the system that I would like to generate a random interger. Thanks a lot. Regards, Eric Chan
View Replies !
Auto-increment My Primary Key: Why 2 Instead Of 1?
Hi all,I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same. By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet. Thank you very much,Kenny.
View Replies !
Auto Increment An Primary Key
How do I make a Primary Key in a table automatically increment as a new row of data is entered? do i have to do it with a trigger?? if so what is the code behind it....what I have is a Applicant table, which holds the ApplicantID (<<<needs auto incremented), FirstName, LastName, Address, City, State, Zip, and PhoneNumber. I enter all the other information through visual web developer, and what I need to do when the hit submit on the form with the data i want to automatically set an id right there. any suggestions will help!
View Replies !
Auto Primary Key With Fill In
I want a table to have an int auto primary key, that will fill in deleted rows. EG 1 2 4 5 8 The next primary key should be 3 (NOT 9) This table changes often, so just adding 1 to the last number used, will fast run out of numbers. The table is accessed by many users, so it can not be manually generated.
View Replies !
Primary Key-how To Auto Incriment
I can't for the life of me find out how to auto increment the primary key like you can in access. I want to use userIds that will auto increment as new users are added. Can you guys help me out at all? As of now I used this code as an example. I'm using SQL server 2005 CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30));
View Replies !
How To Auto Increment An Alphanumeric Primary Key In SQL? :(
How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue? T___T I hope u get my point.. I want my table to look like this if i have added the dataABC-0001ABC-0002DEF-0001DEF-0002AAA-0001then if il add a new 1, for example ABCit will auto increment to 0003 in the same field, it will look like this after addingABC-0001ABC-0002ABC-0003DEF-0001DEF-0002AAA-0001Will it be possible? :(
View Replies !
Primary Key Auto-increment Reset
Ok - I have two tables that are relational. I have been inserted data into the tables because of testing. I also have been deleting data. My question is how do I reset the auto-incremented Primary key values. For example: Primary key of table one is bizID. Well there are only 3 record currently in the table. The bizIDs are 1-3. If I insert another record the bizID will be 88 because that was the next auto-incremented number. I obviously deleted the other records. I want to start the primary key value over from zero. How do I accomplish this? thnks
View Replies !
How To Auto Increment A Primary ID Field?
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View Replies !
Dropping An Auto Numbered Primary Key And Add A New One
The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table. I need to write SQL to drop the current primary key and add a new one Say "RecordId" as the new primary key and which should be a autonumber too. any idea. thanks in advance
View Replies !
Primary Key Auto-increment Reset
I'm still looking for a solution on msdn, but i've decided also to post my question here. How can i reset my primary key field auto-increment back to 0 in runtime (or in designtime)?
View Replies !
Put Inserted Value Into A Parameter In A Trigger
How would i get the value of a field that i just inserted and put that into a parameter, so that i could update another table. This is the code that i used in the trigger that did not work: @field1 = select srcfield1 from inserted Anyway here is the full code: CREATE TABLE Source (srcID int IDENTITY, srcField1 nvarchar(50)) CREATE TABLE Destination (destID int IDENTITY, destField1 nvarchar(50)) go CREATE TRIGGER tr_SourceInsert ON [dbo].[Source] FOR INSERT @Field nvarchar(50) output AS SELECT @Field1 = SELECT Field1 FROM inserted UPDATE Destination SET Field1 = @Field where destID = '1' go INSERT Source(srcfield1) VALUES ('A') go
View Replies !
Redefining An Auto-generated Primary Key Constraint Name
Hey there :)I'm using Ruby on Rails to create a migration of a legacy database onan MS SQL Server. I'd like to name my constraints myself, such as'pk_authors', but in the cases where a table has an auto incrementedIDENTITY(1,1) id field, Rails takes over and adds the primary keyitself when creating the table.This is fine, except then the constraint gets a name like'PK__authors_384934' which is not very intuitive or easily remembered;)I'd like to just throw in an 'ALTER TABLE authors...' statement justafter that table has been created, but I'm not sure how to go aboutrenaming the auto generated constraint since the name it gets ispartially random. Is there any way to indirectly refer to theconstraint like 'RENAME PRIMARY KEY CONSTRAINT ON authors TOpk_authors' or something like that, so that I can rename theconstraint?Thanks in advance,Daniel Buus :)
View Replies !
Help - Deleting Existing Auto-generated Primary Key
hi guys, just a question regarding database design i have a table with an auto-generated primary key but the problem is this: say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again. how do i remedy this? thanx
View Replies !
How To Auto-increment Primary Key When Adding A New Row Using Update Method?
Hi guys,I followed the ASP.net official tutorial to create a DAL & Business Logic Layer (http://www.asp.net/learn/dataaccess/tutorial02cs.aspx). I have a table with a int ID field. I wish to write a function to add a new entry into this table but have the ID field auto-increment.The ID field is set as the Identity Column and has a Identity Increment & Seed of "1". If I manually go to the table and insert a new record leaving the ID value null it automatically increments. But if I create a C# function to add a new entry I get an error saying that the ID field can't be Null. Is there any way to use the Update method as shown on line 14 below to add a new entry but with it automatically incrementing? I did create a function called InsertDevice that simply inserts the other fields using a SQL INSERT and it auto-increments fine, just wondering if there is a way to do it using the DataTable and the Update method? Thanks for any help!!! 1 public bool AddDevice(string make, string model) 2 { 3 //cannot have the same device entered twice! 4 if (Adapter.FillDeviceCountByMakeModel(make, model) == 1) 5 return false; 6 7 RepositoryDataSet.DevicesDataTable devices = new RepositoryDataSet.DevicesDataTable(); 8 RepositoryDataSet.DevicesRow device = devices.NewDevicesRow(); 9 10 device.make = make; 11 device.model = model; 12 13 devices.AddDevicesRow(device); << Error thrown Here! 14 int rows_affected = Adapter.Update(devices); 15 16 return rows_affected == 1; 17 }
View Replies !
Getting The Value That Was Just Inserted Passed Back Output Parameter
I have a SP that inserts data into a table with an identity column I want to get that id and use it in my response.redirect however I keep getting 0 back instead of the identity. Relevent Sql 2005 Declare @NEWEMPID int --after an insert happens in sqlSet @NEWEMPID= SCOPE_IDENTITY()--last thing in my sqlReturn @NEWEMPID Or I tried NEWEMPID as an output parameter VB.Net Dim retValParam As New SqlParameter("@NEWEMPID", SqlDbType.Int) retValParam.Direction = ParameterDirection.ReturnValue'execute the sqldatasource insert command sqlEmpNewEmp.Insert() Dim EmpIDVar As Integer = Convert.ToInt32(retValParam.Value) Response.Redirect("http://www.mysite.com?EMPID=" + Server.HtmlEncode(EmpIDVar)) sqlEmpNewEmp.InsertParameters.Clear()
View Replies !
SQL Auto Send Email Based On Parameter
I would like some help conceptualizing how to acomplish this task. My client has requested the ASP.NET web/SQL Server application send an email to one of our locations based on whether that location complied (via the web app) to each week's report. Here's how the flow would go: The location checks the webpage weekly and selects their compliance to each record on the report. (They could overlook one of the records and not make a selection.) The SQL db would then query the compliance table and create a report of those locations with at least one "non-compliance." Then the Db would send a reminder email to each location on that report. The Db tables have the email address info assigned to each location, so I can join that to the compliance log table. Can this be done? I am using SQL Server Management Studio Express and Visual Web Developer (VB.net). Thank you for your ideas,SMc
View Replies !
Auto Reload Of Report On Parameter Selection??
this might be a stupid question. I have a parameter which has a default value set to it. when a second value is selexcted is it necessary to click the view report button. Could it be set to auto reload on dropdown selection. I am using reporting Services 2000 Thanks.
View Replies !
How To Read Parameter Array In Stored Procedure
for example i have 2 array parametereg: @event =injury,opd,icu. @month=jan,feb,mar,apr,dec. based on this parameter i wanted to retrive respective patient history. at the first how to split array in stored procdure, and write paricular event. normally in asp.net c# the following code is excuted as follows. string[] event = @event .Split(''', ','); string[] month = @month.Split(''', '"', ','); public ArrayList mai = new ArrayList(); public string[] janhistory ; for (int i = 1; i < event.Length; i++) { if (event[i].Equals("injury")) { foreach (string Items in month) { if (Items.Contains("jan")) { mai.Add(Items.ToString()); } } janhistory = mai.ToArray(Type.GetType("System.String")) as string[]; gh.getmailcomm(janhistory, "Acc398"); getmailcomm is function used as below public void getmailcomm(string[] month,string accno) { k = comm.Length; getmailcomarr = new string[k]; vasi = new int[k]; vasisid = new int[k]; for (int m = 0; m < k; m++) { string l = comm[m]; com = l.Substring(0, 3).ToString(); getma = l.Remove(0, 3).ToString(); com = com + "Comments"; if (getma == "Mail") { SqlConnection con = new SqlConnection(cn); con.Open(); string sql = "Select VAS_Id," + com + ",VAS_Sub_Id from Acc_MVP_Table1 where " + l + "='1' and AccSlno='" + acc + "'"; SqlCommand getmacom = new SqlCommand(sql, con); rd = getmacom.ExecuteReader(); getmailcomarr[m] = "NoComments";// default value if (rd.Read()) { getmailcomarr[m] = rd.GetString(1); vasi[m] = rd.GetInt32(0); vasisid[m] = rd.GetInt32(2); } } } } } and also i first splited both array, but on above query i not able write stored procedure
View Replies !
Generating An Incremented Column
I am trying to update a new column called "sorting" which is initialized with value '0', with incremented values strating at 1 (incremented by 1 each time), before doing that I need the table to be sorted using three columns. I am using a cursor but it is not working I need your help. Thanks in advance: FOLLOWING IS THE CODE: set @ordering_count=0 declare review_test_cursor cursor scroll keyset for select oid,decision_date,ranking,sorting,decision_id from DECISION_FLAGS order by oid,decision_date,ranking asc open review_test_cursor fetch next from review_test_cursor while (@@fetch_status = 0 ) Begin update DECISION_FLAGS set sorting = CAST ((@ordering_count + 1) as VARCHAR) set @ordering_count = @ordering_count + 1 fetch next from review_test_cursor end close review_test_cursor deallocate review_test_cursor
View Replies !
The Report Parameter 'param1' Is Read-only And Cannot Be Modified. (rsReadOnlyReportParameter)
hey guys.. I'm trying to get the report that I need to be filtered by a parameter value (named Param1) I created a parameter in the data tab and tried to pass a value from my Web application to the report services.. now my report have that textbox that prompts me to enter the filtering value for the report I want.. everything works fine till this point.. now I have another webpage that has a report viewer and a textbox for the user input the value they want.. and pass it to the report.. and I'm using the following code to pass the value to the report I made earlier.. Dim par(0) As Microsoft.Reporting.WebForms.ReportParameter par(0) = New Microsoft.Reporting.WebForms.ReportParameter("paramSiteCode", TextBox1.Text.ToString) ReportViewer1.ServerReport.SetParameters(par) ReportViewer1.ServerReport.Refresh() however.. now I'm getting an error.. "The report parameter 'paramSiteCode' is read-only and cannot be modified. (rsReadOnlyReportParameter)" what am I missing here? I've been thru the help and forums.. and the code should work.. or so I hope it would.. thanks in advance..
View Replies !
Read Rows AND An Output Parameter From Codebehind Returned By Stored Proc?
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
View Replies !
Odd Error Upon SqlDataReader.Read() &"Invalid Length Parameter Passed To The Substring Function.&"
An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error: "Invalid length parameter passed to the substring function." Here's the code in question: orderConnection.Open() orderReader = orderCommand.ExecuteReader() setControls(orderReader) ... Private Sub setControls(ByVal dr As SqlDataReader) If (dr.Read()) Then '<--*******problem line******* The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?
View Replies !
Ssrs - Auto Time And Auto Updates In Report
My report in SSRS does update only when i run that query in MS access. I migrate the 97 database to 2003 ms access database. I imported that report in ssrs using import tool in ssrs. i have set up shared data sources using odbc connection to ms access database. auto refresh works only when i manually run query in ms access. Here's my query....it has several table. SELECT Call_Completed_by_Engineer_Daily.FullName, Call_Completed_by_Engineer_Daily.[Remaining Calls], Call_Completed_by_Engineer_Daily.[Fairfax Calls], Call_Completed_by_Engineer_Daily.[SystemS Calls], Call_Completed_by_Engineer_Daily.[AACPS Calls], Call_Completed_by_Engineer_Daily.[NSN Calls], Call_Completed_by_Engineer_Daily.[MVA Calls], Call_Completed_by_Engineer_Daily.[Other Calls], Call_Completed_by_Engineer_Daily.[Total Calls], Call_Completed_by_Engineer_Daily.[All Tech], Call_Completed_by_Engineer_Daily.Department FROM Call_Completed_by_Engineer_Daily WHERE (((Call_Completed_by_Engineer_Daily.FullName) Not Like 'Jim Rowland*' And (Call_Completed_by_Engineer_Daily.FullName) Not Like 'Bill Bockmiller*' And (Call_Completed_by_Engineer_Daily.FullName) Not Like 'Hong Nguyen*' And (Call_Completed_by_Engineer_Daily.FullName) Not Like 'Hossein Talaei*' And (Call_Completed_by_Engineer_Daily.FullName) Not Like 'James Hawkins*' And (Call_Completed_by_Engineer_Daily.FullName) Not Like 'Joe Cooley*' And (Call_Completed_by_Engineer_Daily.FullName) Not Like 'Peter Threatt*')); This is a final query that i am getting report from. They are other queries that grab the information from table. For example, FullName query - Remaining Calls - Fairfax Calls...... FullName Remaining Calls Fairfax Calls SystemS Calls AACPS Calls NSN Calls MVA Calls Other Calls Total Calls All Tech Department Jay Brittain 10 4 4 JB DC Jeremy Ditto 2 2 1 3 JD DC Jing Kenny Hu 8 1 1 1 3 JKH DC Phone Soe 11 1 2 3 PS DC This is datasheet view of that query. I imported access report to sql reporting service. i can see it that report in web server too. However, i have to hit refresh every time. Can you tell me where to set up time? i want autorefresh time with all auto updates information. can anybody tell me plz?
View Replies !
HELP!!!! - Report Parameter &&"Read Only&&"
I'm stuck with a report not being able to use the URL parameter. I get the error: The report parameter is read-only and cannot be modified. I do have a prompting text for the report, and I've checked the XML and there are <PROMPT></PROMPT> tags!
View Replies !
Reset Database Files From Read-only To Read-write
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message: Server: Msg 3415, Level 16, State 2, Line 1 Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded. What command(s) are needed to make these files read_write? thanks
View Replies !
|
TRACKER
