Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1083
  • Last Modified:

Trouble adding new recordset

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
Glatty
Asked:
Glatty
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
Ryan ChongCommented:
>>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
 
TomLaw1999Commented:
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
 
GlattyAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GlattyAuthor Commented:
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
 
TomLaw1999Commented:
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
 
GlattyAuthor Commented:
The autonumber is the only (primary) key in the table.
0
 
dwopsCommented:
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
 
TomLaw1999Commented:
Glatty could you please repost your code as it is now (since the suggested modifications).
0
 
TomLaw1999Commented:
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
 
GlattyAuthor Commented:
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
 
dwopsCommented:
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

Technology Partners: 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!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now