[RESOLVED] 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:\excel\xldata.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

three things:
1) what is ConvertTxtToExcel(lSourceFile As String)
2) where have you declared con as a datatype?
3) where have you declared rs as a datatype?
4) where have you declared str as a datatype?

I see that youre using ADODB, so 2 and 3 should be:

VB Code:

Dim con As ADODB.Connection, rs As ADODB.Recordset

4 should be

VB Code:

Dim str As String

VB Code:

Option Explicit
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim conn As ADODB.Connection
Dim conn1 As ADODB.Connection
Dim conn2 As ADODB.Connection
Public myXLObj As Object
 
Private Const XLDELIMITED = 1
Private Const XLDOUBLEQUOTE = 1
Private Const XLNORMAL = -4143
 
Private Sub CmdOpen_Click()
'MsgBox (Z)
CommonDialog1.Filter = "Text Files (*.xls)|*.xls"
CommonDialog1.ShowOpen
ConvertTxtToExcel CommonDialog1.FileName
CmdOpen.Enabled = False
End Sub

i have already declared those

youre using a string called str in your update sql statement but do not see it below as declared anywhere.

Quote:

---

Originally Posted by elixir_5000

VB Code:

Option Explicit
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim conn As ADODB.Connection
Dim conn1 As ADODB.Connection
Dim conn2 As ADODB.Connection
Public myXLObj As Object
 
Private Const XLDELIMITED = 1
Private Const XLDOUBLEQUOTE = 1
Private Const XLNORMAL = -4143
 
Private Sub CmdOpen_Click()
'MsgBox (Z)
CommonDialog1.Filter = "Text Files (*.xls)|*.xls"
CommonDialog1.ShowOpen
ConvertTxtToExcel CommonDialog1.FileName
CmdOpen.Enabled = False
End Sub

i have already declared those

---

also, with ConvertTxtToExcel(lSourceFile As String), you have the last line as End Sub but i do not see anywhere where you have opened the Sub. so shouldnt ConvertTxtToExcel(lSourceFile As String) be:

VB Code:

Private Sub ConvertTxtToExcel(lSourceFile As String)

?

Quote:

---

Originally Posted by BrailleSchool

also, with ConvertTxtToExcel(lSourceFile As String), you have the last line as End Sub but i do not see anywhere where you have opened the Sub. so shouldnt ConvertTxtToExcel(lSourceFile As String) be:

VB Code:

Private Sub ConvertTxtToExcel(lSourceFile As String)

?

---

i am sorry that is actually

VB Code:

Public Sub ConvertTxtToExcel(lSourceFile As String)

that's why i have written end sub

still there is the same error plz help

Its Done The Data Type In The Data Base Was Not Same As That Of Variables

glad you got it fixed. also note that you can change the types in vbcode. for example, if you were using a string and you wanted to change to an integer, you can use CInt(). there are many of these change types in VB6.