[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Recordset update

I have form level recordset variable. And I am using an SQL statement in a routine and using Set Testset = test_Db.OpenRecordset(Sql, dbOpenDynaset, 0, 1). But the problem is when I tried to update my 'Testset' recordset it is not updating. I am using
        Bldset.Edit
           Bldset!BLDGCODE = txtBuilding.Text
           Bldset!BLDGNAME = txtName.Text
        Bldset.Update

It is not updating and is not showing any errory message also. And I am sure I am opening my database as read/write mode
0
svenkatapuram
Asked:
svenkatapuram
1 Solution
 
DaveD072798Commented:
If the code you've given is correct, you need to replace all occurances of "Bldset" with "Testset", as in

Testset.Edit
   Testset!BLDGCODE=txtBuilding.Text
   Testset!BLDGNAME=txtName.Text
Testset.Update

Also, you could use a With...End With to avoid typing Testset over and over. Just use the period "." and exclamation point "!" inside the With...End With pair.
0
 
svenkatapuramAuthor Commented:
Dave,

It was a typo in my question, it was Bldset. Some how my recordset is setting to read only. I do not know why? Any solution
0
 
kilobugsCommented:
Are you sure there isnt any error message??? If there isnt any error message, then the process should be ok! Was you app connecting to a .mdb before???
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
svenkatapuramAuthor Commented:
Originally my app was using a .mdb file and later I changed to SQL server. For some tables it is updating (when I use recordset.edit) but for some it is not. But when I use direct SQL commands like update it is working. So any way I started changing my code
to SQL commands instead of recordset.

Thanks
0
 
DennisHCommented:
One of the most common reasons that you can't insert or update a record in a SQL Server table is if the table does not have a Primary Key.  Does this table have a Primary Key?
0
 
svenkatapuramAuthor Commented:
I do have primary key for my tables
0
 
kilobugsCommented:
Then I know what is your problem!!!
0
 
kilobugsCommented:
please check some of your paths are incorrect! When you use SQL statement, pls check to see if you path to the SQL Server is correct. If you dont even have a path to the SQL Server for edit or update, or I should say when you want to send SQL statement for execution in the SQL Server, you should have a proper connection established before you execute any SQL. I think you still have some of the connections pointed to the .mdb instead of SQL Server since you have used the .mdb before. Double check the .mdb to see if updating has been done!!! I used to overlook things like those, too!
0
 
svenkatapuramAuthor Commented:
I do have porper connection to the SQL server. I thing I figured out the problem. Because I am setting the recordset(testset) in one routine, then I am passing the same SQL statement to another routine where I am using the following to populate the grid
FillGrid(f As Form, Ssql As String)
Data1.Connect = "odbc;uid=sa;pwd=;dsn=NAME"
Data1.RecordSource = Ssql$

Then I am resetting the data1.recordset to the first recordset(testset). Becuase of this it is not working. I do not have to reset again. I can use the original recordset itself for the Grid also. Any way thank you for your suggestion
0
 
svenkatapuramAuthor Commented:
I already added comment to your reply

Thanks
0
 
haavaldCommented:
.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now