DB issues when dropping DB and inserting test data

[pezza]

Hi All,

Having a few issues with some DB functions in my code at the moment. This code was developed by another user who kindly helped me out, and I know for sure it was working. The difference being that after having some compatability issues when upgrading to a new version of VS, I decided to create a new project from scratch and basically type the whole lot out again.

Whilst this fixed the issues I was having previously, it seemed to introduce some issues on the DB side.

As far as I can see (and my eyes are now sore through checking!) it is all correct, but I am wondering if someone can maybe spot something that I am not seeing.

So, the issues that I am getting is within the function to drop the database. I get an error saying that the database is in use (yet the same code worked perfectly previously)

Also, when using the Test Data Insert function, the Schedule data is failing with a SQL Exception error relating to scalar declaration. Again this worked fine previously.

Screenshot for the Insert Data Error below, and I have also attached the solution.

Thanks

Andrew

[Reverend Jim]

I'm thinking you are using Parameterized Queries with OleDB. I have noticed that when I try

cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname LIKE ?"
cmd.Parameters.AddWithValue("@parm", "D%")

my code works but when I try

cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname LIKE @parm"
cmd.Parameters.AddWithValue("@parm", "D%")

I get Must declare the scalar variable "@parm". There is another thread in this forum where someone claims that the second format actually works. I am awaiting clarification but for now if you use the first form you shouls be able to eliminate the error. Keep ini minid that you must use AddWithValue in the same order in which the parameters appear ini the query.

[pezza]

Thanks Jim,

So the particular code doing this is...

                Dim sqlText As String = String.Empty

                sqlText = "INSERT INTO Schedule (teamMemberEmpID, start, finish) "
                sqlText += "VALUES (@teamMemberEmpID, @start, @finish)"

                ' Open Connection

                cn.Open()

                ' Create new SqlCommand

                Using sqlCmd As New SqlCommand()

                    sqlCmd.CommandText = sqlText
                    sqlCmd.Connection = cn

                    sqlCmd.Parameters.AddWithValue("@teamMemberEmpID", teamMemberEmpID)

                    Dim paramName1 As SqlParameter = New SqlParameter()

                    paramName1.ParameterName = "@start"
                    paramName1.SqlDbType = SqlDbType.DateTime
                    paramName1.Value = start
                    sqlCmd.Parameters.Add(paramName1)

                    Dim paramName2 As SqlParameter = New SqlParameter()

                    paramName1.ParameterName = "@finish"
                    paramName1.SqlDbType = SqlDbType.DateTime
                    paramName1.Value = finish
                    sqlCmd.Parameters.Add(paramName2)

                    ' Execute

                    sqlCmd.ExecuteNonQuery()

                End Using

So how would you use your example with this kind of insert statement?

The data that is coming in to this function/sub is presented as follows:

        SQLExpressDB.InsertScheduleTbl("100", New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 17, 0, 0), New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59))
        SQLExpressDB.InsertScheduleTbl("100", New DateTime(DateTime.Now.AddDays(1).Year, DateTime.Now.AddDays(1).Month, DateTime.Now.AddDays(1).Day, 17, 0, 0), New DateTime(DateTime.Now.AddDays(1).Year, DateTime.Now.AddDays(1).Month, DateTime.Now.AddDays(1).Day, 23, 59, 59))
        SQLExpressDB.InsertScheduleTbl("100", New DateTime(DateTime.Now.AddDays(2).Year, DateTime.Now.AddDays(2).Month, DateTime.Now.AddDays(2).Day, 17, 0, 0), New DateTime(DateTime.Now.AddDays(2).Year, DateTime.Now.AddDays(2).Month, DateTime.Now.AddDays(2).Day, 23, 59, 59))
        SQLExpressDB.InsertScheduleTbl("100", New DateTime(DateTime.Now.AddDays(3).Year, DateTime.Now.AddDays(3).Month, DateTime.Now.AddDays(3).Day, 17, 0, 0), New DateTime(DateTime.Now.AddDays(3).Year, DateTime.Now.AddDays(3).Month, DateTime.Now.AddDays(1).Day, 23, 59, 59))

...and there is a section like this for 4 different people (id).

It is using the AddWithValue and the parameters are in the order that the data is coming through, i.e. ID, Start, Finish and the parameters are created in the order of @start and @finish

I am beginning to think that I have a buggy setup, as I have had a few weird occurrences lately, and this did work previously with the same code, so I might even try to re-install VS2013.

Could VS2005 Team Suite be conflicting, if both are installed? I noticed that when installing VS2013, it didn't upgrade or grumble about another instance so I assume they can both run side by side??

Thanks

Andrew

[Reverend Jim]

Try just

sqlCmd.Parameters.AddWithValue("@teamMemberEmpID", teamMemberEmpID)
sqlCmd.Parameters.AddWithValue("@start", start)
sqlCmd.Parameters.AddWithValue("@finish", finish)

[pezza]

Thanks Jim, that worked :-)

Not sure why it was working originally but not anymore, although I MUST have missed something out somewhere in the rest of the code when I typed it all out again. As long as it comes in with the correct format, then that is good with me!

Just need to work out now why the other random issues are reporting when trying to drop the database. Also another issue seems to have returned now when dropping the tables, but only on one of them! It really is random, as previously, it was the TeamMember table that complained, currently it is the Schedule Table, yet sometimes they all work, sometimes some work and sometimes others, yet it does still seem to drop the tables anyway :-S

...and the Database Drop...

Thanks for the help!

Andrew

[cgeier]

Your original error was occuring in lines 26-31 above. You had the following:

Dim paramName2 As SqlParameter = New SqlParameter()

paramName1.ParameterName = "@finish"
paramName1.SqlDbType = SqlDbType.DateTime
paramName1.Value = finish
sqlCmd.Parameters.Add(paramName2)

The name is "paramName2", but you (accidently) wrote "paramName1" here (paramName1.ParameterName, paramName1.SqlDbType, paramName1.Value)

Change to the following:

Dim paramName2 As SqlParameter = New SqlParameter()

paramName2.ParameterName = "@finish"
paramName2.SqlDbType = SqlDbType.DateTime
paramName2.Value = finish
sqlCmd.Parameters.Add(paramName2)

[cgeier]

When clicking on "Drop Tables", you need the offer the user the opportunity to cancel, in case the user accidently clicked the button.

Issue: The error "Error (Open Connection): Error in dataAdapter. Invalid object name 'Schedule'", is occuring during the table drop because the timer is still executing a query on the table.

Solution: Stop the timer (call "stopTimer") before dropping the tables.

Issue: Receiving "database in use" error when trying to drop the database.

Solution: Switch to 'master' before dropping the database. Also, set database to single-user mode before dropping.

    Public dbName = "ShiftRota"
    Public connectStr = "Server=.\SQLExpress;Database=" & dbname & ";Trusted_Connection=Yes;"

    Public Sub DropDatabase(ByVal dbName As String)

        If (System.Windows.Forms.MessageBox.Show("Are you sure you want to drop the database: '" & dbName & "'?  This will delete all TABLES and DATA in " & dbName & ".", "Database Drop", MessageBoxButtons.OK, MessageBoxIcon.Warning) = DialogResult.Cancel) Then

            ' Exit is user pressed Cancel

            Return

        End If

        Try


            Using cn As New SqlConnection(connectStr)

                Dim sqlText As String = String.Empty

                'switch to 'master' db and 
                'put db into single user mode to prevent connections, then drop it
                sqlText = "use master; Alter Database " & dbName & " set SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE " & dbName


                ' Open Connection

                cn.Open()

                ' Create new SqlCommand

                Using sqlCmd As New SqlCommand(sqlText, cn)

                    ' Execute

                    sqlCmd.ExecuteNonQuery()

                End Using

                System.Windows.Forms.MessageBox.Show("Database: '" & dbName & "' dropped.", "Drop Database", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End Using

        Catch ex As SqlException

            System.Windows.Forms.MessageBox.Show("Error:: DropDatabase ( " & dbName & "): " & ex.Message, "Error - Drop Database", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch ex As Exception

            System.Windows.Forms.MessageBox.Show("Error:: DropDatabase ( " & dbName & "): " & ex.Message, "Error - Drop Database", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub

    Public Sub DropTable(ByVal tableName As String)

        If (System.Windows.Forms.MessageBox.Show("Are you sure you want to drop table '" & tableName & "'? This will delete all data from the table and possibly from any tables that reference this table.", "Drop Table", MessageBoxButtons.OK, MessageBoxIcon.Warning) = DialogResult.Cancel) Then

            ' Exit is user pressed Cancel

            Return

        End If

        Try

            Using cn As New SqlConnection(connectStr)

                Dim sqlText As String = String.Empty

                sqlText = "DROP TABLE " & tableName

                ' Open Connection

                cn.Open()

                ' Create new SqlCommand

                Using sqlCmd As New SqlCommand(sqlText, cn)

                    ' Execute

                    sqlCmd.ExecuteNonQuery()

                End Using

                System.Windows.Forms.MessageBox.Show("Table: '" & tableName & "' dropped.", "Table Dropped.", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End Using

        Catch ex As SqlException

            System.Windows.Forms.MessageBox.Show("Error:: DropTable ( " & tableName & "): " & ex.Message, "Error - Drop Table", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch ex As Exception

            System.Windows.Forms.MessageBox.Show("Error:: DropTable ( " & tableName & "): " & ex.Message, "Error - Drop Table", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub

[cgeier]

I added some functions to 'SQLExpress.vb' to check if the database exists, to check if a table exists, and modified "DropDatabase" as described above.

DatabaseExists:

    Public dbName = "ShiftRota"
    Public connectStr = "Server=.\SQLExpress;Database=" & dbname & ";Trusted_Connection=Yes;"

    Public Function DatabaseExists() As Boolean

        Dim dbExists As Boolean = False

        Dim lConnectStr As String = connectStr

        'attempting to connect to a db that doesn't exist, will 
        'throw an error. use master instead.
        lConnectStr = lConnectStr.Replace("Database=" & dbName, "Database=master")
        lConnectStr = lConnectStr.Replace("Catalog=" & dbName, "master")

        Using cn As New SqlConnection(lConnectStr)

            Dim sqlText As String = String.Empty

            sqlText = "use master; SELECT count(name) as DBCount from sys.sysdatabases where name = '" & dbName & "'"

            Try

                ' Open Connection

                cn.Open()

                ' Use SqlDataReader to get data from query

                Using sqlCmd As New SqlCommand(sqlText, cn)

                    Try

                        ' Use SqlDataReader to read data from query
                        Dim dr As SqlDataReader

                        dr = sqlCmd.ExecuteReader

                        While dr.Read()
                            If dr("DBCount") = 1 Then
                                dbExists = True
                            End If
                        End While

                    Catch ex1 As Exception

                        Throw New Exception("Error in DataReader. " + ex1.Message)

                    End Try

                End Using

            Catch ex As SqlException

                System.Windows.Forms.MessageBox.Show("Error:: DatabaseExists ( " & dbName & "): " & ex.Message, "Error - Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Catch ex As Exception

                System.Windows.Forms.MessageBox.Show("Error:: DatabaseExists ( " & dbName & "): " & ex.Message, "Error - Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End Try

        End Using

        Return dbExists
    End Function

TableExists:

    Public Function TableExists(ByVal tableName As String) As Boolean

        Dim tblExists As Boolean = False

        Using cn As New SqlConnection(SQLExpressDB.connectStr)

            Dim sqlText As String = String.Empty

            sqlText = "SELECT count(name) as TableCount from " & dbName & ".sys.tables where name = '" & tableName & "'"

            Try

                ' Open Connection

                cn.Open()

                ' Use SqlDataReader to get data from query

                Using sqlCmd As New SqlCommand(sqlText, cn)

                    Try

                        ' Use SqlDataReader to read data from query
                        Dim dr As SqlDataReader

                        dr = sqlCmd.ExecuteReader

                        While dr.Read()

                            If dr("TableCount") = 1 Then
                                tblExists = True
                            End If
                        End While

                    Catch ex1 As Exception

                        Throw New Exception("Error in DataReader. " + ex1.Message)

                    End Try

                End Using

            Catch ex As SqlException

                System.Windows.Forms.MessageBox.Show("Error:: TableExists ( " & tableName & "): " & ex.Message, "Error - Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Catch ex As Exception

                System.Windows.Forms.MessageBox.Show("Error:: TableExists ( " & tableName & "): " & ex.Message, "Error - Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End Try

        End Using

        Return tblExists
    End Function

*Note: Single-user mode code (Alter database....), in previous post, taken from here.

[cgeier]

Rather than changing the database name in the connectStr like this:

Dim lConnectStr As String = connectStr

lConnectStr = lConnectStr.Replace("Database=" & dbName, "Database=master")
lConnectStr = lConnectStr.Replace("Catalog=" & dbName, "master")

Using cn As New SqlConnection(lConnectStr)

             ...

It would be better to create a new connection string and use it when necessary (in CreateDatabase, DropDatabase, and DatabaseExists):

Public managementConnectStr = "Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;"

               ...

 Using cn As New SqlConnection(managementConnectStr)

               ...

SQLExpresDB.vb has been updated.

[cgeier]

The following changes will disable the timer while using the Adminstration form, and re-enable it when the Administration form is closed.

    Private Sub AdministrationToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AdministrationToolStripMenuItem1.Click

        Dim adminFrm As AdministrationFrm

        adminFrm = New AdministrationFrm()

        'disable timer
        localTimer.Enabled = False

        adminFrm.ShowDialog()

        're-enable timer
        localTimer.Enabled = True
    End Sub

*Note: The Adminstration form should probably only be available to an application administrator--not to all users.

[pezza]

Hi cgeier,

Thanks for that. Re: the parameter, I don't know how many times I looked over that yet still missed it every time! :-( Sometimes I think you can look at something too long.

I have updated the parameter and have added the stoptimer() during the load of the admin form. It makes sense actually to do that whilst admin of the DB is occurring. For now, I have triggered the timer enabled on a form closing event, when the Admin form is closed, as I wasn't sure if the code to show the form paused until closed, or whether it would just continue after showing the form and end up immediately starting the timer again, but I can certainly try both. It seems to be working on the form closing event anyway.

I have also added the code to change to the master, and everything appears to be working again.

Re: the admin form. This certainly will only be available for certain users as I will be expanding on this to give the ability to create schedules, add/delete people and teams, so the whole admin form will be based on who is logged on. Just working on that bit now, pulling the current logged on user, but a lot of this I can only test in an AD environment. Only going to be about 3 users who will have this access :-)

Thanks for the replies

Andrew

[cgeier]

"Show" is not modal (ex: adminFrm.Show()), and execution continues after the form is opened. "ShowDialog" is modal, and further execution does not continue until the form is closed (ex: adminFrm.ShowDialog()). You can test this by putting Console.WriteLine statements before and after the statements:

        Dim adminFrm As AdministrationFrm

        adminFrm = New AdministrationFrm()

        Console.WriteLine("Before show")
        adminFrm.Show()
        Console.WriteLine("After Show")

and then

        Dim adminFrm As AdministrationFrm

        adminFrm = New AdministrationFrm()

        Console.WriteLine("Before ShowDialog")
        adminFrm.ShowDialog()
        Console.WriteLine("After ShowDialog")

[pezza]

Excellent! Thanks cgeier. I will have a play this evening. Looks like when i get some free time, i need to do a fair bit of reading up on all of this!

Thanks

Andrew