VB SQL - Fieldname,etc - simple PROBLEM

VB SQL - Fieldname,etc - simple PROBLEM

• justin000

  There is a problem with your sql statement. I usually dont use number for column name and would suggest renaming the columns if possible. Try placing the column name in [] so it knows to look in your column named 3 because 3 will never equal 12.
  
  Select * FROM ninesv where [3] = 12
  
  In the future you should ask vb4 questions in the vb newsgroups this forum is for vb.net questions.
  
  
  

• M. Lohmeijer

  Spotty, thanksfor the heads up, will try your suggestions, many thanks!
  

• HT

  A few comments on this post.

  
  Comment 1 - these forums are for VB.NET and there are better places to find answers for older versions of VB. Maybe the VB6 newgroups - http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.vb.general.discussion&lang=en&cr=US

  Comment 2 - This form of design for a lookup table, I've seen this a lot and it is rather a poor design for relational databases. It normally occurs as people look at either existing paper documents or spreadsheets and try and mimic these rather than think about how is best to achieve the result.

  Comment 3 - Limitations on this lookup means that you have a finite amount of lookup fields you can store, if you want to store more than 5 fields you have to add fields and then adjust your code/queries etc. and its not particularly efficient to search them. A lot more work and more inefficient SQL.

  Comment 4 - Now to try and give you some directions on achieving you result. Access SQL is different from SQL Server/SQL Express SQL so you may have to work a little to get the code working in Access, although this code of the stored procedure code should give you a pointer.

  ALTER PROCEDURE dbo.StoredProcedure1
  @a varchar(10)
  AS
  
  Select P,
  case @a
  when <TableName>.P101 then 1
  when <TableName>.P102 then 2
  when <TableName>.P103 then 3
  when <TableName>.P104 then 4
  when <TableName>.P105 then 5
  else ''
  end
  From <TableName>
  WHERE <TableName>.P101 = @a OR
  <TableName>.P102 = @a OR
  <TableName>.P103 = @a OR
  <TableName>.P104 = @a OR
  <TableName>.P105 = @a
  

  Now if you were to change your lookup table to have a simpler layout - something like this

  P, Type, Value

  with you values stored

  1 P101 1286
  1 P102 2847
  1 P103 5288
  1 P104 2299
  1 P105 3300
  2 P101 3101
  ...

  Your code would be so much simple

  Select P, Type
  FROM <Tablename>
  WHERE <TableName>.Value = @a

  This would allow you to have as many lookups for each P item and not be dependent upon adding fields if you want more than 5 items per value of P. The code is so much simpler to get the lookup values.

  If you wanted to report all the lookups for Say P = 1 then you would use a SQL Statement such as where @a is set = 1

  Select Value
  FROM <Tablename>
  WHERE <TableName>.P = @a

  
  So that should give you a pointer in some changes you should seriously consider for you lookup tables.

  
  Comment 4 - for what you appear to be showing about values in the database being bound to controls. This is a feature called data binding which enables to to associate the values of controls to fields in a dataset or database fairly easily with minimal code.

  However this feature is not something that was around in VB4 (or if it was it was a very poor implementation in comparison). So you may be out of luck for a simple solution to achieving this using databinding. You would probably have to code you own routines to set values from a dataset to controls properties and vice versa.

  The solution to this problem would be to get with the program an try out the VB Express and SQL Express products which are the latest release. These express products are FREE, yes I did say free to download and are full functionality. You will have data binding which will make Question2 very simple.

  
  

• tryToLearn

  thanks Ken!
  

• Stephen L

  VB SQL - Fieldname,etc - simple PROBLEM
  Having trouble with what should be a ridiculously easy small, brief routine in VB. Any insight, would be really helpful.
  All Im trying to do is have VB pull data from two databases, two different ways;

  
  database (1)
  Simple table, with entries such as (strucuture of db);

  P 101 102 103 104 105
  -------------------------------------------------------------------
  1 1286 2847 5288 2299 3300
  2 3101 3024 3503 3024 2305
  3 3016 3074 3058 3029 8310
  4 3111 3142 3135 3214 3315
  5 3116 3417 3518 2319 9400

  
  The above is the table. It is only a static lookup table, no writing or updating will occur, where column 'P' is the primary key field.
  VB generates a number through a different internal process, this number will exist in the table within database (1). I want VB to return the field name the number
  is found in, along with the primary key field number. I won't know which field the number will be in, (unless I were to open the database manually and scan
  all the fields). The field names in this table are numeric, that is to say in the above table;
  Field Name 1 = 101, Field Name 2 = 102, Field Name 3 = 103, Field Name 4 = 104, etc....
  So the VB app generates say the number '3214', I want VB to search the entire table for the number 314, returning the fieldname it is found in and the corresponding
  primaryukey index number, so the correct answer would be;
  3214 = FieldName ; " 104 " and Primarykey Index number = 4
  Then I want the values, "104, 4" stored in a variable and stuck in a text box or label.

  Thats its for database (1).

  So here is what is driving me buggy;

  My constraint is I'm using VB 4.0 professional version. Yes I know its old, but due to my current circumstances I am unable to upgrade. The database used is Access MDB, the Jet version, 3.0.
  To do the above should be really simple, I've tried all kinds of different approaches. I've poured through the massive VB tips and tricks HLP file, the huge
  VB BUGS hlp, the online standard HLP, various textbooks, and I can't find any clear guidelines that I can grok to figure out how to implement this.
  I'm probably just missing something obvious, I can be a bit dense and slow sometimes but usually can there by plodding. I've been plodding on this one for a while, and have made some progress but am just simply stuck at the moment.
  I've tried using recordsets, the data control, snapshots, dynasets, SQL, etc.... often get really weird big error reports. Checked the VB error dox, and found there was a lot of misleading, erroneous bungles reported on the use and intermixing of all these.... Syntax, methods, properties that are reported to work a certain way, don't.
  Ideally, I'd just like to use a recordset, pass through an SQL statement and get the data I want. I've tried using 'findfirst', seek, etc.... I'm open to these instead of SQL if need be, IF they will work. I can't get seek to do what I want.
  Talked with a guy I know who's an SQL jockey ( I'm not), he tells me I need to know the fieldname in advance if I want to pull the number back with its field name,
  I thought using a Select * (all) would let me just pick the whole table, wanting the routine to just automatically search all fields until it finds the value Im after and then tells me the field name, is this not possible Do I have to specify each and every field name explicitly to search through, if so that seems a bit frustrating.....
  Could someone show me some code that will perform this basic operation, please

  For database (2)

  Simple table, with entries such as (strucuture of db);

  LOOK
  UP
  CODE# Text 1 Text 2 Text 3
  --------------------------------------------------------------------------------------------
  1286 Text Entry 1 Text Entry 2 Text Entry 3
  3101 Text Entry 1 Text Entry 2 Text Entry 3
  3016 Text Entry 1 Text Entry 2 Text Entry 3
  3111 Text Entry 1 Text Entry 2 Text Entry 3
  3116 Text Entry 1 Text Entry 2 Text Entry 3

  
  This table has only four fields. Field one is "LOOK UP CODE #", Field two is Text 1, Field three is Text 2, Field four is Text 3
  The VB app generates a look up code #. It then pass this number to the database table, the corresponding text entries found
  in fields Text 1,Text 2,Text 3, are returned. Each text entry is stored in a different variable, and each variable is displayed to a different
  textbox, i.e.

  
  var1 = Text1.Text Entry 1
  var2 = Text2.Text Entry 2
  var3 = Text3.Text Entry 3

  textbox1.text = var1
  textbox1.text = var2
  textbox1.text = var3

  Ideally, where ever SQL can be used in conjunction with a recordset is appealing, since its the fastest, but I am most concerned about functionality, just getting this to work without bursting anymore blood vessels in my head, if using seek or some other approach does the trick Im open to this.
  I've included some of the code I've been tweaing with below, but its not very pretty and it doesnt do what I describe above, really.... just shows some of the stuff I've been trying .... included as a reference to give an idea as to where I'm at....

  
  Thanks for any responses.... I know this sounds ridiculous but I've been working on this for quite a while, not making much headway.....

  Best regards

  P

  Private Sub Command8_Click()

  Dim MyDatabase As Database

  Dim MyRecordset As Recordset, MyField As Field

  Dim MySQL As String, I As Integer

  Dim MyTable As Recordset

  
  Set MyDatabase = Workspaces(0).OpenDatabase("C:\TEST.MDB")

  Set MyRecordset = MyDatabase.OpenRecordset("TABLENAME", dbOpenTable) ' Open table.

  MyRecordset.Index = "PrimaryKey" ' Define current index.

  MyRecordset.Seek "=", "12" ' Seek record.

  If MyRecordset.NoMatch Then

  
  MsgBox "match was not found"

  Else

  MsgBox "match was found"

  End If

  For I = 0 To MyRecordset.Fields.Count - 1

  Set MyField = MyRecordset.Fields(I)

  Debug.Print MyField.Name ' Print field name.

  Debug.Print MyField.SourceTable ' Print original table name.

  Debug.Print MyField.Value ' Print original table name.

  Debug.Print "name of column is ;"; MyField.SourceField ' Print original field name.

  MyRecordset.Index = "PrimaryKey"

  MyRecordset.MoveNext

  Next I

  MySQL = "SELECT * FROM ninesv WHERE 3 = 12" 'here, 3,4 represnt field, (column names)

  For I = 0 To MyRecordset.Fields.Count - 1

  Set MyField = MyRecordset.Fields(I)

  Debug.Print MyField.Name ' Print field name.

  Debug.Print "FIELD NAME ;"; MyField.Name ' Print field name.

  Debug.Print MyField.SourceTable ' Print original table name.

  Debug.Print "SourceField ;"; MyField.SourceField ' Print original field name.

  Debug.Print MyField.SourceField ' Print original field name.

  Next I

  End Sub

  
  

  
  
• VB SQL - Fieldname,etc - simple PROBLEM