Results 1 to 8 of 8

Thread: SQL in VB

  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Exclamation Unanswered: SQL in VB

    Hi all , im currently trying to connect a SQL server using the Microsoft OLE DB Provider for SQL Server (Data Environment) in VB
    The error that came out while refreshing server is = "Error Enumerating data servers, Enumerator report Unspecified error"

    Im a pure Sql noob coz before this im using MS Access so i would like to know how do i actually connect a sql server in VB's data environment?
    Am i to understand that, the Sql server is used to hold multiple databases so everytime we need to view the data in another machine...we had to export/import those databases to Sql Server?

    thats all..hope someone could help

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    Hi,

    We mainly use OLEDB to connect to SQL Server via VB/ASP/.NET

    Something along the lines of,

    objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=SQLOLEDB.1;User ID=username;Password=password;Initial Catalog=databaseName;Server=servername or ip address"

    objConn.Close
    Set objConn = Nothing

    Is that the sort of thing you are after?

  3. #3
    Join Date
    Aug 2003
    Location
    Sweden
    Posts
    4
    If I were you I'd not use the VB data environment at all, since I think it sucks a bit and I feel like I get less controll. I think it's better to use e.g. ADO and write all your code manually.

    If you are using more than one database-server (if you have one db for development and one live db) it might be perfered to use a DSN.

    E.g.

    set objConn = new ADODB.Connection
    objConn.Open "DSN=yourdsn;UID=youruid;PWD=yourpwd;"

    set rsCon = new ADODB.RecordSet
    rsCon.ActiveConnection = objConn

    strSQL = "select * from tblUser order by vchFirstName"
    rsCon.open strSQL
    rsCon.Close

    set rsCon = Nothing
    objConn.Close
    Set objConn = Nothing

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Unless you are using a scripting language, never use CreateObject, as it will be very slow compared to Swinger's syntax (I'll leave out the IDispatch vs v-Table binding speech, but trust me, it's faster the way Swinger is saying).

    The use of DSN's can be easier. It will also not be as fast as using an OLEDB connection string, as you will have to go VB->ODBC->OLEDB->SQL and back. A connection string will let you skip the ODBC layer. Most likely, you will not notice the performance hit unless you are really hammering the app.

    FYI: a connection string might look like:

    SET objConn=New ADODB.Connection
    objConn.Open "Provider=SQLOLEDB.1;DataSource=MyServer;Initi al Catalog=MyDatabase","myUsername","myPassword"

    Oh, and before you use this type of syntax, you will need to create a reference to the Microsoft ADO Library in Tool/References.

    This sounds a lot like the VB board....
    -bpd

  5. #5
    Join Date
    Aug 2003
    Posts
    2
    hi, thx for all who replied
    well i once used OLEDB before, its pretty time consuming (im kinda running outta time :P) , that is until i found out how use Data Environment, which is way faster than OLEDB
    well guess i'll have to adapt the OLEDB style then
    another question i need to ask
    lets say i've managed to connect to the SQL server with OLEDB
    now i need to run a test, running the program in different pc (in a LAN network) to store data in the SQL server
    how do i do this? can i just pass the data to the server (example Con1.addnew) or do i need to learn socket programming to handle this?
    can the Sql server be automaticly recognized within the network?

  6. #6
    Join Date
    Aug 2003
    Location
    Boston, MA
    Posts
    7
    As long as the SQL Server in question is on the LAN you should be able to access it (assuming you have the proper permissions). You should PING the machine to make sure it is available on the network.

    Another note on Connection Strings:

    The syntax for connections strings can get a little bit nasty. A simple way to get your connection string is to create a text file (test.txt) on the desktop or some other directory and change the extension to .udl (test.udl). Once done the icon will change to that of a data link. Double click on the icon and enter the necessary data on the tabbed form. Once done change the extension back to .txt, open the file and copy and the connection string into your vb project. This method works for both DSN as well and lets you test the connection.

    Regards,
    CPN

  7. #7
    Join Date
    Aug 2003
    Location
    India
    Posts
    1
    The best way to get the connecting strinngs is to drop the adodc control on the form, follow the steps and copy the string generated.

  8. #8
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    ...and no, you don't need to know anything about socket programming.

    cpn: great tip on the .udl file trick. I used to use the ADOControl trick, and have forced myself to learn the connection string syntax, because I hated constantly opening a VB project just to create a connection string -- tough when you connect to a bunch of different data sources (SQL, Informix, flat-files, etc). The best thing is I can build and keep a single text file with all the strings I will need.
    Well, I suppose I could have done that anyway... hmmm.
    -bpd

Posting Permissions

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