|
Just to add to what Christian said. OleDbDataAdapter uses a OleDbCommand object internally to get the data from the database. There is no advantage, in terms of contentious resources (multiple opens), to using an OleDbDataAdapter over a OleDbCommand. The only advantage you get is that an OleDbDataAdapter will open a connection if it is shut already (by calling OleDbConnection.Open() ) and will shut it afterwards, if it opened it. It is generally good practice, if you are using the Command objects to open the connection, run the query and close the connection again rather than just leave it open.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have an ASP.NET application that needs to access a SQL Server. I was planning on using the MS Data Access Application Block (DAAB) but do not want to store SQL Server login credentials in config file as the DAAB seems to suggest. I want to collect the SQL Server account's username and password at runtime with a standard login webform.
Am I missing something, or does the DAAB not easily handle this? I am not using Windows Integrated security, I am using SQL Server security.
Has anyone been able to assign SQL Server login credentials to the DAAB at runtime as I was hoping to do?
I'd greatly appreciate any suggestions.
Thanks.
|
|
|
|
|
I'm using sql server 2000.
Is it possible to use a parameter passed to a stored procedure as the name of a table in a query? If I hardcode the table name into the query it works fine. But when I try changing the procedure to have the name passed, the syntax check fails with "Error 170: line 2: Incorrect synatax Near '@Tablename'."
[code]
CREATE PROCEDURE CreateTestTable2 @Tablename char(30) AS
CREATE TABLE @Tablename
(
TestID INT PRIMARY KEY NOT NULL,
TestInt INT,
TestString VARCHAR(10)
)
GO
[/code]
|
|
|
|
|
You cannot do this in the way you are trying. However, you can create some dynamic SQL and execute that.
What you do is create a string in SQL Server, say a varchar(1000) , and build the CREATE TABLE statement into the string.
Once you have built the string you can then do EXEC(@MySqlStatement)
However, be aware that this carries a security risk with it as it could more easily be the weak point for a SQL Injection Attack (See SQL Injection Attacks and Tips on How to Prevent Them[^] for more information).
To help mitigate the risk your stored procedure should ensure that the @Tablename parameter contains only valid characters (say 0-9, a-z, and an underscore) before building the dynamic SQL Statment and running it.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Colin Angus Mackay wrote:
Does this help?
Yes. Thankyou. At the moment I'm just playing around with sql and ado.net to see what I can do so the security problem''s not an immediate concern.
|
|
|
|
|
While using the SqlDataReader, there is often a NULL value giving back when I read it with GetString(index). It's possible to check this with "IsDbNull", but is there a better way to solve this problem ?
|
|
|
|
|
No.
You could change your database schema so that columns don't allow NULL values, and instead provided a default value (such as an empty string.)
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Miekec wrote:
but is there a better way to solve this problem ?
There isn't a better way than that. However Luis did come up with an alternative, which was to have the database query omit some sort of default value in place of a NULL. Sometimes people treat the empty string like a NULL value. However, be aware that NULL values have an important meaning. They exist to say that this is the total absence of a value, whereas an empty string is a value, that happens to be a string of zero length.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
How can I get product of two columns.
example Column1 * Column2 = Result
I used this to be more clearly...
|
|
|
|
|
You need to give the column an alias.
e.g.
SELECT 1 * 2 AS total FROM tablename
|
|
|
|
|
|
I know. I assumed he wanted to name his column because of the = Result in his example and was trying to indicate that he could achieve this by using an alias. Perhaps I read more into the example than was required.
I think it is good practice to name your calculated columns as it makes your commands easier to read and understand.
|
|
|
|
|
|
Colin is correct. You don't have to provide an alias in order to get the product of the two columns. I assumed that you were trying to name your column because of the = Result in your example. If that is not what you were trying to do I hope I didn't confuse you.
|
|
|
|
|
SUBJECT: Criteria query - Parent's property as select criteria.
Hi, I'm trying to issue a SELECT statement via Criteria query, but can't get it working...
<br />
Basically, I have two classes:<br />
<br />
Public Class MyChildClass<br />
...<br />
Public Property MyParent As MyParentClass<br />
...<br />
End Property<br />
<br />
Public Property ChildProp1 As Int32<br />
...<br />
End Property<br />
...<br />
End Class<br />
<br />
Public Class MyParentClass<br />
...<br />
Public Property ParentProp1 As Int32<br />
...<br />
End Property<br />
...<br />
End Class<br />
Now, ...
<br />
criteria = BIS_session.CreateCriteria(GetType(MyChildClass))<br />
criteria.Add(Expression.EqExpression.Eq("ChildProp1 ", SomeValue))<br />
OKAY, so far so good. BUT, what do I do to do this...:
<br />
criteria.Add(Expression.EqExpression.Eq("MyParent.ParentProp1", SomeValue))<br />
Can you do table joins with "Criteria Queries"? I checked Hibernate's documentation... , wasn't able to locate information I needed.
Thanks in advance.
Norman Fung
|
|
|
|
|
Caveat: I'm still learning nHibernate and I'm using Session.Find() instead of criteria.
What I had to do was use the unique value of the parent when searching for the child. Here's how I'm using it.
I have a class Employee, which contains a property Position (also a Hibernate class). In the database, Position is referenced through Employee.PositionID. When searching for all the Employees in a chosen Position, I do...
<br />
Position posToSearchFor = service.FindPositionByName("Test Position");<br />
session.Find("from Employee where PositionID = ?", posToSearchFor.ID.ToString(), NHibernate.Type.TypeFactory.GetStringType());<br />
|
|
|
|
|
Thanks Martin. I think I could do with HQL. But, I have already written a bunch of things in the method using Query Criteria - try to stick with it. But, is this a limitation of Query Criteria?
Norman Fung
|
|
|
|
|
Hello Friends,
Recently I came one problem, when i opened Sql Enterprise Manager, on right side of window list of Tables, there when i right click on table and i am getting total number of rows say 5 but when i double click then here i am getting 12 rows!!!!!!!!!!!!!!
How That possible?????
I checked through Query Analyser also there also i am getting 5 rows!!!
Can Any body know this
Please Help me out!!!!!!!!!
Thanks In Advance
Shashank
|
|
|
|
|
|
Hello Colin Angus Mackay,
When I right click on table i am getting menus there open table>> Return all rows
here i am getting 5 rows while when i double click on table_name there i am getting 12 rows!!!
how that is possible?????
Thanks In Advance
Shashank
|
|
|
|
|
|
under2811 wrote:
how that is possible?????
It's impossible.
<italic>Work hard, Work effectively.
|
|
|
|
|
Yulianto. wrote:
It's impossible.
Actually, it isn't. I've seen the database report in Enterprise Manager so many rows for a table, but if I do a SELECT COUNT(*) FROM TableName in the Query Analyser I get a different result. This is because the database statistics haven't been rebuilt recently so some parts of the database are operating on out of date information. This does not affect the results of a query (it will always be accurate based on the current state of the database) however, out of date statistics can slow down the database as the query optimiser may choose an incorrect query plan.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I see. Never notice it. Thank you for the info.
<italic>Work hard, Work effectively.
|
|
|
|
|
Any way Thanks A Lot!!!!!!!
|
|
|
|