Entry
ASP.NET: Visual Basic: Database: Microsoft: SQL server: How to connect to database? [SqlDataAdapter]
May 22nd, 2004 13:18
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 23 December 2003 - 00:14 am -------------------
ASP.NET: Visual Basic: Database: Microsoft: SQL server: How to connect
to database? [SqlDataAdapter]
---
Steps: Overview:
1. -Setup a database connection (Connection)
2. -Open the database connection (Connection.Open())
3. -Fill a dataset with the desired data
1. Do e.g. an SQL query (select * from MyProducts)
2. Execute the command (SqlDataReader)
4. -Read the data
1. Set up a dataview to display data
5. -Bind a server control to the dataview via data binding
6. -Close the connection (SqlDataReader.Close())
---
---
Steps: Worked out:
1. -Start a new Visual Basic Windows or ASP.NET application.
2. -Add a datagrid control to the form
(to display records from your database)
1. -on the left click on the vertical 'toolbox',
2. -click button 'Web Forms'
3. -double click button 'DataGrid'
3. Add an SqlDataAdapter control to the form
(to connect to your database)
1. -on the left click on the vertical 'Toolbox'
2. -click button 'Data'
3. -double click button 'SqlDataAdapter'
4. -This will start a 'Data Adapter Configuration'
wizard
5. -click button 'Next'
6. -Select an existing connection from the list,
otherwise click button 'New connection'
(if there should be a blank entry in
'Which data connection should the data adapter use')
1. -This will open a window 'Data Link properties'
2. -choose a (Microsoft SQL) server name
e.g. DELLI8500
You should supply the same server name as the SQL server that is
running (you can choose this name while
installing the
Microsoft SQL server database,
or ask the administrator of the computer
on which the Microsoft SQL server runs on the
network)
To see this, put the mouse on the taskbar over the Microsoft SQL
server icon, and there you see e.g.
\\DELLI8500 - MS SQL Server
(adapt this name to your conditions)
3. -enable radio button 'Use Windows NT Integrated security'
(or otherwise when enabling 'Use a specific user name and
password',
supply the login name + password, if you did choose this
option while setting up Microsoft SQL server)
4. -select the database on the server.
A typical demonstration database for
Microsoft SQL server is 'Northwind'
5. -possibly click button 'Test Connection' to test
(if successful, you see 'Test connection succeeded')
6. -click button 'OK'
7. -Select then this database in:
'Which data connection should the data adapter use' select
(e.g. DELLI8500.Northwind.dbo)
7. -accept e.g. the default enabled radio button
'Use SQL statements'
Other possible options here are:
o 'Create new stored procedures'
(=specify a 'Select' statement and the wizard
will generate new stored procedures to select,
insert, update and delete records)
o 'Use existing stored procedures'
(=choose an existing stored procedure for each
operation (select, insert, update and
delete)
8. -in window 'Data Adapter Configuration Wizard',
you can (for example) type the following text:
SELECT * FROM Products
9. -click button 'Next'
10. -click button 'Finish'
11. -you will now see that 2 items are present
under the 'Component1.vb [Design]' tab
1. 'SqlDataAdapter1'
2. 'SqlConnection1'
12. -possibly give the form focus,
1. -select from menu option 'View'
2. -select from list 'Designer'
13. -generate a dataset
1. -select from menu option 'Data'
2. -select from list 'Generate DataSet...'
(Note:
if you should select from the list 'View Dataset', and then click
button 'Fill Dataset', you can see the result of your query
(e.g. SELECT * FROM Products))
14. -a dialog box 'Generate Dataset' opens, and
asks to choose a 'DataSet'
1. I chose 'New'
2. I chose to leave the default name 'DataSet1'
3. I enabled checkbox 'Add this dataset to the designer'
4. click button 'OK'
15. -you will now see that totally 3 items are present
below
1. 'SqlConnection1'
2. 'SqlDataAdapter1'
3. 'DataSet11'
This 3 objects have the following function:
1. Connection object
This represents your database connection
2. Data Adapter object.
This uses a connection and a command to fill a DataSet with
Records
3. DataSet
This represents a set of records that were requested using a
command.
The command I chose was here
SELECT * FROM Products
which asks for all records (*) from the Products table.
To associate the set of records returned from our SQL query with
the 'DataGrid' control you have to set the DataSource property.
Establishing an association between a control and a database is
referred to as binding a control to data.
In the following steps you will have to bind the 'DataGrid'
control to the 'DataSet'.
16. -set the 'DataSource' property of the 'datagrid' control
1. -make sure the 'Properties' window is visible
1. -select from menu option 'View'
2. -select from list 'Properties Window'
2. -click once on the 'datagrid' control on the form
3. -In the 'Properties' window in the right pane:
1. set the 'DataSource' property to
1. If you are creating a Visual Basic windows application,
select:
DataSet11.Products
2. If you are creating a Visual Basic ASP.NET
application, select:
DataSet11
17. -create a connection and fill a dataset
1. -select from menu option 'View'
2. -select from list 'Designer'
3. -double click on the form to open the 'Code Editor' window,
focused on the Form1_Load event handler.
4. -type the following text:
' change possibly your select query here
' SqlDataAdapter1.SelectCommand.CommandText = "SELECT * FROM
Products"
' SqlDataAdapter1.SelectCommand.CommandText = "SELECT * FROM
Products WHERE ProductName = 'Aniseed Syrup'"
1. If you are creating a Visual Basic windows application,
type:
SqlDataAdapter1.Fill( Me.DataSet11 )
2. If you are creating a Visual Basic ASP.NET
application, select, type:
SqlDataAdapter1.Fill(Me.DataSet11)
DataGrid1.DataSource = DataSet11
DataGrid1.DataBind()
18. Run this application
1. -press <F5>
or
select from menu option 'Debug'
then select from list 'Start'
4. -now the 'DataGrid' control should display all the records from
the Products table
Navigation, modification, addition, and deletion of records are
possible with the DataGrid control.
---
---
Troubleshooting
1. If you get the error
'Login failed for user 'Machinename\ASPNET'
then a general solution is to add this
'Machinename\ASPNET' user to that
Microsoft SQL server database.
(so add e.g. a user to the database 'Northwind')
---
2. If you run the application and you get a blank screen:
Try one or more of the following:
1. Check if you did bind your datasource to the
datagrid control
1. -click on the datagrid control on the form
2. -in the window 'Properties' goto property
'datasource' and select from the list
'datasource'
1. If you are creating a Visual Basic windows
application, select:
DataSet11.Products
2. If you are creating a Visual Basic ASP.NET
application, select:
DataSet11
2. Try to check if your query produced any data
1. -select from menu option 'Data'
2. -select from list 'Preview data...'
3. -click button 'Fill Data'
4. -you should now see some data filled in
in your table
3. If creating for ASP.NET, check if you indeed typed the text:
DataGrid1.DataSource = DataSet11
DataGrid1.DataBind()
---
---
Book: see also:
[book: Utley, Craig - a programmer's introduction to Visual Basic.NET -
ISBN 0-672-32203-X - p. 106 'Accessing a database from a Windows
Application']
---
---
Internet: see also:
---
Database: Microsoft: SQL server: User: Add: How to add a user to a
database? [Northwind]
http://www.faqts.com/knowledge_base/view.phtml/aid/27359/fid/147
---
Database: Microsoft SQL server: Error 'Login failed for user '<your
computer name>\ASPNET'
http://www.faqts.com/knowledge_base/view.phtml/aid/27428/fid/1601
---
Building Visual Basic .net windows applications
http://media.wiley.com/product_data/excerpt/86/04714254/0471425486.pdf
---
Microsoft: .NET: Visual Studio: Visual Basic: How to create a Visual
Basic .net windows application?
http://www.faqts.com/knowledge_base/view.phtml/aid/27271/fid/176
---
Microsoft:SQLserver:[DBNETLIB][ConnectionOpen Connect()]SQL Server
does not exist or access denied
http://www.faqts.com/knowledge_base/view.phtml/aid/27281/fid/147
----------------------------------------------------------------------
