locked
Get information from database

    Question

  • Hello

    I was using Visual Basic 6 and when I want to get information from a database, I write:

    Dim Con As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    Set Con = New ADODB.Connection
    Set rst = New ADODB.Recordset

    Con.Open "Provider=MSDataShape.1;Extended Properties='Jet OLEDB:Database Password=111';Persist Security Info=False;Data Source=D:\Programs.mdb;Data Provider=MICROSOFT.JET.OLEDB.4.0"

    rst.ActiveConnection = Con
    rst.Open "Select tab1.* from tab1 wehre tab1.Name='Example'"

    MsgBox rst.Fields("FileName")

    When I wrote that in VB 2005, it said about this line (MsgBox rst.Fields("Dir")): Argument 'Prompt' cannot be convered to type 'String'

     

    Can anybode fix it in VB 2005 for me, please?

     

    Wednesday, March 29, 2006 1:19 AM

Answers

  • Assuming you want to keep using ADO, if the rest of this worked, then I would presume you just need to cast the value to string, probably by calling it's ToString() method.

    However, you'd do better to move your data access code to ADO.NET, if you can.

     

    Wednesday, March 29, 2006 1:23 AM
  • They are in to toolbox.  To be frank, this is about as detailed as the help here can get.  If you're still having trouble, you probably need to take a step back and learn some fundamentals about the IDE and the language ( preferably via a book ) before trying to work with a database.

     

    Wednesday, March 29, 2006 11:00 PM

All replies

  • Assuming you want to keep using ADO, if the rest of this worked, then I would presume you just need to cast the value to string, probably by calling it's ToString() method.

    However, you'd do better to move your data access code to ADO.NET, if you can.

     

    Wednesday, March 29, 2006 1:23 AM
  • I didn't get it.

    What's ToString?

    Sorry, but I am a new of VB 2005

    Wednesday, March 29, 2006 1:40 AM
  • ToString is a method that all objects in .NET support.  It returns a string :-)
    Wednesday, March 29, 2006 1:41 AM
  • OK

    Can you give me links and examples about ADO.NET, please?

     

    Regards

    Wednesday, March 29, 2006 2:08 AM
  • In ADO.NET, here are the general steps. (Compare these to your example above)

    Look on the Data tab of your toolbox to find these objects...

    1) Define a Connection object. (This specifices where your database is located, and how you are going to read from it, such as by using SQL)

    2) Define 1 or more Data Adapter objects  (These determine which table(s) you will be pulling data from.)

    3) Define a dataset to store your information in. (Basically, a copy of one or more tables, but stored locally. ADO.NET allows you to work "offline" from your database, making additions, changes, and deletions, then you can save your changes all at once.)

    4) Use the Data Adapter Fill method(s) to fill your dataset

    5) You can bind the fields in your dataset to one or more controls like textboxes, datagrids, etc for ease of display or editing. (Using the DataSource to specify a table in your dataset, and Datamember to specify a record.)  You can also work with items in the dataset directly, through programming. (To sum up the value of inventory, for example.)

    6) When you're done, you can write just those changes needing to be made back to your dataset.

     

     

    Wednesday, March 29, 2006 3:52 PM
  • Taking your example a line at a time.....

    Dim Con As New ADODB.Connection

    Drag a new OleDbConnection object onto your form... Name it "Con" if you like...

    Dim rst As New ADODB.Recordset

    Drag a new OleDbDataAdapter object onto your form...  Name it "DBAdapter" if you like
    Drag a new Dataset object onto your form     (Replaces your recordset)    Name it "dst"...

    Set Con = New ADODB.Connection

    Define the connection string here for the "Con" object you added above
    Con.ConnectionString="Provider=MSDataShape.1;Extended Properties='Jet OLEDB:Database Password=111';Persist Security Info=False;Data Source=D:\Programs.mdb;Data Provider=MICROSOFT.JET.OLEDB.4.0"

    Con.Open "Provider=MSDataShape.1;Extended Properties='Jet OLEDB:Database Password=111';Persist Security Info=False;Data Source=D:\Programs.mdb;Data Provider=MICROSOFT.JET.OLEDB.4.0"

    In ADO.NET, the connection can be automatically opened as needed to read/write, improving access and sharing. (It doesn't have to, you can manually open/close a connection.)  So no code needed for this line.

    rst.ActiveConnection = Con

    Ditto

    rst.Open "Select tab1.* from tab1 wehre tab1.Name='Example'"

    Define our adapter:
    DBAdapter.SelectCommand.CommandText = "Select tab1.* from tab1 wehre tab1.Name='Example'"

    Now Fill the dataset. (This opens the connection, reads, then closes it. Neat, huh?)
    DBAdapter.Fill(dst)

    MsgBox rst.Fields("FileName")

    Umm, not sure if you can assign multiple fields at once.... If not, just use a for-next loop to build a string. Or simply make a listbox and assign the Datasource as Dst, and data member as your field "Filename".

    Thompson Course Technology has a nice easy book you can use to learn about databases in VB.NET: "Visual Basic .NET: An Object Oriented Approach."  Very good examples, and it walks you through step by step.

    Wednesday, March 29, 2006 4:11 PM
  • Thank you David for your wondeful reply.

    But I am a beginner of VB 2005.

    I don't know how to drag a new Dataset and OleDbDataAdapter onto my form.

    Can you tell me how, please?

    Wednesday, March 29, 2006 10:56 PM
  • They are in to toolbox.  To be frank, this is about as detailed as the help here can get.  If you're still having trouble, you probably need to take a step back and learn some fundamentals about the IDE and the language ( preferably via a book ) before trying to work with a database.

     

    Wednesday, March 29, 2006 11:00 PM
  • I can connect to database in VB 6.

    But I faced a trouble in VB 2005.

    VB 2005 didn't accept this line "MsgBox (rst.Fields("Dir"))"

    I don't know why.

    Wednesday, March 29, 2006 11:08 PM
  • First of all, MsgBox does not exist in VB.NET, unless it's one of these magic VB shortcuts.  It's MessageBox.Show.  Then, rst.Fields("Dir") probably needs to be cast to a string.

    I would recommend finding a website that covers the differences between VB6 and VB2005. 

    http://www.google.com.au/search?hl=en&q=vb6+VB.NET+2005+upgrade&meta=

    there seem to be a few in there.

     

    Wednesday, March 29, 2006 11:10 PM
  •  

    "First of all, MsgBox does not exist in VB.NET, unless it's one of these magic VB shortcuts. "

    Ummm I use msgbox all the time in vs2005 VB.

    But I'm really wondering if recordsets actually exit in ADO.NET.

    I've been doing a lot of access and ADO.NET work lately and I seen rows but not record sets.

     

    Wednesday, March 29, 2006 11:44 PM
  • // Ummm I use msgbox all the time.

    You're right.  It 's in the VisualBasic namespace, a dll that exists to drag VB6 stuff into .NET.  So, I've never seen it, as I use C# mostly.  *blush*

    // But I'm really wondering if recordsets actually exit in ADO.NET.

    I think he's using ADO somehow.  His original question was that the message box could not turn object into string, that's why I suggested using tostring, at the top of this thread.  I only spotted the MsgBox thing today when I was trying to work out why he still had trouble.

     

    Wednesday, March 29, 2006 11:50 PM
  • Msgbox definately exists in the VB.Net language

    Recordsets can exist if you are using ADO (which you can still use although for most purposes the features of ADO.NET allow things to be done better/simpler.

    But recordsets do not exist in ADO.NET object model.

     

     

    Wednesday, March 29, 2006 11:51 PM
  • Be careful if you choose to use recordsets in ADO.NET!  ADO.NET does support Reading a recordset, but updates are not supported. You're better off to change over to datasets if you're going to using .NET.

    In .NET, the Dataset replaces the old Recordset type.

    Dataset is a bit more detailed, and contains more methods of looking at data, but the old familiar methods are there for the most part. You can still "drill down" to the level that you want, such as

    pay = dataset1.Tables("Employees").Rows(1).Item("Pay")

    A Recordset is basically one table (IIRC...), while a dataset can contain multiple tables.

    For the most part, you can get away with simply changing the declaration if you are upgrading an old application. Then you just hunt down those lines and modify references as needed.  Datasets are powerful, in that when you "Drill down", you can use both an Integer Index (Row 1 above) or the string name of a field.

    Regardless, ADO.NET is worth learning... hopefully you can pick up the basics using the examples above.

    Thursday, March 30, 2006 2:36 PM
  • Can anybody give a sample file about database?

     

    regards

    Thursday, March 30, 2006 2:39 PM
  • www.google.com

    msdn.microsoft.com

    There is 2 sites that will learn you anything you need to know.  Just search and read.  You'll figure it out.  Hell that's 90% of the fun of it, isn't it?  8-)  Good luck!

    Thursday, March 30, 2006 2:43 PM
  • Try http://www.w3schools.com/ado/default.asp

    An all around excellent site for learning the basics of a ton of programming...

    Thursday, March 30, 2006 4:25 PM
  • Absolutely, that is where I go for help with PHP and MySQL when I have the "pleasure" to work with those system.
    Thursday, March 30, 2006 10:12 PM
  • I just want somebody to fix it for me.

    This code works completely in VB 6 but doesn't work in VB 2005

    Dim Con As New ADODB.Connection

    Dim rst As New ADODB.Recordset

    Con = New ADODB.Connection

    rst = New ADODB.Recordset

    Con.Open("Provider=MSDataShape.1;Extended Properties='Jet OLEDB:Database Password=123456;Persist Security Info=False;Data Source=D:\Programs.mdb;Data Provider=MICROSOFT.JET.OLEDB.4.0")

    rst.ActiveConnection = Con

    rst.Open("Select tab1.* from tab1 where tab1.Name='Word'")

    MsgBox(rst.Fields("FName"))

    VB 2005 says that this line (MsgBox(rst.Fields("FName")))  is "Argument 'Prompt' cannot be converted to type 'String'."

    Can anybody fix it for me, please?

    Saturday, April 01, 2006 9:52 PM
  • ARGH !!!!!

    Have you tried what I told you the first day you posted this ? If so, you've never said anything about it. 

    MsgBox(rst.Fields("FName").ToString())

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingadofromadonet.asp

    is a page covering using ADO in .NET. 

    Sunday, April 02, 2006 11:56 PM
  • have you tried this code here?????


    (MsgBox rst.Fields("Dir").value)
    Saturday, August 23, 2008 10:46 AM