How do I use VB to connect to a database in VS2005?

How do I use VB to connect to a database in VS2005?

• devdept

  One good place to look is: http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnvs05/html/NewDtaStVS05.asp
  
  It mentions one of my favorites features. I've cut part of the article out below:
  
  

  A simple code snippet will help illustrate these features. In Visual Studio 2002/2003, even using a typed DataSet, the code to execute a simple query with two parameters would be non-trivial—for the query

  SELECT FirstName, LastName from Employees WHERE Country = @country AND City = @city 

  we would have to write something like this:

  
  
  Me.SqlAdapter1.SelectCommand.Parameters ("@country").value = _ 
     Me.CountryListbox.SelectedValue.Trim() 
  Me.SqlAdapter1.SelectCommand.Parameters ("@city").value = _ 
     Me.CityTextbox.Text.Trim() 
  Me.SqlAdapter1.Fill(Me.NorthwindDataSet.Employees) 
   

  Of course, as the number of parameters increase, so does the number of lines of code. More significantly, the odds of correctly remembering and typing each of the parameter names decrease greatly. Even if I get the parameter name right, I then need to remember the data type of the parameter. The worst part of all this is that if I do incorrectly enter the field name or try to assign a value of the wrong type, I don't find out about it until run-time!

  With the TableAdapter in Visual Studio 2005, once I define the command FillByCountryAndCity, all I need to do to use it anywhere is to write one line of code, passing in the parameter values:
  

  
  
  Me.EmployeesTableAdapter.FillByCountryAndCity ( _ 
  Me.NorthwindDataSet.Employees, _
  Me.CountryListbox.SelectedValue.Trim(), _ 
  Me.CityTextbox.Text.Trim() )
  
   
   

  It is important to note that not only do we get multiple named commands from a single TableAdapter, but these commands are strongly-typed. This means that as we are writing code in Visual Studio, we get full IntelliSense to see these commands as methods of the TableAdapter. We also get compile-time type checking of the parameters to these commands, as well as the tool tip with the method and parameter type definitions to help us along. The TableAdapter can have multiple methods that execute different commands and accept different parameters.

  
  
  
  

• Frank ORourke

  Hi Alan,

  I use VS Express edition. I'm beginner with VB.

  Presently i'm using wizard and the items from the left menu to put it on forms.

  I could not see dataconnection or data adapter icons there. is it usual or is there anyother way to do it in 2005

  i cant even see those oledb connection and oledb adapters.

  Could u please give me a little brief on this.

  Thank you.

  
  
  

• Ashok_Roy

  I am also looking how to bind form object data into an Access query. So the ability to put a variable (form object) into the query.

  The following is pseudo code as I know you can't access the object variable within a query as I am attempting to here.

  Code Snippet

  SELECT ID, Program, Person, Address
  FROM Sheet1
  WHERE (Person = PersonBox.Text)

  Any thoughts

  Thanks

  
  

• killauea

  Thank you for your msg, But my problem is dataset generated using the datasource wizard, But I can't update or delete the record only insert is possible, then I opened the dataset designer that only contain the infrm. of insert query only, update and delete statement query is not inserted how do I insert it plz. explain briefly
  
  

• RobertPalmer

  here is an sample to code t execute a sql command
  what you need to do are,
  1. Add reference to System.data.dll
  2. Initialize connstr and cmdText
  3. include them in your code
  
  

  Dim conn As New System.Data.SqlClient.SqlConnection()
  conn.ConnectionString = connstr
  conn.Open()
  Dim cmd As New System.Data.SqlClient.SqlCommand(cmdText, conn)
  cmd.ExecuteNonQuery()
  conn.Close()

  
  
  

• Motris

  When I paste this into my project or a new one I get errors on the second line conn "declaration expected" the same with cmd on the 5th line. What am I missing here Thanks... PGP
  

• Hemant Kanoujiya_29

  Can someone pls tell me step by step how to connect my code with the database.Im using SQL Server 2000 as backend.

  If someone could explain it with the help of a simple example,i would be very thankful.

  
  
  

• mgattani

  well if u dont use

  imports system.data
  imports system.data.oledb (or sqlclient for sql server)

  of course u get an error

  an example for connecting to an access db;

  imports system.data
  imports system.data.oledb

  dim conn as new oledbconnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=data.mdb;")

  If TextBox1.Text <> "" Then
  Dim adp As New OleDbDataAdapter("Select * From tabloadi where MalzemeAdi='" + TextBox1.Text + "'", conn)
  

  Dim ds As New DataSet
  adp.Fill(ds)
  DataGridView1.DataSource = ds.Tables(0)
  End If
  
  

  u can use these code where u wanna use

  And this example is for sql server database

  Imports System.Data
  Imports System.Data.SqlClient
  
  
  Public Class Form1
  Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=aok;Trusted_Connection=True;")
  
  Dim ds As New DataSet
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  
  conn.Open()
  Dim adp As New SqlDataAdapter("Select * From personal", conn)
  
  
  adp.Fill(ds)
  
  DataGridView1.DataSource = ds.Tables(0)
  conn.Close()
  
  
  End Sub
  
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  
  
  conn.Open()
  
  Dim yeni As New SqlCommand("INSERT INTO personal(name,surname) VALUES ('ibrahim','ersoy')", conn)
  
  yeni.ExecuteNonQuery()
  
  
  End Sub
  
  
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  
  conn.Open()
  
  Dim sil As New SqlCommand("DELETE FROM personal where name='ibrahim' AND surname='ersoy'", conn)
  sil.ExecuteNonQuery()
  
  
  End Sub
  End Class
  
  

  thats all u can improve your skills on that

  Take care

  
  
  
• How do I use VB to connect to a database in VS2005?