Question Trying to use the INSERT INTO Query Statement

[JimRyanVB]

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)
*******************************************

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)
*******************************************

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

*******************************************

 

[jmcilhinney]

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]

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)
*******************************************

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)
*******************************************

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]

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]

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]

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]

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()