Access remote sql server using VB.NET

Question

I've found a few examples of using vb.net to access an sql database, so far none of them have worked . They all involve using DataReaders. Maybe its the fact that the sql db is not on the same machine as the application.

I was just wondering if anyone had a more comprehensive example of using VB.NET to access a remote sql server.

Thanks!

EDIT: I've received a few helpful comments an replies already. So far my connection string looks like: "server=sqlblah.myhost.com;uid=myuser;pwd=pass;database=testdb"

Probably also good to mention their is no editing of the tables a this point, just reading.

Answer 1

Check out the SQLClient class.

One convienent way to access a SQL database is to fill a DataSet object with query data with a DataAdapter object.

Dim sSQL As String = "SELECT * FROM ???"
Dim conn As New SqlClient.SqlConnection("connection string")
Dim da As New SqlClient.DataAdapter(sSQL, conn)
Dim ds As New DataSet

da.Fill(ds, "TABLE NAME")

You can then access the "TABLE NAME" table in the DataSet object. The "connection string" is obviously your SQL connection string. Use the sSQL string to query as necessary.

Answer 2

Quick side note - a helpfull tool for creating connection strings:-

Open up a text document (notepad, wordpad etc) and save a blank document with the extension ".UDL". This will give you a "Data link Properties" mini app.

Open up the app and change the provider in the "Provider" to whichever provider you need (in this case OLE DB Provider for SQL Server).

You then need to build up the connection in the connection tab. Once you have chosen the criteria (ServerName (the drop down list will show you all visible Servers), Security permissions,Database (this drop down list will be populated based on the server chosen)) you can test your connection (to make sure you have permissions etc).

Click ok to close the App, rename the file to have a ".txt" extension and re-open in a text editor, hey presto, one built up connection string (as below).

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YOURDBNAME;Data Source=YOURSERVERNAME

Answer 3

my connection stirng is =(ConfigurationManager.ConnectionStrings("viss").ConnectionString) my database is EIS on a remote machine name(" NODE05")

i want to know what have to write in web.config file

Answer 4

i use vb.net 2008 and sql server2008,what i have to write in web.config file to connect with data base