Database

Re: Preventing double column value

30-Dec-05 3:40

I have a table with two columns named IdHorse and IdRider.

How can i prevent the table of having more then one horse with the same IdHorse. Can i do that in the INSERT INTO statement ore alter the table somehow

Colin Angus Mackay30-Dec-05 9:04

Specifying DSN in connection string

30-Dec-05 9:04

The detail you supply is a little vague. Here is my understanding and assumptions:

There is a table with two columns
IdHorse
IdRider

[Assumption] There is only to be one IdHorse in the table. It identifies the horse, therefore it is the primary key.

Solution: Make IdHorse the primary key. It will enforce uniqueness.

However, from the way you described it, I have a feeling there is some missing information somewhere which would invalidate my assumption and therefore the solution.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Maqsood Ahmed29-Dec-05 21:03

Maqsood Ahmed

29-Dec-05 21:03

Hello,
I am having a problem with specifying DSN in the SqlConnection object's connection string. How can I specify DSN for SqlConnection string. (Can I specify it at all?)
Thanks in advance.

Maqsood Ahmed - MCAD.net
Kolachi Advanced Technologies
http://www.kolachi.net

Re: Specifying DSN in connection string

abhinish30-Dec-05 0:02

abhinish

30-Dec-05 0:02

simply type

imports system.data.sqlclient<br />
<br />
dim conn as sqlconnection=new sqlconnection("data source=<servername>; initial catalog=master; uid=sa; password=;")

abhinav

Reporting Services Programming

mipooo29-Dec-05 18:10

mipooo

29-Dec-05 18:10

How can I generate reports using SQL Server Reporting Services (Programatically)

better views or procedure?

sasire1829-Dec-05 18:09

sasire18

29-Dec-05 18:09

hi which is better views or procedure where i have store the data and autogenrate a number and store its value in the database + doing some calculation for a paricular column in the web page code behind and storing the data in the same table.

thanks

sasi

Re: better views or procedure?

Colin Angus Mackay30-Dec-05 0:46

Specified cast is not valid

30-Dec-05 0:46

Personally, I prefer to access all data through stored procedures as this adds some extra protection in my application as I can revoke the access to the tables which means that if anyone gains access they can only do what the stored procedures petmit them, rather than have unfettered access to the tables directly.

Views kind of come in the same category as they allow the actual tables to be hidden behind the façade of the view.

There are not really any advantages or disadvatages either way. It depends on how you prefer to work. If the calculation is simple enough then a view will be fine.

However, if you require to filter the rows at any point then a stored procedure may be better as you can filter early and this sometimes helps the query optimiser find a better plan.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

gharry29-Dec-05 9:38

Re: Specified cast is not valid

29-Dec-05 9:38

Can someone help me with this one i have writen a function that retrieves a Id from a table but the result is not found and i get the above error.

Public Function getID(ByVal strTable As String, ByVal strValue As String) As String
Try
Dim IDConn As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=EventMngt")
Dim IDString As String = "SELECT Id FROM Boekelo_Horses WHERE Horse_Passport = '" & strValue & "'"
If strTable = "Boekelo_Riders" Then
IDString = "SELECT * FROM Boekelo_Riders WHERE Rider_FEIId = '" & strValue & "'"
End If
Dim IdCommand As New SqlCommand(IDString, IDConn)
IDConn.Open()
Dim IDReader As SqlDataReader = IdCommand.ExecuteReader()
IDReader.Read()
getID = IDReader.GetString(0)
IDConn.Close()
Catch ex As Exception
MessageLabel.Text = strValue & " is not on the DataFile, try again.<br>Error text:" & ex.Message
getID = "-error-"
End Try
End Function

If i comment out the getID = IDRea... line then the function returns no error, if in the function it produces a error. But why??

Colin Angus Mackay29-Dec-05 15:30

Re: Specified cast is not valid

29-Dec-05 15:30

gharry wrote:
i have writen a function that retrieves a Id from a table but the result is not found and i get the above error

If there is no result then what can it cast to a string?

You need to check if the result is null first, then get the value (if it exists).
Use:

IDReader.IsDBNull(0)

to determine if the value is null before trying to get an actual string value.

See Also: SqlDataReader.IsDBNull()[^]

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

gharry29-Dec-05 23:02

Re: Specified cast is not valid

29-Dec-05 23:02

I replaced some code to check on it but no result.

Dim IDReader As SqlDataReader = IdCommand.ExecuteReader()
IDReader.Read()
If Not IDReader.IsDBNull(0) Then
getID = IDReader.GetString(0)
Else
MessageLabel.Text = strValue & " is not on the DataFile."
getID = "-nodata-"
End If

gharry29-Dec-05 23:44

Changing the location for a replication subscription

29-Dec-05 23:44

O stupid me, i found the problem.
Tryed to get a Id field that is autonumbering i forgot that that colomn is not a string but a Int32 value. After changing that it works

SeXyProwler29-Dec-05 9:37

SeXyProwler

29-Dec-05 9:37

How can the location (ip address/hostname and instance) be changed for a publication/subscription merge replication scenario? Basically I have SERVER01\SQLInstance with a database that is setup to publish to itself as a distributor, then I have SERVER02\SQLInstance with a database that is a subscribor to that distributor. If I change the NETBIOS name of SERVER02 or if I am using IP addresses instead of NETBIOS names and the IP address of either machine changes, the syncronization will fail. Is there a way short of creating a new subscription that I can keep the syncronization working. IE. Can I change where the subscription is mapped to on the fly?

-Kevin

SQL Server security

Fad B29-Dec-05 6:50

Fad B

29-Dec-05 6:50

Hello
MS SQL Server do not have database security, if we stop the service and copy the database file to other computer we can see the schema & data without any password !
So is there is any method for securing the data in SQL Server ?
Thanks for any help Rose | [Rose]

Colin Angus Mackay29-Dec-05 15:38

29-Dec-05 15:38

Fad B wrote:
MS SQL Server do not have database security,

Actually, it has quite a lot of security.

Fad B wrote:
if we stop the service and copy the database file to other computer we can see the schema & data without any password

That is due to your misunderstanding of how it works, not a lack of security on the part of SQL Server. If you stop the service that controls the databases there isn't much SQL Server can do to secure your data.

Fad B wrote:
So is there is any method for securing the data in SQL Server

You need to put in place a comprehensive policy of who gets access to the filesystem on the server so that they cannot move files around arbirtrarily. This also applies to backups. Who gets the physical media and where do they go has to be part of a comprehensive security policy.

You cannot place all of your security in one location. It is a multifaceted problem that must be tackled on all levels. You have seen that by stopping the SQL Server Service (something that won't happen in normal operation) you can get at the files, move them and attach them thus bypassing the inbuilt security in SQL Server. So, you must therefor secure your file system.

There is no single button that you can press on any computer system that will make it secure. As there is no way that it can be done. If you over secure a system then people who have a genuine need cannot access the information they need, if you undersecure it then people who shouldn't have access may be able to gain access. What access is permitted and what is not is determined by the business that it runs in.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius

Paul Conrad29-Dec-05 18:25

Paul Conrad

29-Dec-05 18:25

Colin Angus Mackay wrote:
You need to put in place a comprehensive policy of who gets access to the filesystem on the server so that they cannot move files around arbirtrarily. This also applies to backups. Who gets the physical media and where do they go has to be part of a comprehensive security policy.

You cannot place all of your security in one location. It is a multifaceted problem that must be tackled on all levels. You have seen that by stopping the SQL Server Service (something that won't happen in normal operation) you can get at the files, move them and attach them thus bypassing the inbuilt security in SQL Server. So, you must therefor secure your file system.

There is no single button that you can press on any computer system that will make it secure. As there is no way that it can be done. If you over secure a system then people who have a genuine need cannot access the information they need, if you undersecure it then people who shouldn't have access may be able to gain access. What access is permitted and what is not is determined by the business that it runs in.

Excellent points made. Security is in the eye of the beholder.

PC

Fad B29-Dec-05 21:14

Fad B

29-Dec-05 21:14

Ok,
But I did not mean that,
I have SQL Desctop Engin & I'm installing it on any PC
I need to secure my data schema ! and my data !
Since I put them on install CD... so any one will install the database and get its desin...
So any solution to encrypt the database file without the help of the OS ?
Thanks