ADODC

[Jaseem Ahmed]

i have a database in sql server2000...
i have some of them tables, that i want to connect with visual basic..
the connection is tested, its connected..
now first tell me which data control should i use??
And then the code for updating, editing, adding a record in my database through visual basic..
and then when i m trying to assign a field to dtpicker, it is erroring that data bind faild.
please help me..

[AndreRet]

You need to break your questions into pieces and open more new posts with them. It will be impossible to cover all here without confusing other readers.:)

I'll cover the first part here. The others on your new posts. Also, keep in mind to show us some effort. No one here will just give you code for edit, delete atc.

What control to use?>>> Try and keep away from controls, there uses are "limited". If you start now with proper coding, the long run will show some results.;)

'Depending on your sql connection...., lets call it conn.

Dim rsNew As ADODB.Recordset
Set rsNew = New ADODB.Recordset

rsNew.Open "SELECT * FROM Yourtablenamehere", conn, adOpenstatic, adLockoptimistic

rsNew.AddNew

rsNew!Firstfieldname = Text1.Text
'and so on....

rsNew.Update

rsNew.Close
conn.Close

[Jx_Man]

so you use adodc control?
post your code..how far u do this..

[Jaseem Ahmed]

@AndreRet...
ok i will start new theards, but u just tell me that i shouldnt use ADODC? and plz post the way to update and add the new record.

[Jaseem Ahmed]

@andreRet...
i have an individual command buttons to Add, Update record..whre should i use ur given code?

[Jx_Man]

andreret already posting codes to add new record with adodb way..post no #2
as he said too, if you want to add,edit or delete record please divide your thread into a couple threads..
also how far you doing this..we didn't do all entire codes for you but we help to fix your codes :)

[AndreRet]

ADODC is short for Active Data Object Data Control, which is still a control.

My code above is for adding a new record, so you will use the code under your cmdAdd Button. Just remember that the code will not work without a proper connection. This all depends on what database you are using, access, Mysql etc.

Deleting of a record is a bit more involved, because you need to first find the correct record to delete, otherwise you will delete the first record in the table every time you select delete. Open a new thread with the title "How to delete a record from access?". It will not be covered in this thread.:)

[Jaseem Ahmed]

i have not kept any option for delete...
so i dont want deleting coding..
and i told u i have Ms Sql server 2000 database..
please tell me the code for add,update,edit...

[AndreRet]

and i told u i have Ms Sql server 2000 database..

My bad, first line of OP.

Now, I told you the code above is for adding a new record. Did you even try and use it.?

[AndreRet]

Ok, seeing that you are still confused with all 3 posts, here a sample of what you need. Just add the controls (cmdAdd, Text1 etc.)
Also add a reference to MS ActiveX Data Objects 2.x, where x is the version you have. -

'In your form code window...
Option Explicit

'Code from JX, thanks dude....
Private Con As New ADODB.Connection 'reference to your connection...
Public rs As New ADODB.Recordset 'reference to your recordset...
 
Private Sub MyDataCon() 'Create a sub where you can call the connection every time you need it. This can also be added to a module where it can be called throughout your application, less code, more smiles...
If Con.State = 1 Then Con.Close 'Close the connection, otherwise you will receive an error, or your connection might not work...
    Con.ConnectionString = "Provider=MSDASQL;Driver={SQL Server}; " & _
    " SERVER=USER;Database=Test;Uid=;Pwd=" 'The actual driver call to open the connection so that you can interact with your database...
    Con.Open
MsgBox "VB6 has connected with SQLServer Database" 'If all went well, you should get the message box... You can also remove the msgbox above if you do not want to see it all the time. Leave it for now so that you know all is working fine.
End Sub

Private Sub cmdAddNew_Click()'We are going to add a new record here...

'Firstly open the connection...

Call MyDataCon

Set rs = New ADODB.Recordset 'Set an instance of your recordsource...

rs.OPEN "SELECT * FROM YourTable", Con, adOpenStatic, adLockOptimistic 'Select which table to open... Replace 'YourTable' with your table name!!!!...

rs.AddNew
rs!Id = txtId.Text 'Assuming that your field is called 'Id' and that you have a textbox called txtId!!!. Change as you need...
rs!Name = txtName.Text 'Same as above, change where needed...
rs!Surname = txtSurname.Text
rs.Update 'Use update to add the new record. Anytime that you are adding, deleting, editing data, you have to use "Update" to complete the transaction with the database...

'You are now done with the recordset AND the connection, close both...
rs.Close
Con.Close
End Sub

Private Sub cmdEdit_Click() 'We are going to edit a record here...
'Firstly open the connection...

Call MyDataCon

Set rs = New ADODB.Recordset 'Set an instance of your recordsource...

'Here comes the serious part, you need to find the CORRECT recordset in the table to edit...
'Firstly make sure the user has entered a valid 'Id' in txtId, so you can do a proper search for the exact recordset...
If txtId.Text = vbNullString Then 'No text entered...
msgbox "You have to add a valid Id to search by", vbOkOnly+vbInformation,"No Text Added"

txtId.SetFocus
Exit Sub
Else
rs.OPEN "SELECT * FROM YourTable WHERE Id = '" & txtId & "'", Con, adOpenStatic, adLockOptimistic 'Select which table to open... Replace 'YourTable' with your table name!!!!...

'Now check if a valid record has been returned or not...
If rs.BOF = True Or rs.EOF = True Then
msgbox "No Record found. Please enter another Id to search by.",vbOkOnly+vbInformation,"No Record Found"

txtId.SetFocus
Exit Sub
Else
'Do not change the Id field, that is most probably your primary key. Let user change only the name or surname... 
txtId.Enabled = False
rs!Name = txtName.Text 'Same as above, change where needed...
rs!Surname = txtSurname.Text
rs.Update 'Use update to add the new record. Anytime that you are adding, deleting, editing data, you have to use "Update" to complete the transaction with the database...
'Now do some cleaning up first...
txtId.Enabled = True
'You can now add the code below under cmdShow, AFTER!!!! rs.Open 9remember that it is still open in this context) if you want the record that has been edited to show in your text boxes...

'You are now done with the recordset AND the connection, close both...
rs.Close
Con.Close
End Sub

Private Sub cmdShow_Click() 'We will now add some data to the text boxes. After this, we will add some navigation controls to move to the next etc...
'Firstly open the connection...

Call MyDataCon

Set rs = New ADODB.Recordset 'Set an instance of your recordsource...

rs.OPEN "SELECT * FROM YourTable", Con, adOpenStatic, adLockOptimistic 'Select which table to open... Replace 'YourTable' with your table name!!!!...

'Firstly, If there are no records to display, then let the user know...
If rs.BOF = True Or rs.EOF = True Then
msgbox "No Records foundto display. Please add some records first",vbOkOnly+vbInformation,"No Record Found"

Exit Sub
Else
'First move the recordset to the first record...
rs.MoveFirst
'Show the data, records were found.
txtId.Text = rs!Id 'Assuming that your field is called 'Id' and that you have a textbox called txtId!!!. Change as you need...
txtName.Text = rs!Name 'Same as above, change where needed...
txtSurname.Text = rs!Surname

'You are now done with the recordset AND the connection, close both...
rs.Close
Con.Close
End Sub

'Lastly, I will show you how to use navigation buttons to navigate through the records... This will show only for '.MoveNext'. Use '.MoveLast, .MovePrevious, .MoveFirst for the others. I'm sure you can manage that much :)

Private Sub cmdMoveNext_Click() 'Move to the next record if there is one...

'Firstly open the connection...

Call MyDataCon

Set rs = New ADODB.Recordset 'Set an instance of your recordsource...

rs.OPEN "SELECT * FROM YourTable", Con, adOpenStatic, adLockOptimistic 'Select which table to open... Replace 'YourTable' with your table name!!!!...

If rs.EOF = True Then 'The last record was reached, there are no more records to view...
msgbox "No more records to display.",vbOkOnly+vbInformation,"Lat Record Loaded"

Exit Sub
Else
rs.MoveNext

txtId.Text = rs!Id 'Assuming that your field is called 'Id' and that you have a textbox called txtId!!!. Change as you need...
txtName.Text = rs!Name 'Same as above, change where needed...
txtSurname.Text = rs!Surname

'You are now done with the recordset AND the connection, close both...
rs.Close
Con.Close
End Sub

This is about all you need. If you have more questions, just open a new thread if it does NOT have relevance to this thread.

Also closed ALL 3 threads please. They are now all answered under this thread.:)

[Jx_Man]

@AndreRet : this post will answered all his thread..haha..
you really in good mood friend :)

@Jaseem Ahmed : you really lucky today..so try it..don't say that u still confuse after andre write this much codes..good luck with your project :)

[Jaseem Ahmed]

Jx i m Really thank full to you and Andre.,....u both have helped me alot...
now i understood, no any confusion...
once again very much thanks to Andre....

[AndreRet]

Thanks JX, you had a hand in it as well.

@Jaseem, please mark this as solved, thanks.

[AndreRet]

Ignore my last post, It was solved, although only after I refreshed the page. Daniweb is going through some teething problems at the moment.:)

[Jaseem Ahmed]

i m getting an error still...
some of fields are assigned numeric values in database,
just as Orgcode, Phone no.Fax..
when i m adding a record, it is erroring type mismatch..?

[AndreRet]

Jaseem, although having reference here, this is marked as solved. Please open a new thread, I am in there now. Will have a look. Also post the code and tell me where the error occurred, thanks bud.