|
Thanks fo ryour reply,
FYI, I have using the ODBC connection to connect the *.dbf file before and it's working fine ,and i don't know why know , suddently i have problem to connecting to the database.
I have attached my source code for your refference.Plzzzzzzzz guide me on how to solve tyhis problem
using System.Data.Odbc;
string thisConnection = @"Driver={Microsoft dBASE Driver (*.dbf)};SourceType=DBF;SourceDB=C:\data\;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
string sql = @"SELECT * FROM C:\data\QC.dbf";
OdbcConnection conn = new OdbcConnection(thisConnection);
OdbcCommand cmd = new OdbcCommand(sql);
cmd.Connection = conn;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader[1].ToString());
}
reader.Close();
}
catch (OdbcException err)
{
MessageBox.Show(err.Message);
}
finally
{
conn.Close();
}
|
|
|
|
|
Hi All! I am new to using the .NET framework database objects and have a question about how to design my program using Adapter/DataTable queries.
My Database...My database consists of about 10 different tables. No one table is indepedent. All have at least one relationship with another table.
My Program...The program needs to be designed such, that when the user clicks "OK" on whichever dialog, the program will commit the new data to the database.
Here is the jist of what I'm curious about...What is best design for the interaction between my OleDbDataAdapter and the DataSet or DataTables? Do you think these objects should be created with each form? When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk and not an In-memory object.
Currently, on startup my program reads each table into a DataSet object. For basic queries involving a single table this works fine (using the Select method of DataTable). But anything more complex I now need to create a new DataAdapter and DbCommand objects.
Would it be reasonable to have one OleDbConnection object for application. Then mutiple OleDbAdapter objects for each form. I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data.
To illustrate my startup code is below.
Thanks for your help!!!
m_dsPTData = New DataSet<br />
<br />
m_dbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilename)<br />
m_dbConnection.Open()<br />
<br />
m_dbDataAdapter = New OleDbDataAdapter()<br />
<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Congregations", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Congregations")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM HomeTalks", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "HomeTalks")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakers", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakers")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakerOutlines", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakerOutlines")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Outlines", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Outlines")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM OutlineCategories", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "OutlineCategories")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Readers", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Readers")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Chairmen", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Chairmen")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Hospitality", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Hospitality")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM AwayTalks", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "AwayTalks")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Notes", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Notes")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Conductors", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Conductors")<br />
<br />
Dim commandBuilder As OleDbCommandBuilder = _<br />
New OleDbCommandBuilder(m_dbDataAdapter)<br />
<br />
m_dbConnection.Close()<br />
|
|
|
|
|
I think your post would have been better off in Design and Architecture forum, but anyway.
David Hovey wrote: when the user clicks "OK" on whichever dialog, the program will commit the new data to the database
Just a note: Take care that database operations are done transactionally and no user interference is allowed while transaction is running.
David Hovey wrote: Do you think these objects should be created with each form
Forms and data (and logic) should be separated so that you can modify each (almost) independently. Get to know for example MVC (Model-View-Controller) concept.
David Hovey wrote: When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk
I don't see any reason why you should store the results to a disk. You use joins in the SQL statement.
David Hovey wrote: But anything more complex I now need to create a new DataAdapter and DbCommand objects
That would be logical in many cases, although you can reuse the command if you change the command text.
David Hovey wrote: Would it be reasonable to have one OleDbConnection object for application
You should get and open the connection to the database when you need to operate with it. After executing the statements, you close the connection. Also use connection pooling.
David Hovey wrote: Then mutiple OleDbAdapter objects for each form
Again, separate forms and database operations. For example if you someday want to run the logic on a middle-tier and only the UI on client (or web page) it greately helps you if you have a good and logical layering in your code.
David Hovey wrote: I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data.
Same as previous, but note what I wrote in the beginning about transactions and user interference.
Hope this helps you forward,
Mika
|
|
|
|
|
It does very much. I found a PDF of excerpt from a book "Object-Orientated Development with ActionScript 2.0" (chapter 18) that talks about MVC.
I can see the basic concepts of each part of MVC and I can see the benefits, but exactly how to relate it to my program is difficult.
The example in the book is a Clock with digital and analog views. Rather simple.
Can you help me a beginning question I have though? From my understanding each View should have one Controller. Does that mean the Controller is designed specifically for that particular view?
 This sure does require alot more planning that the way I was going about it. It will take more time...
Will need to study MVC further obviously.
|
|
|
|
|
David Hovey wrote: how to relate it to my program is difficult
In the beginning it is, but don't worry. When you get over the 'new concepts' and 'ideology that changes everything' part you actually discover that it's no rocket sience. Just a new three (or four) letter abbreviation to a very simple and logical thing.
I think there are several good articles about MVC at this site, have a look at them, for example: Building an MVP Framework for .NET. Part 1: The Basics of MVC and MVP[^]
David Hovey wrote: each View should have one Controller
That's the basic idea but in some implementations one controller can have several views.
David Hovey wrote: This sure does require alot more planning that the way I was going about it
It does, but you don't have to use Visual Studio or Rational Rose etc. In the simplest form it's basic OOP principles and what code do you write and where. So actually what I typically use is pencil and paper. Well, lots of paper because the first versions always go to the garbage pin 
David Hovey wrote: It will take more time
It will in this project, but you also learn a lot. That's an investment!
|
|
|
|
|
I am trying to connect SQL Server 2005 with asp.net there is one problem
Kindly reply me quick as i able to do my work.
This is the problem
Could not load file or assembly 'Microsoft.SqlServer.Management.Sdk.Sfc,Version = 10.0.0.0,Culture = neutral, PublicKey Token = 89845dcd8080cc91'or one of its dependencies.
|
|
|
|
|
At a guess Id say that its failing to load an assembly. Have you added all required references?
|
|
|
|
|
One reason may be if you previously had SQL Server 2008 installed on this machine and then unistalled it. If this is the case, try reinstalling SQL Server 2008 client files.
|
|
|
|
|
Hi,
I have an Access database which was converted to Access 2007. The file is about 60MB big. When I double click it, the application opens up, and all that I see is an input form, looks almost like a Windows application. I need to see the database structure "behind" it with all the tables. How do I see it? Currently I can just see the input form.
Please can someone advise.
Thanks
Brendan
|
|
|
|
|
Is this a compiled database? If yes, then you cannot view the tables or other objects at all...
If not - Do you get a warning message appearing when you try to open the database, asking if it is safe to open? Depress the Shift key and click on the Open button - this should take you to the design mode. This works in Access 2003 - not sure about 2007, though.
HTH
|
|
|
|
|
Someone forgot to assign you an Administrator role.
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
hi i am trying to create a new database role with the name "EmpSelf" but i am getting the error as role already exist but i am unable to find that role ? please help me any one who know this !...
Awaiting Reply ....
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for DatabaseRole 'EmpSelf'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+DatabaseRole&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
User, group, or role 'EmpSelf' already exists in the current database. (Microsoft SQL Server, Error: 15023)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
|
|
|
|
|
You can use for example sp_helprole[^] to list all roles in a database.
|
|
|
|
|
Iam using a table variable inside a stored procedure to search for a name. When executing the stored procedure for the first time in my asp.net web application it times out, while the second time it takes 3 seconds and less.
How can i getrid of this time out the first time?
Here is my SP:
ALTER PROCEDURE [dbo].[SearhBYFullName]
@FullName nvarchar(250)=null,
@Language varchar(10)=null,
@TableName varchar(50)=null,
@BadInfo bit=null,
@Client bit=null,
@Source nvarchar(50)=null,
@top int=1000,
@Filter varchar(10)=null
AS
BEGIN
Declare @Tmp_SearchByName table(
pk int identity(1,1),
RefId int ,
FullName nvarchar(200),
TableName varchar(50),
TableOrder varchar(50))
insert into @Tmp_SearchByName(RefId,FullName, TableName, TableOrder)
(
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table1 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table2 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table3 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrderc from table4 where FullName=@FullName
union
SELECT DISTINCT RefId,FullName, TableName, TableOrder from table5 where FullName=@FullName
)
SELECT distinct top(@top) tmp.RefId, tmp.TableName, tmp.TableOrder, tmp.FullName,
Reference.RefSourceId, Reference.RefNumber, StRefSource.Code as source,
StRefSource.Description
FROM @Tmp_SearchByName tmp
INNER JOIN Reference ON tmp.RefId = Reference.RefID
INNER JOIN StRefSource ON Reference.RefSourceId = StRefSource.Id
left outer join vw_SelectClients as BA on BA.Refid=tmp.RefId
where ((tmp.TableName=@TableName and @TableName is not null) or @TableName is null)
and ((BA.Code='BA' and @Client is not null) OR @Client is null)
order by tmp.TableOrder,StRefSource.Code,tmp.FullName,tmp.RefId
|
|
|
|
|
Without any information about the data amounts etc I would guess that the operation just takes too long.
Why this happens only for the first time sounds like that second time you execute this, buffer manager has enough data to execute the whole thing in acceptable time. This isn't the case when executing the procedure first time (most likely because of high physical I/O amount).
Based on the statements I would say that you need to optimize your statements. Investigate the execution plan for all of the statements and try to eliminate the bottlenecks. To achieve good performance results you may even have to rethink the e/r-model.
|
|
|
|
|
Hi,
I need to exceute a query in my DB when the postgres service starts .
Is there any means to achieve this.
I tried calling an exe when system starts it worked but need to exceute as soon as the service starts .
Thanks
|
|
|
|
|
I don't know if Postgres supports executing statements at startup, but one way to do this is to create a simple wrapper which:
- starts up the database
- gets a connection
- executes the statement.
In this case you would set the postgres service startup option to manual and start it using the wrapper. If you want, you can create the wrapper as a service which then again is set to start automatically.
|
|
|
|
|
hello forum
i want to give an option of search on frontend (asp.net) that will find the given keyword in selected columns of each table present in database
how can i do that perfectly and without effecting the performance.
How to create a stored procedure to apply a search on every table in a database
can anybody help me.
regards
rahul
|
|
|
|
|
Assuming SQL Server 2005/2008 there is a built in command called sp_MSForEachTable
exec sp_MSForEachTable @command1 = "select col1 from ? where x = y"
Note the use of double quotes around the command. When the query runs it replaces the question mark with the table name. I have a feeling you made need single quotes around the question mark, but I don't have sql server on this pc to check.
Hopefully this will get you in the right direction
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanx Friend
I m working on that....
regards
Rahul
|
|
|
|
|
You're welcome, I hope you get the result you need
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hello Bob
I did some job on that but the output is not according to my desire.
Let me explain
the Demo tables are
txnNews
NewsId Title News
txnWallpapers
WallpaperID Name Path
txnHoroscope
SunSignId Horoscope
Now i want to do search on the News and title columns of txnNews on Name column of txnWallpapers and Horoscope column of txnHoroscope
the code that i used is
declare @search varchar(128)
declare @mycommand varchar(128)
declare @mywhereand varchar(128)
declare @returnvalue varchar(156)
declare @colname varchar (50)
set @search = '%India%'
set @mycommand = 'select * from ? where news like '''+ @search+''''
exec @returnvalue= sp_MSforeachtable
@command1 = @mycommand
print @returnvalue
that gives an error for the other two tables of invalid column
what should i do
regards
rahul
|
|
|
|
|
Think, if that is the error you are getting what do you THINK may be causing the problem. The columns are different in each table, you expect SQL to be telepathic. Also you don't need to search EVERY table in the database which this will do. looking for the same column in EVERY table.
Your requirement is to search different columns in 3 tables, write 3 select statements and deal with the result sets.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello all,
We're about to start using reporting services. It looks like it will do most things we want, however, we are having some trouble figuring out how to do things in a way that doesn't create more work for us long term. We would like to set things up so that our ASP.NET applications that call reporting services can specify which connection the reports actually run against. In other words, in dev, we want to make sure it is using the dev connection, while in live, we obviously want the real data. The suggestions I've seen so far are as follows:
1) Use multiple instances of reporting services, and use shared connections with different connection strings on each, then alter the URL you are calling to retrieve reports.
2) Have a dynamic connection string which uses data passed in as a parameter to determine the host and database to use for building the report.
Item #2 is pretty much out, since it would involve manually going back to a static connection string any time we want to edit a report. I can see potential for that breaking all over the place. So, is #1 the only way to do this or is there something easier and more obvious that we are missing? We currently have automated deployments for our other code, and we'd like to do this in a way that will eventually allow the same approach.
Any thoughts?
Will
|
|
|
|
|
I think SSRS does this automatically - as long as you configure the Data Source through the web front end and make sure it's named the same in each environment it should be okay.
I've got projects that I deploy between different environments and don't have to make any changes to the data source. It might be worth pointing out that when deploying to the remote environment I upload the RDL files manually rather than deploying from the VS Solution (as VS isn't installed on the UAT and Production machines) though you can probably get the same end result by changing your project properties so that the datasource isn't overwritten on deployment.
At any rate, I don't remember having to do anything particularly clever when I installed it last (though that was over a year ago...!)
It definitely isn't definatley
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
