TheSwamp

Code Red => VB(A) => Topic started by: Jim Yadon on September 07, 2005, 09:43:38 PM

Title: ADO question
Post by: Jim Yadon on September 07, 2005, 09:43:38 PM

I have been noodling over this one for days now. Can anyone help me out with this one?

I keep getting a 3265 error but the data is there. I can remove ITM# or DESC (along with the relevant SQL bulding syntax) and pull the data for one field but not both together. In the past it's been my SQL. I've tested it within the MDB though so I'm reasonably sure that's not it.

Here's the code -

Code: [Select]

Private Sub UserForm_Initialize()
    Call Populate2ComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "ITM#", "DESC", "JOB DETAIL LIST", "JOB", "00-0000")
End Sub

Public Sub Populate2ComboWhere(objCombo As ComboBox, strDBase As String, strField1 As String, strField2 As String, strTable As String, strControlField As String, strControlData As String)
  Dim strSQL As String
  Dim objConn As ADODB.Connection
  Dim objRSet As ADODB.Recordset
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDBase & ";" & _
        "Persist Security Info=False"
  With objConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .mode = adModeReadWrite
  End With
  objConn.Open
  strSQL = "SELECT [" & strTable & "].[" & strField1 & _
                "],[" & strTable & "].[" & strField2 & _
                "] FROM [" & strTable & _
                "] WHERE [" & strTable & "].[" & strControlField & "]=""" & strControlData & _
                """ ORDER BY [" & strTable & "].[" & strField1 & "]"
  Set objRSet = objConn.Execute(strSQL, , adCmdText)
  Debug.Print strSQL
  Do Until objRSet.EOF
    objCombo.AddItem objRSet.Fields(CStr(strField1) & "~" & strField2)
    objRSet.MoveNext
  Loop
  objRSet.Close
  objConn.Close
End Sub


Title: Re: ADO question
Post by: MP on September 07, 2005, 09:47:29 PM

Use single quotes instead of double quotes for string tests and terminate with a semicolon.

i.e. Select * from tablename where fieldname = 'some value';

/guess

Title: Re: ADO question
Post by: Jim Yadon on September 07, 2005, 10:02:45 PM

Same result. Hmmm...

Here's the revised version what I'm using. It's a basic Access 2000 db BTW.

Code: [Select]

Private Sub UserForm_Initialize()
'    Call PopulateComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "ITM#", "JOB DETAIL LIST", "JOB", "00-0000")
'    Call PopulateComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "DESC", "JOB DETAIL LIST", "JOB", "00-0000")
    Call Populate2ComboWhere(cmbValueLisT, "c:\edbs\edbs.mdb", "ITM#", "DESC", "JOB DETAIL LIST", "JOB", "00-0000")
End Sub

Public Sub Populate2ComboWhere(objCombo As ComboBox, strDBase As String, strField1 As String, strField2 As String, strTable As String, strControlField As String, strControlData As String)
  Dim strSQL As String
  Dim objConn As ADODB.Connection
  Dim objRSet As ADODB.Recordset
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDBase & ";" & _
        "Persist Security Info=False"
  With objConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .mode = adModeReadWrite
  End With
  objConn.Open
  strSQL = "SELECT [" & strTable & "].[" & strField1 & _
                "],[" & strTable & "].[" & strField2 & _
                "] FROM [" & strTable & _
                "] WHERE [" & strTable & "].[" & strControlField & "]='" & strControlData & _
                "' ORDER BY [" & strTable & "].[" & strField1 & "]"
  Set objRSet = objConn.Execute(strSQL, , adCmdText)
  Debug.Print strSQL
  Do Until objRSet.EOF
    objCombo.AddItem objRSet.Fields(CStr(strField1) & "~" & strField2)
    objRSet.MoveNext
  Loop
  objRSet.Close
  objConn.Close
End Sub

Public Sub PopulateComboWhere(objCombo As ComboBox, strDBase As String, strField1 As String, strTable As String, strControlField As String, strControlData As String)
  Dim strSQL As String
  Dim objConn As ADODB.Connection
  Dim objRSet As ADODB.Recordset
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDBase & ";" & _
        "Persist Security Info=False"
  With objConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .mode = adModeReadWrite
  End With
  objConn.Open
  strSQL = "SELECT [" & strTable & "].[" & strField1 & "] FROM [" & _
            strTable & "] WHERE [" & strTable & "].[" & strControlField & "]='" & _
            strControlData & "' ORDER BY [" & strTable & "].[" & strField1 & "]"
  Set objRSet = objConn.Execute(strSQL, , adCmdText)
  Debug.Print strSQL
  Do Until objRSet.EOF
    objCombo.AddItem objRSet.Fields(strField1)
    objRSet.MoveNext
  Loop
  objRSet.Close
  objConn.Close
End Sub

Title: Re: ADO question
Post by: Jürg Menzi on September 08, 2005, 02:40:22 AM

How about:

Code: [Select]

objCombo.AddItem CStr(objRSet.Fields(strField1)) & "~" & CStr(objRSet.Fields(strField2))

Title: Re: ADO question
Post by: Jim Yadon on September 20, 2005, 12:51:17 PM

Simple answer to my own question. Make sure the references are correct for the project you are working in. :doh: :ugly:

Title: Re: ADO question
Post by: Kerry on September 21, 2005, 06:29:07 AM

Hi Jim

Just to see what the future holds ...

http://msdn.microsoft.com/netframework/future/linq/

C# 3.0 LINQ in action
C# 3.0 XLinq in action
C# 3.0 DLinq in action

the thought of the possibilities blew me away ..
The VB Versions are also available < I assume >

Title: Re: ADO question
Post by: MP on September 21, 2005, 07:42:48 AM

Quote from: Kerry Brown on September 21, 2005, 06:29:07 AM

Hi Jim

Just to see what the future holds ...

http://msdn.microsoft.com/netframework/future/linq/

C# 3.0 LINQ in action
C# 3.0 XLinq in action
C# 3.0 DLinq in action

the thought of the possibilities blew me away ..
The VB Versions are also available < I assume >

Very interesting and lol: "... Anders Hejlsberg, Chief randomizer on the C# development team ..."

Title: Re: ADO question
Post by: Jim Yadon on September 27, 2005, 12:12:27 PM

Cool stuff!!!  :lol:  Thanks Kerry!