open more than one table

[Dubya007]

i have front end app that i need to access records from 2 different tables. and then i need to be able to hceck and see if the recrds are different at all. i have a sub that i already made but it doesn't work the way that i want it. any help is greatly appriciated.

[Lightning]

Show the code that you have, what is does and more important what is DOESN'T. Do you want to compare records? If so what are the table like?

[Dubya007]

this is my code. there are two tables that have info about the same tools. i need to check both of them to see if they have info on the same tool number. if they do then the info neeeds to be displayed on the screen. my problem is editing these records. i need to be able to edit all the info from both tables. this is my code to check if it has changed

Private Sub checker()
If txtDrNum.Text <> rs![Drawing Number] Then
    changes = True
    MsgBox "Drawing number is different"
ElseIf cmbTType.Text <> rs![Type of tool] & "" Then
    changes = True
    MsgBox "Tool Type is different"
ElseIf txtDie.Text <> rs![Die Type] & "" Then
    changes = True
    MsgBox "Die Type is different"
ElseIf txtDate.Text <> rs![Date Created] & "" Then
    changes = True
    MsgBox "Date is different"
ElseIf cmbVend.Text <> rs!Vendor & "" Then
    changes = True
    MsgBox "Vendor is different"
ElseIf cmbDepart.Text <> rs!Department & "" Then
    changes = True
    MsgBox "Department is different"
ElseIf txtAssign.Text <> rs![Tool Number Assigned by] & "" Then
    changes = True
    MsgBox "Someone Else assigned the number"
ElseIf txtSketch.Text <> rs!PSketch & "" Then
    changes = True
    MsgBox "The sketch is different"
ElseIf txtStore.Text <> rs![Storage Location] & "" Then
    changes = True
    MsgBox "Storage location is different"
ElseIf txtPMType.Text <> rs!PMType & "" Then
    changes = True
    MsgBox "Different PMType"
Else
    changes = False
End If
Dim sql As String
sql = "SELECT * FROM Status WHERE [Tool number] = '" & ToolID & "'"
 
If SDrs.State = adStateClosed Then
    SDrs.Open sql, DBConn, adOpenDynamic, adLockOptimistic
Else
    GoTo KeepGoing
End If
KeepGoing:
    If cmbStatus.Text <> SDrs!status & "" Then
        changes = True
    ElseIf txtDesc.Text <> SDrs!Description & "" Then
        changes = True
    Else
        changes = False
    End If
End Sub

[vb_dba]

Can you just join the two tables on the Tool Number? That will give you only tools that have information in both tables, provided it's an Inner Join.

[Dubya007]

i wanted to do that @ first but i can't becausethere are tools that need to show up that don't have a status assigned to them. the two tables are: Tool Data & Status, i only need 2 fields from status. everything works fine until a tool number is found that is not in status. then the app thinks that it had reached eof and doesn't display any thing else in those 2 boxes after that. there are tools that still have a status assigned to them but they don't show up because the rs for the status table has ended.

[vb_dba]

You could do a Left Join on [Tool Data] to [Status]. That would give you everything in [Tool Data] and return NULL values for the [Status] columns in the Select statement.

Select td.Col1
     , td.Col2
     , st.Status
     , st.Description
From [Tool Data] td Left Join [Status] st On td.[Tool Number] = st.[Tool Number]

You code would remain the same, except that you would use rs in place of SDrs.

[Dubya007]

so how would i update the status table if i change the status of a tool?

[vb_dba]

What all is in the status table and why would you not include the status as a column in [Tool Data]?

You could always check if the "Status" field in the recordset is null and if so, issue an Insert statement to insert it into the Status table.

[Dubya007]

i didn't make the tables up or else the status column would be in there.
there are 4 fields in the status table
tool number
description
status
date
the only ones that i need are description and status, tool number is already there. how would i go about an insert statement?

[vb_dba]

You will need to requery your recordset after performing this update to pick up the new values:

Dim sSql As String
 
sSql = "Insert Into [Status] ([status], [description], [tool number], [date]) " & _
       "Values ('" & cmdStatus.Text & "', '" & txtDesc.Text & "', '" & ToolID & "', '" & Date & "')"
 
'Assuming conn is your Connection Object
conn.Execute sSql

[Dubya007]

so what you're suggesting is this
use a left join to get all of the records that i want
then when i need to update from editing use the following

Dim sSql As String
 
sSql = "Insert Into [Status] ([status], [description], [tool number], [date]) " & _
       "Values ('" & cmdStatus.Text & "', '" & txtDesc.Text & "', '" & ToolID & "', '" & Date & "')"
 
DBConn.Execute sSql

[vb_dba]

What I suggest you do is redesign your database!

According to what I understand you are trying to do, yes. If you do a left join and the tool does not exist in the Status Table, then NULL will be returned for the Status & Description Columns. During your check to see if anything has changed, if rs!status is null and cmdStatus.Text <> "" then you would want to run the insert statement (assuming that if a tool is in the Status table, it's status can't be NULL).

If the status field is not null, meaning the tool exists in the Status table, I think you will be okay with doing a rs.Update to update the recordset.

[Dubya007]

redesigning the DB sounds like a wonderful idea to me as well but i am only the low man on the totem pole, and i don't have enough expeirence with SQL to do that. i will try the suggestions you told me tomorrow morning as i won't have enough time to impliment today. thanx for your help so far, i'll let you know how it turns out. g'day

[Dubya007]

ok i have another question. say for instance that i am going to add a new tool, that would require me to put up a new entry in the Tool table as well as the status table. how am i going to be able to add some thing to both of the tables?

[vb_dba]

That depends on how your tool number is generated. Are the user's entering the Tool Number or is your database generating the Tool Number?

If your users are assigning the number, then I'd run two seperate Insert Statements. One to insert data into the [Tool Data] table and the other to insert into the [Status] table.

What type of database are you using? (Access, SQL Server, mySQL, etc.)

[Dubya007]

i think that we are using SQL server here(not entirely sure) but i do know that in order to generate a new tool number we have a table with one number in it. when the user adds a new tool the app opens that table and then grabs that number adds the appropraite letters and then adds one to the number that was in the table. and closes the table.
EX.
NEWNUM Table
100

The user clicks the add a tool button
the letters are added
OLMOV100
the app writes that number back to the table adding one.

NEWNUM Table
101

[vb_dba]

If you are using SQL Server, I'd create one Stored Procedure to handle this for you:

Create Procedure ins_Tool @ToolID varchar(50)
                        , @ToolDataField2 varchar(50)
                        , ...
                        , @Status varchar(50)
                        , @Description varchar(100)
                        , @StatusDate smalldatetime
As

Insert Into [Tool Data] (ToolID, ToolDataField2, ...)
Values (@ToolID, @ToolDataField2, ...)

Insert Into [Status] (ToolID, [Status], [Description], [Date])
Values (@ToolID, @Status, @Description, @StatusDate)

Then in your app, instead of running two seperate insert statments, just run the stored procedure:

Dim sSql As String
 
sSql = "ins_Tool @ToolID='" & txtToolID.Text & "', "
sSql = sSql & "@ToolDateField2='" & txtSomeField.Text & "', "
'Fill in other fields/parameters here
sSql = sSql & "...=...,"
sSql = sSql & "@Status='" & cmdStatus.Text & "', "
sSql = sSql & "@Description='" & txtDescription.Text & "', "
sSql = sSql & "@StatusDate='" & Date & "'"
 
conn.Execute sSql

[Dubya007]

um..............i've never done a stored procedure, i have heard of them but i donno how in the world to even start to do one.

i put in the left join Select statement and i got a syntax error @ the word left.

OpenSQL = "SELECT [Tool Data].ToolNumber, [Tool Data].[Drawing Number],[Tool Data].[Type of Tool], [Tool data].[Die Type]," & _
              "[Tool Data].[Date Created], [Tool Data].Vendor, [Tool Data].Department, [Tool Data].[Tool Number assigned by]," & _
              "[Tool Data].PSketch, [Tool Data].[Storage Location],[Tool Data].PMType,Status.Description,Status.Status, Status.Date" & _
              "FROM [Tool Data] LEFT JOIN [Status] ON [Tool Data].ToolNumber = Status.[Tool number]"

[vb_dba]

You need a space before the FROM clause:

OpenSQL = "SELECT [Tool Data].ToolNumber, [Tool Data].[Drawing Number],[Tool Data].[Type of Tool], [Tool data].[Die Type]," & _
              "[Tool Data].[Date Created], [Tool Data].Vendor, [Tool Data].Department, [Tool Data].[Tool Number assigned by]," & _
              "[Tool Data].PSketch, [Tool Data].[Storage Location],[Tool Data].PMType,Status.Description,Status.Status, Status.Date" & _
              " [b]FROM[/b] [Tool Data] LEFT JOIN [Status] ON [Tool Data].ToolNumber = Status.[Tool number]"

[Dubya007]

ok that works, ther space made the form load. the problem is this though. this is my display code.

txtNum.Text = rs!ToolNumber
            txtDrNum.Text = rs![Drawing Number] & ""
            cmbTType.Text = rs![Type of tool] & ""
            txtDie.Text = rs![Die Type] & ""
            txtDate.Text = rs![Date Created] & ""
            cmbVend.Text = rs!Vendor & ""
            cmbDepart.Text = rs!Department & ""
            txtAssign.Text = rs![Tool Number Assigned by] & ""
            txtSketch.Text = rs!PSketch & ""
            txtStore.Text = rs![Storage Location] & ""
            txtPMType.Text = rs!PMType & ""

the values from the Tools table are input into the boxes, the values from the status table are not being input to the boxes. it works the first time but doesn't work after that. i tried putting this in there
cmbStatus.text=rs!Status.
but it doesn't work.

[Dubya007]

never mind about the last post i got it to work it was some thing stupid. i forgot to put it in the other parts of the display function.

[Dubya007]

so now i have it all working fine except for saving the changes and saving a new tool. i would do two insert statements correct?
one for the Tools table and one for the Status table?


sSql= INSERT INTO [Tools Data]([Tool number].......)
VALUES('"&txtTNum.text &'",...............)

dbconn.execute sSql

this would be the same for the status table as well, and it would obviously only be done if the user wanted to save some thing(Changes or New tool)

[vb_dba]

If the user is adding a new row to the [Tool Data] table, ie) Adding a new tool, then you would use the INSERT statements. If the user is updating an existing TOOL, then you would need to issue an Update statement:

Dim sSql  As String
 
sSql = "Update [Tool Data] "
sSql = sSql & "Set Field1 = '" & txtField1.Text & "', "
sSql = sSql & "    Field2 = '" & txtField2.Text & "' "
sSql = sSql & "Where ToolID = '" & ToolID & "'"
 
Conn.Execute sSql

You should really look into using stored procedures. They are the recommended way to handle queries rather than inline SQL statements such as the one's we've been passing around in this thread.

[Dubya007]

ok thanx, do you know where there is a place that i can read up on stored procedures? i'm kinda scared to try one as i don't want to mess the DB up. but i'm willing to give it a try.

[Dubya007]

StatSql = "INSERT INTO [Status] ([status], [description], [tool number], [date]) " & _
       "Values ('" & cmbStatus.Text & "', '" & txtDesc.Text & "', '" & ToolID & "', '" & Date & "')"
 
ToSQL = "INSERT INTO [Tool Data]([Tool Number],[Drawing Number],[Type of Tool], [Die Type], " & _
        "[Date Created],[Vendor], [Department], [Tool Number assigned by],[PSketch],[Storage Location], [PMType]) " & _
        VALUES('" & txtNum.Text & "', '" & txtDrNum.Text & "', '" & cmbTType.Text & "', '" & txtDie.Text & "', '" & _
               & txtDate.Text & "', '" & cmbVendor.Text & "', '" & cmbDepart.Text& "', '" & txtAssign.Text & "', '" & _
               & txtSketch.Text & "', '" & txtStore.Text & "', '" & txtPMType.Text & "')"

those are my insert statements but the bottom one is in red do you know why?

[dglienna]

looks like you're missing a quote before, and a space after VALUES:

StatSql = "INSERT INTO [Status] ([status], [description], [tool number], [date]) " & _
"Values ('" & cmbStatus.Text & "', '" & txtDesc.Text & "', '" & ToolID & "', '" & Date & "')"

ToSQL = "INSERT INTO [Tool Data]([Tool Number],[Drawing Number],[Type of Tool], [Die Type], " & _
"[Date Created],[Vendor], [Department], [Tool Number assigned by],[PSketch],[Storage Location], [PMType]) " & _
" VALUES('" & txtNum.Text & "', '" & txtDrNum.Text & "', '" & cmbTType.Text & "', '" & txtDie.Text & "', '" & _
& txtDate.Text & "', '" & cmbVendor.Text & "', '" & cmbDepart.Text& "', '" & txtAssign.Text & "', '" & _
& txtSketch.Text & "', '" & txtStore.Text & "', '" & txtPMType.Text & "')"

[Dubya007]

yeah i found that, thanks for the help tho. to everyone, vb_dba you are the man

[szlamany]

Originally posted by Dubya007
ok thanx, do you know where there is a place that i can read up on stored procedures? i'm kinda scared to try one as i don't want to mess the DB up. but i'm willing to give it a try.

You should have no fear about creating a simple stored procedure that does a SELECT statement, for example.

We create all of our SPROCS in QUERY ANALYZER with a .SQL script file. Some people might choose to just load them into the DB and then edit them in the DB, but since we have so many customers, we need to distribute changes to SPROCS, so having them in .SQL files kind of treats them like SOURCE vs COMPILED.

At any rate - here is a simple .SQL script that we execute in QA. It's stored in a text file called GETAPPELE_P.SQL - created in QA and saved/edited/re-saved from QA.

The first thing we do is a USE statement - making sure we are "in the right DB" - we have lots and lots of DB's. The two SET statements are just boilerplate we've been using forever - don't even remember why...

The "if exists" statement will DROP the SPROC if it's already in the DB.

Then we have a very simple SELECT statement - all this SPROC does is return a RS. It's as if the SELECT was in-line in VB code - no danger to the DB with this. The SELECT is wrapped in a "CREATE PROCEDURE" statement and "ended" by a GO. The SET NOCOUNT ON is a standard statement we use in SPROCS to keep ADO from getting those little "1 row(s) affected" message in the RS - these can sometimes get in the way.

At the end we GRANT EXEC permission to this SPROC to the "SQL role" that needs it - and then finish up with some standard "SET" statements.

Whenever we go into QA and change this SPROC, we save it (so the .SQL text file is kept up to date) and then press the "EXECUTE QUERY" button on the top toolbar. Basically dropping and re-creating the SPROC in the DB.

SPROCS are great - you do not have to give users access to tables, only SPROCS - so they are a secure way to offer your data to the user group. They are pre-compiled - so they run faster.

Plus they can be very complex - we actually adjudicate medical claims in a SPROC - it's 2472 lines long!!

Use Stufiles
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAppEle_P]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAppEle_P]
GO

Create Procedure GetAppEle_P
as
  Set NoCount On
  Select AER.AppID, AER.ROleID
  FROM AppElemRole_T AER
  WHERE AER.AppId < 10000 and AER.RoleId<1000
  ORDER BY AER.AppId DESC
GO
GRANT EXEC ON GetAppEle_P to StufilesUser
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[Dubya007]

first i'd like to thank you for your response szlamany. second: i'm sure you already told me this but, maybe i didn't understand it. lets say that i am going to make an sproc. do i start in SQL or in VB. and then lets say that i have made on called Retrieve that grabs all of my data. in VB how do i access it?

[szlamany]

First question - then I'll answer the rest...

This is MS SQL Server we are talking about - right? Also, you do use QUERY ANALYZER - right?

[Dubya007]

i use sql server. i don't use query analyzer, i donno what it is.

[szlamany]

Do you use ENTERPRISE MANAGER?

[Dubya007]

yes

[szlamany]

Ok, in ENTERPRISE MANAGER (EM) - open up the CONSOLE ROOT on the left side and open up all the way till you get to your DB open.

Click on TABLES - you will see all your tables in the panel on the right.

From the TOOLS menu up top, select SQL QUERY ANALYZER.

This launches QUERY ANAYLZER (QA) - giving you a simple query window.

Type something like "SELECT GETDATE()" in that window and click the EXECUTE QUERY button up top.

It will execute that simple query and show a RECORDSET in the panel below.

The button on the left - NEW QUERY - will create a new empty query window. In that window, I would type all the stuff I posted in the last post about CREATE PROCEDURE.

Once done typing all that, I would click SAVE QUERY button and give the file a name. It will get saved with a file extension of .SQL, but it is simply a NOTEPAD-LIKE text file.

Once saved, I would then click the EXECUTE QUERY button and it would load that SPROC into the DB.

[Dubya007]

just copy & paste all that code in there?

[szlamany]

Sure - why not?

Just hand edit every ocurrence of GETAPPELE_P to be the name you want for your SPROC. Also change the SELECT statement in the CREATE PROCEDURE to be the SELECT you are looking to do.

The USE STUFILES - should be USE {name of your db}

And if you don't do security through roles, get rid of the GRANT EXEC statement at the bottom.

Once you EXECUTE QUERY and the SPROCS is in your database, open a NEW QUERY window and put in that window just

EXEC {procedure name}

That will test run your SPROC...

[Dubya007]

GetAppEle_P this is the name of the sproc?

[szlamany]

That is the name of the SPROC.

Feel free to ask questions about any part of what I posted...

[Dubya007]

Use Stufiles
what is that then? i thought that was the name of the sproc

[szlamany]

USE is a command that "directs" QA to USE a particular DATABASE.

Even though you got to QA from the TOOL menu in EM - with your database open - you still have a DROP DOWN at the top of QA that would allow you to change to a different DATABASE - MASTER for instance.

And putting your SPROC in the MASTER DB would be a bad thing.

So USE {databasename} is just a safety net - making sure that the commands that follow will be executed against the proper DATABASE.

You can also get into QA through START>PROGRAMS - if you do it that way, the DATABASE selected in that dropdown box at the top will not always be what you want - but will be what the "admin" people have set your default DATABASE to be.