Can"t insert records from asp page with request.form

I'm trying to insert records into an sql database coming from a page
using the request
..form method. The table "general" has a primary key 'geid .' I get the
following error:

Cannot insert the value NULL into column 'geid', table 'general';
column does not allow nulls. INSERT fails.

....not sure how to include the 'geid' field into the scheme.

strSql = "insert into general (firstname,surname,company) values ('"
strSql = StrSql & Request.Form("firstname") & "', '"
strSql = StrSql & Request.Form("surname") & "', '"
strSql = StrSql & Request.Form("company") & "')"
myconn.Execute (StrSql)

Thanks in advance

Re: Can"t insert records from asp page with request.form

..Net Sports wrote:
> I'm trying to insert records into an sql database coming from a page
> using the request
> .form method. The table "general" has a primary key 'geid .' I get the
> following error:
>
> Cannot insert the value NULL into column 'geid', table 'general';
> column does not allow nulls. INSERT fails.
>
> ...not sure how to include the 'geid' field into the scheme.
>
> strSql = "insert into general (firstname,surname,company) values ('"
> strSql = StrSql & Request.Form("firstname") & "', '"
> strSql = StrSql & Request.Form("surname") & "', '"
> strSql = StrSql & Request.Form("company") & "')"

Insert these lines here:

Response.Write strSql
Response.End

> myconn.Execute (StrSql)
>
> Thanks in advance

Run the page and look at the resulting sql statement in the browser window.
This is the statement being sent to the database to be executed. Does it
look like it will run as desired in QA? if you still can't figure out the
problem, show us the sql statement.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures:
http://groups.google.com/group/microsoft.public.inetserver.a sp.general/msg/5d3c9d4409dc1701?hl=en&


--
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: Can"t insert records from asp page with request.form

".Net Sports" wrote in message
news:1167955242.695574.11590@v33g2000cwv.googlegroups.com...
> I'm trying to insert records into an sql database coming from a page
> using the request
> .form method. The table "general" has a primary key 'geid .' I get the
> following error:
>
> Cannot insert the value NULL into column 'geid', table 'general';
> column does not allow nulls. INSERT fails.
>
> ...not sure how to include the 'geid' field into the scheme.
>
> strSql = "insert into general (firstname,surname,company) values ('"
> strSql = StrSql & Request.Form("firstname") & "', '"
> strSql = StrSql & Request.Form("surname") & "', '"
> strSql = StrSql & Request.Form("company") & "')"
> myconn.Execute (StrSql)
>
> Thanks in advance

A PK cannot be null, so something must assign it every time a row is
inserted. Often that something is the default for that PK column, such as
(NEWID( ) ) if the column type is uniqueidentifier, or the next identity
value if it is an identity column. These sorts of self-maintaining PK
fields tend to make for a workable design.

If the PK in your table has no default (apparently the case) then your
insert code must generate a value for that field, that does not exist in the
table yet. Guaranteeing atomicity for this from [inherently multi-user] ASP
code can be a daunting task, so your best bet is to resolve this value on
the db side, as noted above.


-Mark

Re: Can"t insert records from asp page with request.form

Mark McGinty wrote:
> ".Net Sports" wrote in message
> news:1167955242.695574.11590@v33g2000cwv.googlegroups.com...
> > I'm trying to insert records into an sql database coming from a page
> > using the request
> > .form method. The table "general" has a primary key 'geid .' I get the
> > following error:
> >
> > Cannot insert the value NULL into column 'geid', table 'general';
> > column does not allow nulls. INSERT fails.
> >
> > ...not sure how to include the 'geid' field into the scheme.
> >
> > strSql = "insert into general (firstname,surname,company) values ('"
> > strSql = StrSql & Request.Form("firstname") & "', '"
> > strSql = StrSql & Request.Form("surname") & "', '"
> > strSql = StrSql & Request.Form("company") & "')"
> > myconn.Execute (StrSql)
> >
> > Thanks in advance
>
> A PK cannot be null, so something must assign it every time a row is
> inserted. Often that something is the default for that PK column, such as
> (NEWID( ) ) if the column type is uniqueidentifier, or the next identity
> value if it is an identity column. These sorts of self-maintaining PK
> fields tend to make for a workable design.
>
> If the PK in your table has no default (apparently the case) then your
> insert code must generate a value for that field, that does not exist in the
> table yet. Guaranteeing atomicity for this from [inherently multi-user] ASP
> code can be a daunting task, so your best bet is to resolve this value on
> the db side, as noted above.
>
>
> -Mark

thanks for your reply, I am trying to assign 'geid' a default value
while editing the DDL table parameters, but all the expressions I use
(+ 1, 'geid' + 1, 'geid') are not accepted.
?????

Re: Can"t insert records from asp page with request.form

"Bubba" wrote in message
news:1168013294.502503.250460@v33g2000cwv.googlegroups.com.. .
>
> Mark McGinty wrote:
>> ".Net Sports" wrote in message
>> news:1167955242.695574.11590@v33g2000cwv.googlegroups.com...
>> > I'm trying to insert records into an sql database coming from a page
>> > using the request
>> > .form method. The table "general" has a primary key 'geid .' I get the
>> > following error:
>> >
>> > Cannot insert the value NULL into column 'geid', table 'general';
>> > column does not allow nulls. INSERT fails.
>> >
>> > ...not sure how to include the 'geid' field into the scheme.
>> >
>> > strSql = "insert into general (firstname,surname,company) values ('"
>> > strSql = StrSql & Request.Form("firstname") & "', '"
>> > strSql = StrSql & Request.Form("surname") & "', '"
>> > strSql = StrSql & Request.Form("company") & "')"
>> > myconn.Execute (StrSql)
>> >
>> > Thanks in advance
>>
>> A PK cannot be null, so something must assign it every time a row is
>> inserted. Often that something is the default for that PK column, such
>> as
>> (NEWID( ) ) if the column type is uniqueidentifier, or the next identity
>> value if it is an identity column. These sorts of self-maintaining PK
>> fields tend to make for a workable design.
>>
>> If the PK in your table has no default (apparently the case) then your
>> insert code must generate a value for that field, that does not exist in
>> the
>> table yet. Guaranteeing atomicity for this from [inherently multi-user]
>> ASP
>> code can be a daunting task, so your best bet is to resolve this value on
>> the db side, as noted above.
>>
>>
>> -Mark
>
> thanks for your reply, I am trying to assign 'geid' a default value
> while editing the DDL table parameters, but all the expressions I use
> (+ 1, 'geid' + 1, 'geid') are not accepted.
> ?????

You're trying to reference the value in the previous row, it doesn't work
that way. Is there a reason you can't make it an identity column?

-Mark

Re: Can"t insert records from asp page with request.form

Thanks Mark, ( and Bob), yes , making it an identity column is what I
was overlooking.


Mark McGinty wrote:
> "Bubba" wrote in message
> news:1168013294.502503.250460@v33g2000cwv.googlegroups.com.. .
> >
> > Mark McGinty wrote:
> >> ".Net Sports" wrote in message
> >> news:1167955242.695574.11590@v33g2000cwv.googlegroups.com...
> >> > I'm trying to insert records into an sql database coming from a page
> >> > using the request
> >> > .form method. The table "general" has a primary key 'geid .' I get the
> >> > following error:
> >> >
> >> > Cannot insert the value NULL into column 'geid', table 'general';
> >> > column does not allow nulls. INSERT fails.
> >> >
> >> > ...not sure how to include the 'geid' field into the scheme.
> >> >
> >> > strSql = "insert into general (firstname,surname,company) values ('"
> >> > strSql = StrSql & Request.Form("firstname") & "', '"
> >> > strSql = StrSql & Request.Form("surname") & "', '"
> >> > strSql = StrSql & Request.Form("company") & "')"
> >> > myconn.Execute (StrSql)
> >> >
> >> > Thanks in advance
> >>
> >> A PK cannot be null, so something must assign it every time a row is
> >> inserted. Often that something is the default for that PK column, such
> >> as
> >> (NEWID( ) ) if the column type is uniqueidentifier, or the next identity
> >> value if it is an identity column. These sorts of self-maintaining PK
> >> fields tend to make for a workable design.
> >>
> >> If the PK in your table has no default (apparently the case) then your
> >> insert code must generate a value for that field, that does not exist in
> >> the
> >> table yet. Guaranteeing atomicity for this from [inherently multi-user]
> >> ASP
> >> code can be a daunting task, so your best bet is to resolve this value on
> >> the db side, as noted above.
> >>
> >>
> >> -Mark
> >
> > thanks for your reply, I am trying to assign 'geid' a default value
> > while editing the DDL table parameters, but all the expressions I use
> > (+ 1, 'geid' + 1, 'geid') are not accepted.
> > ?????
>
> You're trying to reference the value in the previous row, it doesn't work
> that way. Is there a reason you can't make it an identity column?
>
> -Mark