Guys,

Please help me with the following code. I am trying to connect with MS Access database to save records. I am using Visual Studio 2008. I have typed the following code to save a record. Kindly help me by telling where I am wrong.

**********
Imports System.Data.OleDb

Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = Cocuments and SettingsAdministratorDesktopNew_Database1.mdb")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("select firstname, lastname, designation, age from IO", cn)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
TextBox4.Text = dr(3)
' loading data into TextBoxes by column index
End While
Catch
End Try
dr.Close()
cn.Close()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:New_Database1.mdb")
cn.Open()
str = "insert into IO values(" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text &"')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
'displays number of records inserted
Catch
End Try
cn.Close()
End Sub
End Class
**********

I don't know anything about VB, but it's a terrible practice in any language to do this:



Eventually your code will be hard to maintain. There are better and secure ways of dealing with that problem (I don't know if they exist in VB, but looking for them worth a shot).

For example in Haskell you can do this:



I want you to focus on the lines 8 and 9. There you can see a call to a function called "run". You give to that function the Database Handler (dbh), the SQL statement with question marks where you want the data to be inserted, and a list with the data you want to insert. This way the SQL statements are much clearer, and maintaining this code it's a lot easier for the reader.

Another example, this time in Java:



As you can see in line 2, the SQL statement and the question mark. The line 3 just replace the question mark for the formField.

And now that you know the idea, this is a Perl example:



These approaches are SQL injection proof. So the resultant code is maintainable and secure: The dream of every software developer.

That's just an advice.

BTW, for the people that read your posts would be clearer if you post your code between the code tags, that way we'll see the indentation in your code and no smileys in it. =D

I hope this help.

P.S: I know some of you are allergic to functional programming. Sorry for the inconveniences.