Drop Table Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Drop Table Error

Author

Topic

Davew01
Starting Member

1 Post

Posted - 2008-08-25 : 15:22:01

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 23:47:19

Include a check to see if table exists and only if its true drop it.use like below
IF EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='yourtable')
--drop table code here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources