default value in field

default value in field

am 25.01.2005 10:26:21 von Lex Luthor

how can i get default value field in a db like .mdb , swl server and mysql
with asp programming ??

Re: default value in field

am 25.01.2005 10:41:55 von exjxw.hannivoort

Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:

> how can i get default value field in a db like .mdb , swl server and
> mysql with asp programming ??

Something like this?

SQL=
"UPDATE myTable SET myField='myDefault value' "&_
"WHERE myField='' OR isNull(myField)"


--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: default value in field

am 25.01.2005 12:16:51 von Lex Luthor

i want to GET no SET..
"Evertjan." ha scritto nel messaggio
news:Xns95E96CD8F780Aeejj99@194.109.133.29...
> Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:
>
>> how can i get default value field in a db like .mdb , swl server and
>> mysql with asp programming ??
>
> Something like this?
>
> SQL=
> "UPDATE myTable SET myField='myDefault value' "&_
> "WHERE myField='' OR isNull(myField)"
>
>
> --
> Evertjan.
> The Netherlands.
> (Replace all crosses with dots in my emailaddress)
>

Re: default value in field

am 25.01.2005 13:02:18 von reb01501

Lex Luthor wrote:
> how can i get default value field in a db like .mdb , swl server and
> mysql with asp programming ??

You'll need to ask about mysql in a mysql group, but the value of the
default property can be retrieved form the other two via ADOX in the case of
Jet databases, and system views and procedures in SQL Server. Start here:
http://www.aspfaq.com/show.asp?id=2177
http://msdn.microsoft.com/library/en-us/ado270/htm/admscadod dloverview.asp
http://msdn.microsoft.com/library/en-us/oledb/htm/oledbprovj et_provider_specific_properties_in_dbpropset_jetoledb_column .asp

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: default value in field

am 25.01.2005 16:30:52 von exjxw.hannivoort

Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:
> "Evertjan." ha scritto nel messaggio
>> Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:
>>
>>> how can i get default value field in a db like .mdb , swl server and
>>> mysql with asp programming ??
>>
>> Something like this?
>>
>> SQL=
>> "UPDATE myTable SET myField='myDefault value' "&_
>> "WHERE myField='' OR isNull(myField)"
>
> i want to GET no SET..

[please do not toppost on usenet]

Sorry, but that makes no sense to me, Lex.

What is a default value of a field if it is not set that way?

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: default value in field

am 25.01.2005 17:16:17 von reb01501

Evertjan. wrote:
> Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:
>> "Evertjan." ha scritto nel messaggio
>>> Lex Luthor wrote on 25 jan 2005 in
>>> microsoft.public.inetserver.asp.db:
>>>
>>>> how can i get default value field in a db like .mdb , swl server
>>>> and mysql with asp programming ??
>>>
>>> Something like this?
>>>
>>> SQL=
>>> "UPDATE myTable SET myField='myDefault value' "&_
>>> "WHERE myField='' OR isNull(myField)"
>>
>> i want to GET no SET..
>
> [please do not toppost on usenet]
>
> Sorry, but that makes no sense to me, Lex.
>
> What is a default value of a field if it is not set that way?
>

Many rdbms' allow designers to specify default values as part of the
database schema. These values are inserted into new records if no value is
provided in the insert statement for the field for which a default value has
been created. For example, in SQL Server, you could do this:
CREATE TABLE dbo.Calendar
(
cal_date datetime NOT NULL,
cal_start_time datetime NOT NULL,
cal_end_time datetime NOT NULL,
day_type char(1)
CONSTRAINT DF_Calendardaytype
DEFAULT 'w' NOT NULL,
CONSTRAINT PK__Calendar__5CA1C101
PRIMARY KEY CLUSTERED (cal_date)
ON [PRIMARY]
)

The day_type column has a default of 'w', so this insert statement:
INSERT INTO dbo.Calendar
(cal_date,
cal_start_time,
cal_end_time)
VALUES
('20050125',
'07:00',
'15:00')

Will result in a record whose day_type contains 'w'.

I'm assuming this is the property whose value he wishes to retrieve.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: default value in field

am 26.01.2005 09:10:24 von Lex Luthor

an example please..
"Bob Barrows [MVP]" ha scritto nel messaggio
news:OmOw4WtAFHA.2572@tk2msftngp13.phx.gbl...
> Lex Luthor wrote:
>> how can i get default value field in a db like .mdb , swl server and
>> mysql with asp programming ??
>
> You'll need to ask about mysql in a mysql group, but the value of the
> default property can be retrieved form the other two via ADOX in the case
> of Jet databases, and system views and procedures in SQL Server. Start
> here:
> http://www.aspfaq.com/show.asp?id=2177
> http://msdn.microsoft.com/library/en-us/ado270/htm/admscadod dloverview.asp
> http://msdn.microsoft.com/library/en-us/oledb/htm/oledbprovj et_provider_specific_properties_in_dbpropset_jetoledb_column .asp
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: default value in field

am 26.01.2005 14:13:54 von reb01501

?
Everything you need to know is covered in these articles, especially if you
supplement the SQL Server examples with some reading in SQL Books Online.
For example, for SQL Server, here's a copy-paste from the aspfaq article
(with a slight modification to eliminate the dynamic sql, and another
modification to show all the field values of the returned recordset):

<%
dbname = "databasename"
tablename = "tablename"

ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _
"Data Source=;" & _
"User Id=; Password=;" & _
"Initial Catalog=" & dbname

set adodbConn = CreateObject("ADODB.Connection")
adodbConn.Open ConnStr
set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 'adUseClient
adodbConn.sp_columns tablename,rs
set rs.activeconnection = nothing
adodbConn.Close: set adodbConn = nothing
Response.Write "

"
for each fld in rs.Fields
Response.Write ""
next
Response.Write "
" & fld.name & "
"
str = rs.GetString(2,,"		","
"," ")
rs.close: set rs = nothing
str=left(str,len(str)-8)
response.write str
response.write "
"
%>


Substitute your specific information, run this code and look at the
COLUMN_DEF field.

For Access, it is a little harder.

Actually, come to think of it, you may be able to use OpenSchema for all of
your databases. Try this:
<%
dbname = "databasename"
tablename = "tablename"

ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _
"Data Source=;" & _
"User Id=; Password=;" & _
"Initial Catalog=" & dbname

set adodbConn = CreateObject("ADODB.Connection")
adodbConn.Open ConnStr
set rs = adodbConn.OpenSchema(adSchemaColumns, _
Array(Empty,Empty,tablename))
Response.Write ""
for each fld in rs.Fields
Response.Write ""
next
Response.Write "
" & fld.name & "
"
str = rs.GetString(2,,"		","
"," ")
rs.close: set rs = nothing
adodbConn.Close: set adodbConn = nothing

str=left(str,len(str)-8)
response.write str
response.write "
"
%>


You can make this return information about a specific column by changing
this:
set rs = adodbConn.OpenSchema(adSchemaColumns, _
Array(Empty,Empty,tablename))

to this:
col = "SomeColumn"
set rs = adodbConn.OpenSchema(adSchemaColumns, _
Array(Empty,Empty,tablename, col))

This workd with both SQL Server and Jet. I don't know if it will work with
MySQL, but i suspect that it will.

Bob Barrows

Lex Luthor wrote:
> an example please..

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: default value in field

am 26.01.2005 16:29:46 von Lex Luthor

i must get a key name with a simple query
as select @@identity that response key value but not key name

"Bob Barrows [MVP]" ha scritto nel messaggio
news:eH%23aij6AFHA.904@TK2MSFTNGP12.phx.gbl...
> ?
> Everything you need to know is covered in these articles, especially if
you
> supplement the SQL Server examples with some reading in SQL Books Online.
> For example, for SQL Server, here's a copy-paste from the aspfaq article
> (with a slight modification to eliminate the dynamic sql, and another
> modification to show all the field values of the returned recordset):
>
> <%
> dbname = "databasename"
> tablename = "tablename"
>
> ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _
> "Data Source=;" & _
> "User Id=; Password=;" & _
> "Initial Catalog=" & dbname
>
> set adodbConn = CreateObject("ADODB.Connection")
> adodbConn.Open ConnStr
> set rs = CreateObject("ADODB.Recordset")
> rs.cursorlocation = 3 'adUseClient
> adodbConn.sp_columns tablename,rs
> set rs.activeconnection = nothing
> adodbConn.Close: set adodbConn = nothing
> Response.Write "

"
> for each fld in rs.Fields
> Response.Write ""
> next
> Response.Write "
" & fld.name & "
"
> str = rs.GetString(2,,"		","
"," ")
> rs.close: set rs = nothing
> str=left(str,len(str)-8)
> response.write str
> response.write "
"
> %>
>
>
> Substitute your specific information, run this code and look at the
> COLUMN_DEF field.
>
> For Access, it is a little harder.
>
> Actually, come to think of it, you may be able to use OpenSchema for all
of
> your databases. Try this:
> <%
> dbname = "databasename"
> tablename = "tablename"
>
> ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _
> "Data Source=;" & _
> "User Id=; Password=;" & _
> "Initial Catalog=" & dbname
>
> set adodbConn = CreateObject("ADODB.Connection")
> adodbConn.Open ConnStr
> set rs = adodbConn.OpenSchema(adSchemaColumns, _
> Array(Empty,Empty,tablename))
> Response.Write ""
> for each fld in rs.Fields
> Response.Write ""
> next
> Response.Write "
" & fld.name & "
"
> str = rs.GetString(2,,"		","
"," ")
> rs.close: set rs = nothing
> adodbConn.Close: set adodbConn = nothing
>
> str=left(str,len(str)-8)
> response.write str
> response.write "
"
> %>
>
>
> You can make this return information about a specific column by changing
> this:
> set rs = adodbConn.OpenSchema(adSchemaColumns, _
> Array(Empty,Empty,tablename))
>
> to this:
> col = "SomeColumn"
> set rs = adodbConn.OpenSchema(adSchemaColumns, _
> Array(Empty,Empty,tablename, col))
>
> This workd with both SQL Server and Jet. I don't know if it will work with
> MySQL, but i suspect that it will.
>
> Bob Barrows
>
> Lex Luthor wrote:
> > an example please..
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: default value in field

am 26.01.2005 16:30:22 von Lex Luthor

not sense
"Bob Barrows [MVP]" ha scritto nel messaggio
news:uyAX0kvAFHA.208@TK2MSFTNGP12.phx.gbl...
> Evertjan. wrote:
> > Lex Luthor wrote on 25 jan 2005 in microsoft.public.inetserver.asp.db:
> >> "Evertjan." ha scritto nel messaggio
> >>> Lex Luthor wrote on 25 jan 2005 in
> >>> microsoft.public.inetserver.asp.db:
> >>>
> >>>> how can i get default value field in a db like .mdb , swl server
> >>>> and mysql with asp programming ??
> >>>
> >>> Something like this?
> >>>
> >>> SQL=
> >>> "UPDATE myTable SET myField='myDefault value' "&_
> >>> "WHERE myField='' OR isNull(myField)"
> >>
> >> i want to GET no SET..
> >
> > [please do not toppost on usenet]
> >
> > Sorry, but that makes no sense to me, Lex.
> >
> > What is a default value of a field if it is not set that way?
> >
>
> Many rdbms' allow designers to specify default values as part of the
> database schema. These values are inserted into new records if no value is
> provided in the insert statement for the field for which a default value
has
> been created. For example, in SQL Server, you could do this:
> CREATE TABLE dbo.Calendar
> (
> cal_date datetime NOT NULL,
> cal_start_time datetime NOT NULL,
> cal_end_time datetime NOT NULL,
> day_type char(1)
> CONSTRAINT DF_Calendardaytype
> DEFAULT 'w' NOT NULL,
> CONSTRAINT PK__Calendar__5CA1C101
> PRIMARY KEY CLUSTERED (cal_date)
> ON [PRIMARY]
> )
>
> The day_type column has a default of 'w', so this insert statement:
> INSERT INTO dbo.Calendar
> (cal_date,
> cal_start_time,
> cal_end_time)
> VALUES
> ('20050125',
> '07:00',
> '15:00')
>
> Will result in a record whose day_type contains 'w'.
>
> I'm assuming this is the property whose value he wishes to retrieve.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: default value in field

am 26.01.2005 18:10:43 von reb01501

Lex Luthor wrote:
> i must get a key name with a simple query
> as select @@identity that response key value but not key name
>
I do not have a clue as to what you are talking about. Anyone else?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: default value in field

am 26.01.2005 18:46:44 von Roland Hall

"Bob Barrows [MVP]" wrote in message
news:uQRW3n8AFHA.3836@tk2msftngp13.phx.gbl...
: Lex Luthor wrote:
: > i must get a key name with a simple query
: > as select @@identity that response key value but not key name
: >
: I do not have a clue as to what you are talking about. Anyone else?

Ya'. It sounds like you don't have a clue. (O:=.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: default value in field

am 27.01.2005 09:22:59 von Lex Luthor

i want to know the field name as "select @@identity" value.. the last
"Bob Barrows [MVP]" ha scritto nel messaggio
news:uQRW3n8AFHA.3836@tk2msftngp13.phx.gbl...
> Lex Luthor wrote:
>> i must get a key name with a simple query
>> as select @@identity that response key value but not key name
>>
> I do not have a clue as to what you are talking about. Anyone else?
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>