|
A good opportunity to find a way of getting a list of column names for that table from the DB.
|
|
|
|
|
Hi Folks,
I am unable to locate the SqlHelper Class in the Enterprise Library Release April 2007. Can Somebody tell me where exactly I can find the source code , I actually wanted to understand the source code more in order to use it efficiently.
Thanks,
Happy Programming!
Regards,
Nikhil Pagidala
|
|
|
|
|
I've not looked at this version of the Enterprise Library, but didn't they take that out in version 2?
|
|
|
|
|
I am not sure about the Jan 2007 release coz so far I was using the June 2005 release.
Can you please let me know if you get any info.
Thank You!
Happy Programming!
Regards,
Nikhil Pagidala
|
|
|
|
|
Nikhil Pagidala wrote: Can you please let me know if you get any info.
Why don't you just read up on how the Enterprise Library now works rather than how it used to work? There should be plenty of documentation and tutorials on the Microsoft site.
|
|
|
|
|
i have a "Booking" table with 3 Column:
BookingID (numeric - primary key)
CheckInDate (datetime)
CheckOutDate (datetime)
- With input are indate and outdate, i want select out free date range in Booking table, like find free room for hotel booking
Thanks.
|
|
|
|
|
I don't see how you can do it with this, you don't have a room number in the table.
Otherwise, you need to use the date functions in SQL to find which rooms are booked on a given date, or a given range, and return the other room numbers.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Can you show me some db design and algorithm about hotel booking? i tried search a hours but...
Thanks.
|
|
|
|
|
I'm not really sure what to say. You should work through a book on database design, and think your problem through. The SQL Server help has all the info you need on how to use date functions.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
<pre><code> :-O :zzz: :-\ :(( :-O :omg: :mad::confused::~ :doh: </code>
|
|
|
|
|
Hi,
Is it possible to make a stored procedure sort columns either ascendingly or descendingly base on a parameter? If not, should we ignore taking advantage of stored procedures and build dynamic SQL commands instead? (building a dynamic sql string in our stored procedure and then calling EXEC())
We don't want to use dynamic SQL commands due to two reasons:
1- preventing SQL injection vulnerability
2- stored procedures containing dynamic SQL are compiled everytime we execute them, which I think should be avoided as far as possible
thanks for any help
|
|
|
|
|
U can use if condition to test the argument
and write 2 select commands like:
if(@order=1)
select .... order by asc
else
select .... order by desc
Do U really need this or I am wrong somewhere?
Regards,
Arun Kumar.A
|
|
|
|
|
Yes this works, the problem is that for two large blocks of select statements, I should copy the whole select block and paste it into 'else' section of if statement just because I wanna sort it descendingly, it's a little annoying, isn't it?
It would be nice if there was a way to enclose the ASC/DESC section of our sql, something like this:
select * from books order by title
if(@order=1)
asc
else
desc
|
|
|
|
|
select * from books order by
case when(@order=1) then title end, asc
case when(@order=2) then title end, desc
Hope, this helps U.
Regards,
Arun Kumar.A
|
|
|
|
|
I have tried this with the folloing example by changing @a to 1 or 0 alternatively:
It works fine.
declare @a int
set @a=1
select * from tableName
order by case
when @a=0 then columnName
end ,
case
when @a=1 then columnName
end desc
But the problem is we have to design the conditons in such a way that only one of the conditions must evaluate to true.
Change the value "0" to "1" in the above code.
It will throw U an error.
So, please make sure to check the above problem.
Regards,
Arun Kumar.A
|
|
|
|
|
Thank you, this seems to help solve the problem we have,
I just didn't understand what the need of ',' is after the first when statement here:
when @a=0 then columnName
end ,
why sould we use ',' when we have shown the end of WHEN block with an 'end' keyword? I also tried this and it worked:
when @a=0 then columnName
end asc,
Maybe I'm just not familiar enough with CASE WHEN statement syntax in sql! shame on me!
Just one more question, Is there also a way to select different columns conditionaly too? (I mean something like the second solution you offered, just by enclosing a specific part of our select statement)
|
|
|
|
|
Maysam Mahfouzi wrote: Is there also a way to select different columns conditionaly too?
Yes, using simple case we can do that.
Click here
R U asking this?
Or do U want to sort by different columns based on condition?
Regards,
Arun Kumar.A
|
|
|
|
|
The trouble with this approach is that SQL Server generates and caches an execution plan the first time you run a stored procedure, then reuses that execution plan every time until it hasn't been used for long enough to be discarded. Only on the next execution will it then be recompiled.
If you have one of these amorphous stored procedures, where the behaviour is very different - effectively using different columns in the WHERE or ORDER BY clauses - the cached execution plan for one set of parameters can be very wrong for a different set. This translates into bad and unpredictable performance. You can force compilation of the execution plan every time by specifying WITH RECOMPILE, but obviously you incur the overhead of computing the execution plan every time it's executed.
It may well be better to use dynamic SQL explicitly from the client. You can still prevent SQL injection by using replaceable parameters in your SQL query text, and this will actually improve execution plan compilation times as SQL Server prefers retaining execution plans from parameterized versus non-parameterized queries (SQL Server caches all computed execution plans unless specifically told not to, but gives greater weight to stored procedures over parameterized text over non-parameterized text, so the more structured queries are retained longer).
You do lose the access control benefits of stored procedures as the user account used to query the database must be given SELECT access to the tables being queried (rather than just GRANTing access to EXEC the stored procedure).
|
|
|
|
|
Or, you can build the string within the proc, and get the same result without the loss of access control ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I want to make a column in a certain table ,this column consists of 3 chars an 6 autoincrement numbers like "abc-123456" .Furthermore this column is a primary key
how can i do this?
thanks
Mohammed Sobhi
|
|
|
|
|
Create a stored procedure to insert rows into the table.
In the stored procedure extract the last 6 characters from the ID column
and find the maxmimum number.
Add 1 to that number and insert it into the table along with the
other arguments.
Regards,
Arun Kumar.A
|
|
|
|
|
your db will perform better if your keys are numbers instead of strings. However, as someone else said, you can do this, but you can't make it autoincrement, you need to increment it yourself.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hi all,
i use following two string to connect database located at remote sql server (mean on internet)
sql connection string
SqlConn = "Data Source=xxx.xxx.xx.xx\server;Network Library=DBMSSOCN;Initial Catalog=databaseName;User ID=Databaseuser ; Password=DatabasePassword;"
error:
“An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)”
OLEDB connection string
oledbConn = "provider=SQLOLEDB; "Data Source=xxx.xxx.xx.xx\server;Network Library=DBMSSOCN;Initial Catalog=databaseName;User ID=Databaseuser; Password=DatabasePassword;"
error:
“[DBNETLIB][ConnectionOpen (Connect())]Specified SQL server not found”
I already have done the following steps:
1. Enable the TCP/IP protocol using the Surface Area Configuration Utility
2. the TCP/IP protocol is enabled in the SQL Server Configuration Utility
3. the SQL Server browser is started.
4. SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
i dont know where i m wrong. Please Help Me
any help will be appriciated. thanks
Rupesh Kumar Swami
|
|
|
|
|
Is there only one sql server instance on that server. If that is true then your connection string is wrong. You just need to be in the IP address not IP address/server. If you do have multiple sql server instances you may need to set the one you want to use to a static port and then add that port to your connection string. If you need more details on this let me know.
Hope that helps.
Ben
|
|
|
|
|
hi ben,
according to ur specification, i remove the server name from my connection string. but now this time, i face new error which is following
when i use following SQLConnection string (vb.net)
SqlConn = " Data source=xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A socket operation was attempted to an unreachable host.)
when i use following SQLConnection string (vb.net)
SqlConn = " Data source=http://xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
and when i use oledbconnection string (vb.net)
oledbConn = "provider=SQLOLEDB; Data source=xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
and when i use oledbconnection string (vb.net)
oledbConn = "provider=SQLOLEDB; Data source=http://xx.xx.xx.xx; Network Library=DBMSSOCN; Initial catalog=DatabaseName;uid=user ID;pwd=Password;"
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
operating system-XP sp2
Thanks
Rupesh Kumar Swami
-- modified at 7:29 Tuesday 1st May, 2007
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
