f



Accessing an SQL Server Database with VB

I will be designing an app with VB that will access an SQL server
database.

How difficult is this to do in VB?  Is there some built-in
compatibility between the two?

Any suggestions about a book to buy would be much appreciated.  The one
I'm looking at now is Beginning SQL Server 2000 for Visual Basic
Developers
by Thearon Willis.

I've had experience with VB but none with SQL server.

Thanks,
Corey

0
4/21/2005 5:24:19 PM
comp.lang.basic.visual.misc 10153 articles. 0 followers. Post Follow

1 Replies
568 Views

Similar Articles

[PageSpeed] 53

<caramelboy586@hotmail.com> wrote in message
news:1114104259.357833.240460@f14g2000cwb.googlegroups.com...
> I will be designing an app with VB that will access an SQL server
> database.
>
> How difficult is this to do in VB?  Is there some built-in
> compatibility between the two?
>
> Any suggestions about a book to buy would be much appreciated.  The one
> I'm looking at now is Beginning SQL Server 2000 for Visual Basic
> Developers
> by Thearon Willis.
>
> I've had experience with VB but none with SQL server.

You're lucky!

VB is tailor-made for working with SQL-Server.

Just do yourself a favour, if you're goind to do a lot
of db-stuff in the future:
    Learn to work with the database-object directly in code,
    instead of using the Datacontrol and the bound controls.

They are nice to get you started, and for simple "Look Ma!
No hands!" applications, but as soon as things get serious
They don't give you the flexibility you're going to need.

The steps are simple:
1. Choose a db-library to use: (in References)
    * DAO (Older, Still the best for Jet)
    * RDO (A *very* thin object layer on top of ODBC. Nice/fast)
    * ADO (Modern, good & fast. I prefer thisone for SQL-Server)

2. Build a connection string that describes the Server & DB
   you're going to connect to

3. Create an instance of the choosen library's Connection object

4. Create an instance of a RecordSet/ResultSet object ( If your
   query returns data)

5. Build an SQL query string

6. Execute it (possibly returning data)

(7.) If data returned, loop thru the recordset, and do your stuff

Let's try an ADO sample to get all customers from Norway or Sweden:

    Dim sConn As String

    sConn = "driver={SQL Server};" & _
            "SERVER=serverName;DATABASE=dbName;"

    Dim cn as ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open sConn

    Dim sSQL as string
    sSQL = "SELECT FirstName, LastName, Phone"
    sSQL = sSQL & " FROM Customer"
    sSQL = sSQL & " WHERE Country = 'Norway'"
    sSQL = sSQL & " OR Country = 'Sweden'"

    Dim rs As ADODB.Recordset
    Set rs = cn.Execute(sSQL)

    While Not rs.EOF
        Debug.Print rs("LastName") & ", " & _
                    rs("FirstName") & ": " & _
                    rs("Phone")
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

-- 
Dag.


0
me3469 (798)
4/21/2005 6:37:22 PM
Reply:

Similar Artilces:

Access database(Jet Engine) and SQL Server or SQL Server express
Hi, I am trying to find out what are the big differences between access database Jet engine and SQL Server? If I have serveral web applications (same content different copies) running as different sites(on IIS), each site using an access as a back-end database, all sites will be running simultaneously, all the access databases are on the same machine, on this approach, does each copy of access has its own Jet engine to handle database transaction or only has one Jet engine with multiple threads handles multiple copies of access database. Also, I'd like to know what kind database, and database engines are used by big sites such as Yahoo, Hotmail, MSN, etc, and what are the good database structures for the hosting applications that deal with huge,complex information and large number of users. Thanks a lot, your time and help are highly appreciated. On 13 Sep 2004 18:37:35 -0700, penghao98@hotmail.com (Leon) wrote: I think there is only one instance of Jet running. You can check this using Task Manager. More than likely you don't have to worry about Yahoo et al. They take many orders of magnitude more traffic then yours. One thing for sure: they are not using an Access back-end :-) -Tom. >Hi, > I am trying to find out what are the big differences between access >database Jet engine and SQL Server? If I have serveral web >applications (same content different copies) running as different >sites(on IIS), each site using an access as a back-end database...

Visual Basic 6 Accessing XML database on a web server
Hi, I was wondering if someone could help me to use visual basic 6 to connect to a server with a XML file on it and retrieve certain parts. I'm a bit of a newbie in XML programming, so any help would be greatly appreciated. Thanks, Daniel Sullivan Hi Daniel, This should get you the xml from your server down into a DOM document. They are used to process xml in a node tree. Look up XPath as a means of jumping to nodes directly. http://www.w3schools.com/ has good resources on this and other xml style things. Look up DOM on MSDN and you should get the hang of it quite quickly. It is a simple technology. You'll need to add Microsoft XML, 3.0 into your references for the vb project. This requires a reasonably up to date version of IE or a seperate install of MSXML. Regards, Finlay Macrae Dim obj As New DOMDocument Dim bstrString As String Dim objXMLHTTPRequest As New MSXML2.XMLHTTP objXMLHTTPRequest.open "GET", "http://yourserver/your .xml or .asp", False objXMLHTTPRequest.setRequestHeader "Content-type", "text/xml" objXMLHTTPRequest.send If objXMLHTTPRequest.Status = 200 Then bstrString = objXMLHTTPRequest.responseText Else MsgBox "Problem with download :(" End If obj.loadXML bstrString MsgBox obj.xml "Daniel Sullivan" <danielsullivan87@bigpond.com> wrote in message news:<kz4Ta.7948$OM3.2893@news-server.bigpond.net.au>... > Hi, I was wondering if someone could help me to u...

Connect to Access database that resides on web server via Visual Basic?!
Is there a way to use access database on web host (I have a web hosting account) in visual basic. I want to make Visual basic client applications that connect to internet and work with access database on some web server. Can it be done? Tnx. If you are deploying VB clients, I would just use Sql Server instead. A lot of ISP's (including me) support Sql Server and the ability to connect to it over the web using a simple connection string... or you can configure a dsn on the client as well, your choice. You can't simply "connect" to mdb's. One reason is that it is a Jet limitation, thus requiring your to have direct file access to the mdb. I have heard of some people that talk about using "Remoting" in .Net to do this -- but I don't know anything about that yet. -- Jerry Boone Analytical Technologies, Inc. http://www.antech.biz Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and Access "Zvonko Tusek" <ztus@inet.hr> wrote in message news:c1fmvi$556$1@sunce.iskon.hr... > Is there a way to use access database on web host (I have a web hosting > account) in visual basic. > I want to make Visual basic client applications that connect to internet and > work with access database on some web server. > Can it be done? > Tnx. > > > ...

Using GetOleDbSchemaTable and Visual Basic .NET for MS-Access and SQL Server Express
I need to write a VB.Net application that will take an mdb file (Access 2000) and create corresponding copy of it in SQL Express, creating all the tables and relationships, and transferring over the data (using an existing application like DTS in SQL Server or something else is NOT an option - this must be coded from scratch). The Access databases that it must be able to handle will only have tables, relationships, and data (i.e. no forms, macros, queries, reports). I see that in the System.Data.OleDb namespace there is the method GetOleDbSchemaTable that returns all sorts of schema information. I haven't fully explored how to use this method, but do you think I will be able to pull out all that I need from the various schema information returned to rebuilt the mdb file in SQL Express? I just don't want to start heading down this road and invest quite a bit of effort only to hit an impassable road block. Thank you for any thoughts on this! Marcus I don't know of any gotchas with using GetOleDbSchemaTable to roll your own upsizing tool. However, you'll need to come up with your own Access to SQL Server data type mappings. -- Hope this helps. Dan Guzman SQL Server MVP "Marcus" <holysmokes99@hotmail.com> wrote in message news:1139699982.671856.64020@g44g2000cwa.googlegroups.com... >I need to write a VB.Net application that will take an mdb file (Access > 2000) and create corresponding copy of it in SQL Express, creating all >...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45317232402)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45317232402) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45310057605)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45310057605) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45323332409)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45323332409) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312914405)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312914405) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45315132407)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45315132407) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312957611)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312957611) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45324557611)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45324557611) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45319014409)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45319014409) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45311157608)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45311157608) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45313257605)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45313257605) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45324214410)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45324214410) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312657608)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312657608) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45322157611)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45322157611) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45310032411)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45310032411) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs used in program formulation. Convert those to computer language. Maintain and correct existing programs. Please send your current resume in confidence to "Wolf M. Metzner" <WMetzner@EuroSoft-Inc.Com> ..45310032411. ...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45309732403)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45309732403) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45323914407)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45323914407) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45319314405)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45319314405) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45317857621)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45317857621) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45310057605)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45310057605) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312657608)
US-MN-Rochester: VB Specialist, Visual Basic with ASP, DHTML, MS SQL Server (45312657608) ========================================================================================= Position: VB Specialist Reference: WMM00011 Location: Rochester MN Duration: Skills: Visual Basic with ASP DHTML MS SQL Server Windows NT Scope: Convert data from specifications and statements. Recent programming experience in VB ASP, DHTML, SQL and NT a must; Construct logical data models and and designs u...

Web resources about - Accessing an SQL Server Database with VB - comp.lang.basic.visual.misc

Highest Percentage Of Opera Mini Users Accessing Facebook? Macau
If you were asked to guess which country had the highest percentage of users of Opera mobile Web browser Opera Mini users accessing Facebook ...

International Users Accessing Facebook Places Through US VPN Accounts
By using a virtual private network (VPN) hosted in the United States, Facebook users from around the world are accessing Facebook Places. The ...

What are some alternatives to Yodlee for accessing bank information?
Clay Loveless , Founder, Jexy. Co-founder, Mashery. Founder, Jexy. Co-founder, Mashery.

Cloud Console - Accessing files in cloud storage for iPad on the iTunes App Store
Get Cloud Console - Accessing files in cloud storage on the App Store. See screenshots and ratings, and read customer reviews.

Meryl Streep On Accessing The Characters Within - YouTube
Meryl Streep talks about the importance of an actors work representing their ability. CONNECT WITH AFI: http://facebook.com/AmericanFilmInstitute ...

Student pleads guilty to accessing records about Frances Abbott design scholarship
The Sydney student who leaked information about a fashion school scholarship controversially awarded to the daughter of the Prime Minister has ...

Accessing a headline opinion
Accessing a headline opinion

Sharp increase in authorities accessing private data
Australian law enforcement and government agencies have sharply increased their access without warrant to vast quantities of private telephone ...

Former librarian charged with accessing student records of Frances Abbott
A former part-time librarian at a Sydney design school has been charged after she allegedly accessed student records of Prime Minister Tony Abbott's ...

Frances Abbott scholarship: Sydney woman pleads guilty to accessing Whitehouse Institute records on PM's ...
A Sydney woman who leaked the student records of Tony Abbott's daughter pleads guilty. A Sydney woman who leaked the student records of Prime ...

Resources last updated: 3/24/2016 10:12:40 PM