i have an error in my numric fields.
just like Phone no., fax no., zip code.
these field in data base are assigned numeric data type.
now when i m assigning these fields to textboxes, there is error cuming, type mis match.

phTxt.Text = rs!Phone

in this line.

Recommended Answers

All 22 Replies

Type mismatch refers to the data collected. When you created your table with "Phone" as a field,, what type did you gave it, integer or text?

If a user types text, the data will mismatch if the table requires a number. Also, make sure there is no other characters added than only numbers, i.e. (097) 999-22345 etc. This will also raise an error. Spaces as well.

Post all the code in the command button sub, let me have a look.

Also, if you can post a picture of what data was added in the text box.

From there I can add some error trapping code for you.:)

this is what code, i have used for adding new record, and i have assingned numeric data type to phone, fax, n etc in sql server....

Private Sub update1_Click()
Call MyDatacon
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM ORGANIZATION", con, adOpenStatic, adLockOptimistic
rs.AddNew
rs!Orgcode = Text1.Text
rs!Organizationname = Text2.Text
rs!Shortname = Text3.Text
rs!Contactperson = Text4.Text
rs!Address = Text5.Text
rs!City = Text6.Text
rs!Phone = Text7.Text
rs!Fax = Text8.Text
rs!NTN = Text9.Text
rs!Email = Text10.Text
rs!URL = Text11.Text
rs!Lontitude = Text12.Text
rs!Latitude = Text13.Text
rs!Address1 = Text14.Text
rs!Address2 = Text15.Text
rs!State = Text16.Text
rs!Zip = Text17.Text
rs!Country = Text18.Text
rs!Joiningdate = DTPicker1
rs!Leavingdate = DTPicker2
xDate = vbNullString
rs!Category = Combo1.Text
rs!Status = Combo2.Text
rs.Update
rs.close
con.close

this is a picture...
and in this record shown is already stored by me in sql server, now when i clicking on add, it shows an error before typing anything....

phTxt.Text = rs!Phone

This is your problem, you do not have a textbox called phTxt. You must learn to use conventional naming structures. What that means, is naming your controls so you know which refers to what. If you get an error, it is very difficult to determine where if you use as you did above text1, text2, text3 etc.

Rather start using -
rs!Orgcode = Text1.Text - txtOrgCode.Text
rs!Organizationname = Text2.Text - txtOrganizationname.Text etc....
Now to solve your problem, -

Text7.Text = rs!Phone

Sorted, please mark as solved, thanks.:)

hahahah....i knew this....i just edit it to send it before you...
it is written in text7.text=rs!phone...
i myself edit it in this editor, so u can easily understand...
tht is not a problem...

still error is another...
i think i should to change data type from sql server, then it will accept..
and i just tried, when i changed data type from numeric to text, it didnt pass any error..but i have to keep same data type numeric

You will be surprised how small things can fool us around.:)

To do some error trapping, do the following -

BEFORE you call the show method, this

If Not IsNumeric(Text7.Text) Then
msgbox "Numbers Only"
Else
rs!Phone = Text7.Text
End If

'Another Easy way is under your Text7 Change event....
Private Sub Text7_Change()

Dim WshShell As Object

Set WshShell = CreateObject("WScript.Shell")

If Not IsNumeric(Text7.Text) Then
    WshShell.SendKeys "{BackSpace}"
End If
End Sub

This way, your database table will accept the data, because it is only in numerical values.:)

it is not solved....still same error...

Have you try the code above?
Also, did you change the type to either integer/numerical or text?

I'll run all code in a test project to see where the error occurred. I will however test in access and mysql, do not have mssql.

you must to change data type on database..
also i want to know which line of your code that error came up...
about text box input, you can set text box to not allowed an input except a numeric input. so text box just accept a numeric input.

Private Sub Text7_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 48 To 57, 8
        'Let these key codes pass through
        Case Else
        'All others get trapped
        KeyAscii = 0
    End Select
End Sub

:) Which is similar to what I posted above:)

It seems his data types is throwing the error. After a few posts, I have decided to test it and see. If you have mssql, and you can test it, cool.;)

i've tried the similiar code. it running nicely.
as we said to you to change data type in database field in mssql server.
and maybe u need to convert an input to numeric type before add it to database.

Tested on MySql AND Access, working perfect. Change your data types to overcome the error.

how can i change my data type in MSSQL...?
it is primary key, Id, That will b in much need...its data type is numeric length 5, which data type should i keep..
i already told u that when i using text data type it is not erroring...bt as i told u i want the numeric data type, as it is ID..

and as i already told u, it is not erroring when i m adding the record, but when navigating the record, it only points out the fields which have numeric data type.

and as i already told u

No you have not...

Show me the code when navigating the records AND the line where the error occurs.

>>and as i already told u, it is not erroring when i m adding the record, but when navigating the record...
so,when you telling this?? you concern us with your adding codes from the start..
just post your navigate code and provides a full info about error..what an error came up, which line, etc.. so we can fully understand the problem..

Call MyDatacon
Set rs = New ADODB.Recordset
rs.Open "SELECT * From ORGANIZATION", con, adOpenStatic, adLockOptimistic
If rs.BOF Then
MsgBox "First Record Was Reached,no more to display"
Exit Sub
Else
rs.MovePrevious
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname
Text4.Text = rs!Contactperson
Text5.Text = rs!Address
Text6.Text = rs!City
Text7.Text = rs!Phone
Text8.Text = rs!Fax
Text9.Text = rs!NTN
Text10.Text = rs!Email
Text11.Text = rs!URL
Text12.Text = rs!Lontitude
Text13.Text = rs!Latitude
Text14.Text = rs!Address1
Text15.Text = rs!Address2
Text16.Text = rs!State
Text17.Text = rs!Zip
Text18.Text = rs!Country
DTPicker1 = rs!Joiningdate
DTPicker2 = rs!Leavingdate

Combo1.Text = rs!Category
Combo2.Text = rs!Status
rs.Update
rs.close
con.close
End If

when i m pressing move previous button, it is showing type mismatch error and when pressing debug, it goes to

Text1.Text = rs!Orgcode

and when blocking this line, then

Text2.Text = rs!Organizationname

Error is Either BOF or EOf is true, or the current record has been deleted.requested opertion requires a current record

Have a look at your last part of the code -

Combo1.Text = rs!Category
Combo2.Text = rs!Status
rs.Update
rs.close
con.close

The update statement IS ONLY USED WHEN ADDING OR EDITING records.:) Remove "rs.Update" from the movePrevious code and in the other move subs as well if it does appear there.. If it came from my code, I do apologize, remember that I wrote the code sample for you in 15 minutes.;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.