|
When you create the table you set a constraint like this:
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)If the table is already created you can use ALTER TABLE in order to add the constraint (See the SQL Help files for how to use ALTER TABLE)
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
thank you,it worked well.When a user purchases some things the totalitems in ptable is decremented according to it.when totalitems in ptable becomes 0, user cannot update.this was the problem.yes, i solved it using consraints.can i make the validation at the client side ?
|
|
|
|
|
|
the userinterface has a textbox and a button.the user must enter the number of items he wishes to buy and then enter the submit button.the stock table in server side contains the TotalItems of a particular product.if TotalItems is zero,the user should not be allowed.I have added the check constraint in serverside "check(TotalItems>=0)" as you said and done it successfully.so the question is can it be done at client side?
|
|
|
|
|
|
I've posted a message here yesterday, but it seems that i was mis-understood.
I'm using vb6 ADO's Recordset.
i want to use the recordset's Filter property.
but the target column is numeric (the ID column of the table, int).
i want to filter the table, so i'll stay with the rows that begins with a certain number, just as a varchar column i'll use "LIKE" filter.
when trying to do it, i get an error "Filter can not be opened."
any ideas?
thanks
|
|
|
|
|
you could put a textbox on your form, and in the code, use the textboxes text property in your filter. create a varible and set the val(textbox) = to your varible and search that way
ex.
dim SQLSearch as integer
sqlSearch = Val(Text1.Text)
"SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
the more you type in the textbox, the closer your search will be
please note the use of the % in the SQL Query
you could even put your search into the textboxes textchange ()
if your using ado
ex. ado.recordsource = "SELECT Whatever FROM Wherever WHERE Field LIKE '%" & SqlSearch & "%'"
ado.refresh
so as you type, the the search gets smaller
Hope this helps
Help is great only if you ask correctly 
|
|
|
|
|
i built a database in excel sheet. everything seems to be ok except deleting records. i defined everything and opened database.
INSERT INTO works properly but when i use
Sql_string = "DELETE FROM test_base WHERE ID='"+ID_act+"'";
database.ExecuteSQL(sSql);
i get an error - "database error: could not delete from specified tables"
i have no clue why... anyone can provide some working code snippet?
|
|
|
|
|
Is the ID a string ? If it's an integer, try removing the quotes.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
it's a string. just converted integer with some zeros glued in front of it to make all IDs 6-digit long - to make it sensibly sortable :]
|
|
|
|
|
WHERE ID='"+ID_act+"'
I tohught it was '" & ID_act & "' for a string. At least thats what i use and works OK in Access & SQL Server.
Jon
|
|
|
|
|
'&' operator concerns VB (or VBA in access) as far as i remember...
my code was from C++.
|
|
|
|
|
Dear Gurus,
Err Num: -2147467259:
Err Desc: Operation must use an updatable query.
Err Source: Microsoft JET Database Engine
The majority of the users run into this crazy error when trying to insert, update or delete a record. I, my boss and the IT admin have no problems at all with either inserting, updating, or deleting records. The three of us have full access to the location where the MS Access DB resides. The error only happens to the rest of the users at my work place. We added read/write access to the troublesome users but did not fix the problem.
IIS Version: 5.0
App: ASP
Script: VBScript
DB: MS Access
I appreciate any help from you gurus,
Khang
=========================================================================
Here is the code snippets:
Global.asp:
----------
Sub Session_OnStart
' Database Parameters
Session( "Database_Location" ) = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & server.mappath("\CMPSupport\database\support.mdb") & ";"
End Sub
Categories.asp:
--------------
' Open Database Connection
set myConn = Server.CreateObject( "ADODB.Connection" )
MyConn.Open Session( "Database_Location" )
...
If Category <> "" Then
' Insert new Category to DB
SQL = "INSERT into Download_SubCategories (Category) values ('" & Category & "');"
Set RS = MyConn.Execute(SQL)
|
|
|
|
|
If I'm not mistaken, you have to give read/write permission to the folder where the Access .mdb file resides (from your post it seems you only give access to the .mdb).
This is because Access needs to create a temporary file when it is being opened.
Tell me if it fixed your problem.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thank you so much for replying that quick, Edbert. I will let you know when I can get the users carrying out the test.
Happy holidays,
Khang
|
|
|
|
|
to do it programatically, i'll need to write alot of code.
this will reduce my application's performance (i.e. think of a table with 0.5M rows)
i want a simple SQL LIKE (or something like that) that will do it for me.
|
|
|
|
|
Hello,
In my table i have a column of type "bigint".
i want to support two kind of filtering on that column.
a "value" filter (>,<,=).
and a "starts with" filter (i.e. "LIKE '44*'").
the first one is quite easy
the second is unknown to me.... (i don't want to do it programmatically).
any ideas?
|
|
|
|
|
If your database support the function that converts numeric value to string just like the SQL Server function: CONVERT(char(length),numeric), you can use the following SQL statment:
SELECT * from your table WHERE cnovert(char(max_length),numeric_column) LIKE '44%'
Lisoft
|
|
|
|
|
Hi, thanks for the reply.
this is not the problem.
i'm using VB6. The ADO recordset has a Filter property.
i wish to use it to simulate a "LIKE" filter on a numeric column.
currently the filtter property allows only value (=,<,>) filtering on a numeric column (unlike the SQL WHERE, which allows almost anything).
i'm looking for a trick to simulate a "LIKE" filter on the column and still retrain my option to use the regular =,<,> operator when needed.
btw, if i convert the numeric values into string in the select query (first of all, it will return a much bigger table in bytes sum) the =,<,> operators are refering to alphabet positioning, and not Integer value.
thanks
|
|
|
|
|
Hello,
In my table i have a column of type "bigint".
i want to support two kind of filtering on that column.
a "value" filter (>,<,=).
and a "starts with" filter (i.e. "LIKE '44*'").
the first one is quite easy
the second is unknown to me.... (i don't want to do it programmatically).
any ideas?
|
|
|
|
|
|
Try this
where to_char(BIGINT_column,'999999999999') LIKE '44%'
But be prepared to possibly handle a huge number of records.
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
|
|
|
|
|
Hi,
I have to generate a report but I am having difficulty getting the data
out of the SQL Server. The report is produced daily, weekly or monthly
but my query doesn't work all the time.
The report lists the facilities that are available to be hired out. A
facility is something like a meeting room, or conference hall and so on.
There is also a reservation table that lists when each facility is
booked from and until. The monthly reports seem to work okay, but
sometimes the weekly reports don't contain all the data and frequently
the daily reports are missing data.
The two tables are (I've listed only the keys and the fields I need):
Facility: FacilityId (the primary key), Description, LocationId
FacilityReservation: FacilityReservationId (the primary key), FacilityId
(the foreign key), StartTime, EndTime
What I need is a list of each facility with the amount of time it is
booked for. My SQL is:
SELECT f.FacilityId, f.LocationId, tu.TimeUtilized
FROM Facility AS f
INNER JOIN (
SELECT Facility.FacilityID, SUM(DATEDIFF(minute, StartTime,
EndTime)) AS TimeUtilized
FROM FacilityReservation
INNER JOIN Facility ON Facility.FacilityId =
FacilityReservation.FacilityId
WHERE StartTime BETWEEN @ReportStartTime AND @ReportEndTime
GROUP BY Facility.FacilityID) AS tu ON f.FacilityID = tu.FacilityID
Does anyone have any ideas why there is missing data?
Cheers,
Andy
|
|
|
|
|
|
Hi,
i have a windows application developed, now i have created a deployment setup project for it. it includes the project output and dependencies and the .rpt files.
Well when i install it on the windows XP it runs smooth. i am using MS Access 2002 file format.
But when i install it on win 2000, I am not able to get the dataset from the database. The aaplication starts and gives me an error when i try to login. It says " Object reference not set to an instance", well i am using odbc DNS connection to MS Access.
I also tried installing odbc_net driver, but still same result, and also i thought it wont work as it is already available with the installation pack as .dll file.
What might be the problem. Do i need any drivers to install on win 9x and win 2000 OS.
DOES setup wizard need any other thing to add to it for win 2000 or does win 2000 need service packs as well.
Thanks
Imran.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
