|
Assuming there is an @@IDENTITY equivalent in Access (if not kludge one) then change the last line of your insert proc to
select @@IDENTITY as PersonID
Change you command from executenonquery to executescalar and put the returned value int a variable, oh you already do that but on a nonquery?
Instead of relying on the RETURN as in SQL Server you need to explicitly return a result set (PersonID)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks.
I do that (assign to the result variable the value of executeNonquery) to see if it succeeded.
Thanks again.
|
|
|
|
|
check below code
Dim cnDatabase As ADODB.Connection
Dim rsNewAutoIncrement As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim strPathToMDB As String
strPathToMDB = "C:\NewJet4.MDB"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathToMDB & ";"
Set cnDatabase = New ADODB.Connection
cnDatabase.Open strConn
strSQL = "CREATE TABLE AutoIncrementTest " & _
"(ID int identity, Description varchar(40), " & _
"CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "INSERT INTO AutoIncrementTest " & _
"(Description) VALUES ('AutoIncrement Test')"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
strSQL = "SELECT @@Identity"
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _
adLockReadOnly, adCmdText
MsgBox "New Auto-increment value is: " & rsNewAutoIncrement(0).Value
rsNewAutoIncrement.Close
Set rsNewAutoIncrement = Nothing
strSQL = "DROP TABLE AutoIncrementTest"
cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords
cnDatabase.Close
Set cnDatabase = Nothing
or go to followin link
http://support.microsoft.com/kb/232144/EN-US/[^]
for more info will provide info that you want
|
|
|
|
|
Thanks it works
|
|
|
|
|
hi,
i have to insert record in table ,and it has Composite Primary Keys
col 1=PRJID and Col2=RSRID and both columns has foreign keys on two tables.
The problem was when i try to insert a record in table through query analyzer ,i got error msg as time out expire and my lan connection also working fine.
|
|
|
|
|
If a reindexing is going on in that table or the table is being locked by another update/insert that could happen I guess... or maybe a snipper [you are blocking someone else so DBA killed your connection or something like that]
|
|
|
|
|
hi, i got a database table that have 4 column
id,subject,contents,date
the date column is varchar datatype and its the data is DateTime.Now.ToString()
How do i select 7 days record from the table..
Select * from announcement WHERE ????
thanks
|
|
|
|
|
if your date and datetime information were stored as such (and not as a string), then you could simply use SQL stuff such as "ORDER BY" and "TOP N" since databases that support date types also know how to sort them chronologically.
If you insist on storing datetime information as strings, you will:
- be subject to problems due to different regional settings (which day/month/year is 02/03/04 in each country on Earth?)
- need conversion functions everywhere in your SQL statements.
You might want to read this little article[^].
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
As Luc has said you need to change your data type to datetime, leaving it as varchar is a disaster. Think about this, to get the top 7 records you could do the following
SELECT TOP 2<br />
Dt<br />
FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt<br />
FROM Period ) X<br />
ORDER BY Dt desc
That will have to convert EVERY datestring in your table, creating an index is a waste of time and all the regional problem Luc mentioned will be visited upon you.
FIX YOUR DATA NOW!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: to get the top 7 records you could do the following
SELECT TOP 2
Dt
FROM (SELECT DISTINCT CONVERT(DATETIME,PeriodDate) Dt
FROM Period ) X
ORDER BY Dt desc
Call me picky, but won't this give the top 2 records? Or am I missing something here?
|
|
|
|
|
I only had 3 records in the test table I wrote this against  . If the OP can't work out the difference then I probably can't help him.
And yep I call you picky 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: And yep I call you picky
Every forum needs at least one anal-retentive pedant to keep things in line. I do my best.
|
|
|
|
|
David I doubt you are the only one - AR is a required trait for some aspects of development so you are welcome.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What do you mean by "select 7 days record from the table"? Do you mean that you want all the records from the table for the last 7 days? Or do you mean that you want the top 7 records from the table irrespective of date? Or do you mean that you want all the records for the most recent 7 days which are actually in the database (which chronologically may range over more than 7 days if for example you have no entries at the weekend)?
Each one of these will require a different query. None of which are particularly straightforward given the way in which the dates are stored in the table.
|
|
|
|
|
Hi
I am planning to implement a custom version control system.
Is there anyway to represent physical directory structure in database?
Or should I represent them using 'tree' data structure?
If using tree data structure, how can I save the contents of tree to an external file??
thanks
fadi
|
|
|
|
|
which version of SQL are you using?
If SQL Server 2008, you may want to look at the FileStream Datatype[^]
I guess you can create a self referencing table [ heirarchical ] and either use the datatype above or file path and have some code to export it out. I dont know exactly what you want to do but it seems dangerous
|
|
|
|
|
Along with the filestream storage you can use the hierarchicyid you define your tree structure but you are going to have a coordination problem between the physical structure and the hierarchy in the table.
The filestream will not allow you to move the files via explorer so that would be one benefit.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hallo,
Did anyone solve the problem with a Boolean Column in a datatable for .net / c#?
I am new in Oracle development and want to convert my database to oracle. But there is no Boolean datatype with SQL and I want to display a gatagridview column as a checkbox. With Postgres or MSSQL there were no problems with that. But with Oracle I search a elegant solution.
Any ideas?
Thank you.
camau
|
|
|
|
|
Boolean datatype is not defined by ANSI.
So either you'll have to use a number or a char, for example: flag char(1) check (flag in ( 'Y', 'N' ))
|
|
|
|
|
I have installed Oracle 9i I want to install now ( Oracle forms 6i ) ...On inserting the installation CD...the "Oracle installer" screen appears ..then "Oracle installation settings" form appears ..Here we have to specify
Company Name:xyz
Oracle home details
Name: OraHome92
Location: C:\oracle\ora92
Language: English
With theses entries when I click OK button, the following error message with the Title "General Information" appears:
The location specified "C:\oracle\ora92", is already used as an Oracle Home for 'Oracle 82 Beta'. It can not be used as an Oracle home for 'nt'.
The OS am using is Windows XP Professional with
Pentium 4 ...Please help me out...Thanking you in advance.
|
|
|
|
|
Try to rename c:\oracle\ora92
|
|
|
|
|
You probably need to check on some registry entries. Search for HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE. There may be an entry about an Oracle Home that is no longer valid and can be deleted.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
If you search the Oracle forums you will find several threads discussing this issue. I haven't read them in detail but from what I can gather, the installer doesn't like installing Forms 6 and Oracle 9.2 to the same home location. There are a number of suggestions to work around it in this thread:
http://forums.oracle.com/forums/thread.jspa?threadID=340452[^]
|
|
|
|
|
Here's my quandry: I am creating a new table that's holding records of message sent within the system. One of the fields is what type of message is being sent which currently falls into only 3 options.
Currently, it's likely that this is not going to change that much but I'm trying to work out if it's better to create a new table to hold these and thus enforce referential integrity or simply not bother and rely on the enumeration on the code end.
Queries against the data is likely to want grouping by message type but with only 3, it just seems a bit overkill.
Any suggestions?
|
|
|
|
|
From a purist standpoint, it would be better to create a parent table like:
MessageType
ID int,
Description Varchar
and create a constraint against your Message Table to ensure that only valid message types are created in your table. You won't pay much (if any) performance penalty because your parent table is so small.
That's my vote. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
