Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: New to Visual Basic

    I am trying to make a simple application where someone can choose columns from SQL Server database tables, and run a select query to retrieve the results. I want to display available columns in a ListBox. Where do I put the database path in the properties of that ListBox? Is this in the DataSource property? How do I get a list of sources?
    Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    53

    Re: New to Visual Basic

    Hi,

    Have you setup a connection string to your SQL Server database.

    Normally you set up your own datasources.

    Lets Discuss
    Jason

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: New to Visual Basic

    I haven't done anything yet. I have never used Visual Basic before. Do I set it up in the Form_Load()? What is the connect string?
    Thanks for answering.

  4. #4
    Join Date
    Jan 2004
    Posts
    53

    Re: New to Visual Basic

    Hi,

    Have you setup a connection string to your SQL Server database.

    Normally you set up your own datasources.

    Lets Discuss
    Jason

  5. #5
    Join Date
    Aug 2003
    Posts
    328

    Re: New to Visual Basic

    I haven't set up the connection string yet. What is the syntax and where do I set it up?

  6. #6
    Join Date
    Jan 2004
    Posts
    53
    Here is some code for you to try, keep me updated

    This will populate a combobox with the field you specify


    Dim rs As ADODB.Recordset
    Dim SQL As String
    Dim DB As New ADODB.Connection

    DB.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=xxxxx;Initial Catalog=SQLSERVERDBNAME;Data Source=SERVER"

    DB.Open

    Set rs = New ADODB.Recordset

    SQL = "select field from table order by field"
    rs.Open SQL, DB

    Do Until rs.EOF
    Combo1.AddItem rs("field")
    rs.MoveNext
    Loop


    End Sub
    Jason

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    Should it look like this?

    Dim rs As ADODB.Recordset
    Dim SQL As String
    Dim DB As New ADODB.Connection

    Private Sub Form_Load()


    DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=UTSQL01;Data Source=SERVER"
    DB.Open

    Set rs = New ADODB.Recordset

    SQL = "select * from realtors"
    rs.Open SQL, DB

    Do Until rs.EOF
    Combo1.AddItem rs("field")
    rs.MoveNext
    Loop

    End Sub

    I'm using Windows authentication so I took out the password part. With this i get a User Defined type not defined error.

  8. #8
    Join Date
    Jan 2004
    Posts
    53
    Dim rs As ADODB.Recordset
    Dim SQL As String
    Dim DB As New ADODB.Connection

    Private Sub Form_Load()


    DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=UTSQL01;Data Source=(THIS NEEDS TO BE YOUR SERVER NAME)"
    DB.Open

    Set rs = New ADODB.Recordset

    SQL = "select * from realtors"
    rs.Open SQL, DB

    Do Until rs.EOF
    Combo1.AddItem rs("(THIS NEEDS TO BE THE NAME OF THE FIELD YOU WANT IN THE LIST")
    rs.MoveNext
    Loop

    End Sub
    Jason

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    So what need to be here?
    Initial Catalog=

  10. #10
    Join Date
    Jan 2004
    Posts
    53
    The Database Name.

    ie. In SQL Server you may have like 5 databases on 1 server.

    We can try automating this process if you want or would find it easier
    Jason

  11. #11
    Join Date
    Aug 2003
    Posts
    328
    Here is what I have now:

    Dim rs As ADODB.Recordset
    Dim SQL As String
    Dim DB As New ADODB.Connection

    Private Sub Form_Load()


    DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=proform;Data Source=UTSQL01"
    DB.Open

    Set rs = New ADODB.Recordset

    SQL = "select * from lender"
    rs.Open SQL, DB

    Do Until rs.EOF
    ListBox.lstColumns rs.name1
    rs.MoveNext
    Loop


    End Sub

    proform is the database name

    Is this ok?

  12. #12
    Join Date
    Jan 2004
    Posts
    53
    Did you test it yet?

    Im pretty sure the:

    Do Until rs.EOF
    ListBox.lstColumns rs.name1
    rs.MoveNext
    Loop

    Needs to look like this:

    Do Until rs.EOF
    ListBox.lstColumns rs("name1")
    rs.MoveNext
    Loop
    Jason

  13. #13
    Join Date
    Aug 2003
    Posts
    328
    I still get the User-defined type not defined error. The rs As ADODB.Recordset is highlighted with this error.

  14. #14
    Join Date
    Aug 2003
    Posts
    328
    Originally posted by exdter
    I still get the User-defined type not defined error. The rs As ADODB.Recordset is highlighted with this error.
    Also, what did you mean by automating? Is it easier?

  15. #15
    Join Date
    Jan 2004
    Posts
    53
    Ok we need to add a reference library in the main screen Go To -- Project, References,

    Once the box opens select
    Microsoft ActiveX data objects 2.0 Library
    Microsoft data binding collection
    Microsoft data formatting object library


    While your at it go to Project Components and add
    Microsoft Data Control 6.0 (OLEDB)
    Microsoft Common Dialong Control 6.0

    What i meant by automating is that you can have VB create the connection string for you and then copy it from the wizard to your code
    Jason

Posting Permissions

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