Insert record into access Database using VB.net

Question

Animal Mother 8 Light Poster

14 Years Ago

Hi I would like to know if anybody could help me insert records into a MS access database
with VB.net, I'm actually a c# programmer and I have never worked with databases before, I got as far as connecting but as soon as I want to add a record I get a "No value given for one or more required parameters" error. Here is the Code.

Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim tiepe As String = ""
        If radAgent.Checked = True Then
            tiepe = "Agent"
        End If
        If radManager.Checked = True Then
            tiepe = "Manager"
        End If
        If radMandate.Checked = True Then
            tiepe = "Mandate Holder"
        End If
        If radTeller.Checked = True Then
            tiepe = "Teller"
        End If
        Try
            dbInsert.CommandText = "INSERT INTO Access (NameSurname, ABNumber, SleutelWoord, Tiepe) VALUES (txtName.Text, txtUser.Text, txtPassword.Text, tiepe);"
            dbInsert.CommandType = CommandType.Text
            dbInsert.Connection = dbConnect
            dbInsert.ExecuteNonQuery()
            MessageBox.Show("Access created Succesfully for " + Line + txtName.Text)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

vb.net

4 Contributors
13 Replies
7K Views
3 Years Discussion Span
Latest Post 11 Years Ago Latest Post by dnis30

JRitchie777 0 Newbie Poster

14 Years Ago

You have to check at least two things with what you have shown:
1) Do your number and type of variables match your database table?
2) Do you really have values in the txtName, txtUser, and txtPassword fields - remember to declare and initialize variables for use with database query strings. Just good practice.

If that does not solve it show us the table fields and types as well as where you are populating the above mentioned variables.

Good Luck
JR

JRitchie777 0 Newbie Poster

14 Years Ago

Assuming you are entering unique records or your table allows for dups, then you are executing a straight query"
"INSERT INTO [MyTable] VALUES (txtName.Text, txtUser.Text, txtPassword.Text, tiepe);"

The way you have it written now is a parameter query to be used with stored procedures.
JR

JRitchie777 0 Newbie Poster

14 Years Ago

Is this a saved query in Access (i.e. Stored procedure)? That error message leads me to believe that you have declared parameters with the query when you created it in Access, those parameters have to be declared in the query command. Also, is "Access" the name of the table, I thought that was a reserved word in MS Access.
JR

JRitchie777 0 Newbie Poster

14 Years Ago

Follow this syntax for each of your Parameters and Values.

dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "NameSurname"
dbInsert.Parameters.Item("NameSurname").Value = txtName.Text

Good luck.
JR

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Animal Mother 8 Light Poster · Answer 1 · 2010-08-17T13:29:45+00:00

Thanx for the fast reply.
upon receiving this error the values were the first thing I checked. all text boxes have been assigned values. Also all the Database fields has been set for text data.
Isn't there something wrong with my dbinsert.CommandText syntax??

Animal Mother 8 Light Poster · Answer 2 · 2010-08-17T13:31:41+00:00

Thanx for the fast reply.
upon receiving this error the values were the first thing I checked. all text boxes have been assigned values. Also all the Database fields has been set for text data.
Isn't there something wrong with my dbinsert.CommandText syntax??

Animal Mother 8 Light Poster · Answer 3 · 2010-08-17T14:31:44+00:00

Here is the code again nothing seems to work, I made the changes that you suggested but I still get the same error. This is the full code from a - z.

Imports System.Data.OleDb
Public Class form_AccessMaintenance
    Dim dbInsert As New OleDb.OleDbCommand
    Dim dbConnect As New OleDb.OleDbConnection
    Dim Line As String = Environment.NewLine
    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
        Me.Close()
        dbConnect.Close()
    End Sub
    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim tiepe As String = ""
        If radAgent.Checked = True Then
            tiepe = "Agent"
        End If
        If radManager.Checked = True Then
            tiepe = "Manager"
        End If
        If radMandate.Checked = True Then
            tiepe = "Mandate Holder"
        End If
        If radTeller.Checked = True Then
            tiepe = "Teller"
        End If
        Try
            dbInsert.CommandText = "INSERT INTO Access VALUES (txtName.Text, txtUser.Text, txtPassword.Text, tiepe);"
            dbInsert.CommandType = CommandType.Text
            dbInsert.Connection = dbConnect
            dbInsert.ExecuteNonQuery()
            MessageBox.Show("Access created Succesfully for " + Line + txtName.Text)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        

    End Sub
    Private Sub form_AccessMaintenance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dbConnect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Database\WorkWatchMain.mdb"
            dbConnect.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message + Line + "Main Database Not Found" + Line + "Check form_AccessMaintenance source code" + Line + "Database Path", "Critical Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Close()
        End Try
    End Sub
End Class

Animal Mother 8 Light Poster · Answer 4 · 2010-08-17T15:13:33+00:00

Thank You very very very Much it is now working 100%

Here is the final code.

Imports System.Data.OleDb
Public Class form_AccessMaintenance
    Dim dbInsert As New OleDb.OleDbCommand
    Dim dbConnect As New OleDb.OleDbConnection
    Dim Line As String = Environment.NewLine
    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
        Me.Close()
        dbConnect.Close()
    End Sub
    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim tiepe As String = ""
        If radAgent.Checked = True Then
            tiepe = "Agent"
        End If
        If radManager.Checked = True Then
            tiepe = "Manager"
        End If
        If radMandate.Checked = True Then
            tiepe = "Mandate Holder"
        End If
        If radTeller.Checked = True Then
            tiepe = "Teller"
        End If
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "NameSurname"
        dbInsert.Parameters.Item("NameSurname").Value = txtName.Text
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Sleutel"
        dbInsert.Parameters.Item("Sleutel").Value = txtPassword.Text
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Abnum"
        dbInsert.Parameters.Item("Abnum").Value = txtUser.Text
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Job"
        dbInsert.Parameters.Item("Job").Value = tiepe
        Try
            dbInsert.CommandText = "INSERT INTO Table_Login VALUES (txtName.Text, txtUser.Text, txtPassword.Text, tiepe);"
            dbInsert.CommandType = CommandType.Text
            dbInsert.Connection = dbConnect
            dbInsert.ExecuteNonQuery()
            MessageBox.Show("Access created Succesfully for " + Line + txtName.Text)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        

    End Sub
    Private Sub form_AccessMaintenance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dbConnect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Database\WorkWatchMain.mdb"
            dbConnect.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message + Line + "Main Database Not Found" + Line + "Check form_AccessMaintenance source code" + Line + "Database Path", "Critical Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Close()
        End Try
    End Sub
End Class

JRitchie777 0 Newbie Poster · Answer 5 · 2010-08-18T03:33:04+00:00

Great, nice job. I was looking for this last night but since I was very tired I could not find it. Here is an example sub that will build your parameter list for you, if you ever find the need to handle multiple stored procedures -- it can get a bit tiresome to write out all those "Parameter" lines...

'Code:
Public Sub BuildParameter(ByRef p_oCmd As ADODB.Command, _
    ByVal p_strStoredProcParamName As String, _
    Optional ByVal p_DataType As ADODB.DataTypeEnum = ADODB.adCmdUnknown,
_
    Optional ByVal p_ParamDirection As ADODB.ParameterDirectionEnum =
ADODB.adParamUnknown, _
    Optional ByVal p_lngStringSize As Long, _
    Optional ByVal p_varValue As Variant)

On Error Goto ErrHandler

    With p_oCmd
        ' Gets the return value of the stored procedure.
        If p_ParamDirection = adParamReturnValue Then
            .Parameters.Append .CreateParameter(p_strStoredProcParamName,
_
            p_DataType, adParamReturnValue)
        ElseIf p_lngStringSize = 0 Then
            ' This applies for all data types exept for VarChar or Char
types
            .Parameters.Append .CreateParameter(p_strStoredProcParamName,
_
                p_DataType, p_ParamDirection, , p_varValue)
        Else
            .Parameters.Append .CreateParameter(p_strStoredProcParamName,
_
                p_DataType, p_ParamDirection, p_lngStringSize, p_varValue)
        End If
    End With

ErrHandler:
  If Err.Number <> 0 Then
      MsgBox "Error #: " Err. Number & _
      vbCrLF & _
      "Description: " & Err.Description, vbOkOnly, "BuildParameter"
  End If
End Sub

Enjoy!
JR

Animal Mother 8 Light Poster · Answer 6 · 2010-08-18T11:32:52+00:00

That is a great Sub thanx, I see you already replied 7 hours ago sorry JR I'm in South Afria and it's only 7:30am now. But thanx allot for all your help. How do I vote for you to increase your reputation on DANIWEB.

JRitchie777 0 Newbie Poster · Answer 7 · 2010-08-18T12:08:22+00:00

Cool, programmers from around the world unite! I'm just glad I could help.
Regards,
JR

Sagar_123 0 Newbie Poster · Answer 8 · 2012-07-11T18:41:34+00:00

Thanks to All dear frnds i was also having same problem, but this forum discusstion solve my problem

dnis30 0 Newbie Poster · Answer 9 · 2013-10-02T06:38:20+00:00

thanks sir... im happy that your codes are easy to understand :) stay blessed