Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trouble adding new recordset

Posted on 2003-12-01
11
Medium Priority
?
1,080 Views
Last Modified: 2013-11-25
I have an Access database with a table called "Customers", which is linked via a ADODC control to a form in VB.
I have all the textboxes properly linked to the corresponding columns in the DB.  All i want to do is fill in the form, press "Add New", and have the information in the textboxes added to the appropriate fields in the Access DB.  

This code gives me an error stating that "Company Name cannot contain zero-length ...."  but it doesn't... it's filled in and I've double checked the spelling and the links, and they are right... btw, ".Update" is highlighted with this error msg.

With adoCustomers.Recordset
    .AddNew
    !firstname = txtFirstName.Text
    !lastname = txtLastName.Text
    !contacttitle = txtTitle.Text
    !CompanyName = txtCompany.Text
    !address = txtAddress.Text
    !city = txtCity.Text
    !State = txtState.Text
    !zipcode = txtZipcode.Text
    !phonenumber = txtPhone.Text
    .Update
End With

When I do the code without ".Update at the end it just overwrites the first record instead of adding a new one at the end of the db....

Any Thoughts?
0
Comment
Question by:Glatty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 300 total points
ID: 9856374
>>Company Name cannot contain zero-length
Try message out the contain of your text save in Company field:

msgbox(txtCompany.Text)

is the text empty?

.AddNew method always follow by a .Update statement.

By the way, always try NOT use the ADODC data control for your data connection, try use the ADODB.Connection and ADODB.Recordset instead:

example:

SQLstr = "Insert into myTable (firstname, lastname, contacttitle, CompanyName, address, city, State, zipcode = txtZipcode, phonenumber ) values ('" & txtFirstName.Text & "','" &  txtLastName.Text & "','" & txtTitle.Text & "',
" & txtCompany.Text & "','" & txtAddress.Text & "','" & txtCity.Text & "','" &  txtState.Text & "','" & txtZipcode.Text & "',
" & txtPhone.Text & "')"

conn.ConnectionString = myConnectionString
        conn.Open
        conn.CursorLocation = adUseClient

conn.execute SQLstr
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9857167
Excuse me if I've misunderstood but this seems to be a rather complex way of coding your program. Have you set the datafield and datasource properties in each of the text boxes. If you have then there is no need for the:

 !firstname = txtFirstName.Text
    !lastname = txtLastName.Text
    !contacttitle = txtTitle.Text
    !CompanyName = txtCompany.Text
    !address = txtAddress.Text
    !city = txtCity.Text
    !State = txtState.Text
    !zipcode = txtZipcode.Text
    !phonenumber = txtPhone.Text

This code is not needed and may be the problem.

I would suggest that you link your code to two seperate command events. i.e. click an add button to add code and an update button to update it. e.g.

Private sub command1_click()
   adoCustomers.addnew
end sub

Private sub command2_click()
   adoCustomers.update
end sub





0
 

Author Comment

by:Glatty
ID: 9859133
TomLaw

I do have the textboxes linked to the proper datasource and datafields, so i removed the code (!statements) like you suggested.
I get the same problem that it just adds the records in the first spot instead of appending a new record to the database.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Glatty
ID: 9859617
ryancys-

I tried working with your suggestion as well, but i can't seem to figure out what's supposed to happen with it.  I think it's more complicated than it needs to be.  I should be able to do this with just the .AddNew and .Update commands....

That said, I think what the trouble is, is the AutoNumber field (CustomerID) in the database....  

What i want this form to do is take all the info from the textboxes, insert that info into the respective fields in the DB, and have the DB field, CustomerID, assign an autonumber.  Obviously i though i could do it this way, but I'm wrong.  How do i incorporate the autonumber function in and have the form add the info as a new record at the end of the DB?

thanks.
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9859833
The autonumber field should not be a problem, providing that you have built the datafile correctly it should add a number as needed. Is the table indexed on the autonumber or do you have more than one index field?
0
 

Author Comment

by:Glatty
ID: 9859849
The autonumber is the only (primary) key in the table.
0
 
LVL 1

Expert Comment

by:dwops
ID: 9860225
if this form is only for ADDING, not EDITING records, try simply removing the ADODC control and remove all your databindings.  

On the button_click event, create your ADO connection and a recordset object.  Then you just add the new recordset and update the DB (in the exact way you did above).  Basically, just change

With adoCustomers.Recordset

to

With rsCustomers       'or whatever you decide to name your ADO recordset


Their is some conflict happenning with the databinding.  You probably don't need to databind your text boxes at all.
0
 
LVL 4

Expert Comment

by:TomLaw1999
ID: 9860379
Glatty could you please repost your code as it is now (since the suggested modifications).
0
 
LVL 4

Accepted Solution

by:
TomLaw1999 earned 600 total points
ID: 9860503
Glatty I have just tried recreating your problem using an ADODC object, two command buttons and one textbox connected to an existing MDB file using the following code.

Private Sub Command1_Click()    'Add button
   Adodc1.Recordset.AddNew
End Sub

Private Sub Command2_Click()    'Update button
  Adodc1.Recordset.Update
End Sub

It worked perfectly.

If you still hyave a problem this means that either the ADODC object has not been correctly configured or that your data file has a problem. Perhaps you should try with a simpler data file then add fields gradually until you find the one causing the problem.
0
 

Author Comment

by:Glatty
ID: 9860554
I re-did the code opening an ADODB connection...  it's a lot longer, but it works...

here's the code that works for me (for anyone else that's wondering):

Private Sub cmdAddInfo_Click()
Dim cnn1 As ADODB.Connection
Dim rsCustomers As ADODB.Recordset
Dim strCnn As String, strFirstName As String, strLastName As String, strTitle As String
Dim strCompany As String, strAddress As String, strCity As String, strState As String
Dim strCountry As String, strZipcode As String, strPhone As String, booRecordAdded As Boolean
Error = "False"
Call ErrorCheck      'Calls seperate sub to make sure all fields are filled in correctly and give proper error msgs
If Error = "True" Then Exit Sub

'Open Connection
Set cnn1 = New ADODB.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\BCS.mdb;Persist Security Info=False"
cnn1.Open strCnn
       
'Set recordset parameters
Set rsCustomers = New ADODB.Recordset
    rsCustomers.CursorType = adOpenKeyset
    rsCustomers.LockType = adLockOptimistic
    rsCustomers.Open "Customers", cnn1, , , adCmdTable

'Get Info from the form
    strFirstName = txtFirstName.Text
    strLastName = txtLastName.Text
    strTitle = txtTitle.Text
    strCompany = txtCompany.Text
    strAddress = txtAddress.Text
    strCity = txtCity.Text
    strState = txtState.Text
    strZipcode = txtZipcode.Text
    strPhone = txtPhone.Text

'Add the info from above to a new record
With rsCustomers
        .AddNew
        !firstname = strFirstName
        !lastname = strLastName
        !contacttitle = strTitle
        !CompanyName = strCompany
        !address = strAddress
        !city = strCity
        !State = strState
        !Country = strCountry
        !zipcode = strZipcode
        !phonenumber = strPhone
        .Update
    End With
booRecordAdded = True
rstEmployees.Close
cnn1.Close
End Sub

Since I solved it on my own, I'll only distribute partial points.  Thanks for your help though!
0
 
LVL 1

Expert Comment

by:dwops
ID: 9861278
don't mean to nit-pick here, but it seems like you pretty much used my exact solution above.  you eliminated the databinding and even used the same name for the recordset that I suggested, changing the with statement and all...  I didn't even get 1 point!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question