BOOKID not incrementing

I writing below code for incrementing Bookid... but it gives error
1) it is gives type conversion error
2) it is not incrementing when i wanted to add new records i.e. it display
B0001 only all the new records i adding...not incrementing

please help me with folllowing code

Private Sub LOADBOOKID()
On Error Resume Next
dim sr as integer
dim srplus as integer
Dim RSC As New RecordSet
RSC.Open “SELECT * FROM BOOKDETAILS”, Connect, adOpenStatic, adLockOptimistic
sr=RSC!BookID
srplus=SR+1
Txtbookid.Text= “B”&Format(srplus,”0000”)
RSC.Close
End Sub


Private Sub Form_Load()
LOADBOOKID
End sub

‘ add new book
Private Sub Command1_click()
LOADBOOKID
Dim RSCAs New Recordset
....
...

End sub

Quote:

---

Originally Posted by giridher2000

I writing below code for incrementing Bookid... but it gives error
1) it is gives type conversion error
2) it is not incrementing when i wanted to add new records i.e. it display
B0001 only all the new records i adding...not incrementing

please help me with folllowing code

‘ add new book
Private Sub Command1_click()
LOADBOOKID
Dim RSCAs New Recordset
....
...

End sub

---

Where is the Code, which is giving error?

Quote:

---

Originally Posted by ComITSolutions

Where is the Code, which is giving error?

---

Private Sub LOADBOOKID()
On Error Resume Next
dim sr as integer
dim srplus as integer
Dim RSC As New RecordSet
RSC.Open “SELECT * FROM BOOKDETAILS”, Connect, adOpenStatic, adLockOptimistic
sr=RSC!BookID
srplus=SR+1............................type conversion error here....Txtbookid.Text= “B”&Format(srplus,”0000”)
RSC.Close
End Sub


Private Sub Form_Load()
LOADBOOKID
End sub

‘ add new book
Private Sub Command1_click()
LOADBOOKID
Dim RSCAs New Recordset



and As regards incrementing bookid it not showing actual error in code, but it is not incrementing....where is the error in code i dont know but it always shows B0001 only with every new records

What is the datatype of field RSC!BookID?.
If it is of type Integer, its always suggested that ur vaiables sr and srplus should be declared as long

pls make it a practice to use the Code or VBCode Tags while posting the properly indented code snippet

Quote:

---

sr=RSC!BookID

---

what are you trying to do here!

Quote:

---

RSC.Open “SELECT * FROM BOOKDETAILS”, Connect, adOpenStatic, adLockOptimistic

---

It should be

Code:

---

RSC.Open “SELECT * FROM BOOKDETAILS”, Connect, adOpenStatic, adLockOptimistic,adCmdText

---

Edit:
Also remove the On Error Resume Next statement as this would camouflage any error generated. you should be better off without using it!

Quote:

---

Originally Posted by VBFnewcomer

pls make it a practice to use the Code or VBCode Tags while posting the properly indented code snippet
what are you trying to do here!
It should be

Code:

---

RSC.Open “SELECT * FROM BOOKDETAILS”, Connect, adOpenStatic, adLockOptimistic,adCmdText

---

Edit:
Also remove the On Error Resume Next statement as this would camouflage any error generated. you should be better off without using it!

---

i will try and come back to you sir

Quote:

---

Originally Posted by giridher2000

i will try and come back to you sir

---

Sir I tried your solution but it still giving TYPE CONVERSION problem.
First I am using VB6 and SQL server 2000

I have tried bookid with variant type, varchar type,nchar type,big int type as u suggested there is no long type in SQL

Dim RSC As New RecordSet
RSC.Open “SELECT * FROM BOOKDETAILS”, Connect, adOpenStatic, adLockOptimistic
sr=RSC!BookID.................................type conversion
srplus=SR+1................................. type conversion
Txtbookid.Text= “B”&Format(srplus,”0000”)
RSC.Close
End Sub


Second, after removing ON ERROR RESUME NEXT, now it is giving error that either EOF or BOF true, there is no record....

how to solve this problem

pls implement #5 (code tags, indentation).
Anyway...

Quote:

---

Second, after removing ON ERROR RESUME NEXT, now it is giving error that either EOF or BOF true, there is no record....

---

This is your answer! You are outside your data rows.

Quote:

---

sr=RSC!BookID

---

again what are you trying to do?
Also I don't see any code for updating the db with the incremented data!
I would also suggest you to see the ADO tutorial in the FAQ link in my signature
Highlight the portion the first error occurs...

Quote:

---

Originally Posted by VBFnewcomer

pls implement #5 (code tags, indentation).
Anyway... This is your answer! You are outside your data rows.
again what are you trying to do?
Also I don't see any code for updating the db with the incremented data!
I would also suggest you to see the ADO tutorial in the FAQ link in my signature
Highlight the portion the first error occurs...

---

I have written following corrected code but it is giving error at following highligted step :-

Private Sub LOADBOOKID()
dim sr as integer
dim srplus as integer
Dim RSC As New RecordSet
RSC.Open “SELECT * FROM BOOKDETAILS”, Connect,
adOpenStatic,adLockOptimistic, adcmdText
sr=RSC!BookID.........here it is giving error that
EOF or BOF true,no records
srplus=SR+1............here it is giving data
typeconversion error(i tried int,bigint,varchar (as there is no
long) data type
Txtbookid.Text= “B”&Format(srplus,”0000”)
RSC.Close
End Sub

Private Sub Form_Load()
LOADBOOKID
End sub

‘ add new book
Private Sub Command1_click()
LOADBOOKID
....
...

End sub

If your 'Bookid' is stored in the Database as something like "B0001" then you can't add 1 to it since it is not numeric.

You will have to 'remove' the "B", add 1 and then put the "B" back in front
eg

Code:

---

Dim lngBookID As Long
Dim strBookID As String
'
'
lngBookID = CLng(Mid$(RSC!BookID, 2, Len(RSC!BookID) -1))
lngBookID = lngBookID + 1
strBookID = "B" & Format(lngBookID,”0000”)

---

Quote:

---

Originally Posted by Doogle

If your 'Bookid' is stored in the Database as something like "B0001" then you can't add 1 to it since it is not numeric.

You will have to 'remove' the "B", add 1 and then put the "B" back in front
eg

Code:

---

Dim lngBookID As Long
Dim strBookID As String
'
'
lngBookID = CLng(Mid$(RSC!BookID, 2, Len(RSC!BookID) -1))
lngBookID = lngBookID + 1
strBookID = "B" & Format(lngBookID,”0000”)

---

---

i will try and come back to you sir

As VBFnewcomer said, the error means that there aren't any records.. which means that you can't read from them.

Whenever you open a recordset (and want to read from it), you should have code to check if there are records, like this:

Code:

---

Private Sub LOADBOOKID()
  dim sr as integer
  dim srplus as integer
  Dim RSC As New RecordSet
    RSC.Open "SELECT * FROM BOOKDETAILS", Connect,
                          adOpenStatic,adLockOptimistic, adcmdText
    If (RSC.BOF And RSC.EOF) Then
      MsgBox "There are no records!", vbInformation
    Else
      sr=RSC!BookID
      srplus=SR+1
      Txtbookid.Text= "B" & Format(srplus,"0000")
    End If
    RSC.Close
End Sub

---

I don't know what you meant about the type conversion error, especially as there must be a Long data type - unless you aren't using Classic VB (VB6 or earlier).


However, even with these changes, the code is very unlikely to do what you want... forget about the code for a moment, can you explain what are you trying to achieve here? (which BookID do you want to read, and why increment it in code instead of in the database?)


By the way, I would recommend reading the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)


Oh, and as mentioned earlier, when you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

Quote:

---

Originally Posted by si_the_geek

As VBFnewcomer said, the error means that there aren't any records.. which means that you can't read from them.

Whenever you open a recordset (and want to read from it), you should have code to check if there are records, like this:

Code:

---

Private Sub LOADBOOKID()
  dim sr as integer
  dim srplus as integer
  Dim RSC As New RecordSet
    RSC.Open "SELECT * FROM BOOKDETAILS", Connect,
                          adOpenStatic,adLockOptimistic, adcmdText
    If (RSC.BOF And RSC.EOF) Then
      MsgBox "There are no records!", vbInformation
    Else
      sr=RSC!BookID
      srplus=SR+1
      Txtbookid.Text= "B" & Format(srplus,"0000")
    End If
    RSC.Close
End Sub

---

I don't know what you meant about the type conversion error, especially as there must be a Long data type - unless you aren't using Classic VB (VB6 or earlier).


However, even with these changes, the code is very unlikely to do what you want... forget about the code for a moment, can you explain what are you trying to achieve here? (which BookID do you want to read, and why increment it in code instead of in the database?)


By the way, I would recommend reading the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)


Oh, and as mentioned earlier, when you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]

---

Thanks for your reply.....I am developing Library System and I wanted to use BOOKID like B0001.....

I wanted to generate bookid through function so that it can diplays automatically in txtmid.text whenever there is new record to be added... so i have created LOADBOOKID function.

Second type conversion means in earlier reply by somebody sugested me to correct the data type of field to LONG BUT i am using SQL 2000 and there is no LONG data type....

Further if you have any idea or any other suggestion (code snippet) to achieve my aim...that is when user wanted to add new record for BOOK, bookid text box...should be automatically filled with B001 for for first record , for 2nd book it should be B002, 3rd B003...4th B004...so on... my basic idea is this....if you have any other idea please suggest

Is this program ever going to be used on more than one computer at a time?

If so, using your program to increment the numbers is a very bad idea - instead you should let the database system do it for you, as it will make sure that the number is unique. To do that, make the field a numeric data type, and set the "Identity" properties. You will need to add the "B" (and format to the right amount of digits) when you display it in your program, but that is easy enough.

It is nicer to see the number while adding a record, I would recommend against that - instead you should only set the number when the record is actually added, else there will be gaps in the numbers (eg: "B0004" then "B0007") or bad records (only the ID is set, and the rest of the fields are blank) if the user cancels before adding the other details.


There should not have been a data type error occurring.. as you weren't actually reading from the database at that time (just incrementing a numeric VB variable, which has a default value of 0).

Quote:

---

Further if you have any idea or any other suggestion (code snippet) to achieve my aim...that is when user wanted to add new record for BOOK, bookid text box...should be automatically filled with B001 for for first record , for 2nd book it should be B002, 3rd B003...4th B004...so on... my basic idea is this....if you have any other idea please suggest

---

I believe you should be having autoNumber field in your db (if not add it)[Note: check you db manual before doing this as some dbs like MS Access does not allow autonumber to be added after data is added]. Now run your code to find the last/largest autonumber in the db, retrieve it, add 1 to it, and add it to a lable or any other non editable control displayed on the form (for user to see), while prefixing "B00" to it. it should be something like

Code:

---

Me.Label1.Caption = "B00" & lastAutonumber

---

this could be again passed on to the code which is updating the bookID.

sorry I was busy typing without realising si advising you. Thats a good advice.
Simple solution would be to revamp the whole thing you are doing and start afresh, based on the inputs you already got.

As SQL Server is being used, the equivalent of AutoNumber is what I mentioned (Identity).

I would recommend not reading the largest value from this field (ever), as not only does it cause the problems I described before (for multiple users and cancelling a new record), but it also might not be the right number anyway - as the next available number is not always <current max>+1 (such as when a record has been deleted).

I would also recommend not ever writing anything to the field - just let the database create the value instead (which it will do automatically if you don't specify a value).

Quote:

---

Originally Posted by si_the_geek

As SQL Server is being used, the equivalent of AutoNumber is what I mentioned (Identity).

I would recommend not reading the largest value from this field (ever), as not only does it cause the problems I described before (for multiple users and cancelling a new record), but it also might not be the right number anyway - as the next available number is not always <current max>+1 (such as when a record has been deleted).

I would also recommend not ever writing anything to the field - just let the database create the value instead (which it will do automatically if you don't specify a value).

---

So you are saying dont use this LOADBOOKID function. I have to set BOOKID table field and its data type to integer data type and set the same as primary key. Is this you are suggesting. Then in that case i have to write code for that ..... if yes please suggest....me

if u understood what si_the_geek said. Create a stored Procedure to do insert/ Update Operation in your database

In that Sp when No value/0 is supplied to BookId parameter find the Next Id and perform insert.

Code:

---

Select @BookId=isnull(max(BookId),0)+1 from <YourTableName>

---

in Oracle you don't need to add anything. AFAIK there is a func/statement to deliver the next autonumber/sequence to be generated. May be similar thing is there in SQL 200 as well.
bottom line is don't attempt to generate or increment ID programatically instead leave the headache to the db as stated by Si #4

Quote:

---

Originally Posted by giridher2000

So you are saying dont use this LOADBOOKID function.

---

Yes.

Quote:

---

I have to set BOOKID table field and its data type to integer data type and set the same as primary key.

---

It doesn't need to be the primary key, but looks like a good field to use as one (and you should always have one).

The important point here is that in addition to the data type, that field needs the Identity properties set.

Quote:

---

Then in that case i have to write code for that ..... if yes please suggest....me

---

No code.. this is all in the database design.

Quote:

---

Originally Posted by ComITSolutions

if u understood what si_the_geek said. Create a stored Procedure to do insert/ Update Operation in your database

In that Sp when No value/0 is supplied to BookId parameter find the Next Id and perform insert. ...

---

That is definitely not what I meant, and can leave us in a similar situation to before.

As VBFnewcomer correctly said, you should let the database work it out for you - which for SQL Server/Access/etc means simply leaving the field out of your Insert statements.