can a database be made full after 15 entry's

Hello guys I was wondering if it would be possible to make a database full after 15 entry's and how would I go about doing that if it is possible

Hi Warren,

Just to clarify: are you talking about simulating a "full"data file in a database, or some form of constraint which enforces a rule that only 15 rows can exist in given table(s)?

If the former, you'd simulate this by sizing your database very small (2MB for instance), and creating a table in which each column's data size equates to the db size / 15...

If the latter, you would do this with a check constraint and perhaps trigger.

Cheers,

Rob

|||

the check constraint sounds like the way I want to go if you happen to have a sample of how i could code that and set me in the right direction I would be very grateful

thanks again Rob

|||

I was given the following code but it seems to be giving me a bit of trouble

Dim theSqlCommand as new SqlCommand("SELECT COUNT(columnName) FROM tableName")
theSqlCommand.Connection = new SqlConnection(connString)
Dim theNumberOfEntries as new object
theNumberOfEntries = 0
theSqlCommand.Connection.Open()
SqlDataReader theDataReader = theSqlCommand.ExecuteReader(CommandBehaviour.CloseConnection)
if theDataReader.HasRows Then
theNumberOfEntries = theDataReader.GetValue(0)
End If
theSqlCommand.Connection.Close()
'We have retrieved and stored the value of the query into theNumberOfEntries
if Convert.ToInt32(theNumberOfEntries) = 15 Then
'No more records allowed to be inserted
End if

I am not sure where to place it either

|||

Hi Warren,

The (VB) code you've been given above won't really help you as it doesn't really provide any mechanism to enforce your rule if the application is bypassed for data operations.

As an example, say your table you want to limit the number of rows for is called t15:

create table t15>

(

col int not null

)

go

create function fn_t15 ()

returns int

as

begin

return (select count(*) from t15)

end

go

ALTER TABLE t15

ADD CONSTRAINT chkRowCount CHECK (dbo.fn_t15() <= 5 );

GO

In this scenario, you can only insert 5 rows into the table t15. Attempting to insert a sixth with result in an error:

insert into t15 values (123)

Cheers,

Rob

|||

Thanks Again Rob I really appriciate your help

I was messing around with the code you gave me today but I am confused on where to put it/Implement it I went into the table and tried to add a check constaraint but I couldn't get the exception accepted ;-( Sorry to be a pain in the arse

|||

Hi Warren,

No worries at all :)

It's probably easiest to paste the code in a query window in Management Studio. You won't be able to create the constraint in the table design view as the function that the constraint will use needs to be created first anyway.

Cheers,

Rob