problem in moving the memofield of ms access into vb recordset

[prakashleo]

hi to all i am using ADODB.Recordset in vb and i have some problem when i tried to move the text in the memofield of ms access database to the recordset.
while running at first it moves to recordset but suddenly it disappears and showing null value in recordset.

[Killer42]

hi to all i am using ADODB.Recordset in vb and i have some problem when i tried to move the text in the memofield of ms access database to the recordset.
while running at first it moves to recordset but suddenly it disappears and showing null value in recordset.

Could you show us the code?

[prakashleo]

Could you show us the code?

In this code rs2(1) and rs2(3) recordsets shows null values because i am using memo fields for those in ms access database

Private Sub showitemvalues()
Set rs = cn.Execute("select * from template_master where matgroup_id=" & txtnounid & " order by temp_sorted")
Set rs1 = cn.Execute("select count(item_id) from item_master where matgroup_id like '" & txtnounid & "'")
If rs1(0) <> 0 Then
Set rs2 = cn.Execute("select uom_code,addl_data,internal_desc,long_desc,materia l_type,item_id,material_group_id,flag_no from item_master where matgroup_id = " & txtnounid & " and item_id = " & txtitemid)
Set rs3 = cn.Execute("select mat_code from material_group where material_group_id = " & rs2(6))
txtuom.Text = rs2(0)
txtaddl_data.Text = IIf(IsNull(rs2(1)), "", rs2(1))
txtintdesc.Text = IIf(IsNull(rs2(2)), "", rs2(2))
txtlongdesc.Text = IIf(IsNull(rs2(3)), "", rs2(3))
txtmattype.Text = IIf(IsNull(rs2(4)), "", rs2(4))
txtmaterial_group_id = rs2(6)
txtmatgroup.Text = IIf(IsNull(rs3(0)), "", rs3(0))

If rs2(7) = "G" Then
txttype.Text = "Item"
ElseIf rs2(7) = "T" Then
txttype.Text = "Text"
Else
txttype.Text = "Spare"
End If


Else
Exit Sub
End If
End Sub

[Killer42]

I haven't had time to read the code yet. I'm just going to put it in [c o d e] tags for readability. I might also split some of the longer lines, to get them to fit the window here.

Private Sub showitemvalues()
Set rs = cn.Execute("select * from template_master" & _
      " where matgroup_id=" & txtnounid & _
      " order by temp_sorted")
Set rs1 = cn.Execute("select count(item_id) from item_master" & _
      " where matgroup_id like '" & txtnounid & "'")
If rs1(0) <> 0 Then
  Set rs2 = cn.Execute("select uom_code,addl_data,internal_desc," & _
        " long_desc, material_type, item_id, material_group_id," & _
        " flag_no from item_master" & _
        " where matgroup_id = " & txtnounid & _
        " and item_id = " & txtitemid)
  Set rs3 = cn.Execute("select mat_code from material_group" & _
        " where material_group_id = " & rs2(6))
  txtuom.Text = rs2(0)
  txtaddl_data.Text = IIf(IsNull(rs2(1)), "", rs2(1))
  txtintdesc.Text = IIf(IsNull(rs2(2)), "", rs2(2))
  txtlongdesc.Text = IIf(IsNull(rs2(3)), "", rs2(3))
  txtmattype.Text = IIf(IsNull(rs2(4)), "", rs2(4))
  txtmaterial_group_id = rs2(6)
  txtmatgroup.Text = IIf(IsNull(rs3(0)), "", rs3(0))
  If rs2(7) = "G" Then
    txttype.Text = "Item"
  ElseIf rs2(7) = "T" Then
    txttype.Text = "Text"
  Else
    txttype.Text = "Spare"
  End If
Else
  Exit Sub
End If
End Sub