Connectivity of SQL 2000 + VB using ODBC

steps i Follow-
1) Go to Control panel-->Admintrative Tools-->Double -click
Data sources(ODBC)-->click Add button-->I choose SQL server(last option), Is it right or wrong option for connecting to SQL 2000.Then a new screen appears Create a New dataSource-->in name textbox,we can write anyname--Right or wrong. suppose i enter sonia, & my server name is .,so I enter . in server & click next button-->On the Next screen i have checked the option With Sql server authentication-->Login Id-sa, Password-->Blank-->Click next button-->Next-->Finish-->Then I click on button Test Data source-->Mesage Comes TESTS COMPLETED SUCCESSFULLY-->OK-->OK-->Ok.

These steps we have to do or not for Connectivity using ODBC.
If yes,then what after these steps we have to do??Plz reply me early ??Thx in advance.

Plz tell me the coding also for insertion of textbox values into the textbox.

[B]''code for making a connection with the sql server database[/B]
Private Sub Form_Load()
Dim gcn as New ADODB.Connection
gcn.connectionstring="Provider=SQLOLEDB.1;Persist Security Info=False;[B]User ID=bs[/B];[B]Initial Catalog=BILLING_SYSTEM[/B]"
gcn.open
msgbox "A connection to the database is now established"
End Sub
[B]''code for retrieving some rows from a table[/B]
Private sub Command1_Click()
Dim rs as New ADODB.Recordset
Dim str as String
Dim li as ListItem
str="select * from branch_details order by branch_id"
rs.open str,gcn,1,2
if rs.Recordcount>0 then
rs.MoveFirst
While not rs.EOF()
with Listview1
set li=.listitems.add(,,(branch_id))
li.subitems(1)=rs!branch_name
li.subitems(2)=rs!address
end with
rs.MoveNext
Wend
else
msgbox "No record found." & vbcrlf & "Please add some records."
end if
if rs.State=adStateOpen then rs.close
set rs=Nothing
End Sub

look for the Bolded Parts in the connection string. the first one should be replaced by yours database user name and the second one is by your original database name.

for inserting values from textbox to your sql server database use the following code :-

Dim rs as New ADODB.Recordset
if rs.state=adStateopen then rs.close
rs.open "select * from [B]branch_details[/B]",gcn,1,2
rs.AddNew
rs!branch_id=txtID.text
rs!branch_name=trim(txtName.text)
rs!address=trim(txtAddress.text)
rs.Update
if rs.state=adStateopen then rs.close
set rs=nothing
Msgbox "New record added."

look into the Bolded Part. it must be replaced by your table name where you wish save your records.

There is so such control in the Components... Microsoft Active X Plug in ..Is it same as which u r saying?/I m working on 6.0.

absolutely no.
you are in wrong place.

look at your menu bar inside your vb6 IDE. you will find a menu called Project.
inside it there is a command References... goto there,scroll down and add the reference
Microsoft Activex DataObjects ... Library

listen carefully my frnd,Microsoft Activex Data Objects is a reference not a component or control. so it won't be added into the toolbox. each control you use on your vb6 form adds a reference to your project. now if you add an ADODC control on your form it will add the same ref. on your project too. to see this, just add an ADODC control from your component list and now go to Project->References. there you will see Microsoft Activex Data Objects has been added to your project reference list.

i'm telling you again plz read previous replies ten times before you post a reply.
in my previous replies several times i have said that this code doesn't require any control/component to be added on your form. it is using a reference and for that adding MS ADO is enough.

look into the code very carefully. there is a statement Dim gcn as New ADODB.Connection. this is a global variable which is nothing but an instance of ADODB Connection object. so to use this vars or accessing ADO methods/functions inclusion of this reference is very much important. otherwise you will face compilation errors.

Hi Sonia, first of all this is a personal request from me to you....don't call me like Hey choudhary....it becomes better if you use Hey Shouvik....ok..

now find your answers inline.

Hey choudhary,
Names I have mentioned below are all used to connect to VB or not??
ADO,RDO,ODBC,DAO,OLE DB????

Yes, you are quite right. Actually there are physically three connection types available and those are :- RDO,DAO,ADO. The ADO/ODBC/OLEDB refers to same thing which is ADO (Activex Data Objects).

DAO (Data Access Objects) is used to connect to local databases such as an ms-access database.

RDO (Remote Database Objects) was used to connect to remote database objects. this sounds too spooky. actually remote database means connecting to a database which resides on some other server/node/machine other than the machine from where the vb6 application is running but both are in the same network. this was used in LOCAL AREA NETWORK protocols. it had so many limitations. for this microsoft abandoned further improvements on this technology and it was gone. after this ADO comes into the picture which overcomes all limitations of RDO along with DAO and has more features. using ADO you can connect to any databases regardless of its locations such that it can reside in a server node or in the local machine.

AnyMore methods are there to connect to VB??
Plz just give the names as above.

No, there are no such connections method available other than these three.
I think the previous line has its answer.

To connect to ODBC--we require DSN Connection. Right or Wrong?
To connect to OLEDB--we reruire OPEN Connection. Right or Wrong?

yes, to connect an ODBC data source a DSN connection is required. In ODBC connection, you don't require any middle tier control for linking a connection from your vb6 application to the database. just adding the reference Microsoft Activex DataObjects will do the job. after creating the connection use the ADODB objects for data interaction.

There is no connection type "OPEN". "Open" is a method of ADO connection object which creates an active connection to the database with the parameters passed in the connection string. In case of OLEDB, using DSN connection is optional. you can also use a DSN-Less connection . In OLEDB, if you want to use a DSN-Less connection you can use the ADODC control (Microsoft ADO DataControl (OLEDB).

Can u plz give me just one-line short descriptions abt the others.

As RDO is not used today any more, further discussion on it becomes absurd. so, i'm skipping it.
Now if you use DAO technology, the coding is different. here is a sample DAO code for you. this is for opening a connection and referencing a table as a recordset object. Now you are familier with VB.Net and might you have used Dataset in your vb.net coding. In vb6 we have Recordset which performs like Dataset in vb.net. only diff. lies in their connection behaviour. as dataset can work on disconnected environment, recordset works on connected environment only.

for this code you need to add the following reference :-Microsoft DAO <version no.> Objects Library

Dim db as Database
Dim rs as Recordset
set db=OpenDatabase("c:\mydatabase.mdb", False, False, ";pwd=mydatabasepassword")
set rs=db.OpenRecordset("mytablename",dbOpenTable)
if rs.RecordCount>0 then
rs.MoveFirst
else
Msgbox "Please add some records..."
end if

Second thing,The coding given by you to yogesh as scrap,In that method we are connecting using which method???
is that ADO or RDO or DAO.

One thing told me Suppose I learn just one connection to connect to Vb,As there are may ways to connect to VB, In companies there is any restriction that to connect using this particular method.
Suppose as we have in ASP.Net,
Like some companies require ASP with VB,
ASP with C#,
ASP with J#.

The ADO/ODBC/OLEDB refers to same thing which is ADO (Activex Data Objects).
Secondly,told me that even they are refer to the same thing,Steps are different na to connect to the database. THirdly,ADO/ODBC/OLEDB can be connect to remote or local database.Rite??

CAN We call these ADO,DAO.....as DATA ACCESS COMPONENTS.

DAO can be used to connect to local database such as MS-Access. IT means we cannot connect with SQL using DAO.Rite??

In Vb.Net we use dataset in case Of DATAGRIDVIEW. But ib Vb why v use RECORDSET HERE.

1. well, there is nothing to say about. this is completely depend upon the company policy. but as i have seen most of the companies now use ADO techniques from vb6. in my office i'm using ADO methods. you are quite right. the company management may have some project restrictions. like in the project which i'm working on, has restrictions to use the ADO technology only.

2. not too much difference you will found in the connection techniques whether you use ADO/ODBC/OLEDB. the diff. can be while you connecting to the database but the rest of the methods/properties for further database manipulations will be same. now whichever method is use there is a common thing in all of these......they all use Microsoft Activex Data Objects Library.

3. yes, ADO/DAO is known as MDAC (Microsoft Data Access Components)

4. yes, 100% correct. you cannot use DAO to connect to a sql database. you have to use ADO for this purpose.

5. not only the case of datagridview. you have to use the dataset class for data manipulations (inserting,updating,deleting,searching...etc..). in vb6 we use Recordset just for this purpose. the recordset must be used here because there is no concept of dataset i visual basic 6.

The ADO/ODBC/OLEDB refers to same thing which is ADO (Activex Data Objects).
Secondly,told me that even they are refer to the same thing,Steps are different na to connect to the database. THirdly,ADO/ODBC/OLEDB can be connect to remote or local database.Rite??

Thx Shouvik ,I m facing so much of difficulty raegarding connections,U Sort all my probs.
THX Very Much Again.

Madam Sonia,
I think Veena has already given all of your answers. So there is nothing need to say any more from my side except these,

3)
"select * from branch_details",gcn,1,2
Can u explain dat. We have to insert the records into the DataBase,Y we r selectiong it.

in the above statement we are not selecting anything from the database. a connection/reference to the table has just been established or you can say opened so that the recordset object which is "rs" here recognize/know where to send the data when the .update method fires. here we are just setting the destination for the recordset object.

4)
rs!address=trim(txtAddress.text)
In the above line,If I m not wrong,Address is the column name in database.

yes, you are correct. Address is the column name. This is just one type of syntax used here. Instead of this you can also use this, rs("Address") or this rs(2)-->here 2 is the index/subscript value of the address field in the rs recordset.

Apart from this, if you still have any more questions then feel free to post here.

how do you expect this code to run? the first statement contains a big error. apart from this, the insert statement which you have written & passed to the variable query contains syntax errors. errors due to nontermination of string constant and invalid sql command. the following is the corrected syntax. try this...

see the Bolded part. it is the syntax (an ampersand followed by a space and an underscore) for breaking the line. this process is called Line Continuation.

2)Second thing when i run the form,Msg comes Record inserted and when i click on ok then the form comes. And when we fill the fields & click on OK button,then the msg do not come.

sounds confusing!!! would you mind in explaining this a little bit clear?

3) Third thing,roll no is integer na then y u use single quotes as below-
'" & txtroll.text & "'

i was just assumed that. there is no hard-n-fast rule that roll no becomes always integer. it can be alphanumeric like 'R001'. so to show you an example i used that one. you have to change it accordingly based on your data type. if its an integer avoid that single quotes otherwise use those.

well I am unable to speak a single word without see what have you done so far. because the statement i used here is absolutely correct. now if its not so problem for you would you mind in posting your code for inserting record here?

I m writing the below mentioned coding-How Can I explain U-
When i run the form,Msg comes Record inserted and when i click on ok then the form comes. And when we fill the fields & click on OK button,then the msg do not come.

roll name marks
0 blank 0
0 blank 0
By saying this i m trying to say that,The records that are inserted into the database are
Always 0 is inserted for roll & marks,
& Blank is inserted for name.

I hope U Undersyand What I m trying to say.
hey one more thing tell me that Mastering in Visual basic 6.0 is good for freshers nor not.
It is not too highfy book na, dat freshers like me can’t understand. Cz I m planning to purchase dat book.

first, you have placed the record insertion code in wrong section. you are firing that code in form_load event. in this event vb actually initializes all controls. so at this time your three textboxes contain null values like txtroll="" , txtname="" and txtmarks="" . now when your program fires the

gcn.execute "insert..."

statement, it finds nothing in the text boxes. now as per your code it is converting value of those text boxes like ,val(txtroll.text) --> 0
txtname.text --> null
val(txtmarks.text) --> 0 and storing them in your table.
as you already known that val converts equivalent string to numbers. so when it finds a null value it passes only 0. now all your statements are executing fine coz there is no syntax or compilation errors. so the msgbox "Record Inserted" is being displayed. your code has some logical mistakes.

now you said

when click on the insert button the msgbox is not coming

.
this might has the following causes :-
1. you didn't mention the insertion code under the button.
2. though you have coded it but the msgbox statement is not there.

you have to code your record insertion statements in both of your form_load (sounds crazy but as you told) and button_click events so that the same action can be performed by your program when you wish to do so. now you have written in the code in form_load event. that's why the msgbox is coming when start debugging your program and not coming when you click on the button because clicking on the button generates a new separate event which has no relation with the form_load event--->means the form will not be loaded once again after clicking on the button.

hope you will be understood.

now for your consideration i have written the following code using the same database,provider,table,fields and form design. also there is a screenshot for you.
check this out and inform me what have you got...

hey Shouvik ,Till Now I have did the foll--
I inserted the records and as the records are inserted into the database,all the textboxes are clear.
THX FOR UR HELP.
Now I m trying to do myself DELETION, UPDATION, SEARCHING the Records.

The eg,u have given me in ur previous reply is
GRIDVIEW DATABINDING--Rite????

Till Now,I understood that
U write Connection string as procedure.
When the User clicks on Insert button further msg box is displayed.
If yes,Records are inserted.
If no,Focus is set to textbox Roll.

And To Clear The textboxes I did-
Public Sub clear()
Dim txt As Control
For Each txt In Controls
If TypeOf txt Is TextBox Then
txt.Text = ""
End If
Next
End Sub

hey Shouvik,Till Now i do not want GRIDVIEW DATABINDING, I am doing if user types Rollno 1 in textbox Roll and click on search button, then in the corresponding textboxes info is displayed in other textboxes like his name,& his marks.
In VB there is no DataReader.

hey Shouvik ,Till Now I have did the foll--
I inserted the records and as the records are inserted into the database,all the textboxes are clear.
THX FOR UR HELP.
Now I m trying to do myself DELETION, UPDATION, SEARCHING the Records.

that's a good news. so you are on your way. isn't it?
ok...try those and if you need any help then plz create a new thread for that.

The eg,u have given me in ur previous reply is
GRIDVIEW DATABINDING--Rite????

wrong. there is no gridview databinding concept available in vb6. it was just a listview control and it is showing all existing records from your table. look in your VS2005 toolbox......you'll find this control there too...

Till Now,I understood that
U write Connection string as procedure.
When the User clicks on Insert button further msg box is displayed.
If yes,Records are inserted.
If no,Focus is set to textbox Roll.

wrong. i didn't write connection string as procedure. the connection string is simple a method and i just initialized it by putting my server descriptions and used open method to create the connection. the only procedure in that code is DisplayAllStudents which is responsible for fetching & displaying all existing student records from your stuinfo table.
the msgbox's purpose is exactly same what you have said.

And To Clear The textboxes I did-
Public Sub clear()
Dim txt As Control
For Each txt In Controls
If TypeOf txt Is TextBox Then
txt.Text = ""
End If
Next
End Sub

ok....it is 1000% correct. there is no error in it.

so what is the summery?
now you are successfully adding your data into the database or not?
have my efforts showed you some ways or not???

hey Shouvik,Till Now i do not want GRIDVIEW DATABINDING, I am doing if user types Rollno 1 in textbox Roll and click on search button, then in the corresponding textboxes info is displayed in other textboxes like his name,& his marks.
In VB there is no DataReader.

yes, you are right. in vb6 there is no concept of datareader.
but so what? there are still so many methods that you can use to read your records from the database.

so you wish to try the search code yourself?
ok.....go ahead....i'm always here for you.

for searching you can apply one of the these two techniques :-
1. Exact Record Searching
2. Dictionary Searching