List Sql database tables to ListBox

[Goofy]

Iv got a problem with listing sql tables to a listbox. Id like to list all tables from my SQL server -> DatabaseProgram -> Tables directory.

I have a Sql Server 2000 and Visual Baisc.net.

 

[PhilBayley]

Whats the problem? Do you need an sql query to get the table names or do you need the code to make the connection and make the query?

 

[Goofy]

Well i just need to get thoes tables to that list box. i got a tip for a query like
Query - select * from sysdatabase or something like that...
How to use that?

yhteys is
Private Const yhteys As STring = "server=localhost;database=TietokantaOhjelma;uid=sa;pwd="

Dim con As New sqlConnection(yhteys)
Dim cmd As Sql Command = con.CreateCommand
Dim da As SqlDataAdapter("Query...........)

listbox1.Items.Add(????)

something like that i guess

 

[PhilBayley]

ok - Ive taken this from the help file in VS and messed with it a little. You may have to tweak it to fit but it should get you a list of databases with your sql server. You will need to pass it the correct connection string and convert it to VB (which shouldnt be too hard (only Im no good at it)).

public void ReadMyData(string myConnString) 
{
  listBox1.Items.Clear();
   string mySelectQuery = "SELECT NAME from sysdatabases";
   SqlConnection myConnection = new SqlConnection(myConnString);
   SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
   myConnection.Open();
   SqlDataReader myReader = myCommand.ExecuteReader();
   try 
   {
     while (myReader.Read()) 
     {
       listBox1.Items.Add(myReader.GetString(0));
     }
    }
    finally 
    {
    // always call Close when done reading.
    myReader.Close();
    // always call Close when done reading.
    myConnection.Close();
    }
 }

If you need the tables rather than the dbs you have to find the correct sql query

 

[Nerseus]

Actually, I think you want to use sysobjects with an xtype of U (for user defined). If your connection is already made to the right database, use:

select [name] from sysobjects where xtype = U

If not, prefix the query above with "use [DatabaseName]". Make sure you have permissions (I assume youre running as sa...).

If you ever need to get the columns for a table you can use the syscolumns table and join on the id column from both tables.

As per your original question, Im not sure what you mean by "SQL server -> DatabaseProgram -> Tables directory". SQL Server stores its databases in files but you shouldnt care where they are, only the database name where the tables are stored. And, of course, the server name itself.

-Nerseus

 

[Goofy]

hmm. this aint helping me yet PhilBayley. If i try that code it only give exeptions. And i included my own connection string....

Nerseus: ok. so that folder thing doesnt matter. I only need to know the name of the database where my tables are. Its "TietokantaOhjelma". So do you have any idea or should that PhilBayleys example work?

 

[PhilBayley]

What sort of exceptions are you getting- have you tried stepping through the code to see what is incorrect and have you tried error catching which will give you more information. You should also make sure that you have privileges on the SQL Server to access the things your looking for.

Let me know

 

[Goofy]

ok. im not good at english and pretty new with vb.net too. so. I have a program which can make tables to sql database (works fine), save edited table(goin to do that next and shouldnt be that hard...) and open table from sql database.

PhilBayley.
I started like this:

Private Const Yhteys As String = "server=localhost;database=TietokantaOhjelma;uid=sa;pwd="
--------
ListBox1.Items.Clear()
Dim da As Nrw SqlDataAdapter("Select Name from TietokantaOhjelma", yhteys)

Then i cant understand this part of code:

SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();

......

 

[PhilBayley]

Originally posted by Goofy

Then i cant understand this part of code:

SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();

......

My VB is not very good but here goes (maybe someone else will correct this if im wrong)

   Dim myConnection As new SqlConnection(myConnString)
   Sim myCommand As new SqlCommand(mySelectQuery,myConnection)
   myConnection.Open()
   Dim myReader As new myCommand.ExecuteReader()

Hope this helps a bit or look up SqlConnection in the visual studio help as this has a vb example.

 

[Goofy]

hmm i cant understand right now. ill be back tomorrow. i hope that someone can write for me a code which works already and can also comment it alot so i know exactly what does it do. cya tomorrow i guess

edit.
about that Save table button.
this should work but it gives error while runing.

name = the name of the table
connect = connect to sql database

Dim da As New SqlDataAdapter("select * from" & name, connect)
Dim cb As New SqlCommandBuilder(da)
da.UpdateCommand = cb.GetUpdateCommand()
da.Update(ds.Tables(name))
ds.AcceptChanges

When i create a table and write some information in it and select that Save table button it stops and gives an error
"An unhandled exception of type System.InvalidOperationException occurred in system.data.dll

Additional information: Dynamic Sql generation for the updatecommand is not supported against a selectcommand that does not return any key column information.

And it points that green line to "da.UpdateCommand = cb.GetUpdateCommand()"

when i put my cursor on that green line it tells "This code has called into another function. When that function is finished, this is the next statement that will be executed.

Thats weird because i made this straight from one tutorial and it should work :/

 

[Nerseus]

First, its probably good to start with a simple "prototype" SQL program. Id suggest playing around with connection objects, data adapters, and such on a test app or test form to get a better understanding of the core database objects. It will save you a lot of headaches down the road and shouldnt take but a few hours.

Having said that, youve got a number of problems with the code from your last post. Ill give a few suggestions here, but you should really look into getting the basics down. MS has lots of samples for making connections, filling dataset or datareaders, and updating a database. Id start with those first and try to tweak them to your needs. After that, Id try something more "advanced" like dynamic table creation and using the sysobjects table.

Here are some things I saw in your code:
1. The SQL passed to the SqlDataAdapter needs a space between the FROM and the table name, as in:
Dim da As New SqlDataAdapter("select * from " & name, connect)

2. As the error says, to use the CommandBuilder you must have a Primary Key column defined on your table. Normally this would be an INT field, probably an AutoNumber (in Access) or an Identity (in SQL Server). Since youre creating your tables manually, youll have to add the Primary Key column if you want to use the Command Builder. You can also build your SQL by hand, assuming you know how. It shouldnt be hard since youre the one creating the table.

3. To get a list of tables in your database, you want something like this:
Dim da As Nrw SqlDataAdapter("Select [Name] from sysobjects where xtype = U", yhteys)
In a couple of posts down, you were using:
Dim da As Nrw SqlDataAdapter("Select Name from TietokantaOhjelma", yhteys)
but it sounded like TietokantaOhjelma was the name of the Database. You only need to use that name in the connection string to get you to the right database (which it looked like you were, based on your ConnectionString).

Im not sure how youre creating your tables so I cant help you with how to create a Primary Key.

By the way - and I should have asked this first - why are you creating tables on the fly (dynamically)? Can you not create them in the database by hand and then have your app use them? It doesnt seem unreasonable to create tables on the fly, but Im asking because its not done very often and maybe theres an easier or better way to do what you want.

-Nerseus

 

[Goofy]

In my program (as a school work...) user doesnt have to know anything about sql. He can just make a table of hes own by klicking a button and thats it. Like if my brother list hes dvds with my database program he can just make hes own table and import data and save it. Im going to do a php index soon where i can read the database over the net etc...

Now i have that primary key....

cmd.CommandText = _
"Create table " & nimi & "(" &_
"Numero int" &_
"Primary Key NOT NULL, " & _
.......................

Im working on that save database button right now. Now it just clear the written data. not saving it

But still i cant understand that part of code i noticed earlier. Im working on it too but no results yet

 

[Goofy]

Ok. now my tables are in a list box.

Dim da As New SqlDataAdapter _
("Select Name from sysobjects where xtype=u and name <>dtproperties", ConnectionString)

That name <>dtproperties because it listed also some how that kind of a
table and i dont want to list it...

dim ds As New DataSet("sysobjects")
da.Fill(ds, "sysobjects")
ListBox.DataSource = ds.Tables(0)

 

[Nerseus]

Excellent. Sorry, I forgot to mention dtproperties. Its used by SQL Server to hold diagram information if I remember correctly.

-ner