Urgent Anyone please help me datagrid

raj_msAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis ParaskevopoulosCommented:
Add a datagrid control and a button on a form.When the form is loaded try changing the value of a cell and then click update.Then check your original database, and you'll see it is updated.


Dim mycnn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub cmdUpdate_Click()
rs.UpdateBatch
End Sub

Private Sub Form_Load()
Set mycnn = New ADODB.Connection
Set rs = New ADODB.Recordset
mycnn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;Server=FIT4;Database=Host;UID=sa;PWD=;"
mycnn.Open
rs.Open "Add your SQL statement here", mycnn, adOpenDynamic, adLockBatchOptimistic

Set DataGrid1.DataSource = rs

End Sub
0
raj_msAuthor Commented:
No i want the datagrid values to be updated in the table
0
raj_msAuthor Commented:
and not the datagrid
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ioannis ParaskevopoulosCommented:
You need the changes made to the datagrid by the user to be updated to the database,or to be able to see the changes of the database displaied on the datagrid?
0
raj_msAuthor Commented:
i need the changes made to the datagrid by the user to be updated to the database
0
Ioannis ParaskevopoulosCommented:
yes,i get you.I'm working on it.

what kind of database are you working with?access,oracle,sql server .....
0
raj_msAuthor Commented:
MySQL
0
Ioannis ParaskevopoulosCommented:
Make sure the record you are trying to access is still on the database when you try to update it

Is it possible that this time any other user has access and or deletes and or adds records to that database?
0
Ioannis ParaskevopoulosCommented:
Have you opened the connection?If you have,can you give me the connection string?
0
raj_msAuthor Commented:
Yes i did
0
anvCommented:
one thing i wanna ask u...

r u updating the table which is having single record...or which has more than one record??
0
raj_msAuthor Commented:
more than 1 record
0
Noel_CastilloCommented:
do you have your connection string right? something like:

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
     
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=127.0.0.1;" _
            & "DATABASE=test;" _
            & "UID=testuser;" _
            & "PWD=12345;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
 
    conn.Open

and after opening your recordset, did you assign this to the datagrid?      
are you using a localhost?
can you post your connection string?

:)
0
Noel_CastilloCommented:
write your recordset like

    Set rs1 = New ADODB.Recordset
    rs1.CursorLocation = adUseClient
    rs1.CursorType = adOpenStatic
    rs1.LockType = adLockOptimistic
    rs1.Open "SELECT * FROM MyTable", conn
   
    Set DataGrid1.DataSource = rs1 '<- assigns the recordset to the datagrid

check the following properties of datagrid.

Enabled
AllowAddNew
AllowUpdate

You may also try to check if theres a part in your code that modifies this properties.
make sure that your lock type is not readonly. :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raj_msAuthor Commented:
i can assign to datagrid but i want the vice versa. load the datagrid with data from database, modify an update in the database
0
Ioannis ParaskevopoulosCommented:
With the code i gave you try adding a timer control with the interval property set to 1000(1 second)


Add this code:

Private Sub Timer1_Timer()

    rs.Requery

End Sub

This will refresh your datagrid every second.Ignore the fact that it is annoying when you try to change row etc,and tell me if this is what you want in the first place.If it is we will try and solve the rest problems afterwards.

To try this code open the window with you r records where you can modify them,and run the project.
Then in the first window change a cell,and then check the changes made in the project.

Tell me if that is what you want.
0
Noel_CastilloCommented:
once you have loaded the records from the recordset to the datagrid, the updating will follow provided that all necessary properties of datagrid are set. Assuming that all properties are set (just like what i'd mentioned above), All modification in the datagrid will be reflected in the database. Are you using MySQL Control Center to view the records of your database?
0
Noel_CastilloCommented:
One thing more. Once you modify a record in the datagrid, you have to leave the row being modified before the changes will take effect in your database. Or you can programatically force the update if necessary. Maybe thats the reason why anv asked you if you have more than one record. logically, you cant change current row(to trigger the update) if there is only one record or none at all.
0
Noel_CastilloCommented:
May i ask the version of MySQL you are using. And version info of connector as well. tnx :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.