ODBC command

hi,

I have a search function.

I was able to display attributes of a record in one table, but

I also need to get one attribute from another table.

My question is how can I implement inner join in this kind of query in odbc command?

this is a part of my code:

1'||||| Create Command Object2Dim odbcCommand_searchAs OdbcCommand =New OdbcCommand("Select TM0001.syain_id, TM0001.syain_name, TM0001.syain_pass, TM0001.empl_date, TM0001.birth_date, TM0011.office_name from TM0001,TM0011 where TM0001.syain_id = ? or TM0001.syain_name = ? and TM0011.office_id = TM0001.office_id ", MyConn)34'||||| Parameters and set values.5 odbcCommand_search.Parameters.Add("@.P1", OdbcType.Char).Value = TextBox_id_name.Text'emp_id.ToString6 odbcCommand_search.Parameters.Add("@.P1", OdbcType.Char).Value = TextBox_id_name.Text'emp_name.ToString789Dim objReaderAs Odbc.OdbcDataReader10 objReader = odbcCommand_search.ExecuteReader()11While objReader.Read()1213 TextBox_id.Text = objReader("syain_id")14 TextBox_name.Text = objReader("syain_name")15 TextBox_pswd.Text = objReader("syain_pass")16 DropDownList_office.Text = objReader("office_name")17 hire_date = objReader("empl_date")181920End While2122 objReader.Close()2324

All values can be displayed except for the "office_name" which is from another Table "TM0011".

hope you can help me with this.

thanks

sheila

You want something like

...from TM0001INNER JOIN TM0011ON TM0011.office_id = TM0001.office_id where TM0001.syain_id = ? or TM0001.syain_name = ?

That is the gist. Just make sure you are joining the matching fields that you are looking for.

|||

You can also try this:

Select TM0001.syain_id, TM0001.syain_name, TM0001.syain_pass, TM0001.empl_date, TM0001.birth_date, (SELECT TM0011.office_name FROM TM0011 WHERE TM0011.office_id = TM0001.office_id) FROM TM0001 WHERE TM0001.syain_id = ? OR TM0001.syain_name = ?

ODBC Call Fail/Record locked

I have recently moved some native Access tables to SQL Server 7. These tables are updated in code. When the update is done via a SQL statement, I get
"This record is being modified by another user. . . Save, Copy to Clipboard, Drop Changes"

When the record is being updated via DAO code, I get,
"ODBC Call Fail"

Both errors are most irritating and I desperately need to find a way around this. Any suggestions would be greatly appreciated.

CrystalHi there,

Maybe this information helps you on the way...

-- Microsoft Knowledge Base Article - 128809

-- Zoek in de Google nieuwsgroepen naar Onderwerp:
"auto_increment fields and #Deleted in MS Access?"
Nieuwsgroep:
-> mailing.database.myodbc

Greetz,
DePrins
:)

ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
Crystal
You should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
CrystalYou should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC 4,099 byte record limit?

I'm connecting to an Access DB through ODBC on a windows server from a PHP
page. The problem is the text retrieved from the select (nfo) is truncated
at
4,099 bytes everytime.
How to I get around this 4k limit?
I read about SET TEXTSIZE but I don't know how to use it in the script?
The full text is in the DB field so it is being truncated somewhere in the
ODBC.
PHP Script:
--
$connect = odbc_connect("datab_nfo", "", "");
$query = "SELECT nfo FROM Titles where ID=15";
$result = odbc_exec($connect, $query);
while(odbc_fetch_row($result)){
$nfo = odbc_result($result, 1);
print("$nfo\n");
}Look for these values in php.ini
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096
"Repo" wrote:

> I'm connecting to an Access DB through ODBC on a windows server from a PHP
> page. The problem is the text retrieved from the select (nfo) is truncated
> at
> 4,099 bytes everytime.
> How to I get around this 4k limit?
> I read about SET TEXTSIZE but I don't know how to use it in the script?
> The full text is in the DB field so it is being truncated somewhere in the
> ODBC.
> php Script:
> --
> $connect = odbc_connect("datab_nfo", "", "");
> $query = "SELECT nfo FROM Titles where ID=15";
> $result = odbc_exec($connect, $query);
> while(odbc_fetch_row($result)){
> $nfo = odbc_result($result, 1);
> print("$nfo\n");
> }
>
>

ODBC 4,099 byte record limit?

I'm connecting to an Access DB through ODBC on a windows server from a PHP
page. The problem is the text retrieved from the select (nfo) is truncated
at
4,099 bytes everytime.
How to I get around this 4k limit?
I read about SET TEXTSIZE but I don't know how to use it in the script?
The full text is in the DB field so it is being truncated somewhere in the
ODBC.
PHP Script:
$connect = odbc_connect("datab_nfo", "", "");
$query = "SELECT nfo FROM Titles where ID=15";
$result = odbc_exec($connect, $query);
while(odbc_fetch_row($result)){
$nfo = odbc_result($result, 1);
print("$nfo\n");
}
Look for these values in php.ini
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textlimit = 4096
; Valid range 0 - 2147483647. Default = 4096.
;mssql.textsize = 4096
"Repo" wrote:

> I'm connecting to an Access DB through ODBC on a windows server from a PHP
> page. The problem is the text retrieved from the select (nfo) is truncated
> at
> 4,099 bytes everytime.
> How to I get around this 4k limit?
> I read about SET TEXTSIZE but I don't know how to use it in the script?
> The full text is in the DB field so it is being truncated somewhere in the
> ODBC.
> PHP Script:
> --
> $connect = odbc_connect("datab_nfo", "", "");
> $query = "SELECT nfo FROM Titles where ID=15";
> $result = odbc_exec($connect, $query);
> while(odbc_fetch_row($result)){
> $nfo = odbc_result($result, 1);
> print("$nfo\n");
> }
>
>

Occasional record fails to merge replicate, but SQL Server reports no errors

Rob,
I have seen this happen in 2 circumstances:
(1) Firstly when the filter was set to 1=2 and inserts
were made while the merge agent was running
(2) Secondly, if you bulk insert the rows and choose the
defaults, then FIRE_TRIGGERS is false and consequently
the rows are not added to MSmerge_contents.
In either case, you need to run sp_addtabletocontents to
include the rows then resynchronise. Alternatively you
can use sp_mergedummyupdate for a single row.
For your case I'd first check to see if there are
corresponding records in MSmerge_contents - ie did the
triggers fire? This should help narrow things down.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul, thanks for the quick reply. I have no filters on the merge and do not
do any bulk inserts, but I will take your suggestion and review
msmerge_contents next time this occurs.
Rob Kraft
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:2dd801c4c0fd$22539b60$a601280a@.phx.gbl...
> Rob,
> I have seen this happen in 2 circumstances:
> (1) Firstly when the filter was set to 1=2 and inserts
> were made while the merge agent was running
> (2) Secondly, if you bulk insert the rows and choose the
> defaults, then FIRE_TRIGGERS is false and consequently
> the rows are not added to MSmerge_contents.
> In either case, you need to run sp_addtabletocontents to
> include the rows then resynchronise. Alternatively you
> can use sp_mergedummyupdate for a single row.
> For your case I'd first check to see if there are
> corresponding records in MSmerge_contents - ie did the
> triggers fire? This should help narrow things down.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Obtaining the latest record by date

Hi,

I have two tables, one stores items, and another one that stores history for the items from first table. They are related by a foreign key from items table.

In one of my queries, I need to obtain the latest history entry from table 2. For this I use MAX(RecordDate) aggregate. Another way that I know is:

SELECT TOP(1) RecordDate, HistoryID, ....
FROM ProductHistory
WHERE (SerialNumber = '20070101000010')
ORDER BY RecordDate DESC, HistoryID DESC

The query is part of a larger query where I obtain more data from other tables.

The reason I prefer the second option is that, at times I have multiple entries for the same item on the same date (no time info). In such cases, the data returned is the latest entry (HistoryID) in to the system of the rows with same date, thanks to ORDER BY clause. Another reason is that I can select any columns, whereas in the first query type I need to use subquery to obtain other fields after I find my relevant row.

My question is, is there any problem using the second method that I am not aware of? Is this method reliable (TOP(1))? I appreciate if you can guide me to the right method. Thanks!

You're absolutely fine to use TOP in the way that you have done.

The most common mistake when using TOP is to omit an ORDER BY clause where it should really be included, which can make the results unpredicatable and inconsistent. As you have included an ORDER BY clause then you'll be fine.

Chris

|||

Thank you for your answer Chris. My main concern was whether TOP was applied before ORDER BY or to the result of the query at the end. It seems it is applied after all the filters are applied, showing only the portion of the result matching the whole query. Please corrent me if I am wrong. I will mark your response as the answer.

Migrant

Obtaining the last record

Let's say I need to be able to dynamically retrieve the last record in a table (ChData) in order to be able to continuously monitor the flow of data. Since theoretically there is no such thing defined in SQL, let's say I have an index column - ChTimestamp - (which, for some odd reason, is a DOUBLE), which grows monotonically. Thus, I want the row with the largest ChTimestamp value. The data columns would be Ch0, Ch1, and so on. Let's also assume that for some reason I am limited to using the LabVIEW database connectivity toolkit and because of that, I don't have full use of the SELECT statement but I have to supply the table and column names for the "SELECT FROM " and only the WHERE and the like predicates can be freely specified (I don't know how freely). What would be an elegant query to achieve the desired result? Thanks!
Kamenelegant query to achieve your result:select phonenumber
from dbms_vendors
where dbms in ('sql server','oracle','mysql','postgresql','sybase','db 2')
sorry, couldn't help it

:cool:|||select * from chdata
where
chtimestamp=(select max(chtimestamp) from chdata)

or am I missing something?

Alternatively you could write a trigger to update a known row in another version of the table with a single row so you can have a simpler where clause.

select * from mystupidtable where x='1'|||SELECT TOP 1 * FROM Table1 ORDER BY chTimestamp DESC|||Thank you very much, LoztInSpace and Peso. No it's not a homework, I'm a professional, it's just that I have to deal with so many different technologies, programming languages, APIs, etc., that sometimes it's just easier (not to mention quicker) to ask those who deal with it every day instead of trying to jump start my fried brain. :o

Kamen :angel:|||Maybe it is time to make your niche?|||Actually, small companies appreciate multivalent engineers like me. Plus, I'm too old to change. :cool:
Kamen