Results 1 to 15 of 15

Thread: Correct Syntax

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Correct Syntax

    Can someone please give me the correct syntax for retrieving data from a mysql table and putting it into a textbox? I have the connection string and everything else. I just need the command to populate the textbox.
    textbox.text= ?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Correct Syntax

    dim rs as adodb.recordset
    dim cn as adodb.connection

    set cn = new adodb.connection
    set rs = new ADODB.recordset

    cn.connectionstring = "Your Connection String here"
    cn.open

    rs.activeconnection = cn

    rs.open "SELECT Field1,Field2 FROM TABLE"

    textbox.text = rs.fields("Field1").value
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    I got that from someone else, but that could get messy because there are lots of fields in my query. I want to use a datagrid but have no idea about how to do it.
    Thanks

  4. #4
    Join Date
    Jan 2004
    Posts
    184

    Re: Correct Syntax

    The same technique applies to the datagrid. Retreive your connection and recordset as usual but then set the grids datasourse to the recordset

    set DataGrid.DataSource = rs

    (Remember if you close your recordset the data in the grid will also close)
    In abundance of water only the fool is thirsty. Bob Marley.

  5. #5
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    I got all that and am getting an error. What refrences do I need with the grid?
    Thanks

  6. #6
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    Here is what I have:


    Private Sub cmdSelect_Click()

    Dim rs As ADODB.Recordset
    Dim DB As New ADODB.Connection
    Dim sSQL As String
    sSQL = "select * from look_up"
    DB.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=survey"
    DB.Open
    rs.Open sSQL, DB
    'Set rs = New ADODB.Recordset
    Set DataGrid1.DataSource = rs

    End Sub

    I get an error with this.

  7. #7
    Join Date
    Jan 2004
    Posts
    184

    Re: Correct Syntax

    I am using the:

    Microsoft DataGrid Control 6.0 SP5 (OLEDB)

    My only other reference is:

    Microsoft ActiveX Data Objects 2.5 Library (ADO)

    And the following line works:

    Set grdMain.DataSource = rsClients

    What is your exact error?
    In abundance of water only the fool is thirsty. Bob Marley.

  8. #8
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    I am getting 'Object variable or With Block variable not set'

    Thanks alot.

  9. #9
    Join Date
    Jan 2004
    Posts
    184

    Re: Correct Syntax

    1) Declare your recordset globally (outside of the Sub) since when the sub finishes the recordset goes out of scope and closes and so does the grid.

    2) You must create a new rceordset (you have that line commented out.

    Here is what this should look like

    Private rs As ADODB.Recordset

    Private Sub cmdSelect_Click()

    Dim DB As New ADODB.Connection
    Dim sSQL As String

    sSQL = "select * from look_up"
    DB.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=survey"
    DB.Open
    Set rs = New ADODB.Recordset
    rs.Open sSQL, DB
    Set DataGrid1.DataSource = rs

    End Sub
    In abundance of water only the fool is thirsty. Bob Marley.

  10. #10
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    I get a 'The rowset is not bookmarkable' error.

  11. #11
    Join Date
    Jan 2004
    Posts
    184

    Re: Correct Syntax

    Open the recordset in Static mode and also set the CursorLocation to adUseClient.

    rs.CursorLocation = adUseClient
    rs.Open strSQL, cn, adOpenstatic, adLockreadonly

    Finally, verify if the table has a primary key.
    In abundance of water only the fool is thirsty. Bob Marley.

  12. #12
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    I don't have a primary key in my table. Is this the problem?

  13. #13
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    I set a column with a primary key and I still get the bookmarkable error.

  14. #14
    Join Date
    Jan 2004
    Posts
    184

    Re: Correct Syntax

    Did you also open the recordset in Static mode and also set the CursorLocation to adUseClient?

    rs.CursorLocation = adUseClient
    rs.Open strSQL, cn, adOpenstatic, adLockreadonly
    In abundance of water only the fool is thirsty. Bob Marley.

  15. #15
    Join Date
    Aug 2003
    Posts
    328

    Re: Correct Syntax

    Thanks Rami. I found this, rs.CursorLocation = adUseClient in another forum as I was getting desperate and didn't hear from you. I have no idea what adUseClient means, so if you could explain that I would be grateful. My program works fine now. Are you Israeli?
    Thanks again.
    Last edited by exdter; 03-08-04 at 10:32.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •