i have a database consisting of a table PROJECT(p_id,p_name,P_manager) i am using oracle 9i as my backend and auto increment is possible only through triggers and sequence. instead i would like to retreive the data p_id onto a combo box.

simply put- when i add data into my table PROJECT i should be able to add only p_name and p_manager. p_id should get auto incremented from last value and get stored in database. here is the code i use:

dim a as double
rs.MoveLast
a = rs.Fields(0)
a = a + 1

con.Execute "insert into project values (" + a + ",'" + Text2.Text + "','" + Text3.Text + "')"
                          'text2 and text3 is where i input p_name and p_manager

MsgBox "Project Added Successfully!!"

the error i am getting is type mismatch. p_id is number(in oracle) and here i declared a as double. where am i going wrong? is there a better way to write this code? please help..

I assume the code is erroring out on line 3? If so, you could always use one of the conversion functions like CDbl to convert rs.Fields(0) to a Double. You might also consider using the "val" function. The drawback is you might run into truncation issues.

I'm not that familiar with the characteristics of all the Oracle numeric datatypes (it's been a long time since I worked with Oracle). Does the NUMBER datatype return an integer value or a decimal value? (e.g. 1 as opposed to 1.00) If integer, you might consider defining variable "a" as something different. Maybe a Long (long integer) or a Variant rather than a Double (double-precision floating-point decimal).

Hope this helps some! Sorry I can't be of more assistance.

dear BitBit,

NUMBER dataype returns an integer value. and i tried declaring a as Long but it still shows same error. how do i use cdBI?

If the datatype in your database is Number, maybe try declaring it as integer in the program.

Dim a as Integer

rs.MoveLast
a = rs.Fields(0)
a = a + 1

Is p_id not set to an auto increment number in your database? If so, it will return an error if you are trying to overwrite the number after it has been set to auto.

i am using oracle 9i, there is no inbuilt function such as auto increment.

Then abelingaws code should work fine. If you still have problems, declare a as a variant for test purposes. a Variant will return any value or length -

Dim a 
rs.MoveLast
a = rs.Fields(0)
a = a + 1

msgbox a ''See if a value/correct value is returned...

This eats resources but will help in solving your problem...

Edited 4 Years Ago by AndreRet

This question has already been answered. Start a new discussion instead.