• 0

Database access


Question

I have a school project that requires me to write a VB.NET program to access a database.

I have written VB programs before but not using SQL commands and database connection before.

Could someone please help me get started?

Do I need any other programs for connective purposes between VB and the database system? What keywords or libraries am I suppose to pay attention or use. I have a beginner's VB book but I dont know what to look for. I just want to keep this project as simple as possible so nothing flashy is needed.

Thanks

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0

.NET actually makes connecting to a DB quite painless. Just look under tools, and there's also a bar called server explorer. You can connect to a database through these and even make some queries through this. All automated, no knowledge of SQL really needed.

Link to comment
Share on other sites

  • 0

.NET makes data access pretty simple. I mostly use the SqlClient and Odbc namespace (their objects and constructs are very similar and easily interchangable). SqlClient is best for connecting to an MS SQL Server/MSDE database and Odbc can be used to connect to various other Odbc data sources (MS Access, dBase, excel spreadsheets, etc). I'll give you a quick example of both from which you should be able to figure the rest out.

SqlClient

Dim SqlAdapter1 As New SqlClient.SqlDataAdapter("SELECT * FROM Table1", New SqlClient.SqlConnection("server='[server]'; user id='[username]'; password='[password]'; database='[database name]'; packet size=4096;initial catalog=[database name]"))
Dim SqlDataSet1 As New Data.DataSet
SqlAdapter1.SelectCommand.Connection.Open()
SqlAdapter1.Fill(SqlDataSet1)
SqlAdapter1.SelectCommand.Connection.Close()
DataGrid1.DataSource = SqlDataSet1
DataGrid1.DataBind()

This will fill an ASP.NET DataGrid webcontrol (DataGrid1) with everything from the Table1 table.

Odbc

Dim OdbcAdapter1 As New Odbc.OdbcDataAdapter("SELECT F2, F3, F5 FROM ['spreadsheet 1$']", New Odbc.OdbcConnection("MaxBufferSize=2048;DSN=Excel Files;PageTimeout=5;DefaultDir=[path to excel file];DBQ=[path and filename of excel file];DriverId=790"))
Dim x as Integer
Dim Spreadsheet1 As New Data.DataSet
OdbcAdapter1.SelectCommand.Connection.Open()
OdbcAdapter1.Fill(Spreadsheet1)
OdbcAdapter1.SelectCommand.Connection.Close()
For x = 0 To Spreadsheet1.Tables(0).Rows.Count - 1
 ? ? ?cboF2.Items.Add(Spreadsheet1.Tables(0).Rows(x).Item("F2"))
 ? ? ?cboF3.Items.Add(Spreadsheet1.Tables(0).Rows(x).Item("F3"))
 ? ? ?cboF5.Items.Add(Spreadsheet1.Tables(0).Rows(x).Item("F5"))
Next

This will fill combo boxes cboF2, cboF3, and cboF5 with the repsective column from the excel file specified by DBQ=. There's a more efficient way of doing this that involves databinding but you should only look into that once you get a firm grasp on the basics.

The objects you'll be using most are SqlDataAdapter/OdbcDataAdapter, SqlConnection/OdbcConnection, SqlCommand/OdbcCommand, and Data.DataSet. A DataAdapter is used to fill a DataSet with data that is taken from a Command which has a Connection specified for it. Before using a DataAdapter or Command you always have to open the Connection first and the close it again afterwards. You can also easily create a DataAdapter by dragging one onto a form from the designer toolbox (under Data). It'll give you a nice wizard to select your datasource and it will automatically generate the connection and command that it needs. This will spare you from having to write your own DSN's (connection strings). You also don't need to use DataSets all the time, for example you can use a Command's ExecuteScalar method to retrieve the value of a query that returns a single value and there are other methods for running queries that don't return anything.

Link to comment
Share on other sites

  • 0
.NET actually makes connecting to a DB quite painless.  Just look under tools, and there's also a bar called server explorer.  You can connect to a database through these and even make some queries through this.  All automated, no knowledge of SQL really needed.

584807192[/snapback]

That's not a good way because it doesn't teach them how to actually do it. Here's some sample code (note: assumes you're using SQL server).

Dim connection as SqlConnection = new SqlConnection("your_connection_string")

' For selecting data: the easiest way
Dim ds as Dataset = new Dataset
Dim myCommand as SqlDataAdapter = new SqlDataAdapter("SELECT * FROM WHATEVER", connection)
myCommand.Fill(ds, "MyTableName")
ds.Tables("MyTableName").GetValue(columnindex)

' For selecting data: the efficient way:
Dim myCommand as SqlCommand = new SqlCommand("SELECT * FROM WHEREVER", connection)
connection.Open()
Dim reader as SqlDataReader = myCommand.ExecuteReader()
While reader.Read()
   reader.GetValue("columnName")
End While
reader.Close()
connection.Close()

' For inserting / updating / deleting data
Dim myCommand as SqlCommand = new SqlCommand("DELETE FROM WHATEVER", connection)
connection.Open()
myCommand.ExecuteNonQuery()
connectionClose()

I'm not sure if that code will work. But it's a good way to get started. Look up on MSDN if you have more questions. :p

Link to comment
Share on other sites

  • 0

As for what kind of server I am connecting to, I dont know. All I know is that it is stored on a sun system and that I can access it using command prompt. I think I am connecting using SQL Plus since that is what I have to type to access the database.

Thanks for all the help. Let me see how far I can get with the tips here.

Link to comment
Share on other sites

  • 0
As for what kind of server I am connecting to, I dont know. All I know is that it is stored on a sun system and that I can access it using command prompt. I think I am connecting using SQL Plus since that is what I have to type to access the database.

Thanks for all the help. Let me see how far I can get with the tips here.

584811739[/snapback]

You're going to have to find out a little more information. Once you find out what kind of DB it is, get back to us and we can make the appropriate code changes.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.