Question How to update created table to database

[thomas008]

Hi

What i am trying to do is check if a table exists by checking if the tablename is in select * from sys.tables. If the table exists i want to show it in a gridview. This part works fine but if the table doesn't exist i want to create the table, place it in my dataset and then update it to the database. When i use my normal procedure of updating a table this doesnt work.

This is the code i use to create the table and update it

            Dim table As DataTable = New DataTable()
            Dim column As DataColumn

            table.TableName = txtnr.Text
            column = New DataColumn()
            column.DataType = System.Type.GetType("System.Int32")
            column.ColumnName = "ID"
            column.AllowDBNull = False
            column.AutoIncrement = True
            column.AutoIncrementSeed = 1
            column.AutoIncrementStep = 1
            column.Unique = False
            table.Columns.Add(column)

            column = New DataColumn()
            column.DataType = Type.GetType("System.Int32")
            column.ColumnName = "routing"
            column.AllowDBNull = False
            table.Columns.Add(column)

            column = New DataColumn()
            column.DataType = Type.GetType("System.String")
            column.ColumnName = "HHMMSSmin"
            column.AllowDBNull = False
            table.Columns.Add(column)

            column = New DataColumn()
            column.DataType = Type.GetType("System.String")
            column.ColumnName = "HHMMSSmax"
            column.AllowDBNull = False
            table.Columns.Add(column)

            column = New DataColumn()
            column.DataType = Type.GetType("System.Int32")
            column.ColumnName = "goto"
            column.AllowDBNull = False
            table.Columns.Add(column)

            myDataset = New DataSet

            myDataAdapter.Fill(myDataset)
            myDataset.Tables.Add(table)
            If myDataset.HasChanges Then
                myDataAdapter.Update(myDataset, txtnr.Text)
            End If
            Dim view As New DataGridView
            view.DataSource = myDataset.Tables(txtnr.Text)
            view.Visible = True
            view.Width = 600
            Dim punt As New Point(600, 300)
            view.Location = punt

            Controls.Add(view)

I'm fairly new to using databases so please be kind

 

[thomas008]

I'm currently using the following code

Dim cnn As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Routing.mdf;"_
"Integrated Security=True;User Instance=True")
        Dim sql As String = "CREATE TABLE [" & txtnr.Text & "] (ID int NOT NULL PRIMARY KEY,"_
"Routing int NOT NULL,HHMMSSmin varchar(255) NOT NULL,HHMMSSmax varchar(255) NOT NULL,[goto] int NOT NULL)"
        Dim cmd As SqlCommand = New SqlCommand(sql, cnn)
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()

Can this be done with a dataset or is this the only way?

And i am also wondering why i have to put brackets around goto (my last fieldname)?

 

[MattP]

And i am also wondering why i have to put brackets around goto (my last fieldname)?

This should answer your question about GOTO: GOTO

 

[Tom]

Place your Create Table coding into its own stored procedure then call it when needed.

 

[cjard]

Can this be done with a dataset or is this the only way?

No. You have to create the table separately, and then create an update/insert/delete set of queries for the data adapter, that target the new table

Typically in a production environment you WOULDNT do this unless youre writing an app that manages databases. What you would do is have one table that has a column for whatever your txtnr.Text is:

do this ONCE in a database designer program:
CREATE TABLE whatevername (txtnr_text VARCHAR2(100) NOT NULL PRIMARY KEY, ID int NOT NULL PRIMARY KEY, Routing int NOT NULL,HHMMSSmin varchar(255) NOT NULL,HHMMSSmax varchar(255) NOT NULL,[goto] int NOT NULL)

Get it? instead of having hundreds of tables lying around with different names, you have one table whose name is fixed and the txtnr_text column allows you to pull different rows is the same way as your old design would

SELECT * FROM whatever WHERE txtnr_text = 'aaabbbccc'
vs
SELECT * FROM aaabbbccc

And i am also wondering why i have to put brackets around goto (my last fieldname)?

Because GoTo is a programming reserved word in the RDBMS

 

[thomas008]

No. You have to create the table separately, and then create an update/insert/delete set of queries for the data adapter, that target the new table

Typically in a production environment you WOULDNT do this unless youre writing an app that manages databases. What you would do is have one table that has a column for whatever your txtnr.Text is:

do this ONCE in a database designer program:
CREATE TABLE whatevername (txtnr_text VARCHAR2(100) NOT NULL PRIMARY KEY, ID int NOT NULL PRIMARY KEY, Routing int NOT NULL,HHMMSSmin varchar(255) NOT NULL,HHMMSSmax varchar(255) NOT NULL,[goto] int NOT NULL)

Get it? instead of having hundreds of tables lying around with different names, you have one table whose name is fixed and the txtnr_text column allows you to pull different rows is the same way as your old design would

SELECT * FROM whatever WHERE txtnr_text = 'aaabbbccc'
vs
SELECT * FROM aaabbbccc





Because GoTo is a programming reserved word in the RDBMS

I understand what you mean. But i'm not sure if that is the best solution in my case. I will try and explain why I think I need seperate tables. My application will have to control a telephonecenter. Every minute i have to check my database to see if the current time is inbetween my HHMMSSmin and HHMMSSmax. If it is i have to send a command too the center to program its forwarding differently. This is al working fine but the user also has to be able to adapt the database and its tables. so if i have only one table i have to show a very big gridview which is quite confusing. In seperate tables it is easier to control one routingpoint. By routingpoint i mean a telephone with lets say number 200 will have to be routed to telephone 201 between 09:00 and 10:00. Would you still recommend one table or should i just use my way?

Thanks for all the feedback.

 

[cjard]

Step back and think about your solution for a second

You're saying you want separate tables because one big table would be confusing..
..but where did you get the idea that you always have to select and retrieve the entire contents of a table all the time?

If you're still struggling with this, assume the following tables exist:

TableA.Fruit
------
'Orange'
'Pear'

TableB.Fruit
------
'Apple'
'Melon'

BigTable.ID, BigTable.Fruit
-------
'A', 'Orange'
'A', 'Pear'
'B', 'Apple'
'B', 'Melon'

Now please explain to me the difference in the output of the following queries:

SELECT Fruit FROM TableA
vs
SELECT Fruit From BigTable WHERE ID = 'A'



SELECT Fruit FROM TableB
vs
SELECT Fruit From BigTable WHERE ID = 'B'

("your way", "my way")

-

If you can successfully convince me that there is a difference between yours and mine, then go with your solution..

 

[thomas008]

Step back and think about your solution for a second

You're saying you want separate tables because one big table would be confusing..
..but where did you get the idea that you always have to select and retrieve the entire contents of a table all the time?

Well my boss gave me the idea that i had to use a different table for each routingpoint.
I see there is no difference in the methods only that mine will take up more diskspace i guess.
I will suggest your method to him. Thanks for your insights on this subject!

 

[cjard]

I see there is no difference in the methods only that mine will take up more diskspace i guess.

Disk space is not an issue. Good database design principles are completely violated with separate tables though, and it's much more resource intensive for the DB (beyond the scope of this thread. Summary: it's bad).

 

[thomas008]

I'v encountered a new problem. Currently i still have the tables called

"routingpoint" & txtroutingpointnumber.text

for each routingpoint
i also have a table called

"vacationdays" & txtroutingpointnumber.text

the fields of the tables are

routingpoint###
-ID
-HHMMSSmin
-HHMMSSmax
-goto

vacationdays###
-date
-goto

So for me to put it all in one table i would have to set the HHMMSSmin,HHMMSSmax and date fields to allow nulls

This is not what i want because i always need or HHMMSSmin and HHMMSSmax or the date field

Maybe i should make two tables? One for all the routingpoints and one for the vacationdays?

Thanks for your help so far

 

[cjard]

You need to provide more info about these tables, their content and purpose

If the relationship between routingpoint and vacationdays is 1:1 then you might find that they should be the same table

 

[thomas008]

I don't think they should be in one table. one table contains a date, the phonenumber to route and the phonenumber to route to. This table will be checked first. If the date in the table equals the current date then it will perform the routing if not i will check the second table.

The second table contains the phonenumber to route, the time for when to start routing, the time for when to stop routing and the phonenumber to route to. This table will be checked for the timefields. If the currenttime is between time to start and time to stop then the routing will be executed.

I hope this is clear enough. There is no relation between the tables. a phonenumber can have 100 entries in table 1 and zero in table 2 and visa versa.

 

[cjard]

Your problem becomes more and more confusing. I don't see a difference in what you are attempting to route here.

You want to run a query every minute and set an office telephone system to have forwarding enabled or not depending on the current time?

 

[thomas008]

Thats the basic of what i'm trying to do yes. But i see your point. If 1 use one table with a double primary key(one=ID,two=type) my data could be in one table. But there are 2 type and they need different data. The datafields they both need are where to route from and where to route to.
Then one type needs the time to start routing and the time to stop routing.
The other type needs the date so the routing will stay the whole day.

So the the fields they both need i set to NOT NULL
But what to do with the fields that aren't always needed?

 

[cjard]

The confusing thing for me is that you talk of date and time like they are two different things, but in computing terms they arent

What kind of forwarding are you trying to do on your phone system?

I'd probably have one table, with a number from and to and a date from and to. Whenever I needed to know where to route, I would select all relevant records, and order them by the size of the window (to_date - from_date) - the idea being that you make a general rule that phone number 123 maps to 123 from 01-jan-1901 to 31-dec-9999, and then if that guy goes on holiday for all of april 2009 you add another record saying "phone 123 maps to 234 from 01-apr-2009 00:00:00 to 30-apr-2009 23:59:59"

If your phone system does a (Oracle syntax):

--take the top row from a list of all valid maps for this phone number
SELECT * FROM (
  SELECT toNum FROM phonemap WHERE fromNum = 123 and sysdate BETWEEN fromDate AND toDate
  ORDER BY toDate - fromDate
)WHERE rownum = 1

Have you any other uses for your system?