By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,594 Members | 2,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,594 IT Pros & Developers. It's quick & easy.

Error 3421: "Data Type Conversion Error.

P: 25
Hi Guys

Season greetings to everyone.

Does anyone have an idea to this error message.

Thanks
gilsygirl
Dec 17 '07 #1
Share this Question
Share on Google+
10 Replies


debasisdas
Expert 5K+
P: 8,127
that simply means the target variable and the content does not match.
Dec 17 '07 #2

P: 25
that simply means the target variable and the content does not match.
Hi thanks for the reply. how do i treat a text variable define as string. cos that is all i have done. but the error keeps coming from my sql statement.
Is there any other suggestion u have.

thanks
Dec 24 '07 #3

debasisdas
Expert 5K+
P: 8,127
Hi thanks for the reply. how do i treat a text variable define as string. cos that is all i have done. but the error keeps coming from my sql statement.
Is there any other suggestion u have.

thanks
Kindly post what exactly youare trying to do and also the SQL statment that you are trying to execute.
Dec 24 '07 #4

Expert 5K+
P: 8,434
Ignore me. I'm just registering an interest in this thread so I can tell when new messages are posted.
Dec 24 '07 #5

P: 25
Kindly post what exactly youare trying to do and also the SQL statment that you are trying to execute.
Thanks for the reply

I am trying to extra a record from a database using a combination of 3 of the fields in the database as a key. So i am querying them in an Sqlstmt variable use as the recordset. the fields in the database are define as text and in the vb form the variable agruments are define as string.

So when i enter the variables on the form a second form should load displaying a record base on the variables passed provided it is in the database.

this my code

Expand|Select|Wrap|Line Numbers
  1. Public Function initdb()
  2. On Error GoTo error
  3. Set ws = DBEngine.Workspaces(0)
  4. Set db = ws.OpenDatabase("c:\resultchecker.mdb")
  5. Set rs = db.OpenRecordset("candres", dbOpenTable, dbReadOnly)
  6. Set rs1 = db.OpenRecordset("subject", dbOpenTable, dbReadOnly)
  7.  
  8. Exit Function
  9. End Function
  10.  
  11. Sub cmdCheck_click()
  12.  
  13. Dim Exam_Sr As String
  14. Dim Exam_Yr As String
  15. Dim Cand_No As String
  16. Dim CentNo As String
  17.  
  18. If rs.RecordCount = 0 Then Exit Sub
  19.    rs.MoveFirst
  20. For i = 1 To rs.RecordCount ' Read all the records from database
  21.  
  22. sqlStmt = "select * From candres where ExamSeries='" & Exam_Sr & "'and ExamYear='" & Exam_Yr & "'and CentNo='" & Cand_No & "'"
  23.     rs.OpenRecordset sqlStmt, db
  24.  
  25. If Not (rs.EOF) Then
  26.        frmCandResult.Show vbModal, Me
  27.     Else
  28.         MsgBox "sorry not a perfect match"
  29.     End If
  30.  
  31. CentNo = rs("CentNo")
  32. Cand_No = rs("IndexNo")
  33. Sex = rs("Sex")   
  34.  
  35.  frmListing.Cent_No.Caption = rs("CentNo")
  36.    frmListing.Cand_No.Caption = rs("IndexNo")
  37.    frmListing.Cnd_Name.Caption = rs("CandName")
  38.  
  39.     frmListing.Show vbModal, Me
  40.        rs.MoveNext
  41.        Next i
  42. End Sub
Dec 27 '07 #6

debasisdas
Expert 5K+
P: 8,127
1. Which line of code is creating the error ?
2. Why using a function with out any out type and nothing to return ?
3. Why opening the same recordset twice from two different sources ?
4. You have never called the function in the click event.
5. Try to use ADO code , that is much more flexible.
Dec 27 '07 #7

Expert 5K+
P: 8,434
My guess is that the CentNo field is numeric. If you have a close look at line 22 of the code, you'll see that it is being compared to variable Cand_No, delimited by quotes. This may be the source of the problem.
Dec 27 '07 #8

P: 25
1. Which line of code is creating the error ?
2. Why using a function with out any out type and nothing to return ?
3. Why opening the same recordset twice from two different sources ?
4. You have never called the function in the click event.
5. Try to use ADO code , that is much more flexible.
Thanks for the reply. I have been able to solve the query. I was passing the values to a different form then the required form. The new query is in the SQL statement. It is only picking the first record in the database.

Now I want to know which function you are referring to and where I am opening the recordset twice.
Thanks for the help, really appreciated.
gilsygirl newbie
Jan 2 '08 #9

P: 25
My guess is that the CentNo field is numeric. If you have a close look at line 22 of the code, you'll see that it is being compared to variable Cand_No, delimited by quotes. This may be the source of the problem.
Thanks for the reply. I have been able to solve the query. My latest problem is the SQL statement. It is only picking the first record from the database.

This is the code:
Expand|Select|Wrap|Line Numbers
  1. If rs.RecordCount = 0 Then Exit Sub
  2. rs.MoveFirst
  3. For i = 1 To rs.RecordCount ' Read all the records from database
  4.  
  5.   sqlStmt = "select * From candres where ExamSeries='" & Exam_Sr & "'and ExamYear='" & Exam_Yr & "'and CentNo='" & Cand_No & "'"
  6.   rs.OpenRecordset , sqlStmt
  7.  
  8.   If Not (rs.EOF) Then
  9.     Call dispres
  10.     ' frmListing.Show vbModal, Me
  11.   Else
  12.      MsgBox "sorry not a perfect match"
  13.   End If
  14.  
What is wrong with this code?

Thanks for help it's really appreciated.
gilsygirl newbie
Jan 2 '08 #10

Expert 5K+
P: 8,434
... What is wrong with this code?
Not sure where to start. :)

But one thing to consider is that RecordCount may not be populated until the records are accessed. When you first open the recordset, I think it just holds 0 (if no records) or 1 (if any records). So you might see some improvement if you insert rs.MoveLast before line 2. Hm... or since you're immediately replacing rs anyway, maybe just change line 2 from MoveFirst to MoveLast.



I have a question. Is the recordset opened in line 5 supposed to contain the same records that were already in rs when we entered this part of the code? If so, there's no need to open anything, which I think is what Veena was getting at. You have the records there, just go ahead and use them.
Jan 3 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.