|
Hi Gang,
Here's what I've got,
I've got information stored in one table/database that I'd like to move to another table/database.
Sounds pretty simple doesn't it?
Well, here's where the pickle comes into play.
The tables could potentially be dynamic. So I can't used a predefined DataSet to do the moves, so I have to recreate the datasets each time. That part isn't even necessarily the problem.
The problem I'm running into is:
What happens when my tables have memo fields in them?
Some of our tables have Ole embedded (Not my choice, but I have to live with it) in them that also need to be mapped.
I'm trying to do this all with ado.net using C#.
If I can't I'll probably just end up writing it in VB6. I know we can do it in there, we have. But then again, that's using DAO, not ADO.Net.
Anybody have any suggestions?
Thanks,
Nick
Env: Studio 2003, MS:Access 2000 Databases, WinXP Pro+Patched
This are my own opinions. You know the rest.....
|
|
|
|
|
Hi, I´m using the CDao classes from the MFC to manipulate and store data in an Access 97 db, using the Jet 3.5. engine. I was wondering if there are any good books that someone could suggest for reading. Any thing I´ve found sofar is all to to with VB. Even the various help files use VB examples. (I´m using Dev Studio 97 VC++ 5.0).
Normal opererations like deleteing,adding,modifying,
querydef´s and the like is quite simple, but creating relationshiups between tables is quite annoying, and the msdn does not help that much.
Thanks in advance
Phil
bum... and I thought I´d got rid of all the bugs
|
|
|
|
|
Phil
Most of the samples you'll find will be VB. Many of the Office related technologies are promoted (at least, their automation models are!) through use of VB, VBA or VBS. In the case of JET this isn't exactly a surprise, given the use of VB as the scripting language.
However, I assume you've already got the DAO SDK (I believe it's on the VC++ CD). You could try getting hold of the JET Programmers Guide, which is (or was) an MS Press book.
Another thing to do is get hold of the VB->VC++ automation conversion tool that was on the MSDN site some time ago. It doesn't do 100% conversion of course, but usually enough to get you started on the right road.
Steve S
|
|
|
|
|
Thanks Steve, I had almost given up getting a reply
I´ll look into what you have suggested, and thanks once again for your time.
Phil
bum... and I thought I´d got rid of all the bugs
|
|
|
|
|
Hi Steve, me again....
I´ve managed to find the book you mentioned, I´ll have to see if I can order it.
As for the automation conversion tool you mentioned, I have not been able to find it, do you still have a link to it?
Thanks in advance
Phil
bum... and I thought I´d got rid of all the bugs
|
|
|
|
|
Hi,
Is there any article available here which gives me an idea of connecting a c++ application to the database. I want to write a console database application. I don't want to use the MFC classes.
Kindly help.
regards,
vini
|
|
|
|
|
Then you can use ODBC directly. Or, better yet, get or write your own wrapper classes around ODBC.
I have used my own set of ODBC wrapper classes since ODBC came out. Never have to think about it again...
onwards and upwards...
|
|
|
|
|
i insert one calculate datacolumn into datatable,and set the column expression, and set databinding to datagrid . when i set related column value form datagrid and update database via adosqladapter, it prompt me that i can't modify the calculate datacolumn' readonly value to be false ,however ,i never changed the column' readonly property value .why? who can help me ?
|
|
|
|
|
I had problems as well updating a join table dataset. What I did is simple check the .HasChanges (I think) property and update each table individually in a transaction.
try
'Begin a transaction
'......
If ds.Table(0).HasChanges then
'Update table(0)
end if
if ds.Table(1).HasChanges then
'Update table(0)
end if
'commit the transaction
'.....
catch ex
'Rollback the transaction
'.....
finally
'set any variable to nothing
'.....
end try
Hope this is helpful.
Michael
|
|
|
|
|
halo...everyone.l use the ado connect to the database to find out some records.And just first record display on the page but the results show in the database are over 20. and when l wrote out the record count, the answer is -1.please help me
The codes as below:
Dim objConn,objRs,strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=myName;Driver={Microsoft Paradox Driver (*.db)}"
Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "Select * from (mytable) WHERE condition='" & Request.Cookies("UserID") & "'"
objRs.Open strSQL,objConn,adOpenKeySet,adLockOptimistic,adCmdText
if not objRs.EOF then
Do until objRs.EOF
Response.write.......
objRs.MoveNext
Loop
end if
Thank you at advanced!
|
|
|
|
|
How can i use List Control with ADO?
Please teach me...
|
|
|
|
|
I have a table in an access database. This table has an ID field that is set to be Autonumber and it is the tables primary key.
When I insert new data I would like to know what number the new row got. Is there a nice clean simple way to do this without having to search that table for the data just entered?
|
|
|
|
|
Yes.
SELECT @@IDENTITY
Free your mind...
|
|
|
|
|
Just how do you use it? Is that the complete SQL command I need or do I need to place it in some larger SQL command or something?
|
|
|
|
|
Immediately after you insert statement call the Select @@Identity or preferably Select @ID = @@Identity where @ID is an output parameter.
create proc xyz
( @Name varchar(50),
@ID int output
)
AS
Insert into ....
....
Select @ID = @@Identity
GO
Michael
|
|
|
|
|
Can't do that on Access...
Free your mind...
|
|
|
|
|
C#
OleDbCommand vcommand = new OleDbCommand();
vcommand.CommandText = "SELECT @@IDENTITY";
vcommand.Connection = urOleDbConnection;
int vvalue = (int)vcommand.ExecuteScalar();
VB.NET
Dim vCommand as new OleDbCommand()
vcommand.CommandText = "SELECT @@IDENTITY";
vCommand.Connection = urOleDbConnection
Dim vInt as Integer = CType(vCommand.ExecuteScalar(),Integer)
Free your mind...
|
|
|
|
|
Now I got it working.
Thanks all!
|
|
|
|
|
I'm writing an MFC app and would like to use ADO for the database access. The problem is how? Is there headers I need to include? Are there MFC classes that use ADO? Help is very appreciated.
Thanks.
- monrobot13
|
|
|
|
|
Have a look at the ADO wrapper classes
here[^].
This one[^] is my current favourite class.
I Dream of Absolute Zero
|
|
|
|
|
Thanks for pointing me in the right direction.
- monrobot13
|
|
|
|
|
Hello all,
I want to create some store procedure that retieve data from a table, that storeprcedure should take 2 parameters PageNumber and RowInAPage
so if I use "SomeStoreProcedure 1,100", it will display data from record 1 - 100.
And If I use "SomeStoreProcedure 3,100", it will display data from record 301 - 400.
any idea ?
"Courage choose who will follow, Fate choose who will lead" - Lord Gunner, Septerra Core
"Press any key to continue, where's the ANY key ?" - Homer Simpsons
Drinking gives me amazing powers of insight. I can solve all the worlds problems when drunk, but can never remember the solutions in the morning. - Michael P Butler to Paul Watson on 12/08/03
|
|
|
|
|
You have a couple of choices, Take your pick
http://www.sqlservercentral.com/columnists/glarsen/sequentialnumbering_printversion.asp
Barbara
|
|
|
|
|
a simplified example:
if exists (select * from sysobjects where type = 'U' and name = 'SearchResults')
exec ('drop table SearchResults')
GO
create table SearchResults
(
SearchSeqNo int NOT NULL IDENTITY,
UserKey int NOT NULL,
SearchType tinyint NOT NULL,
-- 1 = Settlement Online Approval (SOA)
-- 2 = Dispatch Tower Activity (DSP)
-- 3 = Assigment Activity (AL)
-- 4 = Salvage Checkin (CI)
-- 5 = Salvage Image (IMG)
-- 6 = Salvage Provider (SPL)
KeyValue int NOT NULL,
Dummy int NULL
)
GO
create unique clustered index SearchResults_idx
on SearchResults (UserKey, SearchType, SearchSeqNo)
GO
if exists (select * from sysobjects where type = 'P' and name = 'ProviderOpenSearch')
drop procedure ProviderOpenSearch
GO
create procedure ProviderOpenSearch @iUserKey int,
@cCriteria varchar(8000),
@iEntriesPerPage int = 0,
@bReturnResults int = 0,
@iSortColumn int = 0
AS
declare
@cUserKey varchar(10),
@cSortBy varchar(7000),
@cSortDirection varchar(20),
@iRowsFound int,
@iPageCount int
set nocount on
delete from SearchResults where UserKey = @iUserKey and SearchType = 6
if IsNull(@iEntriesPerPage,0) = 0
select @iEntriesPerPage = 20
if IsNull(@cCriteria,'') = ''
select @cCriteria = ' 1 = 1'
select @cSortDirection = '', @cSortBy = 'SP.Salvage_Provider_ID', @iSortColumn = IsNull(@iSortColumn,0)
if @iSortColumn > 100
select @cSortDirection = 'DESC', @iSortColumn = @iSortColumn - 100
if @iSortColumn = 1
select @cSortBy = 'SP.Salvage_Provider_ID'
else if @iSortColumn = 2
select @cSortBy = 'SP.Salvage_Provider_Name'
else if @iSortColumn = 3
select @cSortBy = 'SP.Address_Line_1'
else if @iSortColumn = 4
select @cSortBy = 'SP.City_Name'
else if @iSortColumn = 5
select @cSortBy = 'SP.State_Abbreviation'
else if @iSortColumn = 6
select @cSortBy = 'S.Status_Description'
else if @iSortColumn = 7
select @cSortBy = 'SPG.Salvage_Provider_Group_Name'
else if @iSortColumn = 8
select @cSortBy = 'OT.Office_Type_Description'
select @cUserKey = Convert(varchar(10),@iUserKey)
exec ('insert SearchResults
(
KeyValue,
UserKey,
SearchType
)
select
Salvage_Provider_ID,
' + @cUserKey + ',
6
from
Salvage_Provider
where
' + @cCriteria + '
order by
' + @cSortBy + ' ' + @cSortDirection
)
select @iRowsFound = @@rowcount
select @iPageCount = (@iRowsFound/@iEntriesPerPage)+sign(@iRowsFound%@iEntriesPerPage)
select
@iRowsFound 'SearchResultsCount',
@iPageCount 'PageCount'
if IsNull(@bReturnResults,1) = 1
exec ProviderGetPage @iUserKey, 1, @iEntriesPerPage
GO
if exists (select * from sysobjects where type = 'P' and name = 'ProviderGetPage')
drop procedure ProviderGetPage
GO
create procedure ProviderGetPage @iUserKey int,
@iPageNo int,
@iEntriesPerPage int = 0
AS
declare
@iMinKey int,
@iMaxKey int
set nocount on
select
@iEntriesPerPage = IsNull(@iEntriesPerPage,20),
@iUserKey = IsNull(@iUserKey,0)
select
@iMinKey = MIN(SearchSeqNo)
from
SearchResults
where
UserKey = @iUserKey and
SearchType = 6
select @iMinKey = @iMinKey + ((@iPageNo-1) * @iEntriesPerPage)
select @iMaxKey = @iMinKey + @iEntriesPerPage - 1
select
SPL.Salvage_Provider_ID,
SPL.Salvage_Provider_Name,
SPL.Address_Line_1,
SPL.City_Name,
SPL.State_Abbreviation,
dbo.CommaDelimitedLocationTypes(SPL.Salvage_Provider_ID) as Office_Type_Description,
SPL.Salvage_Provider_Group_Name,
SPL.Salvage_Provider_Group_ID,
SPL.Salvage_Provider_Status_Description
from
SearchResults SR,
Salvage_Provider_List_View_New SPL
where
SR.UserKey = @iUserKey and
SR.SearchType = 6 and
SR.SearchSeqNo BETWEEN @iMinKey and @iMaxKey and
SPL.Salvage_Provider_ID = SR.KeyValue
order by
SR.SearchSeqNo
GO
onwards and upwards...
|
|
|
|
|
Hello I am using Sql Server 2000 Developer Edition, Now I know there are limitations too how many connections you have BUT This piece of code below executes a whole lot slower the second time you run it.
switch(cbSearch.SelectedItem.ToString())
{
case "Lastname":
cmd = new SqlCommand("SPSearchRmiInfoLastname", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Lastname", txtSearch.Text);
break;
case "Firstname":
cmd = new SqlCommand("SPSearchRmiInfoFirstname", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Firstname", txtSearch.Text);
break;
}
conn.Open();
Search.Clear();
SqlDataReader drd = cmd.ExecuteReader();
while(drd.Read())
{
Search.Rows.Add(new object[] {drd[0], drd[1], drd[2], drd[3]});
sbp.Text = Search.Rows.Count+" records found.";
}
dg.DataSource = Search;
drd.Close();
conn.Close();
The results are staggering.
First time: 1783 Milliseconds
Second time: 150326 Milliseconds
HOLY SHAZBOT!!
Now that's a difference right there,
Any ideas on why this is happening and how I can change it
Thanks,
Obe
------------------
I'm naked under my clothes...
|
|
|
|
|