whiwex
asked on
Update a Access database that has a autonumber field
I am trying to update a Access database that has a autonumber field. How do you do this with VB 2005?
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
https://www.experts-exchange.com/questions/22056634/Update-Record-VB-2005-Access-DB.html
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
https://www.experts-exchange.com/questions/22056634/Update-Record-VB-2005-Access-DB.html
ASKER
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.
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(strSq l) (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.
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(strSq
like this you can add the record in to the database. I am using this concept in my projects.
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
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
ASKER
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.CustomersR ow
dsNewRow = CType(DsParentCompany.Cust omers.NewR ow(), dsParentCompany.CustomersR ow)
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(d sNewRow)
Dim dsNewRow As dsParentCompany.CustomersR
dsNewRow = CType(DsParentCompany.Cust
With dsNewRow
dsNewRow.Company_Name = txtCompanyName.Text
dsNewRow.Company_Address_1
dsNewRow.Company_Address_2
dsNewRow.Company_Address_2
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I suppose I could sort by company number then add one.
that is odd, I wonder if its a feature of vb 2005
do u suffer the same fate if u use a SQL statement?
do u suffer the same fate if u use a SQL statement?
Hai,
Try using the sql insert into statement to get the correct result as you expect.
Seshu123
Try using the sql insert into statement to get the correct result as you expect.
Seshu123
ASKER
seshu123
can you show what you mean?
can you show what you mean?
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
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(
then you can achieve the things.
Seshu123
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!
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!
Hai,
what happened. did you got the answer for your query.
Seshu123
what happened. did you got the answer for your query.
Seshu123
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
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
With myRecordset
.AddNew
.Fields("myField1") = someValue
.Fields("myField2") = someValue
.Update
iAutoNumberValue = .Fields("myAutoNumberField
End With
Set myRecordset = Nothing
Sorry,
meant DAO, not ADO.
In the example Recordset = DAO.RecordSet, Connection = DAO.Connection
meant DAO, not ADO.
In the example Recordset = DAO.RecordSet, Connection = DAO.Connection
ASKER
The problem was the database not my code. I rebuilt my database ad everything is working fine.
Thanks
Thanks
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