• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Question Trying to use the INSERT INTO Query Statement

JimRyanVB

Member
Joined
Apr 23, 2019
Messages
6
Location
Nashville TN
Programming Experience
10+
I can open the database and extract all information to my form.
But when I enter new information into the same form and click Add
which simply runs the AddUser Sub I get the following message below
"The field is to small to accept the amount of data you attempted to add.
Try inserting or pasting less data."

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

*******************************************
(CUSTOMER.MDB MY DATABASE FOR PROJECT)
*******************************************
RowName - Type - Length
-------------------------------
Recno - AutoNumber - Integer
CustNo - Text - 7
LastName - Text - 15
FirstName - Text - 15
Address - Text - 30
City - Text - 30
State - Text - 2
Zip - Text - 10
Hphone - Text - 14

*******************************************
(A CLASS TO OPEN, QUERY, CLOSE Customer.mdb)
*******************************************
VB.NET:
Imports System.Data.OleDb

Public Class dbControl
    ' Create Your DB Connection
    'Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Customer.mdb;")
    Private DBCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;")

    ' Prepare DB Command
    Private DbCmd As OleDbCommand

    ' DB Data
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable

    ' Query Params
    Public Params As New List(Of OleDbParameter)

    ' Query Stats
    Public RecordCount As Integer
    Public Exception As String

    ' Query SQL Command
    Public Sub ExecQuery(Query As String)
        'Reset Query Stats
        RecordCount = 0

        Exception = ""

        Try
            ' Open a Connection
            DBCon.Open()

            ' Create a New Command
            DbCmd = New OleDbCommand(Query, DBCon)

            ' Load Params into DB Command
            Params.ForEach(Sub(p) DbCmd.Parameters.Add(p))

            ' Clear Params List
            Params.Clear()

            ' Execute Command & Fill DataTable
            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DbCmd)
            RecordCount = DBDA.Fill(DBDT)
        Catch ex As Exception
            Exception = ex.Message
        End Try

        ' Close Database
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

    ' Include Query & Commands Params
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New OleDbParameter(Name, Value)

        Params.Add(NewParam)
    End Sub

End Class
*******************************************
(SUB INSIDE FORM CODE TO ADD NEW CUSTOMER)
*******************************************
VB.NET:
Private Sub AddUser()
        ' ADD NEW USER
        Access.AddParam("@CustNo", Str(CustNo))
        Access.AddParam("@FirstName", txtFirstName.Text)
        Access.AddParam("@LastName", txtLastName.Text)
        Access.AddParam("@Address", txtAddress.Text)
        Access.AddParam("@City", txtCity.Text)
        Access.AddParam("@State", txtState.Text)
        Access.AddParam("[USER=33378]@Zip[/USER]", txtZip.Text)
        Access.AddParam("@Hphone", txtHphone.Text)

        ' EXECUTE INSERT CMD
        Access.ExecQuery("INSERT INTO Customer (CustNo,FirstName,LastName,Address,City,State,Zip,Hphone)" & " " & "VALUES                     (@CustNo,@FirstName,@LastName,@Address,@City,@State,@Zip,@Hphone)")

        ' REPORT & ABORT
        If Not String.IsNullOrEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub

        frmCustomer.ShowAll()
        Me.Close()
End Sub
*******************************************
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,053
Location
Sydney, Australia
Programming Experience
10+
You know the maximum length for each of your columns, so check the length of the values you're trying to save to them and that will show which is too big. Of course, as you're using TextBox controls, logic would dictate that you set the MaxLength property of each one so the user simply can't enter invalid data.
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,512
Location
Lansing, MI; USA
Programming Experience
10+
I can open the database and extract all information to my form.
But when I enter new information into the same form and click Add
which simply runs the AddUser Sub I get the following message below
"The field is to small to accept the amount of data you attempted to add.
Try inserting or pasting less data."

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

*******************************************
(CUSTOMER.MDB MY DATABASE FOR PROJECT)
*******************************************
RowName - Type - Length
-------------------------------
Recno - AutoNumber - Integer
CustNo - Text - 7
LastName - Text - 15
FirstName - Text - 15
Address - Text - 30
City - Text - 30
State - Text - 2
Zip - Text - 10
Hphone - Text - 14

*******************************************
(A CLASS TO OPEN, QUERY, CLOSE Customer.mdb)
*******************************************
VB.NET:
Imports System.Data.OleDb

Public Class dbControl
    ' Create Your DB Connection
    'Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Customer.mdb;")
    Private DBCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;")

    ' Prepare DB Command
    Private DbCmd As OleDbCommand

    ' DB Data
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable

    ' Query Params
    Public Params As New List(Of OleDbParameter)

    ' Query Stats
    Public RecordCount As Integer
    Public Exception As String

    ' Query SQL Command
    Public Sub ExecQuery(Query As String)
        'Reset Query Stats
        RecordCount = 0

        Exception = ""

        Try
            ' Open a Connection
            DBCon.Open()

            ' Create a New Command
            DbCmd = New OleDbCommand(Query, DBCon)

            ' Load Params into DB Command
            Params.ForEach(Sub(p) DbCmd.Parameters.Add(p))

            ' Clear Params List
            Params.Clear()

            ' Execute Command & Fill DataTable
            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DbCmd)
            RecordCount = DBDA.Fill(DBDT)
        Catch ex As Exception
            Exception = ex.Message
        End Try

        ' Close Database
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub

    ' Include Query & Commands Params
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New OleDbParameter(Name, Value)

        Params.Add(NewParam)
    End Sub

End Class
*******************************************
(SUB INSIDE FORM CODE TO ADD NEW CUSTOMER)
*******************************************
VB.NET:
Private Sub AddUser()
        ' ADD NEW USER
        Access.AddParam("@CustNo", Str(CustNo))
        Access.AddParam("@FirstName", txtFirstName.Text)
        Access.AddParam("@LastName", txtLastName.Text)
        Access.AddParam("@Address", txtAddress.Text)
        Access.AddParam("@City", txtCity.Text)
        Access.AddParam("@State", txtState.Text)
        Access.AddParam("[USER=33378]@Zip[/USER]", txtZip.Text)
        Access.AddParam("@Hphone", txtHphone.Text)

        ' EXECUTE INSERT CMD
        Access.ExecQuery("INSERT INTO Customer (CustNo,FirstName,LastName,Address,City,State,Zip,Hphone)" & " " & "VALUES                     (@CustNo,@FirstName,@LastName,@Address,@City,@State,@Zip,@Hphone)")

        ' REPORT & ABORT
        If Not String.IsNullOrEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub

        frmCustomer.ShowAll()
        Me.Close()
End Sub
*******************************************
Do you have the MaxLength property set on the controls on your form (textboxes) to match the max size of the database columns?
 

JimRyanVB

Member
Joined
Apr 23, 2019
Messages
6
Location
Nashville TN
Programming Experience
10+
Yes the max length is set to each text box and I have stepped thru each text box and am assured that the data from each is less than the length required in the database.

Thanks Jim
 

JimRyanVB

Member
Joined
Apr 23, 2019
Messages
6
Location
Nashville TN
Programming Experience
10+
You were both right, I had to create a hidden Custno.text box on the form to hold the CustNo as somehow the CustNo text variable I was using would not work?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,053
Location
Sydney, Australia
Programming Experience
10+
I had to create a hidden Custno.text box on the form to hold the CustNo as somehow the CustNo text variable I was using would not work?
That is undoubtedly false. The Text property of a TextBox is a String like any other. If what you were doing didn't work it was because you were doing it wrong, not because using a String variable couldn't work. The fact that you were using the Str function is evidence that you were doing bad things.
 

JimRyanVB

Member
Joined
Apr 23, 2019
Messages
6
Location
Nashville TN
Programming Experience
10+
What I was trying to say was before my code was...
Dim Custno AS String
Once the txtHphone.text phone number was input 6153563232 I was loading the last 7 digits into the Custno variable and that gave me the error
"The field is to small to accept the amount of data you attempted to add.
Try inserting or pasting less data."

I then created a hidden txtCustno text box on the form with MaxLen = 7. Once the above txtHphone.text was input again using the last 7 digits I placed that in the hidden txtCustno.text box and using that it worked with no errors?

Access.AddParam("@CustNo", Str(CustNo))
Access.AddParam("@FirstName", txtFirstName.Text)
Access.AddParam("@LastName", txtLastName.Text)
Access.AddParam("@Address", txtAddress.Text)
Access.AddParam("@City", txtCity.Text)
Access.AddParam("@State", txtState.Text)
Access.AddParam("@Zip", txtZip.Text)
Access.AddParam("@Hphone", txtHphone.Text)

' EXECUTE INSERT CMD
Access.ExecQuery("INSERT INTO Customer (CustNo,FirstName,LastName,Address,City,State,Zip,Hphone)" & " " & "VALUES (@CustNo,@FirstName,@LastName,@Address,@City,@State,@Zip,@Hphone)")

' REPORT & ABORT
If Not String.IsNullOrEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub

frmCustomer.ShowAll()
 
Top Bottom