August 25, 2008 at 1:17 pm
I have a VB program that I created using MS VB 2008 Express Edition. It uses 13 subroutines similar to the ones shown below. The sub Phase1() runs just fine with no errors but the sub Phase2() always returns a "cannot drop table" error the first time it runs. Tables Phase1 and Phase2 do not exist the first time the program is run.
After I force the program to run a second time the drop error goes away when the Phase2 sub routine runs.
Sub Phase2() seems somewhat identical in structure to 12 others sub routines, but only this one creates the error.
----------
Private Sub Phase1()
Dim connectionString As String = SQLDB2
Try
ds.Tables.Clear()
Using connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(connectionString)
connection.Open()
Dim sqlstr As String = "Drop table Phase1" _
& " SELECT Player.Playerid AS Expr1, Player.GHINNumber, Player.Gender, Player.Firstname, Player.lastname, " _
& " Playerscores.scoreID ,Playerscores.score, Playerscores.rating, Playerscores.slope, Playerscores.Type, " _
& " Playerscores.DatePlayed, Playerscores.Diff into Phase1" _
& " FROM Player INNER JOIN" _
& " Playerscores ON Player.Playerid = Playerscores.playerid" _
& " where type = 'T'" _
& " order by player.playerid, playerscores.dateplayed" _
& " Select * from Phase1 "
da = New SqlDataAdapter(sqlstr, connection)
ds.Tables.Clear()
da.Fill(ds, "TestTable")
DataGridView1.DataSource = ds.Tables("TestTable") ' Fill the datagridview with with the Table
connection.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
-----------------
Public Sub Phase2()
Dim connectionString As String = SQLDB1
Try
ds.Tables.Clear()
Using connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(connectionString)
connection.Open()
Dim sqlstr As String = "Drop table Phase2" _
& " SELECT Player.Playerid AS Expr1, Player.GHINNumber, Player.Gender, Player.Firstname, Player.lastname, " _
& " Playerscores.scoreID, Playerscores.score, Playerscores.rating, Playerscores.slope, Playerscores.Type, " _
& " Playerscores.DatePlayed, Playerscores.Diff into Phase2" _
& " FROM Player INNER JOIN" _
& " Playerscores ON Player.Playerid = Playerscores.playerid" _
& " where type = 'T'" _
& " order by player.playerid, playerscores.dateplayed" _
& " Select * from Phase2"
da = New SqlDataAdapter(sqlstr, connection)
ds.Tables.Clear()
da.Fill(ds, "TestTable")
DataGridView1.DataSource = ds.Tables("TestTable") ' Fill the datagridview with with the Table
connection.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
----------
Private Sub Phase1()
Dim connectionString As String = SQLDB2
Try
ds.Tables.Clear()
Using connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(connectionString)
connection.Open()
Dim sqlstr As String = "Drop table Phase1" _
& " SELECT Player.Playerid AS Expr1, Player.GHINNumber, Player.Gender, Player.Firstname, Player.lastname, " _
& " Playerscores.scoreID ,Playerscores.score, Playerscores.rating, Playerscores.slope, Playerscores.Type, " _
& " Playerscores.DatePlayed, Playerscores.Diff into Phase1" _
& " FROM Player INNER JOIN" _
& " Playerscores ON Player.Playerid = Playerscores.playerid" _
& " where type = 'T'" _
& " order by player.playerid, playerscores.dateplayed" _
& " Select * from Phase1 "
da = New SqlDataAdapter(sqlstr, connection)
ds.Tables.Clear()
da.Fill(ds, "TestTable")
DataGridView1.DataSource = ds.Tables("TestTable") ' Fill the datagridview with with the Table
connection.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
-----------------
Public Sub Phase2()
Dim connectionString As String = SQLDB1
Try
ds.Tables.Clear()
Using connection As SqlClient.SqlConnection = New SqlClient.SqlConnection(connectionString)
connection.Open()
Dim sqlstr As String = "Drop table Phase2" _
& " SELECT Player.Playerid AS Expr1, Player.GHINNumber, Player.Gender, Player.Firstname, Player.lastname, " _
& " Playerscores.scoreID, Playerscores.score, Playerscores.rating, Playerscores.slope, Playerscores.Type, " _
& " Playerscores.DatePlayed, Playerscores.Diff into Phase2" _
& " FROM Player INNER JOIN" _
& " Playerscores ON Player.Playerid = Playerscores.playerid" _
& " where type = 'T'" _
& " order by player.playerid, playerscores.dateplayed" _
& " Select * from Phase2"
da = New SqlDataAdapter(sqlstr, connection)
ds.Tables.Clear()
da.Fill(ds, "TestTable")
DataGridView1.DataSource = ds.Tables("TestTable") ' Fill the datagridview with with the Table
connection.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
---------------
Davew
August 25, 2008 at 1:31 pm
I would suggest adding an if exists clause to see if the table exists first. Just change the name of the object below.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ErrorLog]') AND type in (N'U'))
DROP TABLE [dbo].[ErrorLog]
August 25, 2008 at 2:30 pm
Let me give it a try.
Thanks!!
Davew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply