SQL String Connection

Question

My Connection String:
Dim conString1 As String = "Data Source=igfnav03; Initial Catalog=[Test Navision]; integrated Security=true"

Error in Program:
System.Data.SqlClient.SqlException: 'Cannot open database "[Test Navision]" requested by the login. The login failed.
Login failed for user 'GOURMETFOODS\jenciso'.'

Error in SQL:

Error: 18456, Severity: 14, State: 38.

Answer 1

Hi JorgeEnciso-6991,

Please try the following.

VB.NET

Sub Main
    Const FILENAME As String = "e:\temp\file_ADO.NET.VB.xml"

    Dim builder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder()
    builder.DataSource = "SPACESHIP,1433"
    builder.InitialCatalog = "AdventureWorks2012"
    builder.IntegratedSecurity = True
    builder.ApplicationName = "Wonderful application"
    builder.NetworkLibrary = "dbmssocn"
    builder.PacketSize = 32768

    Using con As SqlConnection = New SqlConnection(builder.ConnectionString)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT TOP(2) * FROM Person.Person FOR XML PATH('r'), TYPE, ROOT('root')"
            con.Open()

            Using reader As XmlReader = cmd.ExecuteXmlReader()
                Dim xdoc As XDocument = XDocument.Load(reader)
                Dim settings = New XmlWriterSettings()
                settings.Indent = True
                settings.OmitXmlDeclaration = False
                settings.IndentChars = Microsoft.VisualBasic.Constants.vbTab
                settings.Encoding = New UTF8Encoding(False)

                Using writer = XmlWriter.Create(FILENAME, settings)
                    xdoc.Save(writer)
                End Using
            End Using

            Console.WriteLine("File '{0}' has been created.", FILENAME)
        End Using
    End Using
End Sub

Answer 2

Dim conString1 As String = "Data Source=igfnav03; Initial Catalog=Test Navision; integrated Security=true"
'"Data Source=igfnav03; Initial Catalog=Test Navision"
'"Data Source=igfnav03; Initial Catalog=[Test Navision];Integrated Security=SSPI "
'"Data Source=igfnav03; Initial Catalog=Test Navision; User ID=jenciso; Password=Garc1n1@16180; integrated Security=true"
'Data Source=igfnav03;Integrated Security=True

                        Using con1 As New SqlConnection(conString1)
                            Using cmd1 As New SqlCommand("SELECT No_, Description, Brand, `Pack Size Description`, `Primary Stocking Location`, `Base Unit of Measure`, `Case Code`, `Unit Conversion`, `UPC Unit Code`
                            'From `International Gourmet Foods$Item` WHERE No_ = '" & TextBox1.Text & "' ", con1)
                                cmd1.CommandType = CommandType.Text
                                Using sda1 As New SqlDataAdapter(cmd1)
                                    Using dt1 As New DataTable()
                                        sda1.Fill(dt1)
                                        DataGridView2.DataSource = dt1
                                        DataGridView2.Refresh()
                                    End Using
                                End Using
                            End Using
                        End Using

Answer 3

Sorry here is the complete code:

                        Dim conString1 As String = "Data Source=igfnav03; Initial Catalog=`Test Navision`; integrated Security=true"

"Alternative strings"
'"Data Source=igfnav03; Initial Catalog=Test Navision"
'"Data Source=igfnav03; Initial Catalog=[Test Navision];Integrated Security=SSPI "
'"Data Source=igfnav03; Initial Catalog=Test Navision; User ID=jenciso; Password=Garc1n1@16180; integrated Security=true"
'Data Source=igfnav03;Integrated Security=True

                        Using con1 As New SqlConnection(conString1)
                            Using cmd1 As New SqlCommand("SELECT No_, Description, Brand, `Pack Size Description`, `Primary Stocking Location`, `Base Unit of Measure`, `Case Code`, `Unit Conversion`, `UPC Unit Code`
                            'From `International Gourmet Foods$Item` WHERE No_ = '" & TextBox1.Text & "' ", con1)
                                cmd1.CommandType = CommandType.Text
                                Using sda1 As New SqlDataAdapter(cmd1)
                                    Using dt1 As New DataTable()
                                        sda1.Fill(dt1)
                                        DataGridView2.DataSource = dt1
                                        DataGridView2.Refresh()
                                    End Using
                                End Using
                            End Using
                        End Using

Answer 4

Hi @Jorge Enciso ,

Please try the following solution.
It shows how build a connection programmatically via SqlConnectionStringBuilder type.

void Main()  
{  
	SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();  
	builder.DataSource = @"SPACESHIP,1433";  
	builder.InitialCatalog = "AdventureWorks2012";  
	builder.IntegratedSecurity = true;  
	//builder.UserID = "<username>";  
	//builder.Password = "<password>";  
	builder.ApplicationName = "Wonderful application";  
	builder.NetworkLibrary = "dbmssocn"; // (TCP/IP)  
	builder.PacketSize = 32768;  
  
	//using (SqlConnection con = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SPACESHIP"))  
	using (SqlConnection con = new SqlConnection(builder.ConnectionString))  
	using (SqlCommand cmd = new SqlCommand())  
	{  
		// stored procedure  
		//SqlCommand cmd = new SqlCommand("spBudget", con);  
		//cmd.CommandType = CommandType.StoredProcedure;  
		//cmd.Parameters.AddWithValue("@budget1", "2020-01-01");  
		//cmd.Parameters.AddWithValue("@budget2", "2020-01-31");  
  
		// dynamic SQL  
		cmd.Connection = con;  
		cmd.CommandType = CommandType.Text;  
		cmd.CommandText = "SELECT TOP(2) * FROM Person.Person FOR XML PATH('r'), TYPE, ROOT('root')";  
  
		con.Open();  
		...  
	}  
}  

Answer 5

YitzhakKhabinsky-0887;

Do I need import a class?
I'm working with Visual Basic