Visual Basic :: 3421: Data Type Conversion Error

SQL Error: Char Data Type Conversion To Datetime Data Type Error
I created an application which, in one instance, queries an
SQL Server 7 table based on a date entered.
I have installed it on 4 machines (one NT, two 98 & one 95).
It works fine on all but one of the 98 machines.

On this !one! machine I receive the following error:
Error 2147217913 was generated by Microsoft OLE DB provider
for SQL Server The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime
value.

I get the date from a datepicker with the format
"dd/MMM/yyy HH:mm:ss"

Help!

Thanks in advance

Data Type Conversion Error
Im getting runtime error 3421 "Data type conversion error", whilst executing this bit of code in Access 2000:

Dim rs As ADODB.Recordset
Dim connection As New ADODB.connection

Set rs = db.OpenRecordset("SELECT [ADDRESS] FROM [UNIFORM_PROPREC1] WHERE [UNIFORM_PROPREC1].[ADDRESS] like '" & Me.txtAddress.Text & "*'", connection, adOpenKeyset, adLockOptimistic)

Me.txtADDFind.Text = rs.Fields("ADDRESS")

The ADDRESS field in UNIFORM_PROPREC1 is a text datatype as is the me.txtAddress.text.

Any ideas? Is this code just fundamentally wrong ?!

Bag

Data Type Conversion Error
What does this error mean?

Data Type Conversion Error
Hi, I am getting a "runtime error 524, data type conversion error" on a dbcombo. This has only happened since I have loaded msoffice'97 onto my computer. I am using visual basic 5. The all the other dbcombos in my program work fine except for this particular one. The program works fine with visual basic but when I compile and test it on the same machine, It brings up this error.
Also all the dbcombos are linked to 2 tables, 1 to populate the combo and the other is where the value is stored. It is only on the first field in the table where the problem is.
Also It is asking me for a dependency file for dblist32.ocx. Whould this be the problem as I can't find one.?
Can someone help me.

Data Type Conversion Error
i'm getting a data type conversion error with this code...can someone help?

Code:
With rstInfo

If .RecordCount = 0 Then
txtfname.SetFocus
Exit Sub
End If

Dim Counter As Integer
Counter = 0

.MoveFirst

.OpenRecordset ("select * from repairtable where custfName = " & "'" & lstlookup.SelectedItem.Text & "'")

Call update_form

End With

this code is inside a lst double click event. the update form just puts the text from the table to the textboxes. On debug the code lights up...

.OpenRecordset ("select * from repairtable where custfName = " & "'" & lstlookup.SelectedItem.Text & "'")

Data Type Conversion Error
i'm getting the error on this code

VB Code:
With rstInfo If .RecordCount = 0 Then txtfname.SetFocus Exit Sub End If Dim Counter As Integer Counter = 0 .MoveFirst .OpenRecordset ("select * from repairtable where custfName = " & "'" & lstlookup.SelectedItem.Text & "'") Call update_form End With

the error seems to be with this code...

VB Code:
.OpenRecordset ("select * from repairtable where custfName = " & "'" & lstlookup.SelectedItem.Text & "'")

Data Type Conversion Error?
Why do I get this error.
It happens when I click the arrow on the data1. And when I click break on all errors it doesnt break. It just says datatype conversion error. And it doesnt tell me what line the error is. Here is all of my code:

VB Code:
Option Explicit Private Sub Command1_Click()Text5.Text = Val(Text5.Text) + 1Text3.SelText = Date & ", "Text8.Text = Date & ", "End Sub Private Sub Command2_Click()On Error GoTo errorhandler Me.Data1.Recordset.FindFirst "[ID] = """ + Text7 + """""" Text1 = rs("ID") Text2 = rs("Student") Text3 = rs("All Lunch Detentions") Text4 = rs("Home Room") Text5 = rs("# Of Detentions") Text6 = rs("Home Phone") Text8 = rs("Detention Date") rs.Close cn.CloseExit Suberrorhandler:MsgBox "Student not found according to search criteria.", vbInformation, "Invalid Search Criteria"End SubPrivate Sub Form_Load()Data1.DatabaseName = App.Path & "ld(converted).mdb"End Sub Private Sub mnuexit_Click()Unload MefrmDataBaseViewer.ShowEnd Sub

Data Type Conversion Error
Hi,
I have a date/Time field (Required = No) in my database. Whenever I try to update this field with an empty value, an error message say "Data type conversion error" appears.

Table.Addnew
Table!Date = "" <---------------------
Table.Update
Table.Close

Can anyone help me to resolve this problem?

Thank you very much.

Data Type Conversion Error In ComboBox
Hey there,

I have a combobox in my form that is connected with a field in a table. Then everytime there is a movement in the DBGrid1 (also connected to the table), I tell the program that it has to change the combobox content. I do that because sometimes the field connected to combobox have nothing and it's a manner to force a default value to this field, when user is navegating through the table. What I mean is, if the field is empty, the first time someone pass by that record the value of the field will be setted to 1 (default). Well, this is my idea, but I can't do that because whenever there is a movement in the DBGrid1, VB returns a "Data Type Conversion error".

Let's see the facts:

The combobox info (properties window):
Name = cmbCliTipo
Datafield = clitipo
Datasource = cliente
Itemdata = 0 1 2 (one on another)
List = Loja Vendedor Crédito (one on another)

The field clitipo is an integer.

My code is:

Code:
Private Sub DBGrid1_rowcolchange(LastRow As Variant, ByVal LastCol As Integer)
If Cliente.Recordset.CliTipo = 0 Then
cmbCliTipo.ListIndex = 0
ElseIf Cliente.Recordset.CliTipo = 1 Then
cmbCliTipo.ListIndex = 1
ElseIf Cliente.Recordset.CliTipo = 2 Then
cmbCliTipo.ListIndex = 2
Else
cmbCliTipo.ListIndex = 0
End If
End Sub
I would be very grateful if someone can help me to know what's wrong. I know it's a stupid question, but, in fact, believe it or not, I have the VB 6 Little Black Book (Steven Holzner) and I have browsing through the net and I have not found anywhere a combobox setting cakewalk...

Thanks in advance!

***RESOLVED*** Data Type Conversion Error
I need to be able to divide two currency values in an MSFlexGrid to come up with a percent value.

The challenge is that I need to have error handling to be sure that if the user puts in a $.0 currency value I do not get an error.

Ex.

Private Sub MSFlexGrid1_LeaveCell()

With MSFlexGrid1
.TextMatrix(1, 1) = Format(.TextMatrix(2, 1) / .TextMatrix(3, 1), "0.0%")
End With

End Sub

Need Response ASAP! Data Type Conversion Error
Our requirement is: User should be able to enter the no value in the datetime field in our case

it is time in the format HH:MM hours:minutes and still the database should store that value as

blank and no other value not even as 0:00.

In MS-Access 97 SR-2 we have table 'T' with datetime datatype field 'F'. We have a query 'Q'

defined on this table to insert record into this table 'T'. We are executing this query 'Q' from

Visual Basic 6.0 and our application requirement is such that user may sometimes not enter any

value in the date field and still the insert query should work.

Here is my query in MS-Access:

Select query:
--------------
SELECT reptime
FROM table1
WHERE id=8;

Insert query:
-------------
PARAMETERS preptime DateTime;
INSERT INTO table1 ( reptime )
SELECT preptime;

Here is my Table definition:

Name:table1

Fields Datatype
idAutonumber
reptimedate/time (short time format)

Here is the code from Visual Basic which gives error as "Data type conversion error":

Private Sub Command1_Click()
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Set db = OpenDatabase("d:personalira.mdb")
Set rs = db.OpenRecordset("table1")
Set qd = db.QueryDefs("insert")

With qd
.Parameters("Preptime") = vbNull
.Execute
End With

Set qd = db.QueryDefs("select")
Set rs = db.OpenRecordset("select")
'rs!RepTime = MaskEdBox1.Text
Text1.Text = rs!RepTime
'rs.Update

End Sub

Problem: When we are exceuting this query using QueryDef from VB application we are getting "data

type conversion error" message. When we did little investigation we found that, when VB is trying

to send some empty string to this date field in MS-Access Database doesn't accept any empty

string.

Here are the options I have tried?

1. When I tried to pass a Null value to the date field in the database when there is no value

entered in this date form variable. MS-Access table is storing that date value as 0:00 which is

no good because user treats it as valid time value.
2. When I tried to execute the query inside the MS-access by passing no value to this date value

it was accepting and the date value is stored as blank but same is not true when I am trying to

sent the blank date value from VB.

Is there any solution for this?

Data Type Conversion Error When Moving A Date From A Label To A Database
I am getting a data conversion error when i am moving a date from a label to a new db when the field is a date format do you guys have any ideas? thanks

SQL Server - The Conversion Of A Char Data Type To A Datetime Data Type Resulted.....
Hi All

I have developed an application with vb6.0 and sql server 2000 database and working well on my desktop.

While client saving records in db, sometimes they are getting following error.

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I'm using ADOs and code like this...

rstCustomer("joindate") = mskJoinDate
rstCustomer("jointime") = mskJoinTime
rstCustomer.Update

' Here mskJoinDate is maskedit control, contains date in dd/mm/yyyy format only.

' Here mskJoinTime is maskedit control, contains Time in hh:mm format only.

' Both are datetime fields in database.

' These two fields won't contain any other data because I have done perfect validation before assign values to recordset.

Anybody have idea about my problem? Please help me to solve this.

Thanks in advance
Pradeep

'The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-ran
Hi,

I am writing a SQl query in visual basic by startdate and end date. It works fine in SQL server and Query Analyser but when query comes into visual basic it error and the message is 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

The code:

declare @cMonth int, @year varchar(10), @first_sDate datetime, @first_eDate datetime, @Second_sDate datetime, @Second_eDate datetime
set @cMonth = datepart(month, getdate())
set @year = datepart(year, getdate())
set @first_sDate = @year + '-01-01' + ' 00:00:01'
set @first_eDate = @year + '-06-30' + ' 11:59:59'
set @Second_sDate = @year + '-07-01' + ' 00:00:01'
set @Second_eDate = @year + '-12-31' + ' 11:59:59'

if @cMonth <= 6
begin
select *
from pub
where created_date BETWEEN @first_sDate and @first_eDate
end
else
begin
select *
from pub
where created_date BETWEEN @second_sDate and @second_eDate
end

In here two sets of data need to represent first six month and second six month.

how do change varchar to datetime? I tried convert but didnot work. Any body can help with the code? The error caused only in visual basic.

Many Thanks

Bravo

3421-Application Uses A Value Of The Wrong Type For The Current Operation.
I get an error when I select more than 15 Customers. But, when I select less than 15 Customers I do not get this error.

ERROR: -> "3421-Application uses a value of the wrong type for the current operation."

The error comes up after it hits the "End Property"

VB Code:
Private mstrCustInNum As String Private mblnAllCust As Boolean Public Property Get CustInNum() As String If mblnAllCust Then CustInNum = "" Else CustInNum = mstrCustInNum End If If mblnOverrideSelection Then CustInNum = mstrCustInNum End IfEnd Property

Also, I ran the Stored Procedure with the same data and it works fine.

Data Type Conversion
i want to convert the datatype from string to double is there any way to do it i am doing as below

dim vv as string
dim k as double
vv = "0.0012"
k=cdbl(vv)

but it gives type mismatch error
how can i retrive the value from string

Data Type Conversion
hi how can i convert a data type using sql for example

Select * from Table where number = '123'

how can i convert the field number to string in access. is this possible in access coz in oracle it is posible. thanks

Data Type Conversion In SQL
I have 2 tables in SQL server as below,

Table1:
T1Col1 --- numeric(9,2)
T1Col2 --- varchar(3)

Table2:
T2Col1 --- varchar(5)
T2Col2 --- varchar(3)

Due to some reason, I need to union the two table, ie
Code: (SELECT * FROM TABLE1) UNION ALL (SELECT * FROM TABLE2)
However, as you notice, the first column of the two tables are of different data type. Can I change the first column of Table1 to varchar in the selection statment, so that the definition of table 1 remains the same?

Data Type Conversion
Hi,
how can I convert data stored in a 4 byte array (written by a VC++ programs from a float data type) in a VB Single data type?
Something just like the following (for Long data type)...

Public Function CByteArrToLong(buff() As Byte) As Long
    Dim TempLong As Long

    Call CopyMemory(TempLong, buff(LBound(buff)), 4)

    CByteArrToLong = TempLong
End Function

thks all.

Marcello

OLE DATA Type Conversion
Hi,

Murli here,

I would like to help me someone that,

How? using access db fields(OLE/Object-Microsoft Equation 3.0 editor) i.e. math equations, store and retrive and editing it through VB6 Programming?

Thanking you!

Murli Gawali

Allowed Data Type Conversion
I am very confused by this one, so let me know if what I say is difficult to understand.

The following is the code I am crashing on (occaisionally):
<cfquery name="eqptotal" datasource="login">
select sum(total) as eqptot
FROM budget, team
where userid = #userid#
and reqtype = '#equipment#'
and coach = #coach#
and team.teamname=budget.sport
and not itemorder=3
</cfquery>

<cfquery datasource="login" name="ship">
select shipcost
from ship
</cfquery>

<cfset eqpship = eqptotal.eqptot * ship.shipcost>
<cfset eqpestTot = eqptotal.eqptot + eqpship>

I get the following message regarding the
"<cfset eqpship = eqptotal.eqptot * ship.shipcost>"

Cannot convert to number.

Please, check the ColdFusion manual for the allowed conversions between data types

eqptotal.eqptot is the total of a field named 'total' ( it holds singles)

ship.shipcost is equal to a field named 'ship' (it also holds singles)
I am using MS Access
To further confuse me the code works for some users but not all????

Decimal Data Type Conversion
I want to convert 23.43535353 to 23.44 like this in VB6.
Can any body help me in this regards.

Thanks in advance.

Polash

VB Error 3421
I keep getting a data conversion error while using vb6 w/access backend. I'm trying to insert a blank or null value into a date field. Can anyone help?

Data Type Conversion In String Variable
Thought I had this taken care of, but I still have a problem with the "Text" data type. When I create a new table below called [Deleted Results], the fileld types are "Memo" when I open the table. I've replaced "Text" with other field types, and the code works fine. I only seem to have a problem with "Text". Field.Size doesn't seem to impact this either (254) instead of (255). How can I ensure that "Text" will be the assigned field type?

For intField = 0 To CurrentDb.TableDefs("Results").Fields.Count - 1
strDataSize = CurrentDb.TableDefs("Results").Fields(intField).Size & ","
strDataTypes = CurrentDb.TableDefs("Results").Fields(intField).Type & ","

If strDataTypes = "10," Then
strDataTypes = "Text"
Else
strDataTypes = "Number"
End If

strFields = strFields & CurrentDb.TableDefs("Results").Fields(intField).Name & " " & strDataTypes & ", "
Next

strFields = Mid(strFields, 1, Len(strFields) - 2)
strFields = Replace(strFields, "AUTONUMBER", "COUNTER")

cnnCei.Execute "CREATE TABLE [Deleted Results] " & "(" & strFields & ");"

Vb Runtime Error 3421
I am getting the error message "value of the wrong type for the current operation" when a procedure runs which stores a value in a sql table.
The value (an item code) is the same amount of digits (6) as other values in the database. The code fails on the line that puts the value into a temporary parameter
Has anyone any ideas what could be causing this

Get A 3421 Error When Compact A Database
When I use the code "DBEngine.CompactDatabase App.Path & "videorent.mdb", App.Path & " mp.mdb", DbLangGeneral, ";PWD=" & DBPass" to compact a database of ACCESS2000 Format, got a error 3421 "Data Type Conversion Error" . why? Thks a ton.

Suddenly Getting 3421 Error After Installing 2000 SP2
We have code that has been working on all Windows platforms, but comes back with the 3421 (Application uses a value of the wrong type for the current operation) when running on a machine with Win2000 SP2 installed. Does anyone know what causes this and how we might be able to correct it?

Any Expert Here (mysql To Mssql - Blob To Image Data Type Conversion)
heloo friends --------- >>>

i want to convert my table from mysql to mssql.

in mysql table i have blob field (containing .pdf document) which i want to convert to mssql image field.

i tried this by using (import export wizard in mssql) and its working fine .

but when i transfer it using vb it transfering but when i try to open that its giving me an error ----

Data Conversion Error
DataControl (DAO) brings data from several fields into textboxes on the form. One of the fields is Number (Integer) data type. It's not Required, it's not Indexed. If it contains some value and user wants to delete that value by clearing a bound Text Box, then when the user wants to save that change by moving to the next or previous record, error saying "Data Conversion Error" occurs. Data can be deleted directly in the table with no problem.
What I'm missing? What should I do in order to allow the user to update existing value to Null?
This is an existing application, so altering it by replacing DataControl with Recordset is not acceptable (too much work related to replacing many other controls getting data from DataControl)
Thank you
Vlad

DBGrid And Error Data Conversion
Hi,

I use dbgrid with num value. When I wont to delete value one cell and change de active cell, I receive an eroor like "Error data conversion"

But...When I put the break point in my code (in this sub)
---------------------
Private Sub DBGrid1_KeyUp(KeyCode As Integer, Shift As Integer)

If KeyCode = 46 Or KeyCode = 8 Then ' the 46 = delete and 8 = backspace
DBGrid1.Text = Null
Else

End If
End Sub
--------------------

And after that I change the active cell and I don't receive the error.

But I don't want to run my programme all the time with a break point.

Some body can help me?

Thanks

Régis

The Conversion Of Char Data Type To A Datetime Resulted In An Out-or-range Datetime Value
can you guys help me and tell me what is wrong with my script here, it's throws the above error and to me it seems everything is right... it's too long but please look at it and tell me what you think:

declare @Type char(2)

SET NOCOUNT ON

DECLARE @flag bit
DECLARE @WEEK_START_DATE smalldatetime, @WEEK_END_DATE datetime
DECLARE @beginDate smalldatetime, @endDate smalldatetime

set @type = 'hv'
set @beginDate = '2006/06/12'
set @endDate = getdate()
SELECT @flag = 1

SELECT @flag = 1
create table #temp
(StartDate datetime, EndDate datetime, Min_Form_Count int, min_Date datetime, max_form_count int, max_Date datetime)

create table #temp20
(StartDate datetime, EndDate datetime, [Total Entries Received] int, GPRS_timestamp datetime)

WHILE @flag = 1
BEGIN
    --GET THE WEEK START DATE
    SELECT @WEEK_START_DATE = @beginDATE

    --GET THE WEEK END DATE
    SELECT @WEEK_END_DATE= @beginDATE + 6

    IF @WEEK_END_DATE < @enddate and (@enddate- @WEEK_END_DATE <7)
    BEGIN
         SELECT @WEEK_END_DATE = @enddate
        SELECT @flag = 0
    END

    IF @WEEK_START_DATE >@endDate or @WEEK_END_DATE > @enddate
        BREAK;
    ELSE
    BEGIN TRANSACTION
     INSERT INTO #temp20
     SELECT @WEEK_START_DATE as StartDate,@WEEK_END_DATE as EndDate, COUNT(1) AS [Total Entries Received],
         convert(varchar(10),GPRS_timestamp,103) as GPRS_timestamp
    FROM DC_Forms INNER JOIN
            DC_Batches ON DC_Forms.PrintBatchID = DC_Batches.PrintBatchID INNER JOIN
            DC_Hierarchy ON DC_Batches.DCUnitID = DC_Hierarchy.ID
    WHERE DC_Hierarchy.Type = @Type AND
        GPRS_timestamp between @WEEK_START_DATE and @WEEK_END_DATE
        and GPRS_timestamp IS NOT NULL AND FormDataID IS NOT NULL
       group by convert(varchar(10),GPRS_timestamp,103)
    COMMIT TRANSACTION
    begin transaction
    INSERT INTO #temp
    SELECT TOP 1 @WEEK_START_DATE as StartDate, @WEEK_END_DATE as EndDate,
     (select min ([Total Entries Received]) as Min_form_Count from #temp20 where GPRS_Timestamp
        BETWEEN @WEEK_START_DATE and @WEEK_END_DATE + 1) ,
     (select min (convert(varchar(10),GPRS_Timestamp,103)) as Min_Date from #temp20 where GPRS_Timestamp
        BETWEEN @WEEK_START_DATE and @WEEK_END_DATE + 1),

           (select max ([Total Entries Received]) as Max_form_Count
        from #temp20 where GPRS_Timestamp BETWEEN @WEEK_START_DATE and @WEEK_END_DATE + 1),
     (select max (convert(varchar(10),GPRS_Timestamp,103)) as Max_Date
        from #temp20 where GPRS_Timestamp
        BETWEEN @WEEK_START_DATE and @WEEK_END_DATE + 1)

         FROM DC_Batches



    SELECT @beginDate = @WEEK_END_DATE +1
    Commit transaction
END
select convert(varchar(10),StartDate,103) as StartDate, convert(varchar(10),EndDate,103) as EndDate,
  Min_Form_Count , min_Date, max_form_count, max_Date
from #temp

Edited by - nomvula on 9/4/2006 4:28:26 AM

Data Type Conversion From Visual C++ To Visual Basic
I'm trying to convert some code from Visual C++ to Visual Basic. There are some types, however, that I cannot seem to figure out what they should be. The C++ types are dpecifically WORD and DWORD. What should they be in Visual Basic? I thought DWORD is a Long, but I have no idea what a WORD would be. Specifically, I'm determining the types from the BITMAPINFOHEADER structure.

SQL Statement Error Or Data Type Error? (RESOLVED)
VB Code:
SELECT MC_NO, PART_CODE,MONTH " & _ "FROM MC_DATA " & _ "WHERE MC_NO = '& txtM1.Text &' AND MONTH = '" & txtMonthHide.Text & "'

above is my sql statement...but i get an error "Data Type Mismatch in criteria expression. i think the error refers to MC_NO = '& txtM1.Text &', because there is no value when i debugged it.FYI the data type for MC_NO in MS Access database is Number..can somene correct my coding mistake??
Thanks in advance.

Error: 64k Max Data For Type
In my declarations for a module I have a few different Type calls that declare my variables:

Code:
Public Type TLVERTEX
X As Single
Y As Single
z As Single
rhw As Single
Color As Long
Specular As Long
tu As Single
tv As Single
End Type

Public Type Composition
TrStrip(0 To 3) As TLVERTEX
ColorTR As Long
ColorTL As Long
ColorBR As Long
ColorBL As Long
Color As Long
End Type

Public Type DXTiles
Public ListDx(500,500) as Composition
End Type

Public DXTile(100) as DxTiles
For some awkward reason when this code runs it returns an error that I can't surpass the 64k Data Max. Well, I know there is ALWAYS a way around with programming, so can someone help me? I may not be utilizing all the tools I have to make a different solution. Basically these calls make a tile map that is 500,500, and then the last call tells the game that it has 100 layers. Erm... Yea..

Thanks for help...
~SpiralEdge

Data Type Error
I have an access field ECONumber which type is set to Autonumber. I get an error upon trying to access it this way:
.Index = "ECONumber"
It says data type conversion error
How can I fix that?
Thanks

Data Type Error!
Hi, In my project I have a search option, this search can browse by code or name, but when I do a search by code "Data type mistmatch in criteria expression" shows up, in my database I have the field name in AutoNumber and is the primary key. What could be the problem?

The code is:

Code:
Set rs = db.OpenRecordset("SELECT * FROM Pacientes WHERE ID='" & Text1.Text & "'", dbOpenDynaset)

frmAltas.Text1.Text = rs!Id
frmAltas.Text2.Text = rs!Apellido
frmAltas.Text23.Text = IIf((rs!Otros_Datos), 1, 0)
frmAltas.Check1.Value = IIf((rs!Problema_Salud1_si), 1, 0)
frmAltas.Text34.Text = rs!Otra_especifique
Beep

Data Type Error
I'm getting a 'Data type mismatch error' in the following line, any idea's as to why? I've used the same syntax before without problem. The Id and cntr variable's are long variable's.

Code:
rssystem1.Open "SELECT TBLSYSTEM.CLIENTNO, TBLSYSTEM.SYSTEMNO, TBLSYSTEM.ITEMNAME, TBLSYSTEM.CATAGORY, TBLSYSTEM.QUANTITY, TBLSYSTEM.PRICEEACH, TBLSYSTEM.DESCRIPTION, TBLSYSTEM.NOTES" & _

Data Type Error
can some one plz help me out the following code is giving an error as data type mismatch in crieteria expressionat line

Set rs = conn.Execute(str)

i am stuck plz help

VB Code:
ConvertTxtToExcel(lSourceFile As String) Set myXLObj = CreateObject("Excel.Application") myXLObj.Visible = True ' to make the Excel app visible On Error GoTo 0 With myXLObj .Workbooks.OpenText FileName:=lSourceFile, DataType:=XLDELIMITED, _ TextQualifier:=XLDOUBLEQUOTE, semicolon:=True, _ FieldInfo:=Array(1, 2) End With Dim i As Integer Dim j As Integer Dim a As String Dim str As String Dim k As String i = 5 j = 1 While i < 180 While j <= 7 If j = 1 Then k = "notice" ElseIf j = 2 Then k = "are1no" ElseIf j = 3 Then k = "Date" ElseIf j = 4 Then k = "pname" ElseIf j = 5 Then k = "IName" ElseIf j = 6 Then k = "tariff" ElseIf j = 7 Then k = "duty" End If Set conn = New ADODB.Connection conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:excelxldata.mdb;Persist Security Info=False") a = Cells(i, j).Value 'str = " update [data] set [" & k & "] = '" & a & "' where [sno] = '" & i & "'" str = "UPDATE [data] SET " & k & "= '" & a & "' WHERE sno = '" & i & "' " Set rs = conn.Execute(str) j = j + 1 Wend i = i + 1 Wend End Sub

Error Data Type Mismatch
I am running a SQL query in VB to search for anything in the comments field defined as Memo, not text in Access database.

When I run search, I got the error data type mismatch....

"WHERE (([DAILY WORK].[Comments])like ' ""*" & [txtCommentSearch.Text] & "*"" ')" & _

(The above code all in one line)

I tried txtCommentSearch.Memo but still not working. The txtCommentSearch is the field in VB that I want users to enter any keywords to search.

Can anyone help me, please? Is the wildcard right in VB statement?
Thanks.
Bill

Error Converting Data Type
I made a StoreProc and I get the following error message.

"Error converting data type varchar to real."

Here's the StoreProc:

CREATE PROCEDURE AjoutRoutePreliminaire
@SecVoulue varchar(255),
@SousSecVoulue varchar(255),
@pExempt tinyint,
@pBarcode tinyint

AS
INSERT INTO RouteSSS (section, soussection, image, tagid, pid, isole, type ,attrib, manif, modele, noequip, ligne, iiid, barcode, itid, exempt, note, taille ,fuitedef)
Select I.Section, I.SousSection, I.Route as Image, P.TagId, P.PID, P.isole, P.Cod3TypeEquip as lType, L.Nom as ligne,
P.ID_IMAGE, P.Barcode, P.id_point, P.exempt, P.note, P.taille,
P.Cod4AttribEquip as Attrib, P.Cod4ManifEquip as Manif, P.ModeleEquip as Modele, P.NoEquip, P.fuitedef
FROM (ImageDef I RIGHT JOIN PointDef P ON I.ID_IMAGE = P.ID_IMAGE)
LEFT JOIN LignePro L ON P.ID_LIGNE = L.ID_LIGNE
WHERE (((I.Section) Like @SecVoulue) AND ((I.SousSection) Like @SousSecVoulue) AND
((P.Exempt) < @pExempt) AND ((P.BCFlag) >= @pBarcode))
ORDER BY I.Section, I.SousSection, I.Route, P.TagID;

And Here's how I call it:

1> exec ajoutroutepreliminaire @SecVoulue = '_%', @SousSecVoulue = '_%', @pExempt = 1,@pBarcode = 1;

I cannot see why it tries to cast a real.

The problem is with the % sign. If I change it for text it then works. Maybe i'm not using the appropriate wildcard character?

Thanks

Data Type Error In Access
im encountering an error "Data type mismatch in criteria expression"

heres my code:

VB Code:
sqlselect = "select * from temp where 1 = 'T'"

FYI: my database is an access file..

Data Type Mismatch Error
Hi! I have this fields and their data type in MS ACCESS

Fields Data Type Fieldsize
Code Number Double
ProdDesc Text
SPrice Number Long
PPrice Number Long
Quantity Number Long
Unit Text
EntryDate Date/time

I have this code to save values in my DB

VB Code:
Private Sub cmdSave_Click() If rsStocks.State = adStateOpen Then rsStocks.Close rsStocks.Open sSQL, oConn, adOpenStatic, adLockOptimistic sSQL = "INSERT INTO tblStocks(Code, ProductDescription, SellingPrice, " & _ "PurchasePrice, Quantity, Unit,EntryDate, ReOrder)" & _ "VALUES('" & txtCode.Text & "', '" & txtDesc.Text & "', '" & _ txtSellingPrice.Text & "' ,'" & txtPurchasePrice.Text & "','" & _ txtQty.Text & "', '" & txtUnit.Text & "', '" & _ txtEntryDate.Text & "', '" & txtROP.Text & "')" oConn.Execute sSQLEnd Sub

When i clicked the save button it i received the error "Data mismatch in criteria expression" What causes this error?

Data Type Mismatch Error
Data type mismatch error in criteria expression, What should I look for here? I can't see whats wrong.

VB Code:
Dim SQL As String SQL = "SELECT * FROM Orders WHERE AccountNumber=11434"Adodc3.CommandType = adCmdTextAdodc3.RecordSource = SQLAdodc3.RefreshSet DataGrid3.DataSource = Adodc3

Data Type Mismatch Error
I changed a field type n my access database from text to a number datatype and now i'm getting a datatype mismatch error when i try to update.I want to keep the field as a number.Is there anyway to rectify this problem?I have tried converting the value to a long using CLng function.

Data Type Mismatch Error....
I have a database that has an autoincrement field. I am trying to lookup records using ADO on that field but I always get a data type mismatch error.

I have tried the following in the sql search without success:

Val(txtFind.txt)
cLng(txtFind.txt)

Data Type Mismatch Error
I got a "data type mismatch in criteria expression" error when i execute this statement

Code:
sql = "insert into jobs(shift,[date],user,prq,prtotal,prtwt)" & _
" select prshift,format(prdate,'dd/MM/yyyy'),pruser,prtotal, "& _
"prQueuename, prTWT from preptbl where pruser = '" & Me.cboname.Text & "' and " & _
"prdate=#" & Format(DTP.Value, "MM/dd/yyyy")& "# and prQueuename= '" & Me.txtQ.Text & "'"

i can't figure what is wrong with this statement!!

Data Type Text Error
I am using the data type Text in my SQL7 database.

When I try to save data to these fields I get the following error.

Run-time error '-2147217900(80040e14)':

Code page transalations are not supported for the text data type. From: 1252 To: 850.

My code is simple.

Rs.open "Select ......
rs.addnew
rs.fields("MonDesc1") = text1.text
rs.update

Anyone have any ideas why I'm getting this error? I have used the data type text in the past and have not received this error.

Thanks

P.S. I can use data type ntext but I would rather not since ntext takes up twice as much spaces as text.