Click here to Skip to main content
15,072,422 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how to display age in textbox. when enter DOB in textbox.

If rs.state = adStateOpen Then rs.Close
rs.Open "Select DATEDIFF(YEAR,CONVERT(datetime,'" & txtBirthdate.Text & "',103),CONVERT(varchar,V_ISSUEDATE,103)) from VISITOR_MASTER_NEW", cn.Conn, adOpenDynamic, adLockOptimistic
txtAge.Text = rs("V_AGE") ""

this above code i have use for calculate and display age in textbox..
i have used two Example
Textbox1 is for enter DOB
Textbox2 is for output didplay..

What I have tried:

If rs.state = adStateOpen Then rs.Close
rs.Open "Select DATEDIFF(YEAR,CONVERT(datetime,'" & txtBirthdate.Text & "',103),CONVERT(varchar,V_ISSUEDATE,103)) from VISITOR_MASTER_NEW", cn.Conn, adOpenDynamic, adLockOptimistic
txtAge.Text = rs("V_AGE") & ""
Posted
Updated 11-Apr-21 23:41pm
v4

You have previously been given solutions to this question at Calculate age in textbox[^] and Calculate age using DOB[^]. Please do not repost the same question.
   
Comments
Bhavesh Vankar 12-Apr-21 5:00am
   
but it still not resolved and in this question different query i am not understanding what to do if calculated age display in age textbox...?
Richard MacCutchan 12-Apr-21 5:29am
   
They are all the same question. Trying to use a string as a date to find the difference between the two. If you still do not understand the issue then you need to go back and study some of the SQL tutorials in more detail.
CHill60 12-Apr-21 5:31am
   
Try to avoid using titles for your questions that are so very similar - it makes people think that you are just reposting the same question, whereas this one is a very different problem and has nothing to do with the textbox
Firstly - concatenating user input in strings like that leaves you vulnerable to SQL Injection Attacks. Use Parameterized Queries instead - it's difficult to find documentation relevent to VB6 these days but I did find this Parameterized Update query in VB6 - Visual Basic (Classic) - Tek-Tips[^]

Your error message is probably being generated because txtBirthdate.Text is not in a valid date format. This could well be fixed by using a Parameterised Query.

You should probably also not be using all those conversions - if you get the txtBirthdate.Text bit correct and use a Parameterised query that part of the query becomes
SQL
Select DATEDIFF(YEAR,@Birthdate,103),
SQL
Select DATEDIFF(YEAR,?,103),
Edit - corrected the code, I forgot that VB6 cannot handle named parameters.

The next thing to note is that the SQL function DATEDIFF is expecting date or time expressions not
the varchar that you are using with
SQL
,CONVERT(varchar,V_ISSUEDATE,103)) from VISITOR_MASTER_NEW
See DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^]

If V_ISSUEDATE is a date or datetime column type on your database then there is no need at all for the CONVERT and if V_ISSUEDATE is not a date or datetime column type on your database then you need to change your database. Always use the correct column type for the data that will be stored in it

EDIT:
I have just spotted another major problem - You can't refer to a column in a recordset by name if you don't give it a name in the first place! Your SQL code should read
SQL
Select DATEDIFF(YEAR,CONVERT(datetime,?,V_ISSUEDATE,103) AS V_AGE from VISITOR_MASTER_NEW
   
v5
Comments
Bhavesh Vankar 12-Apr-21 5:10am
   
in V_ISSUEDATE column stored only date. but main question is using above query i find age successfully but how to display that output age in textbox like txtage.text see my above code after query i have used this line to display age in textbox txtAge.Text = rs("V_AGE") & "" is this right....it generate error "method 'Item' of object 'Fields' failed" and i have replaced datetime instead of varchar in sql query..
CHill60 12-Apr-21 5:49am
   
This is a different problem again. You really need to start using the documentation.
You also need to start sharing the exact wording of any errors.
Now, think about this logically. txtAge.Text is a string
rs("V_AGE") is going to be an object containing an integer value (hopefully). You have added & "" in what appears to be an attempt to force the value to be a string. Why not use the CStr() function provided in VB6?
CHill60 12-Apr-21 5:53am
   
I've just spotted the major problem in your code - see my updated solution
Bhavesh Vankar 12-Apr-21 5:55am
   
sir can you explain me with example i am new and having no much idea about vb6.0 how to use CStr(). and how to program it to display age...
CHill60 12-Apr-21 6:02am
   
You don't know how to use CStr()? Here is a random thought, try googling "VB6 Cstr function". Or look it up in the documentation that is (was) provided when you purchased VB6.
VB6 a dead language and fewer and fewer people exist out here who can help you all. Do yourself a favour and dump using VB6 in favour of VB.NET - at least there are some half-decent tutorials available for that
Quote:
whats wrong kindly suggest....
"THE CONVERSION OF A VARCHAR DATA TYPE A DATETIME DATA TYPE RESULTED IN AN OUT-OF-RANGE VALUE."

The error message tells you that the problem is in the contain of txtBirthdate.Text and of V_ISSUEDATE . Information that you didn't provided.

Simply said, the SQL server don't like 1 of the values converted to date.
   
Comments
Bhavesh Vankar 12-Apr-21 5:28am
   
this is solved but still pending how to display age in textbox by sql query which is computed. means i have used this code
If rs.state = adStateOpen Then rs.Close
rs.Open "Select DATEDIFF(YEAR,CONVERT(datetime,'" & txtBirthdate.Text & "',103),CONVERT(datetime,V_ISSUEDATE,103)) from VISITOR_MASTER_NEW", cn.Conn, adOpenDynamic, adLockOptimistic


in this below line i want to display my computed age how to do this.

txtAge.Text = rs("V_AGE") & ""

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900