I've searched the net but I couldn't find any SQL code on how to insert an autonumber column into an access database and table. Can anyone include the code?
I have an Access database that uses an autonumber for the primary key. I hv app. 110 items in the table, and yet when I add a new item, it autonumbers as 534097 or some ridiculously long number.
How can I get it to go back to a smaller number without removing/trashing existing ID fields?
OK, I realize this is a subject that almost every developer would encounter, so I understand the answer is probably out there somewhere. Suffice it to say that I have been looking, both in this forum and on google, found many SQL server answers, and one for Access, which I have tried with no success. See the following: Code:
sql = "INSERT into someTable(IntColumn) values (" & fakeValue & ") SELECT @@IDENTITY" set rs = conn.execute(sql) response.write "New ID was " & rs(0)
This results in a JET database error, Missing semicolon at end of SQL statement. I tried adding it after the close paren, after the select @@identity, and after both, with the same error.
Select data from 2 tables at the same time but there is no relationship between them. This is what i want but i dont know how to join them.Here is what i was trying.Code:
sqlstr = "SELECT * FROM persinfo,positions WHERE personid.personid = '"& session("personid") &"'" rs.Open sqlstr,conn,0,2
sqlstr1 = "SELECT * FROM positions WHERE jobidid = '"& session("jobid") &"'" rs.Open sqlstr1,conn,0,2
I am trying to write a SELECT query in ASP to retrieve information from two databases (that must be kept seperate :-().
I have managed it using Access using something similar to the code included below, which I have modified for ASP. Looking around this seems to not be possible in ASP, instead the general recommendation is to link tables??? I would prefer to do it using ASP. Code:
-Dim cmd As OleDbCommand -cmd = New OleDbCommand("select * from qmsstats where firstname='jason'", OleDbConnection1) -OleDbDataAdapter2.SelectCommand = cmd -DataGrid1.Visible = True -OleDbDataAdapter2.Fill(DsQmsStats1) -DataGrid1.DataBind()
In the above example I need to change 'jason' to a variable. I need the variable to be pulled from a textbox. IE: txtusername.text. Please advise how I can get the value from txtusername.text in place of 'jason'.
I am trying to come up with a select statement which would select info from the latest 5 dates. I know how to do the latest date using SELECT * from table1 WHERE PDate = (Select Max(PDate)
Is there an easy way to write a select statement to give the latest 5 dates? My data is in a SQL Server database.
I'm creating a basic ASP guest book, keeping my guest book entries in a Microsoft Access database, and using ADO to access it. My problem is that I am using the autonumbered ID field to split my guestbook into pages of 10 entries. It works for the most part, but if I delete an entry, a gap is left in the records and I end up 9 entries instead of 10 on that page. Basically I need a better way of sorting this out - I thought of SELECTing all the records into an array first and then working from that, but I can't get array to be dynamic enough in VBscript
It just inserts a new record into the db. What we did was add an autonumber field to the db, but now it won't update.
We know why - because it needs a value for every category in the db - but we don't know how to code it so it will update the db correctly. So, if anyone could help me with the (????) line in the coding so it will correctly update, that would be perfect!
What is the best way of using an AutoNumber system on an SQL server?I've been used to using Microsoft DBs for my online apps and have now upgraded to using Microsoft SQL server.
I want to be able to insert a new record into the database not such a problem but I want each record to have a unique identity no eg. NewsID. I know that you can do a lookup of the MAX number on insertion but if two people are inserting at the same time this may cause problems with duplication.
I use (something like) following code to add new records to a database, but it doesn't work!!! I need to know the value of an AutoNumber-field when I add the record. Why doesn't it work and how can I make it work? I think it should be easy, but I can't figure it out! Code:
I have a page that does an insert into a table in an Access DB which has an autonumber primary key field. Following this insert I need to be able to get the ID that was generated. If I were using SQL Server I would simply return fetch @@IDENTITY from a stored procedure for example.
Does anyone here know of something similar I can use in Access to be able to get that ID? I tried just using a SELECT query with all the information I had just inserted but it comes up empty unless I put it in a separate request (i.e. redirect to a new page passing all the info through the querystring and then building the query on the redirect page...which is a huge pain and takes FOREVER to run.
I have a form that is filled out and put into an Access database. The id field is Autonumber.
What I would like to do is also insert this data (or some of it) either into another database or another table, but I would like the created AutoNumber to be a part of this data. Is there any way to get this number immediatly after insertion?
I'm trying to write the code for an autonumber to go in to a text field when the page is open. I had it working in access but of course it won't work in asp. The format has to be for example: PBC-1200.
I have a recordset with two three fields. One is an autonumber field called ID , one is a text field called Name and the last is a date/time field called DateEntered. How can I programatically determine the autonumber field ? Code:
I am using Ms Access database and I have set 3 digits (e.g. 000) for an autonumber field of my table.Its working fine in Access and incrementing automatically like this (001, 002, 003 ...), but when I call the data on a web page using ASP its displaying these numbers like this (1, 2, 3, 4 ...) I dont understand why is it not displaying the numbers as it is in the table.
Why won't this code work - the "id" fields are autonumbers
Code:
<select size="1" name="Agent" tabindex=""> <option value="Select">Select Owner</option> <% x = rs("id") Do While NOT rs2.eof y = rs2("id") Response.Write "<option value='" & rs2("id") & "'" If x = y Then Response.Write " Selected" End If Response.Write " >" & rs2("first") & " " & rs2("last") & "</option>" & vbCrlf rs2.MoveNext Loop %> </select>
I insert the question in the correct place which is related to the table 1 but when it come to insert the answer for that specific question id didn't work .. it always take the first question in the table Code:
I want to take the last record in my database and display it on my ASP program. From my understanding, when last record + 1, it will be autonumber right. It's like generaing the autonumber for invoice number.
For easier view, my last record is 21. So I want that 21 appear in my ASP system as 22. Is anyone can give me the idea how to make it?
I'm writing a database admin page in ASP. I had it adding records correctly. Then I added an autonumber field ("ID") to the table, and it stopped working. The sql I'm using is Code:
This is my query.i have a access database, hooked up to dreamweaver mx. inside the database are 3 tables. sun/moons/planets. the planets table is linked to the moons table. In the planets table are the fields: planet name; Text_1; Text_2; planetID; Distance; Pic_1; Pic_2; What i wish to do is from my index.asp page click on a link which then displays the planet and EITHER text_1 or Text_2, and seperately Pic_1 or Pic_2.
I have tried using random nos 1 or 2 but i dont know how to pull the number and access the right table/field
i want to display DATA stored in Access DB by using ASP script.i can read data from access so that task is only to display data is a list like.i have a record of some users and i just want to display the users name in a list.how can i do this.
i am working on a interanet application for Inventory handling, i have a problem while submitting the data to server, the problem goes like this
Data base : Ms-Access Front End : ASP
how to comit the data "say Invoice" to main database is two persons tries to make invoice at same time at different computers, How to maintain the series of Invoice unaffected, how to allocat Invoice number to them, and how to put a check that both of them can't make a invoice of same customer at a same time at different computers.
i am wondering if there's oledb provider for DB2? and where can i get it ? we use asp, not asp.net , therefore we can't install .NET framework , is there any other solution ?
Im trying to write some asp code to copy MS Access Data into an Oracle database. I have one connection open to the access database but when I try and open the oracle connection it says that the ms access tables does not exist.
Basically what Im trying to do is get the data from a row in the Access db and insert it into a row in an oracle db.