Newbie Database Question

Newbie Database Question

• Shidoshi

  Paul~
  
  Thanks for your help!
  
  I'm using VB 2005 (part of VS 2005).
  
  The file that contains the table is named "CCUG PreOp". I don't think it ever got a short name in the IDE. The column I'm filtering on is called "p000Number" - it's the first column, and the key for the table.
  
  Not to push my luck, but I see the MS after your screen name so you're probably the guy I want. I dug through the code examples on the web site/help page and did the walkthrough about using data sources, which involved creating an addressbook database, linking it to a project, and displaying the full grid in the project. I went back and did the same steps with my 15 row 8 column database (the same one as above) and created an .mdf (SQL) file instead of a .mdb (Access) file. I was able to get to the same point in a new project - I got my PreOp data to show up as a grid on a form, but that didn't solve my actual problem. For purposes of conversation, the table in mdf format is called simply "PO".
  
  A few questions (including a question based on your earlier post):
  
  1) Is there an easy way to convert an Access table or an Excel spreadsheet into the correct format for an .mdf table I could cut and past individual cells, but not ranges, and neither Excel nor Access has an export or save as function for .mdf files. I ended up doing a lot of re-typing and cutting and pasting (and forget about carriage returns)...
  
  2) Once I have the table attached and the grid on the form, how do I run a query This is a slightly different question than my initial question - this is about an .mdf table that's already connected to a form. I'd still want the same outcome, which would be to find a record with "p06" or "p11" (or whatever) in column1 (called p000Number), then turn the other 7 cells associated with that row into variables (Name = <contents of cell in column 2>, option1 = <contents of cell in column 3>, etc)
  
  3) Do I have to have that grid in the form I assume I can just make it invisible, but is the step of including it necessary
  
  4) It doesn't seem possible to delete a datasource, although the help system says you can. There's no delete option with right-clicking. I can delete from the "Server Explorer" on the left of the IDE, but not from the "Data Sources" tab on the right - and it just ends up repopulating the Server Explorer when I reopen the project.
  
  5) I like your idea of having the software do the heavy lifting. Your initial advice was:
  

  You should be able to automatically create the TableAdapters that fill datasets and the strongly typed datasets to store the data.  Create an additional query called FillByMyParameter(@Parameter).  Then you can write code like this:
  
  Dim ds as new CCUGDB
  Dim ta as new customersTableAdapter

  ta.FillByMyParameter(ds.Customers, "p06")

  a = ds.Customers(0).Column2
  b = ds.Customers(0).Column3

  This was back when I was working with an Access table. I never tried this, because I wasn't sure how to create the query in the first place (and that seemed to be step 1). I actually have a reasonable amount of programming experience, but none of it seems to translate to the new environment, and I think I'm basically starting over again and need a little handholding...
  
  Anyhow, thanks again for your help! I'm interested in getting over this hump. I was actually able to basically complete the sample project by hard-coding all of the options (no big deal with a small dataset, but will be an issue if I can't access larger tables in other projects) and the rest of the programming was surprisingly easy. This should be even funner and easier once I can access data in tables. There has got to be an easy way to do that without lots of programming gymnastics - I just can't seem to find it.
  
  Thanks again,
  
  ~Jeff
  
  

• smc750

  Paul~
  
  Thank you!
  
  I think I'm getting close. I made some modifications for the details of my project (pathnames, passed variables, etc), and I think I'm just a few errors away from getting it to work.
  
  Currently, I get an error saying that "Customers" isn't a valid table. I assume this was a holdover from a Northwind sample, so I've tried changing it to anything I could think of, but I keep getting the same basic error, no matter what I name it. I've tried MyConnection, MyConnectionString, CCUG Guide, p000Number - it doesn't seem to matter. I've put it back to "Customers" in the code sample below for purposes of illustration.
  
  Column 1 in my table is the identifier for the row - p01, p02, etc up to p15.
  Column 2 is called "Name," and each cell is a string.
  Column 3 is called "Option 1" and each cell is a string.
  
      Public Sub ReadPORow(ByVal POCurrentSelection As String)
          Dim myConnectionString As String
          myConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=c:\Projects\CCU Guide\CCUG PreOp.mdb;"
          Dim myConnection As New OleDb.OleDbConnection(myConnectionString)
          Dim mySelectQuery As String = "SELECT * FROM Customers WHERE p000Number = POCurrentSelection"
          Dim myCommand As New OleDb.OleDbCommand(mySelectQuery)
          myCommand.Connection = myConnection
          myConnection.Open()
          Dim dr As OleDb.OleDbDataReader = myCommand.ExecuteReader()
          Dim a, b, c As String
          If dr.Read() Then
              a = dr("Name").ToString()
              b = dr("Option 1").ToString()
              TextBox2.Text = TextBox2.Text + a + b 'just for visual confirmation
          End If
          dr.Close()
          myCommand.Connection.Close()
      End Sub
  
  Any suggestions
  
  Thanks again,
  
  ~Jeff
  
  

• Art Art

  Paul~
  
  I think we're getting closer!!
  
  Now I get this error:
  
  Keyword not supported: 'provider'
  ---
  When I remove the provider= segment (but leave Data Source=), I get the error:
  
  An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
  ---
  
  Here's the code as it currently stands (PO is the name of the database):
  
  Imports System.Data.SqlClient
  
  Public Class Form1
      Private Sub POBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles POBindingNavigatorSaveItem.Click
          Me.Validate()
          Me.POBindingSource.EndEdit()
          Me.POTableAdapter.Update(Me.CCUG_PreOpDataSet.PO)
      End Sub
  
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          'TODO: This line of code loads data into the 'CCUG_PreOpDataSet.PO' table. You can move, or remove it, as needed.
          Me.POTableAdapter.Fill(Me.CCUG_PreOpDataSet.PO)
          Dim b As String
          Dim c As String
          Dim con As New SqlConnection("'Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Documents and Settings\Jeff\My Documents\Visual Studio 2005\Projects\PreOp\PreOp\CCUG_PreOp.mdf")
          con.open()
          Dim cmd As New SqlCommand("SELECT * FROM PO WHERE p000Number = 'p06'", con)
          Dim reader As sqlDataReader = cmd.ExecuteReader()
          If reader.Read() Then
              b = reader("Name").ToString()
              c = reader("Option1").ToString()
              TextBox1.Text = b
              TextBox2.Text = c
          End If
      End Sub
  End Class
  
  Thanks again,
  
  ~Jeff
  
  
  

• paulconstantine

  Hi,
  
  Try this:
  
  
  Public Sub InsertRow(myConnectionString As String)
  ' If the connection string is null, use a default.
  If myConnectionString = "" Then
  myConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=c:\yourmdblocation.mdb;"
  End If
  Dim myConnection As New OleDbConnection(myConnectionString)
  Dim mySelectQuery As String = "SELECT * FROM Customers WHERE <yoursearchcolumn> = <yourvalue>"
  Dim myCommand As New OleDbCommand(mySelectQuery)
  myCommand.Connection = myConnection
  myConnection.Open()
  Dim dr As OleDbDataReader = myCommand.ExecuteReader()
  Dim a,b,c As String
  if dr.read() then
  a = dr("field2").ToString()
  b = dr("field3").ToString()
  end if
  dr.close()
  myCommand.Connection.Close()
  End Sub
  
  
  cheers,
  
  Paul June A. Domag
  
  
  

• nitin.dac

  Paul,
  
  If I promise to name it "Paul_Rocks_DB" will you tell me how to do it
  
  Thanks,
  
  ~Jeff
  
  

• s0kol

  Paul~
  
  Can you (or can anybody) recommend somewhere or someone who can answer this question
  
  The question is probably lost now in the midst of all these postings. This was it:
  
  I'd like to open a database (one that I've already included in the project); either in mdf (SQL) or mdb (Access) format. Let's call the database "PO_Data". It's a flat table with 15 rows and 8 columns. Each row is a separate record.
  
  I'd like to search for the row in the database with "p06" as the contents of the first cell. Let's call that column "p000Number." If we're in SQL, that column is the key.
  
  Once I find that record, I'd like to read the other 7 columns of that record into variables. Column 2 is named "Name" and contains string info, column 3 is named "Option1" and contains string info, and so on.
  
  Then I'd like to close the database and go on with the rest of the code.
  
  I'm using VB 2005 from VS 2005.
  
  Pseudocode:
  
  Open "PO_Data" as DB
  Find row with "p06" in column "p000Number"
  b = column2.text
  c = column3.text
  d = column4.text
  <etc>
  Close "PO_Data"
  
  Thanks,
  
  ~Jeff Porcaro
  
  
  
  

• AdamZ

  Paul~
  
  Thanks again. I'm not sure if it has a name - I don't think I assigned it one, so whatever the default was is what it still is. If I could delete it and re-install it I'd assign it one this time.
  
  For purposes of discussion, let's assume I'm starting over (which I will do) and doing it right, and I name the database "PO_Data".
  
  ~Jeff
  
  

• JohnRi

  It appears to me you are mixing SQL with Access.  To use the other Paul's example you need to put : Imports System.Data.OLEDB at the top of your Class instead of
  Imports System.Data.SQLClient
  When you do, it will open an Access database
  
  This is where the mixup is happening:
  
  Dim con As New SqlConnection("'Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Documents and Settings\Jeff\My Documents\Visual Studio 2005\Projects\PreOp\PreOp\CCUG_PreOp.mdf")
  
  You will have to mofify the the connection string in order to get it to work with an Access Database.
  
  If you are wanting to work with a SLQ database you need to use the:
  Imports System.Data.SQLClient
  
  And correct your connection string.
  
  Take a look at this website: http://www.connectionstrings.com
  
  There you can find connection strings and much more that can help you.
  James
  aka:Trucker
  
  
  

  
  

• Udaypal Aarkoti

  Hi Jeff,
  
  Yes, I'm one of the people who worked on the design of these features, and I'm happy to help!
  
  One more quick question for you before I form a response:  what is the name of the table you are using in access   (you've told me the database file name and the column, but not the table). 
  
  Best,
  Paul
  

• CV.

  Hi,
  
  Ok, Try this:
  
  ' Open "PO_Data" as DB
  Dim con As New OleDbConnection("PROVIDER=Microsoft.Jet.OleDb.4.0;Data Source=C:\location of your db")
  con.open
  ' Find row with "p06" in column "p000Number"
  Dim cmd As New OleDbCommand("SELECT * FROM yourtabletosearch WHERE p000Number = 'p06'", con)
  Dim reader As OleDbDataReader = cmd.ExecuteReader()
  if reader.Read() then
      b = reader("Name").ToString()
      c = reader("Option1").ToString()
      ... etc.
  end if
  
  
  cheers,
  
  Paul June A. Domag
  
  
  

• Rachana Rao

  Hi,
  
  Paul's suggestion will work well.  It requires a lot of hand-coding. 
  
  If you have VB 2005 you can let it do a lot of the heavy lifting and write much less code.
  
  Please check out- How to connect to an existing database
  
  
  And then- How to work with TableAdapters: 
  http://msdn2.microsoft.com/en-US/library/bz9tthwx.aspx
  http://msdn2.microsoft.com/en-US/library/ms171920.aspx
  
  
  You should be able to automatically create the TableAdapters that fill datasets and the strongly typed datasets to store the data.  Create an additional query called FillByMyParameter(@Parameter).  Then you can write code like this:
  
  Dim ds as new CCUGDB
  Dim ta as new customersTableAdapter

  ta.FillByMyParameter(ds.Customers, "p06")

  a = ds.Customers(0).Column2
  b = ds.Customers(0).Column3
  
  Hope this helps.
  
  Paul

  
  

• Claus P

  Hi,
  
  Actually you don't have to add the mdb to your project. You can just access it or refer to it in your connection string without adding it to ther project. Im not familiar with mdf's but mdf is a sqlserver database file so you'll have to use SqlClient objects (just replace OleDb with Sql, OleDbConnection - SqlConnection, etc).
  
  On your second inquiry, you'll need to import the OleDb Namespace to be able to use the classes without specifying OleDb. Just place Imports System.Data.SqlClient above your code (outside the class).
  
  
  
  cheers,
  
  Paul June A. Domag
  
  
  

• AARONSANDY

  Hi Jeff,
  
  Yes, our samples are based on Northwind and use fictious tables like "Customers".  We need to modify the above code to match your database and your dataset.
  
  For starters, what version of VS or VB are you using (2002, 2003 or 2005)   I can steer my answer based on that.
  
  Other questions:  What is the name of the table you want to program against   What is the name of the column you want to filter your results on  
  
  thanks,
  Paul
  

• KevinH_MS

  Paul~
  
  Thank you for hanging in here with me on this - I think the other Paul has left the building...
  
  I'm now having two separate problems. The first isn't really a problem by itself - thank you for choosing this by default, actually. The issue is that your code seems to be written for mdb files, which I thought was going to be my preferred option.
  
  The second, however, is that I can't seem to get a mdb file imported to save my life. I keep getting unrecognized format problems and unable to create tables problems and a few others, and the files that I do get imported seem to be empty. It doesn't seem to matter how I do it; the mdb file works beautifully in Access 2003 but seems incompatible with VS 2005. I've done Windows Update and everything I can think of to help compatibility, but to no avail. I'm sure it's an idiot mistake that we'll all laugh about over beers in years to come, but so far no luck.
  
  Is there a similar code snippet you can suggest for mdf files I have managed to successfully import an mdf file in this project, so maybe there's hope.
  
  Finally, I get compiler errors with your code, only in that "OleDbCommand", for example, seems unsupported - it insists I add "OleDb." to the front of these statements - resulting in OleDb.OleDbCommand in my final code.
  
  Thanks again,
  
  ~Jeff
  
  
• Newbie Database Question