How To Retrieve Data From Multiple Tables?
Hello all!
I need to retrieve the name of the jobs existing in sql server agent and at the same time, their respective schedule.
Using the statement "SELECT name AS JobName FROM sysjobs" will retrieve the names of the jobs and using the statement "SELECT freq_type AS JobSchedule FROM sysjobschedules" will retrieve the schedule type of the respective jobs.
But I need to display the job name and the job schedule type together, executing the appropriate sql statement in SQL Query Analyzer. How do I go about using the SELECT statement for 2 different tables?
The "appropriate" SQL, when executed, should produce:
--------------------------------
JobName /JobSchedule/
--------------------------------
Job1 /4 /
Job2 / 64 /
Thanks in advance
Edited by - MMC_Newbie on 10/16/2002 11:07:04 PM
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Retrieve Data From E-mails, And Append The Data To Different Tables
I want to retrieve data from e-mails, and append the data to different tables. The e-mail format is constant; but I have no idea how to progress in this matter.
I realize that I need to point at or read an e-mail, select that e-mail, start a script (visual basic for applications) to select the field data, and append a specific table or a selected table.
I am using Outlook for e-mails, and Access for my database. Basically, it would be best to have an Outlook add-in that is user customizable to select e-mails first, then to select e-mail data to be appended to selected tables in Access.
What are your suggestions to resolving or completing this task? Thank you.
How To Retrieve Data From Three Tables Within A Database?
As I know you are all programming experts, may I expect any of you to help me out with a VB problem?
I am doing a project, it is all about developing a Supermarket Transaction Control System which consists of Sales / Purchase / Inventory modules.
I created the database in ACCESS with 6 tables ( ProductRecord ; SalesRecord; CustomerRecord; SupplierRecord; PurchaseRecord and UserRecord). And I use VB6.0 to develop my system.
I use ADO as the data link to linkage the database. In each module, I apply a TOOLBAR (toolbar.buttoms.item(array)) to perform ADD / DELETE and SEARCH functions.
Currently, my Customer Record works. It can perform add new record, delete record and search record. (It retrieves data from CustomerRecord table only). In the same way, I created Sales Record which should retrieve data from 3 different tables within the database ( SalesRecord table; ProductRecord table and CustomerRecord), however, my Sales Record cant perform ADD function properly.
The problem is:-
When I add a new record with proper Sales Record ID; Product ID and Customer ID, it produce me three different records with same Sales Record ID. Let say, I have 3 customers record, 3 product records, when I add a new sales record with one of three customer and one of three products, it produces three records with different customers and different products.
I think my coding part for retrieving data from different tables and write to one table. But I dont know how to fix it. Can any of you help me?
Attached is my SalesRecord coding
Can We Retrieve Data To Datagrid From 2 Tables
I need to retrieve data from 2 table at the same time and to display it to a grid.Only the id field is same in both the tables.Rest of the fields r different.
Now I'm using datagrid and I'm not getting data from 2 tables at one stretch.
Please help me with suggessions
grm
Retrieve Data From Three Diff Tables
if some one of u can resolve my problem. I have three tables for example
No.1= Jobs (Job Master File)
No.2= Trans (Issue Transaction File)
No.3 = Bills (Billing Transaction File)
My query is that to calculate Total Issued Quantity which is in Trans table and Total Billed Quantity which located in Bills table. When i select in record set, the un expected result comes out. Following is coding for that.
With ViewRs
.ActiveConnection = MyDb
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Properties("irowsetidentity") = True
.Open "select e.jobno,sum(t.issueqty) as Issued,sum(b.billedqty) as
Billed " & _
"FROM (enquiry e INNER JOIN trans t ON " & _
"e.jobid = t.jobid) LEFT OUTER JOIN bills b ON " & _
"B.jobid = E.jobid " & _
"WHERE t.manualchallanno > 0 GROUP BY e.jobno"
End With
Expand AllCollapse All
Recalling Data From Multiple Fields In Multiple Tables
I am working on a fairly large project for recording client information into a db which has quite a few fields which are spread out among several tables. The code is already written that saves the data to it's appropriate fields in their appropriate tables.In this particular case, under the front tab of the form is the head person's personal information. On the third tab is information pertaining to the head person's dependents. The code I have written saves the head person's data as well as the data pertaining to that person'd dependents' to a table named [Person]. A few lines lower, those persons' IDs are also entered into an additional table called [Relation], in which are only 2 fields: Relation_Parent_ID and Relation _Child_ID. In another control's code, I recall the data of a given person's ID back into the form from which it was recorded. But how do I code it so that even the children's data is recalled when the parent's data is re-inserted into the form? The code to recall every piece of data that is saved for that person's ID, except for the children's data, is already written and functional. How can I cause the children's data, also, to be recalled when the parent's data is recalled?
Thank you very much for all your help
Happieman
a.k.a. Bill
Edited by - Happieman on 6/11/2005 10:18:40 AM
Getting Data From Multiple Tables
I have a DB w/ 3 tables in it.
Suppliers
Products
Trans
SupplierID is in both the Suppliers and Products Tables,
Three combo boxes named SupList, MatList and TransList
What I am trying to do is to select a supplier, in turn that will automattically
retreive the Materials for that Supplier and populate the Materials combobox.
Can some one get me in the right direction?
Option Explicit
Private MyConn As ADODB.Connection
Private MyRecSet As ADODB.Recordset
Private Sub Form_Load()
'Hide ID Columns
SupList.Columns(0).Visible = False
MatList.Columns(0).Visible = False
TransList.Columns(0).Visible = False
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "DataDelPro.mdb"
MyConn.Open
Set MyRecSet = MyConn.Execute("SELECT * FROM Suppliers")
With MyRecSet
Do Until MyRecSet.EOF
SupList.AddItem MyRecSet.Fields("SupplierID").Value & ";" & UCase$(MyRecSet.Fields("SupplierName").Value)
'Below 2 lines not being used at this time till I get first to work
' MatList.AddItem MyRecSet.Fields("ProductID").Value & ";" & MyRecSet.Fields("ProductName").Value
' TransList.AddItem MyRecSet.Fields("TransID").Value & ";" & MyRecSet.Fields("TransName").Value
.MoveNext
Loop
End With
MyRecSet.Close
Set MyRecSet = Nothing
End Sub
Multiple Tables Data In CR
I have different four tables having records. I want to generate report from these tables via Visual Basic program. But when one table data Complete to print in CR some sataments may print through Parameter passsing from VB. This way four tables data print gradually in one CReport.
Thanx in Advance
Data Report With Multiple Tables
Good morning!
I have a VB application that loads information into Access tables about patients into a Client table, and then supporting information (such as allergies, prescriptions, contacts) into separate tables. There is generally a one-to-many relationship between my client table and the supporting tables. I've inserted a data report into my application to print the information about the patients along with all the supporting information. My instructions said to use the Data Environment designer as a data source. I can select fields from the Data Environment and move them to my data report from the client table and one supporting table, but when I try to select a field from the third table in the Data Environment, I get the round graphic with the slash and VB wont let me drop it on the report.
Am I asking too much of the Data Environment and data report? If so, how do I proceed in getting this information into a report?
Can I use the Data Report without Data Environment and load the report fields with VB code like a form?
Thanks for your help,
Data Report And Multiple Tables
I have been struggling with the Data Report part of VB6 for awhile now. I do have a data environment and data environment connection with no properties set. I am trying to do everything with code and at run-time. I have a report setup right now with a bunch of headings and data fields that are just typed in. My problem is that I need to pull info from multiple tables in one database and display it in the same report. My other problme is that the tables I am pulling info from have some of the same field names. My last problem is that I can't seem to figure out how to do this. Here is the code I have now that I basically got from MSDN but it does pull info from one table and work.
Code:
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "" & dbName
CN.Open connectionString
Set RS.ActiveConnection = CN
RS.Source = "Select * FROM Drivers"
RS.Open , , adOpenStatic, adLockOptimistic
Set rptRaceAll.DataSource = RS
rptRaceAll.Show
I did think that the table.field method might work somehow but I am not sure where or what. If anybody can help me with this I would be very thankful. Also, if anybody knows of any good resources on the data environment and data reporter and doing things at run-time with them please pass it on. Thanks alot!!
-Scott
Retrieving Data From Multiple Tables
Hai, i am trying to retrieve Stud_id and Fname from SD1. I will select City,Area,Center_id and Batch_id from a combo box in another form and i want to select the data which satisfies these criteria.
SD1:
Stud_id,Fname,City,Area
SD3:
Center_id,Batch_id,Stud_id
Code:
Module1.RecordSet ("Select SD1.Stud_id,SD1.Fname from SD1,SD3 where SD1.City = '" & _
Form2.Combo1.Text &
"' AND SD1.Area = '" & _
Form2.Combo2.Text & _
"' AND SD3.Center_id = '" & _
Form2.Combo3.Text & _
"' AND SD3.Batch_id = '" & _
Form2.Combo4.Text & "' ")
MsgBox rs.RecordCount
The following code displays more records than it satisfies.
Thank you
Display Data From Multiple Tables
Ive got 4 tables
suppliers
products
orders (order id's + supplier)
product-orders (which product belongs to which order)
I want to be able to display the various orders and the products in them and i want to be able to edit the orders. How would i do this?
Usually I would use a datagrid for something like this. But I couldnt get that to work.
Adding Data To Multiple Tables (VB)
Does anybody know how to add data to multiple access tables through a vb form. I need to pass the UniqueID from my identification table to all my other related tables in the database, so when my users enter the data into the master table they are also entering the uniqueID into the other tables.
Thanks..
Help......................
Entering Data Into Multiple Access Tables
Ok here's my problem!
I have two access tables, Machine and Component. They are connected via a one-to-many relationship(A Machine has one or more components). The primary key Machine_id in the machine table is the foreign key in the Components table.
I have a form in vb within which i wish to add one machine and then add mulitple components. I have three text boxes which show the machine details eg(name, purchase date and cost) I then have a datagrid which should be empty and allow components to be added. The proble is : i can save a machine easily but the machine_id needs to be placed into the Component table to aloow records to be added. Can anyone send me on the right track please???
How Do You Create Vb Forms That Take Data From Multiple Db Tables?
Please, please can anybody help me as I am really stuck? I’ve created an SQL Server 7 database and would like to create forms to enter the data. I have managed to create forms using the form wizard for data that is from one table but I do not know how to create forms whereby I use data from multiple tables. The form wizard does not seem to allow you to select specific fields from various tables.
Is there anyway I can create a form “manually” using fields from various tables and with the functionality to navigate, add, delete and refresh the records?
If anyone could help I will be incredibly grateful
Shiatsu
How To Link Data Report With Multiple Tables
I am creating data report. I have a database with 4 tables.
Table 1 : Tree
Fields : Treecode, TreeName, Height, Diameter, Cycle Life
Table 2 : SegmentPlan
Fields : TreeCode,SegmentCode, NumberOfTrees, DatePlanted, ForestOfficerInCharge
Table 3 :Segment
Fields: SegmentCode, SegmentPlan, State
Table 4 : Harvest
Fields :TreeCode, SegmentCode, DateOfHarvest, NumberOfTrees, ForestOfficerInCharger, Comments
When I create the data report I only can link to one of the tables. I want to get all the fields from all the tables in one data report. How do I do that? I ve tried using data environment . I created the 1 parent command and 3 child commands but I can only link to 2 tables(Parent and 1 child)...Is there a way to have all my tables to create 1 data report.
And how to acess data report according to "DATE" whereby user inputs the date.
Concantating Data From Multiple Tables To Create An ID Numer
I want to create a BookID depending on the certain criteria. I have three tables: books, categories and subjects
As an example, I want to have the system automatically create the bookID so that it reads like this: APP05-001
APP stands for Application, which is a column named "code" in the category table.
05 stands for the subject ADO, which is the subjectID in the subject table. It has the category id in it to relate to the category table and
001 stands for the first book in that subject which should be stored in the book table. The book table has the subject ID in it to relate to the subject table.
On the front end when adding a new book I want the system to create the bookID.
How do I make this happen?
[resolved Inserting Data Into Multiple Tables/fields
This may not be what the subject line implies. What I want to do is this... I have a form with a text box. I want to insert the text from that box into ONE table's field (MS ACCESS) and have it go into fields of the same name (related fields) in 4 other tables. How can I accomplish that?
Happieman
a.k.a. Bill
Edited by - Happieman on 5/22/2005 10:35:19 AM
Trouble Finding Duplicate Data Accross Multiple Tables
Hi all,
Im having trouble trying to create a VB query in Access XP to locate duplicate data accross 2 tables, then to allow a user to adjust the incomming data to a new value, (recording the old value in the old pack number location.
eg:
Table 1 Pack No
Table 2 Incomming pack no, Old pack number
if anyone could shed some light on it'd be a huge help
Thanks all
Terry
Retrieve From 2 Tables
I wrote a program that retrieved employee information from a sql table. The new version of software has split that table into two tables and now I must get data from both tables using a common field, employee number. I wrote the original program with an ado control. What would be the best way to access the second table... another ado control or some other way?
Bob
Creating A "Data" Report From Multiple Tables
Hi,
I need to start creating data reports which extract data from over four different tables and produce an invoice as a result.
I've been considering extracting all the neccessary information and then entering this into record in a "invoices" table, while this does duplicate data it would allow for invoices to be reprinted easily.
Is this a bad idea? Would it be better to attempt to write a query using the dataenvironment which extracts from four plus tables?
I like the option of reprinting invoices when neccessary.
Thanks
Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
hello all,
I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.
In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, abalance3...so on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.
I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement
Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset
' Open Excel Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
' Open Access Connection
Set cnAccess = New ADODB.Connection
With cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
.Open
End With
' Load ADO Recordset with Excel Sheet1Data
oRs.Open "Select * from [abc$]", cn, adOpenStatic
MsgBox oRs.RecordCount
' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
MsgBox rsAccess.RecordCount
'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
rsAccess.AddNew
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value
Next
rsAccess.Update
oRs.MoveNext
Loop
End Sub
please help me so that i can move forward...
Retrieve Structures Of Tables Through VB Coding?
Hi
My question is this!
Is there any way to retrieve the structures of all the tables in the database
and write them to a file (Text file or somthing else...) through VB coding,
for trouble-shooting and maintenance purposes?
Advance thanks
Ansoft
(I am new to programming)
Retrieve List Of SQL Instances && Databases && Tables, Etc.. VB.NET 2005
Hi,
I want to develop a tool for searching text in ALL tables & stored procedures of a given SQL Server 2005 instances / database.
I found how to find all the instances available on my machine using this code:
Code:
Dim sqlInstances As Sql.SqlDataSourceEnumerator = Nothing
Dim dtInstances As DataTable = Nothing
sqlInstances = Sql.SqlDataSourceEnumerator.Instance
dtInstances = sqlInstances.GetDataSources
But from there, could anyone help me finding all the database available for a given instance.
And in that database, how to find all tables and stored proc.
Thank you.
Edited by - dbelley_office on 6/2/2007 9:27:31 AM
Multiple .MDF Files With 1 Table OR 1 With Multiple Tables?
In the past, I justed just one .MDB (access) database file with multiple tables, but have run across several commercial programs that have multiple .mdb files with one table. Those tables are then all linked.
Is there reason to go one way or the other and why? If using multiple .mdb files, how do you create the links between the tables in different .mdb files?
--Gary
Updating Multiple Tables With Multiple Recordsets
Hi,
I am trying to update two tables at once, based on the same text box inputs I seem to come close but it will not update the second table. Here is my code:
Option Explicit
Dim Rs As Recordset
Dim Rs2 As Recordset
Dim CN, CN2 As Connection
Private Sub cmdBack_Click()
If Not Rs.BOF Then
Rs.MovePrevious
End If
If Rs.BOF And Rs.RecordCount > 0 Then
Rs.MoveFirst
End If
End Sub
Private Sub cmdNext_Click()
If Not Rs.EOF Then
Rs.MoveNext
End If
End Sub
Private Sub cmdRefresh_Click()
Rs.Requery
End Sub
Private Sub cmdSearch_Click()
Rs.MoveFirst
Rs.Find "LastName = '" & txtSearch & "'"
If Rs.BOF Or Rs.EOF Then
If MsgBox("The record you entered was not found, Do you want enter another name ?", vbQuestion + vbYesNo, "ID Check") = vbYes Then
txtSearch = ""
txtSearch.SetFocus
Exit Sub
Else
Rs.MoveFirst
txtSearch = ""
Exit Sub
End If
Else
'Fills in the data with the fields based on the found data
txtLastName = Rs.Fields("LastName")
txtAmount1 = Rs.Fields("Amount1")
txtAmount2 = Rs.Fields("Amount2")
End If
End Sub
Private Sub Command1_Click()
Dim ST As String
Rs.AddNew ' Add a new record
Rs.Fields("LastName") = txtLastName
Rs.Fields("Amount1") = txtAmount1
Rs.Fields("Amount2") = txtAmount2
Rs.Update
'CurrentDB.Execute ("Update tblName2 Set Last_Name = '" & txtLastName & "',Amounta = '" & txtAmount1 & "',Amountb = '" & txtAmount2 & "'")
Rs2.AddNew ' Add a new record
Rs2.Fields("Last_Name") = txtLastName
Rs2.Fields("Amounta") = txtAmount1
Rs2.Fields("Amountb") = txtAmount2
Rs2.Update
End Sub
Private Sub Form_Load()
Set CN = New ADODB.Connection
CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"
Set Rs = New Recordset
Rs.Open "Select LastName, Amount1, Amount2 from tblLastNames", _
CN, adOpenKeyset, adLockOptimistic
Set CN2 = New ADODB.Connection
CN2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"
Set Rs2 = New Recordset
Rs2.Open "Select Last_Name, Amounta, Amountb from tblName2", _
CN2, adOpenKeyset, adLockOptimistic
Set txtLastName.DataSource = Rs
Set txtAmount1.DataSource = Rs
Set txtAmount2.DataSource = Rs
txtLastName.DataField = "LastName"
txtAmount1.DataField = "Amount1"
txtAmount2.DataField = "Amount2"
End Sub
I think it has to do with the adOpenKeyset, adLockOptimistic at the end of the connection string, but if it is this it will not let me take it out or chage it. I also tryed moving things around and that did not woprk.
If any one has any ideas, of waht I am doing wrong, I would really appreciate it
Thank You
Linking Tables Ala Access Get External Data/Link Tables
Hi,
I have a scenario where I am in a network environment running SQL Server. Normally, we create reports using Access by creating an empty database and then using the Get External Data/Link Tables function to populate it with the SQL data and then manipulate our local database rather than accessing the SQL server directly.
I want to do the same thing using Visual Basic 6. I want to provide the user a blank .mdb file, or just create one during runtime, and then have VB link to the SQL tables automatically without any user intervention. How can I go about doing this?
YaMiYuGi1969
Retrieve Multiple Rows
hi
i want to retrieve the multiple rows i am using asp.net and SQL back end the code below
Dim dtr As SqlDataReader
dtr = cmdDetails.ExecuteReader
While dtr.Read
ID = CInt(dtr.GetValue(2))
the above retrieves only the last row
can u please help me
babu
Retrieve Multiple Registry Keys
This code Imports one key to a file.
How can I change it so that it can import multiple keys to one file?
thanks.
Code:
Private Sub ExportRegNode(ByVal strKeyPath As String, _
ByVal strFile As String)
On Error GoTo Hell
'strKeyPath is the key to export
' sample: HKEY_CURRENT_USERSoftwareMicrosoft
'strFile is the file to export to with and extension of .reg
' sample: c:exportedkey.reg
'Perform the export
Shell "regedit /e " & strFile & " " & strKeyPath, vbHide
Exit_For:
On Error GoTo 0
Exit Sub
Hell:
GoTo Exit_For
End Sub
Private Sub ImportRegNode(ByVal strFile As String)
On Error GoTo Hell
'strFile is the file to export to with and extension of .reg
' sample: c:exportedkey.reg
'SilentImport imports the reg keys with no msgboxes or user
' intervention
'Build Switchs
Dim strSwitches As String
strSwitches = "/i "
strSwitches = strSwitches & "/s "
'Perform the import
Shell "regedit " & strSwitches & strFile, vbHide
MsgBox ("Wireless Config Restored")
Exit_For:
On Error GoTo 0
Exit Sub
Hell:
GoTo Exit_For
End Sub
Updating SQL Tables With Data From ORACLE Tables
I am tyring to write a stored procedure which will refresh tables on SQL Server with Data from corresponding Tables in ORACLE. The Oracle database is linked via a Linked Server in SQL. I have witten the following SP:
CREATE PROCEDURE dbo.sp_RefreshDDMSTables
AS
DECLARE @strSQLTable varchar(100)
DECLARE @strDDMSTable varchar(100)
DECLARE @strSQL varchar (100)
DECLARE rs CURSOR FOR
SELECT [Name] FROM dbo.sysobjects WHERE left([name],7)='T44DDMS' AND xType='U'
OPEN rs
FETCH NEXT FROM rs INTO @strSQLTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = 'TRUNCATE TABLE ' + @strSQLTable
SET @strDDMSTable = substring(@strSQLTable,9,len(@strSQLTable)-8)
EXEC (@strSQL)
SET @strSQL = 'Insert Into ' + @strSQLTable + ' SELECT ' + @strSQLTable + '.* From P48..T44DDMS.' + @strDDMSTable
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
EXEC (@strSQL)
FETCH NEXT FROM rs INTO @strSQLTable
END
CLOSE rs
DEALLOCATE rs
GO
Whe this SP is executed the following message is returned:
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Can anyone help please?
Thanks
How Can I Retrieve Cell Values From Multiple Excel Documents In A Folder
I downloaded many xls files. I want to write a macro that reads the xls files and display them in a new empty excel file. I don't want to open each file, just get the cell values within a loop. I couldn't find an example on the internet. If you know a tutorial for visual basic appliacations for excel please let me know.
Thanks.
How To Retrieve Ms Excell Data (sheet Name) To Sql Table Using Vb (Data Conversion)
hi
i have problem to retrieve a ms excell data to sql table (i'am using sql 2000) using visual basic.
then another problem is after the conversion process, how to retrieve the certain sql field (example name field) and
make it into shortname (example robert joseph to robert) without adding the last name and update it into sql table.
my limitation for the shortname is 12 character.
plss help me
Cannot Retrieve Values From A Multiple Line, Pipe Delimited Text File
Hi there,
I am trying to read a pipe delimited file, and then output to a blank document the 'nth' value (e.g. 1st & 4th, as I only want those values) for multiple rows in a file, namely "onlbgl1.sec", stored @ "c: emp".
The file has a header row (row #1), and then subsequent corresponding rows of data.
For each row of data I want to retrieve the 1st & 4th field/values, but I'm currently retrieving 'nothing' - see MsgBox results:
Code:
Dim npath As String
Dim inpstring As String
Dim spipe_loc As Integer
Dim epipe_loc As Integer
Dim onlbgl1(1 To 35) As String
Dim i As Integer
spipe_loc = 1
npath = "c: emp"
'get variables
Open npath & "onlbgl1.sec" For Input As #1
Line Input #1, inpstring
'need second line of data
Line Input #1, inpstring
For i = 1 To 32
If epipe_loc = 0 Then epipe_loc = Len(inpstring) + 1
If spipe_loc > epipe_loc Then spipe_loc = epipe_loc
onlbgl1(i) = Mid(inpstring, spipe_loc, epipe_loc - spipe_loc)
spipe_loc = epipe_loc + 1
'Debug.Print onlbgl1(i); i
Next i
' MsgBox (inpstring)
Close #1
MsgBox (onlbgl1(1))
MsgBox (onlbgl1(4))
Selection.TypeText onlbgl1(1)
Selection.TypeText onlbgl1(4)
Any help would be greatfully appreciated.
Thanks,
Andy
SQL With Multiple Tables...
Hi...I was just wondering...
I have three tables...Agencies, ProductsOrdered, and Orders.
I have 5 listboxes representing 'Monday' to 'Friday'...what I need to do is go into the database and retrieve the Agencies...plop it into these listboxes according to the day the Agencies place and order which is in the ProductsOrdered table. My question is...do I have to go into the ProductsOrdered table, get the OrderID for orders place through Monday to Friday, open up the Orders table, match the OrderID from the ProductsOrdered table to the OrderID from the Orders table, get the AgenyID from this table and open up the Agencies table and finally get the Agency and populate it into the listboxes!!! This is the long way of doing it, can I just SQL with InnerJoin to do all of this? I hope I'd explained this well enough!!!
THANKS!!!
Multiple Tables
What is the syntax for a select statement that searches through multiple tables?
Multiple Tables
Hi guys,
I was wondering, is there anyways to pull out data from 3 tables? if there is how?
Thank you
Multiple Tables
Hello, I have a design problem. What I am doing is creating n-tear application with ASP.NET using VB and using WEB Services.
My web service is responsible for creating connection object to Oracle database, and retrieving information and put it in to dataset. SQL statements is provided by business module class that is calling web service. This information is then displayed in the datagrid. Once changes are made this dataset gets posted to web service for update/insert/delete.
In most of cases these SQL statements will be connecting multiple tables with WHERE a.pk = b.fk etc…
What is the best way to load this data in to data set, so I can use commandObject to automatically post them back to database? Should I load data in to several tables inside of one dataset with separate SELECT statements? What about displaying multiple tables in the datagrid? Parent-child?
Please help me with this design logic, and with some sample code, for this process.
Thanks,
Using Data Environment To Retrieve Data From Stored Procedure
Hey Gang:
I have a project I'm working on and I would like to use the Data Environment. What I'm doing is executing a stored procedure and return a record set. The problem is when I use the DE and add the sp to a command I cannot see the resulting columns. I'm using a Sybase ASE DB running on unix. I would not have to use the DE, but I'm also using the Data Report Designer and I want to group the results by an account number that's being returned, but everytime I try to do it programmatically, I receive an error "Report sections do not match data source". Can anyone help me with my issue in the DE. Is there any wayto show the resulting columns from a sp? I will post another questionconcerning the Data Report Designer in another post.
Thanks,
jocasio
Multiple Tables Summary
Hi,
Here is my problem. I have an excel file with multiple work sheets. In each work sheet i have a table with the following fields: Name, Surname, ID_number. All that I want is to be able to make a new work sheet in which i can generate automaticly a table which contains all students (Name, Surname) who have an ID_number bigger than 100.
PLEASE HELP ME
Self Join With Multiple Tables
Hi, Ive a working Query...
SELECT dbo.TblLookupPreferences.ContactType, dbo.TblEmailAddress.Email, dbo.TblEmailAddress.Name, dbo.TblEmailAddress.Title,
dbo.TblEmailAddress.DoNotMail, dbo.TblLookupPreferences.AssociatedType
FROM dbo.TblAdmMain INNER JOIN
dbo.TblLookupPreferences ON dbo.TblAdmMain.AdminID = dbo.TblLookupPreferences.AssociatedID INNER JOIN
dbo.TblEmailAddress ON dbo.TblLookupPreferences.LookupValue = dbo.TblEmailAddress.EmailID
WHERE (dbo.TblLookupPreferences.ContactType = '7') AND (dbo.TblEmailAddress.DoNotMail = 'N') AND (dbo.TblLookupPreferences.AssociatedType = 'A')
But Now I want to add another table to the same view..
The Other TableName is 'TblProspectmembers'
In need to join tblprospectmembers [Memno] field =
TblLookupPreferences.AssociatedID
WHERE
TblLookupPreferences.AssociatedType = 'M'
but I cant figure out the exact syntax for this.
any help would be highly appreciated!
(oh im using Sql server 2000 and Access 2000 as a front end)
Getting The Count Of Multiple Tables At Once
Is there any way we can get the total count of multiple tables with one sql statement? Something like this:
Select count(Video_ID) As VideoCount, count(Customer_ID) As CustomerCount from Video, Customer
Obviously this doesn't work, I was wondering if there was any correct way of doing this. I would appreciate any help. Thanks.
Reading From Multiple Tables
I can't figure out why my program cannot read records from a certain table in my database. I have 3 related tables that I want my VB program to pull data from. I have 2 of them pulling in fine, however the third table comes up with an error message "No Current Record". I have set up multiple data controls, one for each table, and I am using the seek mehod of the Data Control to connect the controls, using the primary keys in the table. Could someone please look at my code, to double check my work? Maybe I'm overlooking something.
Code:
Private Sub Form_Load()
dtaStateLookup.Refresh
dtaAppointments.Refresh
dtaStateLookup.Recordset.Index = "PrimaryKey"
dtaAppointments.Recordset.Index = "PrimaryKey"
End Sub
Private Sub dtaContactInformation_Reposition()
dtaAppointments.Recordset.Seek "=", dtaContactInformation.Recordset.Fields("ContactID")
If dtaAppointments.Recordset.NoMatch Then
TxtDate.Text = "****"
End If
dtaStateLookup.Recordset.Seek "=", dtaContactInformation.Recordset.Fields("StateAbbreviation")
If dtaStateLookup.Recordset.NoMatch Then
TxtState.Text = "****"
End If
Problem With Multiple Tables
When I open an ADO recordset with JOINs, I couldn't delete (with .delete method) records in the primary table. It gives "Insufficient Column .....". Moreover, on updating value of the key field, the values of the other fields of the secondary table would not be refreshed.
Could anyone give the solution to these problems?
Update Multiple Tables In MS Sql
I want to update two tables with one query.
example:
Instead of running two queries like this...
update contact set contact_id=2 where contact_id=4
update appointment set contact_id=2 where contact_id=4
I want to run the above queries in one query. Is this possible??
Thanks in advance.
How Do You Make Multiple Tables
I used the below code to make a database with one table. Tried with no success to make an additional table by duplicating the table creation code. Question: How do I create a second table. Also, how do I index it? - Steve
Private Sub builddb()
'
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
CurDir & "airmen_test.mdb"
Dim objcat As ADOX.Catalog
Dim objMyTable As New ADOX.Table
Set objcat = New ADOX.Catalog
objcat.Create strConnection
objcat.ActiveConnection = strConnection
With objMyTable
.Name = "tbl_dom_info"
.Columns.Append "first", adVarWChar, 20
.Columns.Append "middle", adVarWChar, 20
.Columns.Append "last", adVarWChar, 20
End With
objcat.Tables.Append objMyTable
Set objMyTable = Nothing
Set objcat = Nothing
End Sub
Select From Multiple Tables
Hi,
I have three tables:
phone_types: idtype, description
phone_object: id_object, description
phone_number: familyid, idtype, idobject, phone_number
When I choose a family: I want to be able to display
type(description) | object(description) | phone_number
but now on a flexgrid i'm only able to display :
idtype | idobject | phone_number
- is using a flexgrid a good idea?
- how do I change my select?
Select From Multiple Tables
I have two tables in an access database that have a 1 to 1 relationship. I want to pull out all fields from the two related records. Something like this:
"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = Charges.ClientID"
Except this always gives me the last record created which I dont want. I want a specific record. I have the ID number of the record I want in a variable called IDnum. How do I get all fields from both tables for that specific ID number (IDnum)?
"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = " & "' IDnum"' & " = " & Charges.ClientID" (A horrible attempt at guessing) or something like that?
Anybody?
|
TRACKER
