SQL DB Listing

[LeoKoach]

If I know the name of the SQL server, how can I connect to it, and get a list of Databases using VB.NET code only... without any 3rd party objects.

Thanks

Leo

[Shuja Ali]

You can connect to the SQL Server using the SQLConnection by giving the proper connection string, the reference to the connection strings can be found here. Remember you need to know the username and password in order to connect to the SQL Server. Connect to the MASTER Database (That comes with all SQL Servers) and use this query

Select NAME As DatabaseName From SYSDATABASES 

to open a DataReader, that will contain the names of the databases.

[LeoKoach]

I think I understand the concept, but since the field is not string, how would I get the names listed again?

Sorry if I am being dumb here, but this is my first time trying this kind of field format

Dim SQLText As String
Dim strDBNames As String
Dim strRS1 As String

Dim Conn As New ADODB.Connection
Dim Rs1 As New ADODB.Recordset

Conn.ConnectionString = "Provider=sqloledb;Data Source=" & lstSql.SelectedItem & ";Initial Catalog=master;User Id=sa;Password=10931093;"
Conn.Open()

SQLText = "Select name as strDBName From sysdatabases"
Rs1.Open(SQLText, Conn, 3, 1, )

strDBNames = ""

Do While Not Rs1.EOF
strRS1 = Rs1("strDBName")
strDBNames = strDBNames & strRS1 & vbCrLf
Rs1.MoveNext()
Loop

MsgBox(strDBNames)

This code gives me an error (obviously) saying I am trying wrong format (string) for binary field

[Shuja Ali]

First of all if you are using VB.NET then you shouldn't be using ADO 2.x, rather you should be using ADO.NET. Now if you use ADO.NET then your code will be much better and easier to understand. Here is what I did

Imports System.Data.SqlClient
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sDBNames As String = String.Empty
        Dim sCon As New SqlConnection("Data Source=rosefsdev57;Initial Catalog=master;User Id=sa;Password=adpadp;")
        sCon.Open()
        Dim sCommand As New SqlCommand("Select NAME From SYSDATABASES", sCon)
        Dim dbReader As SqlDataReader
        dbReader = sCommand.ExecuteReader()
        While dbReader.Read
            sDBNames &= dbReader(0) & Environment.NewLine
        End While
        MessageBox.Show(sDBNames)
        dbReader.Close()
        sCommand.Dispose()
        sCon.Close()
        sCon.Dispose()
    End Sub
End Class

And by the way, NAME field is the varchar, not binary.

Also remember to use CODE tags when you post code, it makes it easier to understand which part of your post is code and which is not.

[LeoKoach]

Thanks

I will try to do better on my questions next time

I much appreciated your help