Cannot Open Database connection within a Funtion for DataReader RSS

• • Reply
  

  rayferns

  Member

  240 Points

  48 Posts

  Cannot Open Database connection within a Funtion for DataReader

  Nov 25, 2005 04:52 PM|LINK

  Hi Programmers,
  
  I need some help on Runtime Logical errors with VB.NET
  
  I am trying to Check whether a username exist within a database after user's entry to a textbox. The data should read from a database and check if the user exist, if exist then request the user to re-enter the username, if it does not exist, then insert the new user.
  
  Below is code function I have used, it might look messy/unstructured due to debugging.
  
  I would appreciate any help or suggestions.
  
  Thanks in Advance.
  
  CODE:
  
  Dim objConnection as OleDbconnection

       sub Page_load()

          Dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0;"
          strConnection += "Data Source=C:\Inetpub\wwwroot\flight.mdb"

  
          Dim objConnection as New OledbConnection(strConnection)

       End Sub
  
  Function CheckUsername(ByVal Uname As String) as Boolean

           Dim returnvalue as Boolean
           returnvalue = false
           Response.write("Debug Statement 1")
           Try
               ObjConnection.Open()

           Dim strSQL as string = "select name from passwords where name = '" & Uname & "'"
           Dim Cmd as New OLEDBCommand(strSQL,objConnection)

           Response.write("Debug Statement 2")
           Dim DR as OLEDBDataReader = Cmd.ExecuteReader()

           Do While DR.Read() = True
                   Dim aaa as String
                   aaa = DR("name").ToString
                   Response.write(" Uname=" + Uname + " aaa=" + aaa)
                   If DR("name").ToString = Uname  Then

  
                   MessageBox.Visible = true
                   MessageBox.style("color") = "Black"
                   MessageBox.Text = "UserName already exist, try again "

                   returnvalue = False

                   Else
                   returnvalue = True

  
                   End If

           Loop

               DR.Close()
               ObjConnection.Close()

               Catch ex As Exception
                       MessageBox.Text = "Error Connecting to Database during checking username!"
               End Try

               return returnvalue

    End Function

   

       Sub submitme(sender As Object, e As EventArgs)

               ' Trims any spaces within the textbox strings
               Dim usernm as String = username.text.Trim()
               Dim passwrd as String = passwd.text.Trim()
               Dim emailId as String = email.text.Trim()

  
           If  (Page.IsValid) Then

               MessageBox.visible = true
               MessageBox.style("color") = "Red"
               MessageBox.Text = "Some of the required fields are empty"

           End If

              ' Check if user exist
              if (CheckUsername (Usernm)=false)
               MessageBox.Text = "UserName already exist, try again "
              End If

              ' Add user
              AddNewUser(usernm, passwrd, emailId)

              MessageBox.visible = true
              MessageBox.style("color") = "Black"
              MessageBox.Text = "You have been registered successfully"

       End sub

  
     private  sub AddNewUser(ByVal strUser As String, ByVal strPass As String, ByVal strEmail As String)

   

          Dim InsertCmd as String = "INSERT INTO passwords (name, email, [password]) values('" & _
                strUser    & "','" & _
                strEmail  & "','" & _
                strPass & "')"

          'Dim CheckUser as string = "SELECT name FROM passwords WHERE (name = '" & strUser & "')"

          Try

               objConnection.Open()

               Dim objAdapter as new oleDbDataAdapter(InsertCmd, objConnection)
               Dim DS as DataSet = New DataSet()

               ' Fill in new username inside the passwords table
               objAdapter.Fill(DS, "passwords")

               objConnection.Close()

  
         Catch ex As Exception
              MessageBox.Text = "Error Connecting to Database!"
         End Try

       End Sub
  
  Any ideas what is missing or have I added to much code? Please help, I have been trying this for so long.

  Ray