Attach MDF file to client computer

[Alexandru_mbm]

I have an app that uses an SQL Server Express 2008, .mdf file.

On my computer where i've coded the app all work's fine using this code:

        If conexiune.State <> ConnectionState.Open Then

            Try

                conexiune.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename=" & My.Settings.locatie_baza_date.ToString & "; Initial Catalog=" & My.Settings.locatie_baza_date.ToString & "; Integrated Security=True; Connect Timeout=30; User Instance=True;"
                conexiune.Open()

            Catch ex As Exception

                frm_afisare_eroare.lbl_cod_eroare.Text = Err.Number
                frm_afisare_eroare.txt_detalii_eroare.Text = ex.Message & vbCrLf & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & ")"
                frm_afisare_eroare.Show()

            End Try

        Else
        End If

On the clients computer i've installed the SQL Server Express 2008 and the application and i got this error:

ExecuteScalar requires an open avalaible Connection.

What's wrong in this code?

[Alexandru_mbm]

No one can help me with this ?

[keystone_paul]

I can't imagine that you get that error message in the code that you've posted as you aren't calling ExecuteScalar anywhere - I'm guessing that you're showing us the code that tries to open the connection.

I think you need to show us more of the code in order to be able to help.

[Grimfort]

I have never tried this, but 1st thing that comes to mind is the file location:

My.Settings.locatie_baza_date.ToString

Have you tried printing this in some way to ensure that the path is valid.

[Alexandru_mbm]

The connection it's placed in a module with this code:

Imports System.Data.SqlClient

Module mod_conectare_baza

    Public conexiune As New SqlClient.SqlConnection

    Public data_table As DataTable
    Public data_adapter As SqlClient.SqlDataAdapter
    Public data_set As New DataSet
    Public data_reader As SqlClient.SqlDataReader
    Public command_builder As SqlClient.SqlCommandBuilder
    Public command_exec As SqlClient.SqlCommand = conexiune.CreateCommand

    Public Sub conectare_db()

        deconectare_db()

        If conexiune.State <> ConnectionState.Open Then

            Try

                conexiune.ConnectionString = "Data Source='.\SQLEXPRESS';AttachDbFilename='" & My.Settings.locatie_baza_date.ToString & "';Initial Catalog='data';Integrated Security='True';Connect Timeout='30';User Instance='True';"
                conexiune.Open()

            Catch ex As Exception

                frm_afisare_eroare.lbl_cod_eroare.Text = Err.Number
                frm_afisare_eroare.txt_detalii_eroare.Text = ex.Message & vbCrLf & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & " - " & Err.Description & " - " & Err.Erl & " - " & Err.Source & ")"
                frm_afisare_eroare.Show()

            End Try

        Else
        End If

    End Sub

    Public Sub deconectare_db()

        Try

            If conexiune.State <> ConnectionState.Closed Then

                conexiune.Close()

            Else
            End If

        Catch ex As Exception

            frm_afisare_eroare.lbl_cod_eroare.Text = Err.Number
            frm_afisare_eroare.txt_detalii_eroare.Text = ex.Message & vbCrLf & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & " - " & Err.Description & " - " & Err.Erl & " - " & Err.Source & ")"
            frm_afisare_eroare.Show()

        End Try

    End Sub

End Module

And here is a part of the code of my form:

#Region "PROCEDURA: Verificare locatie baza de date"

    Private Sub verificare_locatie_baza_date()

        If My.Settings.locatie_baza_date <> String.Empty Then

            conectare_db()

            Try
                conectare_db()

            Catch ex As Exception
                Me.TopMost = False
                frm_afisare_eroare.lbl_cod_eroare.Text = Err.Number
                frm_afisare_eroare.txt_detalii_eroare.Text = Me.Text & " (" & Me.Name & ")" & vbCrLf & vbCrLf & ex.Message & vbCrLf & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & ")"
                frm_afisare_eroare.Show()
            End Try

            Me.Visible = False

            Try
                deconectare_db()
            Catch ex As Exception
                Me.TopMost = False
                frm_afisare_eroare.lbl_cod_eroare.Text = Err.Number
                frm_afisare_eroare.txt_detalii_eroare.Text = Me.Text & " (" & Me.Name & ")" & vbCrLf & vbCrLf & ex.Message & vbCrLf & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & ")"
                frm_afisare_eroare.Show()
            End Try

            Me.Visible = True

            afisare_campuri()
            verificare_disponibilitate_utilizatori()


        Else

            Dim dir_gecup As IO.DirectoryInfo = New IO.DirectoryInfo(My.Computer.FileSystem.SpecialDirectories.CurrentUserApplicationData)
            Dim fis_gecup As String = My.Computer.FileSystem.SpecialDirectories.CurrentUserApplicationData & "\data.mdf"

            Try

                If dir_gecup.Exists Then
                Else
                    dir_gecup.Create()
                End If

                If System.IO.File.Exists(fis_gecup) = False Then
                    System.IO.File.Copy(Application.StartupPath & "\data.mdf", fis_gecup)
                Else
                End If

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

            My.Settings.locatie_baza_date = fis_gecup.ToString
            My.Settings.Save()

            Application.Restart()

        End If

    End Sub

#End Region

[keystone_paul]

I have never tried this, but 1st thing that comes to mind is the file location:

My.Settings.locatie_baza_date.ToString

Have you tried printing this in some way to ensure that the path is valid.

The thing is.. if that path is invalid or the file doesn't exist, you'd expect it to raise an exception and trigger the Catch block and display the error form, which the OP doesn't suggest is happening.

[keystone_paul]

Hmm.. I still don't see anything in the code you've posted that is attempting to ExecuteScalar against the connection. Can you indicate exactly where the code is breaking?

I assume you've tried putting a breakpoint in and stepping through the code to ensure that the code that you think is running is actually being executed?

[Alexandru_mbm]

The file "data.mdf" is located on "Program Files" on the first use of the app.

The app will copy that file using this code:

Dim dir_gecup As IO.DirectoryInfo = New IO.DirectoryInfo(My.Computer.FileSystem.SpecialDirectories.CurrentUserApplicationData)
            Dim fis_gecup As String = My.Computer.FileSystem.SpecialDirectories.CurrentUserApplicationData & "\data.mdf"

In my case to: C:\Users\Alex\AppData\Roaming\Gecup\Gecup\1.0.0.0

Here are the files "data.mdf" and "data_log.ldf".

The file path it's stored to "locatie_baza_date" and it's OK but the database is not attaching to SQL Server Express. I i don't know why.

[Alexandru_mbm]

    Private Sub verificare_disponibilitate_utilizatori()

        ' Verificare disponibilitate utilizatori inregistrati in baza de date
        '-------------------------------------------------------------------------------------------------

        Dim total_utilizatori As Integer
        Dim selectare_utilizatori As String = "SELECT Count(utilizatori_aplicatie.cnp) FROM utilizatori_aplicatie"
        Dim cmd_total_utilizatori As New SqlClient.SqlCommand(selectare_utilizatori, conexiune)

        'MessageBox.Show(conexiune.State.ToString, "1", MessageBoxButtons.OK)

        Try

            conectare_db()

            'MessageBox.Show(conexiune.State.ToString, "2", MessageBoxButtons.OK)

            total_utilizatori = cmd_total_utilizatori.ExecuteScalar()

            If total_utilizatori < 1 Then

                Me.txt_utilizator.Text = "nedefinit"
                Me.txt_parola.Enabled = False

            Else

            End If

            deconectare_db()

        Catch ex As Exception

            Me.TopMost = False
            frm_afisare_eroare.lbl_cod_eroare.Text = Err.Number
            frm_afisare_eroare.txt_detalii_eroare.Text = Me.Text & " (" & Me.Name & ")" & vbCrLf & vbCrLf & ex.Message & vbCrLf & vbCrLf & ex.StackTrace & vbCrLf & vbCrLf & " (" & Err.Source & "-" & Err.LastDllError & "-" & Err.Erl & ")"
            frm_afisare_eroare.Show()

        End Try

    End Sub

[keystone_paul]

Like I say - have you put in a breakpoint and stepped through your code to check that any of this is actually running?

I'm not sure why you call connectare_db twice in the sub verificare_locatie_baza_date (once outside the try block and once inside it). That won't be the cause of the problem but it looks wrong.

[Alexandru_mbm]

Yes i will delete that one... outside of try catch...

But where is the problem ?

On my computer works fine... on the other machine is giving me the error.

Why ?

[keystone_paul]

Hmm... didn't spot the additional code you've posted.

[keystone_paul]

OK so what appears to be happening in your verificare_locatie_baza_date routine is you open the connection and then you close it.

Then you call the verificare_disponibilitate_utilizatori routine which opens it again - should you not just leave it open until you have done whatever you are trying to do?

[Alexandru_mbm]

It's a part of my form... called after the code of location of the database.

[Alexandru_mbm]

I have modified the code by commenting some of "conectare_db" and "deconectare_db".

The same error...

ExecuteScalar requires an open and available connection. The connexion's current state is closed.

???

[keystone_paul]

So... have you placed a breakpoint and stepped through your code?

[Alexandru_mbm]

On my computer where i write the code works fine...

On the other computer i got this:

ExecuteScalar requires an open and available Connection. The connection's current state is closed.

   at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Gecup.frm_splash_screen.verificare_disponibilitate_utilizatori()

 (System.Data-0-0)