0

Can you please tell me what's wrong with the code I'm using? Everytime I execute this, it throws the exception (Failed to connect to database)

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim conn As New System.Data.OleDb.OleDbConnection()

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\socnet.accdb"

Dim sql As String = String.Format("INSERT INTO login     VALUES('{username}','{password}','{secques}','{secans}')", txt_username.Text, txt_passwd.Text, txt_secquestion.Text, txt_secansw.Text)

            Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

            'Open Database Connection
            sqlCom.Connection = conn
            conn.Open()
            Dim icount As Integer = sqlCom.ExecuteNonQuery
            MessageBox.Show(icount)
            MessageBox.Show("Successfully registered..", "Success", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch ex As Exception
            MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        cmd_submit2_Click(sender, e)

    End If
End Sub

I am using Access 2013 and VS 2015 Community, if that helps. Thank you.

rfovaleris
  • 23
  • 1
  • 1
  • 3
  • If it fails to connect to the database the error is in your connectionstring. Could you add it? Where do you initialize the global conn variable? – Steve Mar 02 '16 at 22:13
  • By the way, I forgot that to include that there is a connection string. Dim conn As New System.Data.OleDb.OleDbConnection() conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\socnet.accdb" – rfovaleris Mar 02 '16 at 22:13
  • Hi Steve, thanks for pointing that out. I have already updated my code. Still getting the same error. – rfovaleris Mar 02 '16 at 22:15
  • Using DataDirectory in a desktop app means that your database is in the BIN\DEBUG folder. However looking better at your code, that error message is useless because hides the real error. Please for the debug effort change it to MessageBox.Show(ex.Message) and tell us what is the real error raised in the exception – Steve Mar 02 '16 at 22:15
  • Thanks. Changed the catch block as suggested, the message says **Input string was not in a correct format**. – rfovaleris Mar 02 '16 at 22:18
  • 1
    Better learn to use parameters to avoid sql injection and formatting issues. Someone named O'Brien will fail your query. It's also best to include the field names. – LarsTech Mar 02 '16 at 22:19

1 Answers1

1

You should use a parameterized approach to your commands. A parameterized query removes the possibility of Sql Injection and you will not get errors if your string values are not correctly formatted.

Note that if you don't do anything in the exception block then it is better to remove it and let the exception show itself or at least show the Exception.Message value, so you are informed of the actual error.

Finally every disposable object should be created with the Using statement that ensures a proper close and dispose of such objects (in particular the OleDbConnection)

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim sql As String = "INSERT INTO login VALUES(@name, @pass, @sec, @sw)"
    Using conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\socnet.accdb")
    Using sqlCom = New System.Data.OleDb.OleDbCommand(sql, conn)
         conn.Open()
         sqlCom.Parameters.Add("@name", OleDbType.VarWChar).Value = txt_username.Text
         sqlCom.Parameters.Add("@pass", OleDbType.VarWChar).Value = txt_passwd.Text
         sqlCom.Parameters.Add("@sec", OleDbType.VarWChar).Value = txt_secquestion.Text
         sqlCom.Parameters.Add("@sw", OleDbType.VarWChar).Value =  txt_secansw.Text 
         Dim icount As Integer = sqlCom.ExecuteNonQuery
    End Using
    End Using
End Sub

Keep in mind that omitting the field names in the INSERT INTO statement requires that you provide values for every field present in the login table and in the exact order expected by the table (So it is better to insert the field names)

For example, if your table has only the 4 known fields:

Dim sql As String = "INSERT INTO login (username, userpass, secfield, secfieldsw) " & _ 
                    "VALUES(@name, @pass, @sec, @sw)"
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks! However using the code above gives me an **An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll: Number of query values and destination fields are not the same.** exception. – rfovaleris Mar 02 '16 at 22:36
  • As explained in the last part of the answer. If your table has 5 columns (or more) then an INSERT INTO with less values fails. You should add the missing values or declare which fields receives the values – Steve Mar 02 '16 at 22:40
  • Thanks. I am now aware of the error. My table has 5 columns, the first one being the primary key (an autonumbered "ID"). – rfovaleris Mar 02 '16 at 22:43
  • In this case the field names are mandatory except for the ID field – Steve Mar 02 '16 at 22:48
  • I tried using `"INSERT INTO login " + "(user_name, pass_word, secques, secans)" + "VALUES(@name, @pass, @sec, @sw)"` . It's not showing any error or exception, but my table remains blank. – rfovaleris Mar 02 '16 at 23:02
  • Are you checking the database located in the BIN\DEBUG folder? DataDirectory in connectionstring for desktop app points to that folder (or x86 variant) The insert happens in that database. – Steve Mar 02 '16 at 23:04
  • And be sure to not have your database file listed between your project files otherwise the property Copy to Output Directory could overwrite the database in the BIN\DEBUG with the empty database in your project folder – Steve Mar 02 '16 at 23:05
  • See http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 – Steve Mar 02 '16 at 23:06
  • Checked it, it's there. Thanks for all the help Steve! Really appreciate it. :) – rfovaleris Mar 02 '16 at 23:07
  • I changed the Data Source to a different location (C:/ in my case), and it did the trick. Thanks again. :) – rfovaleris Mar 02 '16 at 23:19