Solved

Update a Access database that has a autonumber field

Posted on 2006-11-27
17
351 Views
Last Modified: 2013-12-25
I am trying to update a Access database that has a autonumber field. How do you do this with VB 2005?
0
Comment
Question by:whiwex
[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
  • 5
  • 5
  • 4
  • +2
17 Comments
 
LVL 4

Expert Comment

by:seshu123
ID: 18025834
Hai,
   you can use the execute method in the vb oledb concept.  in the vb.net i don't know very much.

if you want i will send you the coding also.


Seshu123
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18025880
Well first u need to know the sql

do u want to update a specific record or a number of records?

u cannot update autonumber fields, if anything, u can use that as the primary key and update specific records

sample code can be found here
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22056634.html
0
 

Author Comment

by:whiwex
ID: 18027068
I am trying to add a new record to the database. The problem is the autonumber field will not except a null. So I have put a number in the field when I add the record.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 4

Expert Comment

by:seshu123
ID: 18027285
Hai,
   You can create a sql statement like the following.

    '        strSQL = "INSERT INTO "
    '        strSQL = strSQL & "DepartmentMaster ("
    '        strSQL = strSQL & "DepartmentName) "
    '        strSQL = strSQL & " VALUES ("
    '        strSQL = strSQL & "'" & m_strDepartmentName & "') "

            m_objData.SQLExecute(strSql) (here The m_objData is the connection created in the vb)


like this you can add the record in to the database.  I am using this concept in my projects.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18027523
Ok, when u inserting, thats different to updating

Just specify all the fields u want except for the autonumber field

insert into mytable (field1, field2, field3) values (.....

in the above sql statement, dont specify the autonumber - coded example also given by seshu123

U still need to run it with ExecuteNonQuery (sample code provided in the link I gave ya)

u can also use recordsets to add new records, I find this easier if you have shedloads of fields

0
 

Author Comment

by:whiwex
ID: 18028741
Here's the code I am using. There is a field clled customer number that is a autonumber. When I run this code I have to put something in the field or it errors to null not allowed. Problem is this is the primary key and I can't have duplicates.
   
  Dim dsNewRow As dsParentCompany.CustomersRow
        dsNewRow = CType(DsParentCompany.Customers.NewRow(), dsParentCompany.CustomersRow)
       
        With dsNewRow
            dsNewRow.Company_Name = txtCompanyName.Text
            dsNewRow.Company_Address_1 = txtAddress1.Text
            dsNewRow.Company_Address_2 = txtAddress2.Text
            dsNewRow.Company_Address_2 = txtAddress2.Text
            dsNewRow.Company_City = txtCity.Text
            dsNewRow.Company_State = txtState.Text
            dsNewRow.Company_Zip = txtZip.Text
            dsNewRow.Country = txtCountry.Text
            dsNewRow.Location_Number = "1.0"
            dsNewRow.Parent_Company = txtCompanyName.Text

        End With
        DsParentCompany.Customers.Rows.Add(dsNewRow)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 18029480
But if its a autonumber, you shouldnt need to specify it, surely.

Could u perform a read prior and get the highest value then add one to it?
0
 

Author Comment

by:whiwex
ID: 18029606
If I don't specify a value it errors to null not allowed. If I specify a value then it wokrs as long as it's not a duplicate. I don't know why I have to specfiy a value but I do.
I suppose I could sort by company number then add one.
 
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18031170
that is odd, I wonder if its a feature of vb 2005

do u suffer the same fate if u use a SQL statement?
0
 
LVL 4

Expert Comment

by:seshu123
ID: 18034481
Hai,
    Try using the sql insert into statement to get the correct result as you expect.


Seshu123
0
 

Author Comment

by:whiwex
ID: 18036093
seshu123

can you show what you mean?
0
 
LVL 4

Expert Comment

by:seshu123
ID: 18036473
Hai,
   This is nothing but the one i posted in the previous post.  ok i am agin pasting the same
    here i created one class which is basically supports the database connection( or you can create one database                                connection with out any database class)  and i build one sql string which is i showed in the above. and i executed that sql statement using the connection of the database.


probably the code looks like


dim m_objData as new adodb.connection
dim strsql as string
 strSQL = "INSERT INTO "
strSQL = strSQL & "DepartmentMaster ("
strSQL = strSQL & "DepartmentName) "
strSQL = strSQL & " VALUES ("
strSQL = strSQL & "'" & m_strDepartmentName & "') "
m_objData.executenonquery(strsql)


then you can achieve the things.


Seshu123
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 18097925
Hi whiwex.
From what I've seen in your posts I think you're not actually inserting the row in the database. What you're doing is inserting it in the dataset, which is like a snapshot from the query you made to fill it.

Do you want to make all the insertions and deletions and then submit them to the database? Or do you want to make a direct insertion???

You can do both things in VB '05, the difference is what object you'll use for each one (DataSet or Command)

Let me know and I'll see if I can help you out.

ROAR!
0
 
LVL 4

Expert Comment

by:seshu123
ID: 18121034
Hai,
  what happened.  did you got the answer for your query.



Seshu123
0
 
LVL 3

Expert Comment

by:herreruud
ID: 18121946
Hi,

When dealing with access I would reccomend not using ADO.NET, but ADO.

I'll write some VB6 code to demonstrate:

Dim myRecordset as Recordset
Dim iAutoNumberValue As Integer

Set myRecordset = myConnection.OpenRecordSet("SELECT * FROM myTable")
With myRecordset
  .AddNew
  .Fields("myField1") = someValue
  .Fields("myField2") = someValue
 
  .Update
  iAutoNumberValue = .Fields("myAutoNumberField")
End With
Set myRecordset = Nothing

0
 
LVL 3

Expert Comment

by:herreruud
ID: 18121961
Sorry,

meant DAO, not ADO.

In the example Recordset = DAO.RecordSet, Connection = DAO.Connection
0
 

Author Comment

by:whiwex
ID: 18341655
The problem was the database not my code. I rebuilt my database ad everything is working fine.
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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
Course of the Month11 days, 11 hours left to enroll

623 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