PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] serial number in recordset when such field not available-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] serial number in recordset when such field not available

Hybrid View

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2002
    Posts
    164

    Resolved [RESOLVED] serial number in recordset when such field not available

    Hi all
    I have a table customers with fields
    custcode
    custname
    city


    SELECT srno, custname FROM customers ORDER BY custname

    In this query, srno should increament automatically.
    Is it possible?

    I want to fire a query which will give me following result.
    1 customer1
    2 customer2
    etc

    Thanks

  2. #2
    Hyperactive Member
    Join Date
    Jun 2006
    Posts
    396

    Re: serial number in recordset when such field not available

    In this query, srno should increament automatically. Is it possible?

    No! srno is not a field in your table so it cannot be selected by your query.

    Add a new field to your table called srno and set it to auto increment, then your query will work.

  3. #3
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: serial number in recordset when such field not available

    what is the database that you are using...try ROW_NUMBER()
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jul 2002
    Posts
    164

    Re: serial number in recordset when such field not available

    Thanks for reply.

    GaneshMoorthy:
    Data base is MS SQL Server.
    How to use row_number is select query. will you pls give an example.
    Will row_number work properly with ms sql server database table?

    LinXg:
    Autoincreament field may not be possible in this case as query is intended for data ordered by custname field and where data insert might have happened in any order and so autoincreament field may not have serial values.

  5. #5
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: serial number in recordset when such field not available

    Radhesham, I found this example to get a rownumber in your query...
    VB Code:
    1. SELECT CustCode, CustName,
    2. (SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode >= C.CustCode) AS SrNo
    3. FROM Customers C
    4. ORDER BY CustCode
    hope this works for you...
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jul 2002
    Posts
    164

    Re: serial number in recordset when such field not available

    Thanks.
    Thank you for providing exact solution.
    Radhesham

  7. #7
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: [RESOLVED] serial number in recordset when such field not available

    Radhesham: You are most welcome !
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width