|
Our application reads information from a Jet database distributed as part of the program. We have recently added a new block of records to the
database. They appear in Access in the order that we expect, but our application returns an assorted few of the records first, followed by the rest in order.
We are still using the MFC interface to DAO, now through MFC 7.1. We read the database using the MFC/DAO MoveFirst and MoveNext calls. The SQL query is "( [SECT_TYPE_NUM] = 70 )", checking a single field.
|
|
|
|
|
Andrew Hain wrote:
They appear in Access in the order that we expect
Ordered by which column?
Do you define the query with "order by" statement? Like "SELECT * FROM your_table ORDER BY your_table_id".
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Thank you. I am still mystified at how we got away without that for years.
|
|
|
|
|
Andrew Hain wrote:
Thank you. I am still mystified at how we got away without that for years.
Actually, I got away with something similar. While I had the table opened in Access I hit the sort button on the toolbar so after that the records appeared to be always sorted. Somehow it worked for a while and I thought that I didn't have to write a single line of code to keep the records sorted. Well, it stopped working at some point. 
Time is the fire in which we burn.
|
|
|
|
|
Hello to all the community I am working on a fairly big App and now i am using Crystal to build loads of reports... And my question is about the optimal way to work when using crystal. Now i am creating 1 datadapter and table in a dataset (on design time) for each Report (excepting the ones who are very similar)
How do you think about this, ppl?
Thanks for reading.
|
|
|
|
|
Hi,
I have a Foxpro DBF and want to export its data to MYSQL DB.How can this be done
Pls help
Prabha
|
|
|
|
|
Hi,
In a FOXPRO Database,I need to change the date format from yyyy/dd/mm to dd/mm/yyyy.Pls help..
Is it possible to change the Dateformat in a table when it has values?
Pls help
Ram
|
|
|
|
|
Hello,
I want to perform a query like this:
UPDATE Payment SET Week = (SELECT Week FROM Receipt WHERE Payment = Payment.ID) Basically, the idea is that I have a table of payments, and a table of receipts, each receipt marked with a week number.
Now, I want to get the week number of the receipt for each payment, and put it in the Payments table.
To make things harder, there are two different receipts tables, and only one of the contains a receipt for a specific payment.
In pseudocode, it would be something like this:
foreach(paymentRow in Payments table)
{
WeekNum = SELECT Week FROM Receipts where ID_payment = paymentRow.ID_payment
IF WeekNum = NULL
{
WeekNum = SELECT Week FROM OtherReceipts WHERE ID_payment = paymentRow.ID_payment
}
IF WeekNum != NULL
UPDATE paymentRow SET Week = WeekNum
}It a once-in-a-lifetime update, so it doesn't have to be the best, performance-speaking.
So, any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Ok, here's what might work:
UPDATE Payment
SET Week = WeekNum
FROM Payment
INNER JOIN Receipts ON Payment.ID_payment = Receipts.ID_payment
WHERE Receipts.WeekNum != NULL
UPDATE Payment
SET Week = WeekNum
FROM Payment
INNER JOIN OtherReceipts ON Payment.ID_payment = OtherReceipts .ID_payment
WHERE OtherReceipts.WeekNum != NULL
Basically each query tries to update Payment only when the WeekNum is not null.
Hope that helps,
Edbert
|
|
|
|
|
Wow!! Updates with joins? I was already reading about evil cursors
Let me try this first!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
LuisR wrote:
Updates with joins? I was already reading about evil cursors
I can tell you that updates with joins are much more efficient than using cursors or looping via code.
You should learn how to update using joins as this will be your best friend for most of the time.
Edbert
|
|
|
|
|
Cool!! 
I tweaked it a bit, and it worked!
I didn't know updates with joins existed. But now I'll have a new trick!! I know cursors are not efficient, but this is a once in a lifetime update.
So, basically it's UPDATE table_to_update SET column = new_value FROM table_to_update like in a SELECT? This query should UPDATE all rows in the table. And if I add an INNER JOIN there, then the UPDATE will only work on the rows that would result from the same SELECT FROM statement?
Thanks!!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Yep.
The only limitation you need to remember is that UPDATE can only update 1 (one) table at a time (I'll be glad if somebody proves me wrong).
Therefore:
UPDATE <table1><br />
SET <specify table1 columns to update from values in all other tables specified below><br />
FROM <tables with JOIN and WHERE conditions>
Just for your information, do you know that you can also do
INSERT INTO (<columns>)<br />
(<br />
SELECT <columns><br />
FROM <tables><br />
)
|
|
|
|
|
Great!!
Edbert P. wrote:
Just for your information, do you know that you can also do
INSERT INTO ()
(
SELECT
FROM <tables>
)
Yes. I didn't but sometime ago someone taught me about it here (was it you maybe? or Colin?) and a few weeks ago I answered a question here with that INSERT INTO... SELECT. He didn't believe me until I said, "well, go and try it" and he was amazed!
SQL sometimes is so cool!
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Does anyone maybe know how I can open a sql server 2005 database in sql server 2000? I can't use "view all rows" in 2005, and need to use 2000 from now on.
Thanks
|
|
|
|
|
"Open Table" I believe is the equivalent menu option in SQL Server 2005
|
|
|
|
|
Hello!
I don't know how to connect to SqlServer when this server is not in the localmachine.
I'm using VB.Net and the server can only be joined on the internet.
My connect string looks this :
Cnx.ConnectionString = "Integrated Security=SSPI;Initial Catalog=name_of_my_base; Data Source=name_of_my_machine"
The computer where the server is running can be reached by a domaine name
Thank you for your help (and sorry for my bad english)
SeLoRBIS
|
|
|
|
|
SeLoRBIS wrote:
Cnx.ConnectionString = "Integrated Security=SSPI;Initial Catalog=name_of_my_base; Data Source=name_of_my_machine
Why is the data source the name of your machine?? It should be pointing to the server.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
I've repare my mistake , i've put my ip adresse = the domain name but any sql server was found .
I've cpyin the connection string proposed by the site : connection string.
but any server was found .
SeLoRBIS
|
|
|
|
|
Have you created the DSN name in your local machine pointing to the server that matches with the connection string?
Depending on the bindings that you are using you'll need to create it based on ODBC, ADO, ...
SeLoRBIS wrote:
I'm using VB.Net and the server can only be joined on the internet.
I've never tried to get data directly from Internet with that method  but it should work. Please, let me know if it works well using a DSN pointing to an Internet data server.
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
I use a dns name (domaine name) for resolve my ip which change with the deconnection .
This name never change .
I've try this connection with the base mySql and the connection run .
I think i go to use this base but i go to search the error with SQl Server!
But which solution do you use for get date directly from internet ?
SeLoRBIS
|
|
|
|
|
SeLoRBIS wrote:
But which solution do you use for get date directly from internet ?
I've always used a server (IIS[^]) that must have a DSN created pointing to the database. The connection string must specify the provider as "MS Remote":
set conn1 = new Connection
conn1.Open "Provider=MS Remote;" _
& "Remote Provider=MSDataShape;" _
& "Remote Server=http://www.domain_you_want.com;" _
& "Data Source=DNS_YOu_Specified;" _
& "User Id=User; Password=pwd;"
Marc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Hi,
my work hierarchy is as follows.
sqlStr = "select * from voucherTransaction where CompanyCode='" & compCode & _
"' and vtCode='" & mcmbVoucherType.SelectedItem.col1 & "' and
voucherNo=" & txtVoucherNo.Text
parameter.Value = sql // sqlCommand parameter object
davd.SelectCommand = cmd // cmd is sqlCommand and davd is sqlDataAdapter object
davd.Fill(dsvd, "voucherDetail") // dsvd is DataSet
davd.Dispose()
//after populating dataSet i need to delete a specific row . and the deletion
code is
dsvd.Tables("voucherDetail").Rows.RemoveAt(lastRow)//lastrow is secific row no
dsvd.Tables("voucherDetail").AcceptChanges()
//now i need to add new row as
dsvd.Tables("voucherDetail").Rows.Add(drow) // drow is DataRow
//now updating the DataSet as follows
sqlStr = "select * from voucherTransaction where CompanyCode='" & compCode & _
"' and vtCode='" & mcmbVoucherType.SelectedItem.col1 & "' and
voucherNo=" & txtVoucherNo.Text
parameter.Value = sqlStr
davd.SelectCommand = cmd
Dim cbvd As New SqlCommandBuilder(davd)
davd.Update(dsvd.Tables("voucherDetail"))
davd.Dispose()
Now the error is " can't insert the duplicate key value"
I have displayed the DataSet table (voucherDetail),there isn't any duplicate value but its showing error for that specific row because its not deleting from the database.
Please help me immediately if u have some suggestions
|
|
|
|
|
I'd like to know your opinions, experience and feelings about these DBs:
1. MSDE
2. PostgreSql
3. MySql
Thanks,
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
1. MSDE:
I did not use MSDE (but used MS SQL Server 2000)
It's free with many features (stored procedures/UDF/ocnstraints/triggers/some types of replication) you can consider it a limited SQL Server version regarding performance.
2. PostgreSql:
I did not use it either..But it's free,open source and reliable and semi full featured.
3. MySql:
I used it and I like it..It's free,open source,very fast,and widly used on the internet.But it's features are less complete compared to the previous DBMSs.
the decision of using one against the others depends on the project you make and your previous knowledge.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
