By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,363 Members | 2,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,363 IT Pros & Developers. It's quick & easy.

Creating temporary tables at run-time in SQL using VB.Net

P: n/a
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site
at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp

which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi Coleen,

I do this sort of thing all the time, but generally I create tables that
persist. If I need to truncate them, I do so. Sometimes I just delete then
by calling one sp and then rebuild them with another.

Here's a simple example, creating the 'nhtable' table:
Dim ocmdnh As SqlCommand

ocmdnh = New SqlCommand("exec sp_makenhtable", oconn)

ocmdnh.ExecuteNonQuery()

'open nhtable

Dim danhtable As New SqlDataAdapter("select * from nhtable", oconn)

Dim dsnhtable As New DataSet("nhtable")

danhtable.Fill(dsnhtable, "nhtable")

the sp sp_makenhtable deletes the table and then rebuilds it, and then fills
it with data; in sql server it looks like this:

CREATE PROCEDURE sp_makenhtable AS
if exists (select * from information_schema.tables where table_name =
'nhtable')
drop table nhtable
select imcacct, brname, addr, addr2, city, st, zip, draw, bundlect,
issuecode into nhtable from manifest where draw < 300
Sometimes the build is more sophisiticated, adding primary keys, indices,
etc. Sometimes I even delete and dynamically build the sp itself. I will
do this when the sp has variable needs that are not easily passed into the
sp itself. In that case, I will call it to create it and then call it again
to exec it:

Dim ocmd As SqlCommand

ocmd = New SqlCommand("exec sp_dropsp_buildbranchlistandtable", oconn)

ocmd.ExecuteNonQuery()

Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring

Else

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring & " and custstat
= '" & glf_custstat & Chr(39)

End If

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

ocmd = New SqlCommand("exec sp_buildbranchlist", oconn)

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Let me know if you have questions about these approaches; I'll be glad to
help.

HTH,

Bernie Yaeger

"Coleen" <co**********@yaho.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp
which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen

Nov 21 '05 #2

P: n/a
Hi,

Here is some sample code on how to create a database, table, alter
table and
stored procedure.

Dim conn As SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.MachineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

conn.Open()

CreateDataBase()

CreateClientsTable()

End Sub

Private Sub CreateDataBase()

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdatabases Where Name = 'VET')"

strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch

MessageBox.Show("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTable()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients" & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumber NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDate DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStoredProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Alter table example
strSql = "ALTER TABLE PetInfo ADD Vet int NULL"

cmdUpdate = New SqlCommand(strSql, connVet)

connVet.Open()

cmdUpdate.ExecuteNonQuery()

connVet.Close()

Ken

-----------------------------

"Coleen" <co**********@yaho.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site
at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp

which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen

Nov 21 '05 #3

P: n/a
Bernie, it's because of kind people like you & Cor (not to mention the
countless other who've helped me) that I appreciate this Newsgroup so much.
I guess you could say I'm a good Analyst/Designer, but when it comes to
writing code, I need examples, and I appreciate so much the help that I get!
THANKS!

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ep**************@TK2MSFTNGP11.phx.gbl...
Hi Coleen,

I do this sort of thing all the time, but generally I create tables that
persist. If I need to truncate them, I do so. Sometimes I just delete then by calling one sp and then rebuild them with another.

Here's a simple example, creating the 'nhtable' table:
Dim ocmdnh As SqlCommand

ocmdnh = New SqlCommand("exec sp_makenhtable", oconn)

ocmdnh.ExecuteNonQuery()

'open nhtable

Dim danhtable As New SqlDataAdapter("select * from nhtable", oconn)

Dim dsnhtable As New DataSet("nhtable")

danhtable.Fill(dsnhtable, "nhtable")

the sp sp_makenhtable deletes the table and then rebuilds it, and then fills it with data; in sql server it looks like this:

CREATE PROCEDURE sp_makenhtable AS
if exists (select * from information_schema.tables where table_name =
'nhtable')
drop table nhtable
select imcacct, brname, addr, addr2, city, st, zip, draw, bundlect,
issuecode into nhtable from manifest where draw < 300
Sometimes the build is more sophisiticated, adding primary keys, indices,
etc. Sometimes I even delete and dynamically build the sp itself. I will
do this when the sp has variable needs that are not easily passed into the
sp itself. In that case, I will call it to create it and then call it again to exec it:

Dim ocmd As SqlCommand

ocmd = New SqlCommand("exec sp_dropsp_buildbranchlistandtable", oconn)

ocmd.ExecuteNonQuery()

Dim creationstring As String

If glf_custstat = "" Then

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring

Else

creationstring = "CREATE PROCEDURE sp_buildbranchlist AS "

creationstring += " select imcacct, brname, addr, addr2, city, st, zip,
custstat into branchlist from branches"

creationstring += vbCrLf & "where imcacct in " & longstring & " and custstat = '" & glf_custstat & Chr(39)

End If

Dim sqladapt As New SqlDataAdapter

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

ocmd = New SqlCommand("exec sp_buildbranchlist", oconn)

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Let me know if you have questions about these approaches; I'll be glad to
help.

HTH,

Bernie Yaeger

"Coleen" <co**********@yaho.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to create a temporary SQL table using VB.net? I've checked the Microsoft

site
at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp

which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and read and read but still not "Get" it.... I need an example to go by, and then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen


Nov 21 '05 #4

P: n/a
Again, Thank you, thank you for all the help I've been getting!

Coleen :-)

"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:uN****************@TK2MSFTNGP15.phx.gbl...
Hi,

Here is some sample code on how to create a database, table, alter
table and
stored procedure.

Dim conn As SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim strConn As String

strConn = "Server = " & Environment.MachineName

strConn += "\VSdotNET; Database = ; Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

conn.Open()

CreateDataBase()

CreateClientsTable()

End Sub

Private Sub CreateDataBase()

Dim strSQL As String

strSQL = "if Exists (Select * From master..sysdatabases Where Name = 'VET')"
strSQL += "DROP DATABASE VET" & vbCrLf & " CREATE DATABASE VET"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch

MessageBox.Show("Error Creating DB")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub CreateClientsTable()

Me.Text = "Creating Clients Table..."

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'Clients' " & _

"AND TYPE = 'u')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP TABLE VET.dbo.Clients" & vbCrLf & _

"END" & vbCrLf & _

"CREATE TABLE Clients (" & _

"ID Int NOT NULL," & _

"LastName NVarChar(20) NOT NULL," & _

"FirstName NVarChar(20) NOT NULL," & _

"Address NVarChar(150) NOT NULL," & _

"City NVarChar(20) NOT NULL," & _

"ZipCode NVarChar(5) NOT NULL," & _

"PhoneNumber NVarChar(20) NOT NULL," & _

"WorkNumber NVarChar(20)," & _

"CellNumber NVarChar(20)," & _

"Email NVarChar(50) NOT NULL," & _

"Balance Money NOT NULL," & _

"BalanceDate DateTime NOT NULL," & _

"CONSTRAINT [ID] PRIMARY KEY CLUSTERED" & _

"(ID))"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Clients")

Finally

cmd.Dispose()

End Try

End Sub

Private Sub MakeClientStoredProcedure()

Dim strSQL As String = _

"USE VET" & vbCrLf & _

"IF EXISTS (" & _

"SELECT * " & _

"FROM VET.dbo.sysobjects " & _

"WHERE Name = 'ClientInfo' " & _

"AND TYPE = 'p')" & vbCrLf & _

"BEGIN" & vbCrLf & _

"DROP PROCEDURE ClientInfo" & vbCrLf & _

"END"

Dim cmd As New SqlCommand(strSQL, conn)

cmd.CommandType = CommandType.Text

Try

cmd.ExecuteNonQuery()

cmd.CommandText = "Create Procedure ClientInfo" & vbCrLf & _

"@ClientID int " & vbCrLf & _

"AS Select * " & vbCrLf & _

"FROM VET.dbo.Clients Where ID = @ClientID"

cmd.ExecuteNonQuery()

Catch ex As SqlException

MessageBox.Show(ex.ToString, "Error Creating Stored Procedure")

Finally

cmd.Dispose()

End Try

End Sub

Alter table example
strSql = "ALTER TABLE PetInfo ADD Vet int NULL"

cmdUpdate = New SqlCommand(strSql, connVet)

connVet.Open()

cmdUpdate.ExecuteNonQuery()

connVet.Close()

Ken

-----------------------------

"Coleen" <co**********@yaho.com> wrote in message
news:e0**************@TK2MSFTNGP10.phx.gbl...
Hi All :-)

Can anyone give me a URL where I can find a good example of code on how to
create a temporary SQL table using VB.net? I've checked the Microsoft site at:

http://msdn.microsoft.com/library/de...es_04_8jtx.asp
which is okay at explaining the process but gives me absolutely no code
examples. Unfortunately, I am one of those programmers that can read and
read and read but still not "Get" it.... I need an example to go by, and
then I "Get" it.

Can any one please help?

Thanks so much :-)

Coleen

Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.