Adding new rows to a database using forms (newbie)

[MagicAardvark]

I am totally new to Visual Basic and usig access however do have past experiance of Java so not a total noob when it comes to programming.

Anyway my problem.

I wish to use a form ive created called 'AddClient' to add records to the database. My form has 11 fields and then a button which when clicked should write the contents of the text boxes to the database called 'ClientTable'

I have no clue how to start this really and would appreciate i somebody could help me out. I find I can program fine once its started but need the basics to see what im doing.

Would somebody be able to generate the code for me.

Many Thanks
Kyle

 

[kulrom]

Would somebody be able to generate the code for me.

Many Thanks
Kyle

        Dim connection As New OleDb.OleDbConnection("connectionstringhere")
        ' declare a command object
        Dim command As OleDb.OleDbCommand = connection.CreateCommand
        ' set the command text (insert query)
        command.CommandText = "INSERT INTO ClientTable(FirstName, LastName, UserName, Password, etc.) VALUES(?, ?, ?, ?, etc.)"
        command.CommandType = CommandType.Text
        ' add the parameters
        command.Parameters.AddWithValue("FirstName", FirstNameTextBox.Text).OleDbType = OleDb.OleDbType.VarChar
        command.Parameters.AddWithValue("LastName", LastNameTextBox.Text).OleDbType = OleDb.OleDbType.VarChar
        command.Parameters.AddWithValue("UserName", UserNameTextBox.Text).OleDbType = OleDb.OleDbType.VarChar
        command.Parameters.AddWithValue("Password", PasswordTextBox.Text).OleDbType = OleDb.OleDbType.VarChar
        ' etc.
        Try
            connection.Open()
            command.ExecuteNonQuery()
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            connection.Close()
        End Try

As per the connection string; please refer to the following links
Access Connection String Samples - ConnectionStrings.com
Access 2007 Connection String Samples - ConnectionStrings.com

 

[MagicAardvark]

Thanks for that but im still a bit confused. Which string do I use for Access 2007 and does it go between the brackets in the first line of the code you posted?

Is there away of also not declaring the full path of the database so that it could be transfered to different folders without changing the code?

 

[MagicAardvark]

This is what I have made of the code you posted.

Private Sub AddClient_Click()
Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\p2w.accdb;Persist Security Info=False;")
' declare a command object
Dim command As OleDb.OleDbCommand = connection.CreateCommand
' set the command text (insert query)
command.CommandText = "INSERT INTO ClientTable(Surname, First Name, Aims No, SO, VO, Drug of Use, Start Date, Area, End Date, Outcome) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
command.CommandType = CommandType.Text
' add the parameters
command.Parameters.AddWithValue("Surname", SurnameText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("First Name", FirstNameText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("Aims No", AimsNoText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("SO", SOText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("VO", VOText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("Drug of Use", DrugText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("Start Date", StartText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("Area", AreaText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("End Date", EndText.Text).OleDbType = OleDb.OleDbType.VarChar
command.Parameters.AddWithValue("Outcome", OutcomeText.Text).OleDbType = OleDb.OleDbType.VarChar
' etc.
Try
connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
Console.Write (ex.Message)
Finally
connection.Close()
End Try
End Sub

And i get an error

expected end of statement

any ideas of whative done wrong?

 

[kulrom]

Just add the square brackets around the fields that contains an empty space in the name. e.g.

command.CommandText = "INSERT INTO ClientTable(Surname, [First Name], [Aims No], SO, VO, [Drug of Use], [Start Date], Area, [End Date], Outcome) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

Also remove the spaces in the parameter names and try again

 

[Md Haris]

how the code willbe ....if its from a datagridview .i mean the code to add records in to a databse from a datagrid having more than one row ...
--thanks...