|
Hi everyone,
I am using Access to compare records between a SQL Server database instance and Oracle. I have the tables linked and most of the comparisons work fine. I am using joins on the table and comparing each column.
However, I have columns that have Memo fields and Access does not let me compare these fields. Also, it does not do any comparisons between the date fields.
Is there a way to compare these MEMO and date fields using some VB script. I have not used VB scripts much and would appreciate some help.
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Hi,
I'm just wondering if it's possible to create a new table in a database based on a programatically generated DataTable without having to do it by hand?
thanks,
matt
|
|
|
|
|
If I understand you correctly - No.
|
|
|
|
|
lol dagnammit i didn't think so but thought i'd check.
|
|
|
|
|
Hi,
I created a dataset with 2 tables and a relation in a module.(VB.NET)
Now, I have a form with datagrid that suppose to show only the parent table.
How can I hide the symbol (+) from the parent datagrid without deleting the relation.
Thanks
Roy.
|
|
|
|
|
hi to all
How to connect Oracle Lite Database with ADO.NET in VB.Net for Smart Devaice Applications if anybody know that then plz. inform me.
thanks in advance.
Good bye
Manoj Mevada
|
|
|
|
|
Hi,
my query is given below.
"select dp.detail from procdure p, mentryi m, dtentryp dp
where m.master=p.proc
and dp.master =m.detail
and m.item=481"
when i am running the query its giving me the output, that is fine. now using ado.net DataReader, how do i read individual columns from different tables. I am using DataReader b'coz, I am reading a BLOB field from mysql database and converting into .jpg images, that is also working fine, if i am reading from just one table.
I need to read specified columns from different tables in the same DataReader. if i am using the instance like "p" for procdure table, it is giving me error and its not identifying the variables associated with the table.
Any help would be appreciated..
vijay
|
|
|
|
|
u can acess any column by this
dr.getstring(x)
where dr is datareader object
getstring is data type column
and x index of column which starts from 0 for the first column;P
|
|
|
|
|
I need to create a query where I pass it 'Person' and I create a column called 'There'. I'm using SQL Server 2000.
------------------
Table X
------------------
ID Desc
------------------
1 Glasses
2 Red Hair
3 Blue Eyes
-----------
Table Y
-----------
Person ID
-----------
856 1
856 3
900 1
900 2
900 3
-------------------------------
Needed Result when I pass '856'
-------------------------------
Desc There
-----------------------
Glasses True
Red Hair False
Blue Eyes True
This means that I must always output every possible 'Desc' and set 'There' to 'True' when we have a match otherwise set to 'False'.
Thank You in advance for your help!
|
|
|
|
|
This should do it:
SELECT x.Desc, case when y.ID is null then 'False' else 'True' end as There
FROM X
LEFT OUTER JOIN Y ON Y.ID = X.ID
WHERE Y.Person = 856
I hope I didn't just do your homework assignment for you!
my blog
|
|
|
|
|
hehe, I guess it does look like an assignment I would have had. Actually it's a huge complex query that I simplified as far as I could for the sake of getting a response. I still need to translate the case statement into the full version, then I'll see how well it goes.
Thanks!
|
|
|
|
|
Still can't quite get it. Here's more detail.
tTesterUsage Table
------------------
TesterID | UsageID
------------------
856 | 97
856 | 98
900 | 97
900 | 102
tLuUsage Table
--------------------
UsageID | UsageIndex
--------------------
97 | 60001
98 | 60002
99 | 60003
100 | 60004
101 | 60005
102 | 60006
tUConsumer Table
---------------------------
CMCode | GeneralDescription
---------------------------
60001 | Reads Books
60002 | Reads Magazines
60003 | Reads Newspaper
60004 | Watches TV
60005 | Rents Movies
60006 | Movie Theatre
******************************************
* tLuUsage.UsageIndex = tUConsumer.CMCode*
******************************************
Desired Result Below when TesterID = '856':
-----------------------------
GeneralDescription | There
-----------------------------
Reads Books | True
Reads Magazines | True
Reads NewsPaper | False
Watches TV | False
Rents Movies | False
Movie Theatre | False
I've tried this below, still doesn't quite do it.
CREATE PROCEDURE usp_tp_GetConsUsage
@TesterID char(7)
AS
declare @Out table (There varchar(5), TesterID char(7))
insert into @Out
SELECT tUConsumer.CMCode as There, tTesterUsage.TesterID
FROM tTesterUsage inner JOIN
tLuUsage ON tTesterUsage.UsageID = tLuUsage.UsageID inner JOIN
tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode
where tTesterUsage.TesterID = @TesterID
SELECT distinct tUConsumer.GeneralDescription,
case when Gonz.There is null then 'False' else 'True' end as There
FROM tLuUsage inner JOIN
tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode inner join
tTesterUsage on tLuUsage.UsageID = tTesterUsage.UsageID left join
@Out Gonz on tTesterUsage.TesterID = Gonz.TesterID
GO
|
|
|
|
|
tUConsumer needs to be the main table o the query. Something like this:
SELECT distinct c.GeneralDescription,
case when l.UsageId is null then 'False' else 'True' end as There
FROM tUConsumer c
LEFT OUTER JOIN tLuUsage l ON c.CMCode = l.UsageIndex
INNER JOIN tTesterUsage t ON l.UsageId = t.UsageId
where t.TesterId = @TesterId
my blog
|
|
|
|
|
When I try it that way it only displays the GeneralDescriptions that apply to that TesterID. In other words, it only displays what is True. I need all the possible GeneralDescriptions displayed, with either False or True next to the description.
Thank You Again for all of your help
|
|
|
|
|
Ok, it's worked out now. Thanks for all the extended help!
|
|
|
|
|
Hope the following query would help
select distinct UC.GeneralDescription GeneralDescription, case when TU.TesterID is null then 'False'
else 'True' end As There from tUConsumer UC
inner join tLuUsage LU on UC.CMCode = LU.UsageIndex
left join tTesterUsage TU on TU.UsageID = LU.UsageID
and TU.TesterID = @testerID
|
|
|
|
|
I need to create a query where I pass it 'Person' and I create a column called 'There'. I'm using SQL Server 2000.
------------------ -----------
Table X Table Y
------------------ -----------
ID Desc Person ID
------------------ -----------
1 Glasses 856 1
2 Red Hair 856 3
3 Blue Eyes 900 1
900 2
900 3
-------------------------------
Needed Result when I pass '856'
-------------------------------
Desc There
-----------------------
Glasses True
Red Hair False
Blue Eyes True
This means that I must always output every possible 'Desc' and set 'There' to 'True' when we have a match otherwise set it to 'False'.
Thank You in advance for your help!
|
|
|
|
|
Try something like:
--Create temp table containing all possible descriptions.<br />
select distinct Desc<br />
into #temp01<br />
from YourTable<br />
<br />
--Use outer join to pull list of all descriptions, then cross-match<br />
--with person attributes. If the person does not have a matching<br />
--attribute then the YT.Desc value will be null. The "case" <br />
--statement outputs true/false appropriately.<br />
select T1.Desc,<br />
case when YT.Desc is null then 'True' else false end There<br />
from #temp01 T1<br />
left outer join YourTable YT<br />
on YT.PersonId = 856<br />
and YT.Desc = T1.Desc<br />
order by 1
You can do this without using the temporary table if you wanted (just replace #temp01 in the second query with "(select distinct Desc from YourTable)" to create an in-line view.
Andy
|
|
|
|
|
Can anyone explain to me, What I need to do to create a data dictionary? The following is from the help section of Advantage. I don't know how to implement this into VB.Net code in my ASP.Net project.
Creates a data dictionary.<br />
<br />
Syntax<br />
UNSIGNED32 AdsDDCreate( UNSIGNED8 *pucDictionaryPath,<br />
UNSIGNED16 usEncrypt,<br />
UNSIGNED8 *pucDescription,<br />
ADSHANDLE *phAdminConn );<br />
<br />
Parameters<br />
pucDictonaryPath (I) Full file path of the data dictionary to create. <br />
<br />
usEncrypt (I) A non-zero value will cause the data dictionary data<br />
file to be encrypted.<br />
<br />
pucDescription (I) An optional description of the database in the data <br />
dictionary. If NULL, no database description is <br />
stored in the data dictionary. The database<br />
description can be added or changed later with <br />
AdsDDSetDatabaseProperty.<br />
<br />
phAdminConn (O) Returns a database administrative connection handle <br />
if the data dictionary is created successfully.<br />
<br />
Example:<br />
Create a data dictionary without encrypting the data dictionary data files, and then add a table to the database.<br />
<br />
AdsDDCreate( "n:\\MyData\\myData.ADD", 0, "This is the database of my tables and indexes.", &hDD );<br />
AdsDDAddTable( hDD, "Customer Information", "n:\\MyData\\customer.ADT", ADS_ADT, "customer.adi;customer2.adi",<br />
"This table contains information on all customers." )
Thanks in advance.
Beginner in VB and ASP.Net
|
|
|
|
|
i have 3 records in a datatable that i loop through to update the database the problem is that the the update strored procedure updates all records where recordNo = @RecNo to the last row of my datatable so all threee records in my table at the database have the same values.
|
|
|
|
|
Does your table have a unique primary key?
my blog
|
|
|
|
|
Yes you are on to it I was updating a table not using a key so i had many lineitems with the same itemid, silly me. So i had too change my update statement in my sproc to say WHERE ID = ID AND Name = Name. All is working well now, so thanks for the reply anyway.
|
|
|
|
|
Is there a way to change the Collation name of a database on SQL Server 2000?
Thank you!
theJazzyBrain
Excellence is not an act, but a habit! Aristotle
|
|
|
|
|
ALTER DATABASE DBNAME COLLATE Latin1_General_CI_AS<br />
GO
HTH
|
|
|
|
|
Hi there,
I have a situation where a user may want to search against stored date ranges and single dates... while using either a single date or a date range. A document has content values associated with it.
The data is stored within a Postgresql database (although this is more a strategic thing so for the purpose of this discussion that doesn't really matter) and used by a c# application. In
the content table amongst other things it includes 2 date fields - a start date field (that doubles as the single date storage) and an end date field.
ORs are very hard for databases to do on large tables, so I am trying my best to avoid using an OR - instead joining the same table multiple times and using AND. Of course multiple joins are pretty hard for it too - just doesn't seem to be as hard.
I'm also trying to avoid using temporary tables where possible.
Searching for a single date within a range is pretty easy without resorting to either OR or temporary tables.
Note below isn't the actual code, just a simplified mockup.
Searching a single date against a stored range goes something like: (1990-01-01 being the search term)
select docid from document d, contentvalue v1 where
docid.cvid = v1.cvid AND
v1.type = 'daterange' AND
v1.startdate >= '1990-01-01' AND
v1.enddate <= '1990-01-01';
... a range against a single date goes ...
(1990-01-01 - 2000-01-01 being search term)
select docid from document d, contentvalue v1 , contentvalue v2
where
docid.cvid = v1.cvid AND
v1.type = 'date' AND
v1.startdate >= '1990-01-01'
v1.cvid = v2.cvid AND
v2.type = 'date' AND
v2.startdate <= '2000-01-01';
Right now though I'm stuck trying to figure out a decent way of comparing 2 overlapping ranges without using temporary tables or an OR.
If anyone has any alternate strategies for the whole thing, or for decent date-range against date-range storage within a database, and / or any links to any resources on the web I'd be very thankful.
/**********************************
Paul Evans, Dorset, UK.
**********************************/
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
