Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB
Working with SQL Server and VB Working with SQL Server and VB
Working with SQL Server and VB
Go Back  Xtreme Visual Basic Talk > > > Working with SQL Server and VB


Reply
 
Thread Tools Display Modes
  #1  
Old 12-29-2003, 09:22 AM
red_2003 red_2003 is offline
Newcomer
 
Join Date: Dec 2003
Posts: 21
Question Working with SQL Server and VB


I was wondering if there was a way that I could show all the tables that I have in an SQL Server Database in a listbox that is in VB 6.0? I need the user to be able to choose from the list of tables.
Reply With Quote
  #2  
Old 12-29-2003, 09:34 AM
GMan_NC's Avatar
GMan_NCWorking with SQL Server and VB GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

Yes you can. You can loop through the dbo.sysobjects table in you db and select the user tables. Here's a sample sql statement
Code:
Dim strSQL as String strSQL = "SELECT name, type " & _ "From dbo.sysobjects " & _ "WHERE (type = 'u') ORDER BY name"

Open a recordset based on this and populate your listbox with the results.
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #3  
Old 12-29-2003, 03:01 PM
red_2003 red_2003 is offline
Newcomer
 
Join Date: Dec 2003
Posts: 21
Unhappy

Okay, I'm still confused. Can you start from the beginning? I haven't used SQL Server before. I've always used Access, so I'm not sure I know what you're talking about when you say loop the dbo.sysobjects.
Reply With Quote
  #4  
Old 12-29-2003, 03:06 PM
GMan_NC's Avatar
GMan_NCWorking with SQL Server and VB GMan_NC is offline
Proud Daddy

Forum Leader
* Expert *
 
Join Date: Sep 2002
Location: North Carolina, USA
Posts: 1,962
Default

When you create a new database in SQL Server, it creates some system tables to keep track of the objects in your database (such as tables, columns, views, stored procedures etc.).

I have this code in a program I wrote that will list all the user tables in my database. It uses as Listview instead of a Listbox, but the idea is the same.

Code:
Sub ListTables() Set rst = New ADODB.Recordset Dim strsql As String strsql = "SELECT name, type " & _ "From dbo.sysobjects " & _ "WHERE (type = 'u') ORDER BY name" 'cnn is a declared and opened connection object rst.Open strsql, cnn, adOpenStatic, adLockReadOnly While Not rst.EOF lvTables.ListItems.Add , , rst!Name rst.MoveNext Wend rst.Close Set rst = Nothing End Sub
__________________
A morning without coffee is like something without something else.
Reply With Quote
  #5  
Old 12-29-2003, 04:07 PM
red_2003 red_2003 is offline
Newcomer
 
Join Date: Dec 2003
Posts: 21
Default

I keep trying to establish a connectection and I keep getting an error. Here is the code I'm using:

Dim cnn As New ADODB.Connection
Dim provStr As String

cnn.Provider = "sqloledb"

provStr = "Server = Name; Database = Name; UID = me; PWD = Password"

cnn.Open provStr

Set rst = New ADODB.Recordset.............

What am I doing wrong?
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server problem jabili Database and Reporting 2 08-02-2003 03:48 AM
Access & SQL server ferruh66 Database and Reporting 3 04-19-2003 04:15 PM
Q's re. usage of SQL Server Mike Database and Reporting 6 07-02-2002 08:44 PM
SQL Server 2000 Visual Developer Database and Reporting 4 11-12-2001 04:05 AM
GCSES (VB AND SQL SERVER 2000) Visual Developer Database and Reporting 6 08-29-2001 04:30 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB Working with SQL Server and VB
Working with SQL Server and VB
Working with SQL Server and VB
 
Working with SQL Server and VB
Working with SQL Server and VB
 
-->