I am attempting to learn vb database programming in visual studio.

I have a windows form application project ("MyTestDatabase") to which I have attached an empty SQLExpress database ("Database1"). I added a dataset ("MyTestDataSet") to the project.

The data base contains no table objects.

Would someone be so kind as to show me a short example of a subroutine that will:

1. create/add a table ("testTable") to the database container
2. add columns (fields) to the table ("TestField1", TestField2")
3. then iterate through the database1 container and print the names of the tables it finds and then the names and types of the fields it finds within the table.
4. remove the table when done

This is something I am used to doing in Access, but I am lost as to how (or even if its possible) doing it in visual basic.

Also where I need to put the code (can it be in a module or should it be in the 'MyTestDataSet' vb module

I understand how to create the table manually using add new table, what I want to do is that process in code...

This would be great help in getting me started!

Thankyou

Reference thread: http://p2p.wrox.com/topic.asp?TOPIC_ID=69258

Hi Tom.

I think you will find this article demonstrates how to do everything you are asking in items 1, 2, and 4.

http://www.vbdotnetheaven.com/Upload...LDatabase.aspx

For item 3 you will need to Join the system tables: syscolumns and systables on each other to get this information

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Wow, thats exactly what I wanted to see...

I set it up but can get it to run. I put the changes in that were suggested at the end of the article.

I get "There were build errors. Would you like to continue and run the last successful build"

How do I figure out what it doesn't like?

Thanks

Click 'No' to the message, obviously. After that look in your Error's list to see what the problem is. (The shortcut for this window is a chord: press CTRL + W then press E to bring up the errors list)

Without a specific error I can't be much help to you since "There were build errors." could be anything from a variable being out of scope to an entire class not being defined.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Thanks for your help Doug. This is turned into a good lesson for me. I don't know if you noticed but somebody named Bradley added a couple small changes to that code article link you provided. He said:

Added by bradley on 1/30/2008

In using this code with 2005 .net adn sqlexpress I had to change a few things for it to work: 1. Import system.data.sqlclient 2. Change 'localhost' in all the strings to '.\sqlserver' 3. in the 'ExecuteSQLStmt' routine, delete the two lines below: ConnectionString = "Integrated security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=.\SqlExpress;" conn.ConnectionString = ConnectionString Also, if you have Management studio express running, the database will not show up until you hit 'refresh'. Hope this helps someone!


I did item 1, and 3 in item 2 I used .\sqlexpress instead.

Now the code is working!! But I don't see the new database in the visual studio. When I try to add a new connection manually I am getting this message:

"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enable' to generate user instances."

Help at this page:
httphttp://://msdn2.microsoft.com/en-us/.../ms143684.aspx

shows the following but I don't know where to implement it..
______________________________________________
sp_configure 'user instances enabled','1'
RECONFIGURE;

GO
________________________________________________
To disable generation of user instances, use:
________________________________________________
 sp_configure 'user instances enabled','0'.
________________________________________________


where do I enable the user instances?

Thanks,
Tom

In advanced properties I set User Instance to false and then it connected.

Alternatively, you could have executed this code:

sp_configure 'user instances enabled','1'
RECONFIGURE;

from either a SQL Code window in VS or through a new query in SQL Server Management Studio.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

I seem to have everything working except the ViewView and the ViewSP

Private Sub ViewViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ViewViewBtn.Click
        ' Open the connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=.\sqlexpress;"
        conn.ConnectionString = ConnectionString
        conn.Open()
        ' Create a data adapter
        Dim da As New SqlDataAdapter("SELECT * FROM myView", conn)
        ' Create DataSet, fill it and view in data grid
        Dim ds As New DataSet
        da.Fill(ds)
        dataGrid1.DataSource = ds.DefaultViewManager
    End Sub 'ViewViewBtn_Click

 Private Sub ViewSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ViewSPBtn.Click

        '/ Open the connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=.\sqlexpress;"
        conn.ConnectionString = ConnectionString
        conn.Open()
        ' Create a data adapter
        Dim da As New SqlDataAdapter("myProc", conn)
        ' Create DataSet, fill it and view in data grid
        Dim ds As New DataSet("SP")
        da.Fill(ds, "SP")
        dataGrid1.DataSource = ds.DefaultViewManager End Sub 'ViewSPBtn_Click

When I execute view data I get the columns returned to the datagrid on my form. When I execute the other 2 I don't get anything returned to the grid. I see several differences between the above code and the following code that references the table, I am wondering if this is where the problem lies??

Private Sub ViewDataBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ViewDataBtn.Click
        '/ Open the connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        ConnectionString = "Integrated Security=SSPI;" + "Initial Catalog=mydb;" + "Data Source=.\sqlexpress;"
        conn.ConnectionString = ConnectionString
        conn.Open()
        ' Create a data adapter
        Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)
        ' Create DataSet, fill it and view in data grid
        Dim ds As New DataSet("myTable")
        da.Fill(ds, "myTable")
        dataGrid1.DataSource = ds.Tables("myTable").DefaultView
    End Sub 'ViewDataBtn_Click