|
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...
|
|
|
|
|
Maybe you should change your store procedure, since you didn't post your SP, we don't know what kind of process happend there.
"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
|
|
|
|
|
This is my stored proc
CREATE PROCEDURE dbo.SPSearchRmiInfoLastname
(
@Lastname varchar(50)
)
AS
select * from RmiInfo where Lastname like @Lastname+'%' order by Lastname
GO
------------------
I'm naked under my clothes...
|
|
|
|
|
WOW I figured it out I just put
dg.DataSource = null;
before it exeutes and it fixed it.
somehow when you put a datatable into a datagrid whenever you add back to the datatable it is SLOW.
hope this helps some people who come across this as well =)
------------------
I'm naked under my clothes...
|
|
|
|
|
I currently have a timer that uses an array to see if there are specific exe's running on my machine.
Dim strProcArray() As String={"notepad","calcualtor"}
Dim i As Integer
For i=0 To UBound(strProcArray)
buildProcess(strProcArray(i))
next i
End Sub
So here is my question?
Is it possible to use a data reader (SQL SRVR2K) to to query a table with exe file names in it and loop through that as my array? Is a data reader the right choice? If possible a sample would be great.
Regards
|
|
|
|
|
|
Hi,
I need to create a set of functions that will aid in the creation of tables (and fields) on Access/MS SQL/MSDE databases.
I would like as much as possible to keep the same source code for all three database types, even though there might be a couple of small differences.
I am looking for different methods of creating tables/fields.
Till now I have come across the following:
SQL Queries
ADOX
Am I right in assuming that both these technologies will allow me to create tables/fields on Access/MS SQL/MSDE databases?
Are there other alternatives that I should look at?
Which would be the best one to use in terms of reusable code?
Jeremy.
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
Jeremy Pullicino wrote:
SQL Queries
Some syntaxt for ACCESS and SQLServer are different so you should be careful for that. If you use ADOX I think the only difference is connection string but maybe some functions are supported for one of them. there is no 100% way to do the same code..
Mazy
No sig. available now.
|
|
|
|
|
ADOX sucks.
Been a whole day on it and did not even manage to create a primary key...
I will be try SQL queries now... maybe I should have gone for that option in the first place.
Why do MS keep ADOX if it does not work?
Jeremy Pullicino
C++ Developer
Homepage
|
|
|
|
|
I was just doing some searching for SQL stuff here on CP and saw some things using SQL-DMO. After looking through the SQL Server Books Online that I downloaded I had a quick question. SQL-DMO doesn't seem like a good tool for retrieving information from a database, but more for administering it (adding tables, columns, etc). Am I right in that assesment? Is the best way for an MFC app to use CRecordset, and related ODBC classes?
Thanks.
- monrobot13
|
|
|
|
|
I typically use ADO to run stored procedures and sql statements. I use DMO to write administrative utilities that run backups and script objects...etc.
I would agree with your assessment, at least from my perspective.
~LizardWiz()
|
|
|
|
|
Nice to hear that my assesment was correct. You brought another question to my mind. Is there MFC classes or include files/libs that I need to use ADO? I keep reading about it, but I've no idea how I use it. Thanks for the help.
- monrobot13
|
|
|
|
|
I have a simple ASP.NET page that takes a couple of text string (login, username, password) and stores in a SQL database using ADO.NET.
If I try to store username "Conan O'brien" into the SQL database, I get the following error:
"Incorrect syntax near 'brien'. Unclosed quotation mark before the character string"
Is there any good way to work around this?
Thanks,
Trond Borg
|
|
|
|
|
You must use 2 quotes..
INSERT INTO Table(Column1)
VALUES('Conan O''brien')
Free your mind...
|
|
|
|
|
Use parameters.
Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO tbl VALUES (@username)"
Dim param As SqlParameter = _
New SqlParameter( "@username", SqlDbType.VarChar, 20 )
param.Value = "Conan O'brien"
cmd.Parameters.Add( param ) The data provider takes care of transmitting the parameter.
|
|
|
|
|
Hi Trond Borg
just place another " ' " before the place where u want to palce '
i.e
if u want to save Conan O'brien just write a code look like
INSERT Table1 (chColumnName)
VALUES ('Conan O''brien')
|
|
|
|
|
hi, we know how to search by letter
but it retrive all data contents that letter
for ex:
if i'm looking for the word "data"
the search resultes will be "data" + any character contents the same letters without order
i use this code for search
dataview1.rowfilter = " [discrabtion] like '" * data * "'"
if there is another code for this please send it to us
|
|
|
|
|
Use '%' as a wildcard character.
dataview1.rowfilter = " [discrabtion] like '%" + data + "%'"
Michael
|
|
|
|
|
Has anyone ever figured out a way to use "sp_executesql" on a stored procedure that returns an output parameter. It seems as though when you execute "sp_executesql" SQL server compiles the syntax using a different profile. I can't seem to access any variables declared in the SQL string (even using an optional declaration parameter) nor can I access any variables declared within a stored procedure from within the "sp_executesql" string.
It just doesn't seem possible. Anyone got any creative ideas?
example:
sp_executesql "exec sp_name @varname output"
or:
sp_executesql "exec sp_name @varname output", @varname int
~LizardWiz()
|
|
|
|