Need a pointer on database in VB6 1

ChrisMacPherson · Jul 21, 2000

This may be a huge question, but can anyone give me a basic description of what I need to do, to incorporate a database in to an app. ie. How do I set up the database(which tool to use?) How do I retrieve data from it. I think SQL and one of the datagrid tools are what I'm looking for, but then again they may not be!! Thanks for any help Chris MacPherson

Vince · Jul 24, 2000

It really depends what you want to accomplish. If you just want to display data from a database on a small scale then you best bet is as you said to use a datagrid combined with a data control. If however you want more complex functionality from your database there are many other methods you can try. there are a lot of examples within the MSDN help files in VB. If you run a search under ADO you will find detailed descriptions of how to connect to a DB and how to retrieve and edit data. If you have any specific questions or requirements I will be happy to answer them or to provide you with an example of a particular method Good Luck

ChrisMacPherson · Jul 25, 2000

Vince,        Thanks for the advice, I will tell you what I want to do, it's very simple! I need the user to be able to fill in a form with details about something,say a website: url,description,rating etc. They will also select a category for it to be placed in. Then this information will be placed in a database. I want the user to be able to access the database entries, and to be able to modify them(I think modify will be the same as first part). This means sorting entries by category and displaying them. My main wish is that the user doesn't need to have access or similar installed. I might be mis-understanding that part though (I dont know how that works**). I have actually acheived this just by using listbox for each field in each entry, but this is probably not the way I should be doing it!! I think there may be a limit to what this can acheive and the number of entries it can handle. **I have worked out how to read data from a .mdb file in to a databound listbox and stuff, but does that limit the application to only users with access? And how would I search through that database? SQL? Thanks again for your time Chris MacPherson <a href=mailto:thedamager@hotmail.com>thedamager@hotmail.com</a> <a href=

http://www.technomusic.org/macpweb>Macpweb</a> Bring

on the new Browza's!! 
Learn/t/ing D\HTML, Javascript, Java, VB5-6, COBOL, Pascal

chiph · Jul 27, 2000

Chris - What kind of user load do you anticipate?  1-5 active users can (somewhat) be handled by MS Access.  Anything more than that, and you ought to be thinking about something like Sybase SQL Anywhere.  More than about 25-50 users, start thinking about buying a database with some horsepower -- Oracle, MS SQL Server, Sybase, etc. > My main wish is that the user doesn't need to have access or similar installed. < If you're planning a VB project (and not a web/IIS project), each user will need the Microsoft ADO files installed on their machines.  I think a lot of them get installed if they already have Internet Explorer 5, or NT Service Pack 6a (just guessing, here). If you think you'll need more than just a datacontrol, I'd recommend you get one of the SQL books I listed in the FAQ.  Probably the last one listed, as you'll need the info on creating SELECT statements (don't worry, it's easy, really!). Chip H.

ChrisMacPherson · Jul 27, 2000

I am not thinking about putting this app on the web, it will just be a small stand-alone application that runs on it's own. There will only therefore be one user. I dont know enough(anything!!) about networks and how they run. I'm pretty confused when it comes to the having access installed bit. You say (I think)that to run a VB app, the user must have the microsoft ADO files installed. Does this mean that their computer will then understand what a .mdb file is? So then it would not matter if I used a .mdb file. Do you think that I'm worrying about nothing here. How do I make sure that they have the ADO files installed? Or is that another kettle of fish?     I'm just concentrating on how to construct and manipulate a simple database that has between say 1 - 500 records, each with around 5 or 6 fields. Also - I'm a full-time student and so cannot really afford to buy books that are so expensive. The prices you quote in dollars are pretty similar to the prices it costs here(U.K) even though £1 = $1.5(roughly). Do you know of any websites,tutorials(apart from the MSDN library) that may cover these topics? Chris MacPherson <a href=mailto:thedamager@hotmail.com>thedamager@hotmail.com</a> <a href=

http://www.technomusic.org/macpweb>Macpweb</a> Bring

on the new Browza's!! 
Learn/t/ing D\HTML, Javascript, Java, VB5-6, COBOL, Pascal

Vince · Jul 28, 2000

If the requirements that you specified above are all that there are then maybe the following code will sort it for you Option Explicit 'This example allows you to Add,Edit and Delete Records from the database table 'It also allows you to sort the records by the use of the combobox control on screen 'The code demonstrates the use of both a bound control and a dynamically populated control using SQL 'To use this example you must 'Have Visual basic 6.0 preferably (Service Pack 3, Freely Downloadable From Microsoft Site) 'NorthWind Database '1. Create a Form '2. Place the following controls on screen '   CONTROL TYPE                            CONTROL NAME '   Microsoft Datagrid control 6.0 SP3          DBGrid1 '   Microsoft ADO Data Control 6.0 SP3          Adodc1 '   Command Button                              CmdRefresh '   ComboBox                                    CmbOrder 'You must set the following properties ' ADO DataControl '   Connection String: Select Build, Microsoft Jet 4.0 OleDB Provider '                      Browse to the location of the Nwind.mdb and Select it '                      Test the connection, Select OK if Connection Works '   RecordSource:      Select AdCmdTable as the Command Type '                      Select A Table From The Drop DownList ' DataGrid Control '   DataSource:        Select ADODC1 From the drop down list '                      (It should be the only item in the list) '   AllowUpdate:       Set this to true if you require the '                      user to have edit privilages '   AllowAddNew:       Set this to true if you require the '                      user to have Insert privilages '   AllowDelete:       Set this to true if you require the '                      user to have edit privilages 'To Check that the Connection And All of the controls are configured correctly 'Right Click on the datagrid and Select Retrieve Fields. If the column Headers appear 'in the grid then all is well 'To use this method with a non-local Access Database you will require a Mapped Drive 'or a correct UNC (\\Machine Name\FullPath\Test.mdb) 'You can get these through the connection wizard on the data control by Looking in 'your network neighbourhood Private Sub cmdRefresh_Click() Dim strSQL As String If cmbOrder.Text <> "" Then     'Check that something is in the combobox     Adodc1.CommandType = adCmdText     'Change the command type of the ADO data control         'The type set oigionally was ADOCmdTable this only allow the return of         'complete tables chaging it to adCmdText allows you to execute SQL against it     strSQL = "Select * From Employees Order By " & cmbOrder.Text & " Asc"     'Create the SQL from the combobox entry     Adodc1.RecordSource = strSQL     'Make the new SQL the recordsource of the Datacontrol     Adodc1.Refresh     'Refresh the control     DbGrid1.Refresh     'Refresh the grid     'You must do both to display the changes End If End Sub Private Sub Form_Load() Dim x As Integer For x = 0 To DbGrid1.Columns.Count - 1     'Loop through the columns collection and place the name of each into     'The combobox for user selection     cmbOrder.AddItem DbGrid1.Columns(x).DataField Next x End Sub

ChrisMacPherson · Jul 28, 2000

Vince,      Thanks a lot for this code!! I will be testing it out today or tommorrow and will get back to you on how it works and if it is what I need. It sounds like it will be though. Thanks again!!! Chris MacPherson <a href=mailto:thedamager@hotmail.com>thedamager@hotmail.com</a> <a href=

http://www.technomusic.org/macpweb>Macpweb</a> Bring

on the new Browza's!! 
Learn/t/ing D\HTML, Javascript, Java, VB5-6, COBOL, Pascal

chiph · Jul 28, 2000

> How do I make sure that they have the ADO files installed? Or is that another kettle of fish? < Installation of programs (at least in a production environment) is indeed another kettle of fish.  For a one-off program, you can handle this manually.  Go to <A HREF="

http://www.microsoft.com/data"

TARGET="_new">

http://www.microsoft.com/data</A>

and click the "downloads" link.  You can then download the MDAC (MS Data Access something-that-starts-with-the-letter-'c'-this-week) and install it.  It's a big file(s), but it should have everything you need. > The prices you quote in dollars are pretty similar to the prices it costs here(U.K) even though £1 = $1.5(roughly) < Ooops, didn't know you were in the UK.  Book prices there are a bit steep, aren't they?  I'm always amazed at the price of magazines in the train stations.  I don't know of any better site to learn this stuff than hanging around here at Tek-Tips and asking lots of questions.  I think that Doug & Dave (the guys who are Tecumseh Group) are working on having file archives added, but they're a ways away from having that up & running. Chip H.

ChrisMacPherson · Jul 28, 2000

Hello again!!             You are probably right Chip, I have learnt so much from people here, that it's become an addiction to check out all the latest threads!!            Vince, thanks, that code works fine and shows me how to connect the databse, and I think add and delete records. But, I only want the database to be in the background(not visible) and I want to read data from the database in to seperate boxes so that I can arrange them around the interface. I think (from your code) that I can read data( and maybe add data) using the statements like 'DBGrid1.Columns(x).DataField' to navigate the grid. I think that was what I was really asking as well in the beginning. (It's hard to ask the right questions!!) So, let me try and see if I have got this right. I understand now how to connect the database to the grid. If I want to read the data from column 3, row 4, can I write: variablename = DBGrid1.columns(3).rows(4).text/value (I'm not sure on that bit) Will I be able to set it that way as well? Again thanks for your time    Chris MacPherson <a href=mailto:thedamager@hotmail.com>thedamager@hotmail.com</a> <a href=

http://www.technomusic.org/macpweb>Macpweb</a> Bring

on the new Browza's!! 
Learn/t/ing D\HTML, Javascript, Java, VB5-6, COBOL, Pascal

ChrisMacPherson · Jul 29, 2000

I have just been trying these things out and have worked out how to put text in to and read it out of any cell. I also came accross two methods(I think) to do with AddNew, which I guess is when you add a new record. Can you possibly tell me if this is about adding a new record because I cant find an actual AddNew statement to add another record and I dont know what format the data would have to be in to add it? i.e in a string or in six different strings, one for each field(I'm a bit lost, but starting to get the idea) Thanks a lot. I will be away for a week so I wont be able to test it out for that time! Chris MacPherson <a href=mailto:thedamager@hotmail.com>thedamager@hotmail.com</a> <a href=

http://www.technomusic.org/macpweb>Macpweb</a> Bring

on the new Browza's!! 
Learn/t/ing D\HTML, Javascript, Java, VB5-6, COBOL, Pascal

Vince · Jul 31, 2000

The add new method works as follows 'Create a recordset dim rs as recordset 'set the recordset = to the datasource recordset set rs = adodc1.recordset 'to add a new record rs.addnew 'This must be done for each field rs!Fieldname = text1.text 'You must ensure that the data in the textbox is the 'correct data type for that field 'You can do this by using the conversion method supplied 'by VB eg to convert it to an integer use the folloing ' Cint(text1.text) 'Another thing that I should have mentioned before id that 'if you are using textboxes you can ibd them to the data 'control so that you don't have to have the grid. 'You should also look into how to create a connection 'through code Dim cn as ADODB.connection set cn = new ADODB.connection    with cn       .connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"       .open(Open the connection) if cn.state = adstateopen then    msgbox"Connected" else    msgbox"Could not connect" end if once you have created a connection through code you can attach any number of recordsets to it dim rs as ADODB.recordset set rs = new ADODB.recordset    with rs       .ActiveConnection = cn       .cursortype = AdOpenDynamic       .source = "Select * From Employees" (Place any SQL in Here)       .open    end with You can then assign individual members of the recordset to other controls text1.text = rs!FirstName text2.text = rs!Lastname You can move through the recordset in the following way rs.movenext check for the end of the recordset by using if rs.eof then     msgbox "There are no more records" end if If you are going to use this a lot then I would suggest that you get a book on the use of ADO WROX press havea book called ADO 2.1 which is very good. hope this helps       end with

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need a pointer on database in VB6 1

ChrisMacPherson

Programmer

Vince

Programmer

ChrisMacPherson

Programmer

chiph

Programmer

ChrisMacPherson

Programmer

Vince

Programmer

ChrisMacPherson

Programmer

chiph

Programmer

ChrisMacPherson

Programmer

ChrisMacPherson

Programmer

Vince

Programmer

Similar threads

Part and Inventory Search

Sponsor