Solved

Trouble adding new recordset

Posted on 2003-12-01
11
1,059 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 100 total points
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Glatty
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

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

Expert Comment

by:dwops
Comment Utility
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
Comment Utility
Glatty could you please repost your code as it is now (since the suggested modifications).
0
 
LVL 4

Accepted Solution

by:
TomLaw1999 earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now