Community discussion forum

oledb Exception occurs

• • Reply

  6 months ago

  by JosephNathan

  Joseph Nathan

  , South Africa

  Joined 9 months ago

   i hav got a oledb error each time while inserting or updating but select statements are perfectly working pls ny1 can solv my probl..
  <vb>

  Imports System.Data.OleDb

  Public Class newlogin

       Public Sub clearfields()
          txtname.Text = ""
          txtrole.Text = ""
          txtgender.Text = ""
          txtedu.Text = ""
          txtemail.Text = ""
          txtuser.Text = ""
          txtpass.Text = ""
          txtcourse.Text = ""
      End Sub

  
      Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
          Dim con As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Database.mdb")
          Dim errora As Integer = 0
          Dim query As String
          If con.State = ConnectionState.Open Then con.Close()
          Try

              query = "insert into login(fullname,role,gender,education,email,username,password,course) values( '" & txtname.Text & "' , '" & txtrole.Text & "' ,  '" & txtgender.Text & "' , '" & txtedu.Text & "' , '" & txtemail.Text & "' , '" & txtuser.Text & "' , '" & txtpass.Text & "' , '" & txtcourse.Text & "' )"
              Dim cmda As New OleDbCommand(query, con)
              con.Open()
              cmda.ExecuteNonQuery()
              con.Close()
          Catch exo As OleDbException
              MsgBox(exo.Message, MsgBoxStyle.Critical, "Oledb error")
          Catch ex As Exception
              MsgBox(ex.Message, MsgBoxStyle.Information, "General error")
              errora = 1
          End Try
          If Not errora = 1 Then
              MsgBox("Contact Added")
              clearfields()

  
          End If
          con.Close()
      End Sub

        Private Sub reset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
          clearfields()
      End Sub
  End Class

  </vb>

  Report abuse
• Advertisement

  Simply the fastest line-level profiler for .NET ever

  “The low overhead means it has minimal impact on the execution of my program”
  Mark Everest, Development Team Leader, Renault F1 Team Ltd.

  Try out the new ANTS Profiler 4 for yourself. Download your 14-day trial now

• • Reply

  6 months ago

  by JosephNathan

  Joseph Nathan

  , South Africa

  Joined 9 months ago

  the connection string is also declared publically in the module

  Report abuse
• • Reply

  6 months ago

  by prabathjayatissa

  prabath jayatissa

  , Sri Lanka

  Joined 11 months ago

  1. Which version of access are you using?

  2. What's the exact error message?

  3. Could you post the precise sql string that gets sent to the db?

  Report abuse
• • Reply

  6 months ago

  by TimL

  Tim Leung

  Berkshire, United Kingdom

  Joined 1 year ago

  It might be because you've called your table login and login is a reserved keyword. I'd try the following syntax change and see if it makes a difference.

   query = "insert into [login](fullname,role,gender,education,email,username,password,course) values( '" & txtname.Text & "' , '" & txtrole.Text & "' ,  '" & txtgender.Text & "' , '" & txtedu.Text & "' , '" & txtemail.Text & "' , '" & txtuser.Text & "' , '" & txtpass.Text & "' , '" & txtcourse.Text & "' )"
              

   

  Report abuse
• • Reply

  6 months ago

  by JosephNathan

  Joseph Nathan

  , South Africa

  Joined 9 months ago

  The version of Access i hav is MIcrosoft Access 2000 and the version of VB.Net i m working on is Microsoft Visual Studio 2005...

  i hav put a try and catch block in my coding so when i run my application and try to insert the data in to the login form a run time error will occur and catch stat of my coding displays a msgbox titled Oledb error......and shows  ex.message: "Syntax error in INSERT INTO statement".>>>>

  and then displays another msgbox("Contact Added")

  query = "insert into login(fullname,role,gender,education,email,username,password,course) values( '" & txtname.Text & "' , '" & txtrole.Text & "' ,  '" & txtgender.Text & "' , '" & txtedu.Text & "' , '" & txtemail.Text & "' , '" & txtuser.Text & "' , '" & txtpass.Text & "' , '" & txtcourse.Text & "' )"
  

  the syntax is working if i set this query into Access.

  i can't find my probl soln...

  pls help me....

  

  Report abuse
• • Reply

  6 months ago

  by JosephNathan

  Joseph Nathan

  , South Africa

  Joined 9 months ago

  should i change the name of my table login to newlogin....

  if i do so then what other things i should make change in......

  Report abuse
• • Reply

  6 months ago

  by prabathjayatissa

  prabath jayatissa

  , Sri Lanka

  Joined 11 months ago

  Try changing the name and if that doesn't work post the actual string that gets sent to access (as in insert a breakpoint at

  Dim cmda As New OleDbCommand(query, con)

  and hover the mouse over 'query') I assume all the fields in access are Text? What's their max length?

  Report abuse
• • Reply

  6 months ago

  by satyasahu

  satya narayana sahu

  , India

  Joined 1 year ago

  Because fields like password is a keyword so it can be enclosed within square bracket [] as follow. also enclose the role and login to avoid error.

  query = "insert into [login](fullname,[role],gender,education,email,username,[password],course) values( '" & txtname.Text & "' , '" & txtrole.Text & "' ,  '" & txtgender.Text & "' , '" & txtedu.Text & "' , '" & txtemail.Text & "' , '" & txtuser.Text & "' , '" & txtpass.Text & "' , '" & txtcourse.Text & "' )"
  

  Report abuse
• • Reply

  6 months ago

  by JosephNathan

  Joseph Nathan

  , South Africa

  Joined 9 months ago

  thanks to all the developers..... my probl has been solved

  Thanks once again

  Report abuse