|
I'm stumped on how to perform the following:
I want to programmically create two queries, either physically or virtually (doesn't matter), then build an SQL statement that applies both queries into one table. For example
SQL.Format("SELECT * FROM TABLEA")
Create QueryA
SQL.Format("SELECT * FROM TABLEB")
Create QueryB
SQL.Format("SELECT * FROM QueryA JOIN QueryB ON ...");
Open(SQL)
{
parse records
}
I have been given such tips as Stored Procedures but I could get even the simpliest of SP to work in AccessXP. Is such a procedure possible to code in ADOP or will I have to have these queries hard coded in Access?
Thanks!
|
|
|
|
|
You may be looking for the UNION operator. Example:
SELECT
LastName,
FirstName
FROM
CustomerTable
UNION ALL
SELECT
NameLast,
NameFirst
FROM
EmployeeTable
UNION: Returns distinct rows in Access. No duplicate rows.
UNION ALL: Returns all rows in Access. Duplicate rows possible.
|
|
|
|
|
not quite. The queries are already mapped out in Access and i even created a third query that incorporates Query1 and Query2, however I wanted to do this programmically using ADO so instead of using the hard coded queries, I want to programmically make them and apply them as I would in Access. After doing some of my own digging, I think I can achieve this through using the ADOCommand class. Does this sound correct? I noticed a company out there managed to create an active control that can create queries through the ADO control, however I don't know if they can be saved to be used in a further query.
To be more precise, I have mapped out what I need accomplished for an SQL. This as been my whole project and pretty much what all of my previous questions were leading up too. I have a grotesquely complicated query to hash out and I have gotten what I need to work through Access however it took 3 queries: Query1 which grabs all of my Skills and Subskills, Query2 which is a UNION of all custom created SubSkills from all the Customized tables the user created and Query3 is the combination of query1 and query2 joined together. My question now, which should be the final step is figuring out how I can achieve what I have setup in Access through ADO.
Perhaps I have complicated the issue futher than need be but after trying all kinds of different layouts for queries I can't get it to work any other way. If I could somehow make Query1 and Query2 work in the same query then it would solve not having to make two separate queries and join them in a third. Perhaps my database design is faulty but not being an expert, lacking any thorough documentation on SQL and database design it's the best I could come up with and I guess I'll have to live with it and code around it until I can get a better understanding of Database design. In any event, without crying a river here,  , does it sound like I'm on the right track? 
|
|
|
|
|
I guess I don't follow what you are trying to do. If you have a set of skills which can have many subskills and many custom subskills, it would seem you could just use UNION to join them all together.
It seems that the common table between both queries is the main Skills table. Have both queries join to it and then merge them with a UNION ALL. If you have multiple custom subskills tables, just keep appending more UNIONs. I am not sure why you would want to use a JOIN between Query1 & Query2.
Then again, I probably do not fully understand your problem.
|
|
|
|
|
Hi,
When my app starts, one of the first things it does is go out and look at the database to make sure it is the correct version. If not, it runs the necessary scripts to bring the database version up to the current code version.
The problem is, after I execute the following code to get the database version, when I try to detach the database, I get the error:
"Cannot detach the database 'Training' because it is currently in use."
I have verified that if I skip the code below, the detach works fine. It seems that once I make a connection to the database, there is no way to get rid of it. I tried the dispose method on the connection but even that doesn't do it. Does anyone know how to get ado.net objects to let go of a database?
Dim mConn As SqlClient.SqlConnection
mConn = New SqlClient.SqlConnection(psConnectionString)
Dim sSQL = "SELECT DBVersion FROM CLAwareInfo"
Dim Command As New SqlClient.SqlCommand(sSQL, mConn)
Dim sInstalledDBVersion As String
Command.Connection.Open()
Command.Connection.ChangeDatabase(psDatabase)
Try
sInstalledDBVersion = Command.ExecuteScalar()
Catch ex As Exception
sInstalledDBVersion = ""
Finally
Command.Connection.Close()
End Try
Thanks
David
|
|
|
|
|
You probably also need to Dispose the SqlCommand object.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Try switching the connection to a different database (master) before you close & dispose it.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi,
Thanks for the good ideas above. I tried them and still have the same problem. I created a simple windows form project that reproduces the problem using the Northwind database, and narrows it down to the SqlConnection object. I have a single form with a single button. When the form loads, it creates a connection, opens it, changes the database to master, closes the connection, and disposes the connection object. When the button is clicked, it attempts to detach the database. That raises the error, "Cannot detach the database 'Northwind' because it is currently in use."
I can't figure out what is wrong with this simple code! Any ideas?
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'select a single value from a table in the database
Dim mConn As New SqlClient.SqlConnection("data source=(local);initial catalog=Northwind;integrated security=SSPI")
mConn.Open()
mConn.ChangeDatabase("master")
mConn.Close()
mConn.Dispose()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'detach the database
Dim mConn As New SqlClient.SqlConnection("data source=(local);initial catalog=master;integrated security=SSPI")
Dim sSQL = "sp_detach_db 'Northwind'"
Dim Command As New SqlClient.SqlCommand(sSQL, mConn)
Command.Connection.Open()
Try
Command.ExecuteNonQuery()
Catch ex As Exception
Debug.WriteLine(ex.Message)
MsgBox(ex.Message)
Finally
mConn.ChangeDatabase("master")
Command.Connection.Close()
mConn.Dispose()
Command.Dispose()
End Try
End Sub
|
|
|
|
|
Are you on SQL Server, or MSDE? Do you have access to Enterprise Manager?
To figure out where your unwanted connection is coming from, a good starting point is to look at current activity in Enterprise Manager, and see which hosts/applications/logins are connected to the database you're having issues with.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
My app uses MSDE, though I believe SQL Server is installed on my development box. I'm running XP Pro. I'm running on a stand-alone laptop, no other computers connected.
I do have Enterprise Manager, but I've seen Enterprise Manager itself have an open connection to the database that prevents my app from detaching the database, so I generally close it when I'm running my app.
I've used Enterprise Manager some, but am not familiar with how to find out what hosts/applications/logins are connected to my database, as you suggest. I tried by opening local\Management\Current Activity [datetime]\Process Info in EM, and didn't see any reference to the Northwind database when I ran the sample project I posted. Can you point me in the right direction where to look? Or if you have VB.NET and SQL Server, would you mind creating a new Windows Forms project with one button and dropping my sample code in the form, and see if you get the same error when you click the button?
I'm wondering if the problem might have something to do with the way .NET handles memory - maybe the garbage collection isn't happening when I need it to, so even though my objects are not available in code anymore, the connection is somehow still present in memory?
Any suggestions where to look, what to try, or confirmation that my sample code causes the same error on other boxes, would be much appreciated!
Thanks
David
|
|
|
|
|
I send .xls files to a share on a server. I need to take those .xls files and use the data in them to populate a table on that server.
I'm new at this, any details are welcome.
Thanks.
|
|
|
|
|
I'm a bit stumpt.
The application I am working on needs to produce a basic datamodel of the source database, so I am reading the system tables from from the database to determine the data model. However, I cannot seem to find out how to determine the primary key, the following is the test code I have, and from what I can tell from the documentation sysconstraits is supposed to tell me the primary key, but it doesn't seem to work.
BTW, I'm using SQL Server 2000.
SELECT syscolumns.name as column_name, sysconstraints.status as status
FROM syscolumns
INNER JOIN sysobjects on sysobjects.id = syscolumns.id
LEFT OUTER JOIN sysconstraints on
sysobjects.id = sysconstraints.id AND
syscolumns.colid = sysconstraints.colid
WHERE sysobjects.name like 'test_table'
Any ideas would be appreciated.
TIA.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'test_table'
ORDER BY ORDINAL_POSITION
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for your suggestion.
I had already tried using this view, but it also gives out the foreign keys without distinguishing between them. The constraint name may contain 'PK' in it which could help - but I can't guarantee that some half-crazed database user will keep with that notation.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
select *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'test_table'
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
 So obvious!
Thanks. I can join everything up now. 
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
No problem.
I can never remember what's where in the INFORMATION_SCHEMA views, myself. Whenever I want/need to do something with schema, I always have to run back to Books Online and wade through all of the view descriptions to figure out which one(s) have the info I'm looking for.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi everyone,
I have a stored procedure (that I used a tool to convert from SQL Server) that has the following parameter signature:
(
TestVorgangID IN NUMBER DEFAULT NULL,
VersuchID IN NUMBER DEFAULT NULL,
Kommentar IN VARCHAR2 DEFAULT NULL,
EAMID IN NUMBER DEFAULT NULL,
dspaceSection IN NUMBER DEFAULT NULL,
testID IN OUT NUMBER
)
I try to call this procedure from my C++ program with the following SQL:
{call UpdateTestsTable(10008, 590, '', , 1, ?)}
This always worked when I was on SQL Server. However, now it returns an error saying: Syntax error or access violation.
What am I doing wrong?
Thanks,
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Possibly the access violation is occurring because there is no storage for the final parameter. What does the '?' character as the final parameter mean or imply?
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
|
|
|
|
|
I am using a DataSet which contains two DataTables. The 2 Tables are related using a DataRelation and it's a One-to-One Relationship. On a Form, I create a DataView for the Top-Level Parent Table and access the Child table through the DataMember when using the BindingContext.
One my Form, I am using Me.BindingContext(dvData).AddNew() for the Parent Table. I then use Me.BindingContext(dvData, "ChildRelationship").AddNew() to Add a new Record at the Child Level. I do this because the Form edits data in both Tables at the same time so that to the User, they will not have to make 2 edits just to get data in both tables.
When I debug after doing this, everything is great! I can see both new Rows added to the DataView and the Form works as expected since it is data bound.
However, in my Save Routine, I modify a few Columns if the Data wasn't supplied, and lastly call the Me.BindingContext(dvData).EndCurrentEdit() and Me.BindingContext(dvData, "ChildRelationship").EndCurrentEdit(). When I do this, and attempt to save the Data back to the Database, it has the Parent Row, but the Child Table's Row (that was previously there) is no longer there! Filtering the DataTable on only Modified / New Rows results in no data, and also the Row Count is the same as before any Rows were added. It's as if the New Row on the Child Table just disappeared!!! 
Does anyone have any insight?? 
|
|
|
|
|
Hello all,
I want to write a stored procedure in SQL Server that makes the following:
checks every record entered in the database, once the record is entered, it checks a field named: telNum, and checks if it is written in arabic (means Hindi numbers) with arabic character set, it updates it and converts it into English number (with english character set).
this is a trigger which I want to write in SQL Server to make what I mentioned above.
Could someone tell me how is that done please. And how can I start, and from where shall I start.
I appreciate any help from you ...
Sam
|
|
|
|
|
I have two sql databases: master & transactions
I need to know how to write some code so that if I am searching my transactions database for a matching counterpart in the master database, the transactions entries overwrite those in the master database. In addition, if no match is found I need a new entry to be added to the master database.
Thank you.
|
|
|
|
|
select *
from pending
where custid <> '' and
custid in (
select id from customer
where status=1 and
priority = (
select max(priority) from customer
where custid in (
select distinct custid from pending
where custid <> ''
)
)
)
there is no constriants on both the tables.
|
|
|
|
|
Yup - remove Select * FROM
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
This looks a little cleaner. Are you sure there is both id and custid in the customer table? Something doesn't look quite right. You can also get rid of the correlated sub-query by placing it in the INNER JOIN.
<code>SELECT
pending.*
FROM
pending
INNER JOIN
customer cus
ON (pending.custid = cus.id)
WHERE
pending.custid <> '' AND
cus.status = 1 AND
cus.priority = (SELECT
max(priority)
FROM
customer
WHERE
custid = cus.custid)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
