none
Get data from database

    Question

  • i want to have a button and textbox on my form and when i type in a username and click the button i want a lable to show what the password is for that username.
    Monday, November 27, 2006 9:53 PM

Answers

  • there are many ways of doing this. The best way in your case would be to use a DataReader. Which database are you using?

    for ease, lets use SQL, you can change it to OleDb if you are using MS Access for example.

    you need:

  • connection to database

  • Select command

  • read results returned executing the Select command

     

    something like this....

    import the System.Data.SqlClient namespace then...



  • private function DoGetPassword(byval username as String) as string
     
    Dim theSqlCommand as new SqlCommand("SELECT [password] FROM [TableName] WHERE [username] = @p1", new SqlConnection(ConnectionString))
     
    Dim theParameter as new SqlParameter("@p1", username)
    theSqlCommand.Parameters.Add(theParameter)
    Dim thePassword as String = String.Empty
     
    try
     
       theSqlCommand.Connection.Open()
       Dim reader as SqlDataReader = theSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
     
       if reader.HasRows = true then
     
          reader.Read()
          thePassword = reader(0).ToString()
     
       else
     
          thePassword = "no username found"
     
       end if
     
       theSqlCommand.Connection.Close()
     
    catch ex as SqlException
       'handle exception
       thePassword = "There was an error"
    end try
     
    return thePassword
     
    end function
     
     
    private sub button1_click(byval sender as object, byval e as EventArgs) handles button1.Click
     
       Me.theLabel.Text = Me.DoGetPassword("MyUserName")
     
    end sub

     

     

    this should execute the query and get you back the result and place it in the label. Does this work for you? of course you need to supply the connectionstring and the correct table/fieldnames.

Monday, November 27, 2006 10:00 PM
  • many ways of doing so. There are a good few examples on how to add data to a database. one would be to fill a dataset, bind it to a datasource/bindable UI component, then call the update method of the DataAdapter or table adapter.

    the other would be the "raw" manual approach.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=753872&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=728535&SiteID=1

     

    Tuesday, November 28, 2006 1:04 AM
  • the connectionstring would be typically like this:

    "Data Source=.;Initial Catalog=DatabaseName;Trusted_Connection=true;"

     

    so...

     

    New SqlConnection("Data Source=.;Initial Catalog=DatabaseName;Trusted_Connection=true;"))

     

    if you are using SQL Express then change the data source to:

    "Data Source=.\SQLExpress..........."

    www.connectionstrings.com has all the connectionstrings for your needs. the connection string is basically a string that contains details on how/where to connect to to access your database.

    Sunday, December 03, 2006 11:06 PM
  • All replies

    • there are many ways of doing this. The best way in your case would be to use a DataReader. Which database are you using?

      for ease, lets use SQL, you can change it to OleDb if you are using MS Access for example.

      you need:

    • connection to database

    • Select command

    • read results returned executing the Select command

       

      something like this....

      import the System.Data.SqlClient namespace then...



    • private function DoGetPassword(byval username as String) as string
       
      Dim theSqlCommand as new SqlCommand("SELECT [password] FROM [TableName] WHERE [username] = @p1", new SqlConnection(ConnectionString))
       
      Dim theParameter as new SqlParameter("@p1", username)
      theSqlCommand.Parameters.Add(theParameter)
      Dim thePassword as String = String.Empty
       
      try
       
         theSqlCommand.Connection.Open()
         Dim reader as SqlDataReader = theSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
       
         if reader.HasRows = true then
       
            reader.Read()
            thePassword = reader(0).ToString()
       
         else
       
            thePassword = "no username found"
       
         end if
       
         theSqlCommand.Connection.Close()
       
      catch ex as SqlException
         'handle exception
         thePassword = "There was an error"
      end try
       
      return thePassword
       
      end function
       
       
      private sub button1_click(byval sender as object, byval e as EventArgs) handles button1.Click
       
         Me.theLabel.Text = Me.DoGetPassword("MyUserName")
       
      end sub

       

       

      this should execute the query and get you back the result and place it in the label. Does this work for you? of course you need to supply the connectionstring and the correct table/fieldnames.

    Monday, November 27, 2006 10:00 PM
  • OK so how do i add data
    Tuesday, November 28, 2006 1:01 AM
  • And i want to do this in Visual Basic Express 2005. Using the SQL server with it.
    Tuesday, November 28, 2006 1:03 AM
  • many ways of doing so. There are a good few examples on how to add data to a database. one would be to fill a dataset, bind it to a datasource/bindable UI component, then call the update method of the DataAdapter or table adapter.

    the other would be the "raw" manual approach.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=753872&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=728535&SiteID=1

     

    Tuesday, November 28, 2006 1:04 AM
  • With the code you gave me it comes up with a blue underline under the reader and says that it is not declared??

     

    Why is this and what do i have to do to get it to work???

    Sunday, December 03, 2006 7:56 AM
  • not sure. Did you import the System.Data.SqlClient namespace? as well as this you did declare reader right?

     Dim reader as SqlDataReader = theSqlCommand.ExecuteReader(CommandBehavior.CloseConnection) 

    Sunday, December 03, 2006 2:31 PM
  • now where this code is it has a blue line under ConnectionString

    New SqlConnection(ConnectionString))

    Sunday, December 03, 2006 10:47 PM
  • the connectionstring would be typically like this:

    "Data Source=.;Initial Catalog=DatabaseName;Trusted_Connection=true;"

     

    so...

     

    New SqlConnection("Data Source=.;Initial Catalog=DatabaseName;Trusted_Connection=true;"))

     

    if you are using SQL Express then change the data source to:

    "Data Source=.\SQLExpress..........."

    www.connectionstrings.com has all the connectionstrings for your needs. the connection string is basically a string that contains details on how/where to connect to to access your database.

    Sunday, December 03, 2006 11:06 PM
  • Nice example.  While I'm a bit more familiar with databasing and code than OP, I'm relatively new to .Net.  I prefer hard coding record sets and connections, like your example, to relying on drag and drop data objects.
    Monday, December 04, 2006 10:33 PM