Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am having a problem opening a database from a vb.net program. The program pulls file names from a directory and lists them in a ListBox. I have already created a file stream database to hold these files so I can recall them as needed. When I list the items on the ListBox I want to eliminate the files that already are in the database so only the files that are NOT in the database show on the list. The listBox portion works great, but when I try to open the database to check if the file exists, I get the following errors. I cannot figure out where the problem is.
The database files are located:
I:\MyFileCabinet\FileCabinetDB.mdf
I:\MyFileCabinet\FileCabinetLog.ldf
I:\MyFileCabinet\FileCabinetFS

Here is the complete code:

Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
    Private Sub Form1_Load(Sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub
    Private Sub BtnSearchFiles_Click(sender As Object, e As EventArgs) Handles BtnSearchFiles.Click
        ' Set Variables
        Dim SKFiles() As FileInfo
        Dim FldrInfo As New DirectoryInfo("I:\Document_Files\")
        Dim i As Integer = 0

        'Clear Listbox1
        ListBox1.Items.Clear()

        'Get the filenemes from the directory
        SKFiles = FldrInfo.GetFiles("*.pdf", SearchOption.AllDirectories)

        'Omit the files already in the database
        For Each MySearchFile In SKFiles
            If FindString(MySearchFile.Name) = False Then
                ListBox1.Items.Add(MySearchFile.Name)
            End If
            Application.DoEvents()
        Next
        ' Application.DoEvents()
    End Sub

    'If we select an item from the listbox, display it
    Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
        TxtSelectedFile.Text = ListBox1.SelectedItem
    End Sub

    ' Check the file To see If it Is already In the database
    Public Function FindString(ByVal myString As String) 'As Boolean
        Dim rtn As Boolean = False
        Using connection As SqlConnection = New SqlConnection With
            {
            .ConnectionString = "Server=DESKTOP-LB11ECO\SQLExpress; Database =I:\MyFileCabinet\FileCabinetDB.mdf; "
            }
            Using Command As SqlCommand = New SqlCommand With
                    {
                    .Connection = connection,
                    .CommandType = CommandType.Text,
                    .CommandText = "Select ItemName From Items Where ItemName = " + myString
                    }
                connection.Open()
                Try
                    If File.Exists(myString) Then
                        rtn = True
                    End If

                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                Finally
                    If connection.State = ConnectionState.Open Then connection.Close()
                End Try
            End Using
        End Using
        Return rtn
    End Function


The errors I got are:

Error #1
System.Data,SQLClient.SQLException: Login failed for user “.”
And:
Error #2
System.Data,SQLClient.SQLException: A connection was successfully established with the server, but then an error occurred during the login process(provider: Shared Memory Provider, error 0 – No process is on the other end of he pipe.)


What I have tried:

Changed the connection string to:
Connectionstring=" Server=.\SQLExpress; Database=FileCabinetDB.mdf;Trusted_Connection=True"
Connectionstring=" Server=.\SQLExpress; Database=FileCabinetDB;Trusted_Connection=True"
Connectionstring=" Server=DESKTOP_LB11ECO\SQLExpress;
database=I:\MyFileCabinet\FileCabinetDB;Trusted_Connection=True"..(error#2)
Posted
Updated 8-May-21 19:31pm

Your SQL server is not configured to support trusted connections or requires a "proper" login (which is strongly recommended). Try here: Simple SQL Connection String Creation[^] and get the connection working before you start coding.

But ... Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
   
Not sure if I understand the situation correctly, but it looks like you're trying to attach a db file into a SQL Server instance. If this is true, you should use AttachDbFilename instead of Database. Something like
Connectionstring="Server=DESKTOP_LB11ECO\SQLExpress;
AttachDbFilename=I:\MyFileCabinet\FileCabinetDB;Trusted_Connection=True"

And as OriginalGriff pointed out, always use parameters for values.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900