Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Problem in building dynamic sql update statement

Reply
Thread Tools

Problem in building dynamic sql update statement

 
 
Jack
Guest
Posts: n/a
 
      12-15-2004
Hi,
I am trying to update database from asp. However, the finalupdate field is
not
working here. The finalupdate field is a Access Database field of type
yes/no. Any help/advise is appreciated. Regards

THE DYNAMIC SQL STATEMENT:

strsql = "UPDATE main SET main.FirstName ='" &l_firstname& "', " & _
"main.LastName = '" &l_lastname& "', " & _
"main.ColorPreference = '" &l_colorpreference& "', " & _
"main.FoodPreference = '" &l_foodpreference& "', " & _
"main.FinalUpdate = &l_finalupdate& where " & _
"main.SocialSecurityNumber)='" &l_ss& "'

THE TRANSLATED SQL STATEMENT:


UPDATE main SET main.FirstName ='Cocoa', main.LastName = 'Jones',
main.ColorPreference = 'Choco', main.FoodPreference = 'Lamb',
main.FinalUpdate = &l_finalupdate& where
main.SocialSecurityNumber)='222-22-2222'
 
Reply With Quote
 
 
 
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      12-15-2004
You have your literal variable name in your SQL [non]query.

strsql = "UPDATE main SET main.FirstName ='" & l_firstname & "', " & _
"main.LastName = '" & l_lastname & "', " & _
"main.ColorPreference = '" & l_colorpreference & "', " &
_
"main.FoodPreference = '" & l_foodpreference & "', " &
_
"main.FinalUpdate = & " & CBool(l_finalupdate) & "
where " & _
"main.SocialSecurityNumber)='" & l_ss & "'

Also, notice I put spaces on either side of the & characters above. That is
a good habit to develop, because you'll wind up pulling your hair out one
day trying to figure out why this:

<%
haircolor="brown"
Response.write "<option value="""&haircolor&""">"&haircolor&"</option>"
%>

returns this:

Expected end of statement

Ray at work

"Jack" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I am trying to update database from asp. However, the finalupdate field is
> not
> working here. The finalupdate field is a Access Database field of type
> yes/no. Any help/advise is appreciated. Regards
>
> THE DYNAMIC SQL STATEMENT:
>
> strsql = "UPDATE main SET main.FirstName ='" &l_firstname& "', " & _
> "main.LastName = '" &l_lastname& "', " & _
> "main.ColorPreference = '" &l_colorpreference& "', " &

_
> "main.FoodPreference = '" &l_foodpreference& "', " &

_
> "main.FinalUpdate = &l_finalupdate& where " & _
> "main.SocialSecurityNumber)='" &l_ss& "'
>
> THE TRANSLATED SQL STATEMENT:
>
>
> UPDATE main SET main.FirstName ='Cocoa', main.LastName = 'Jones',
> main.ColorPreference = 'Choco', main.FoodPreference = 'Lamb',
> main.FinalUpdate = &l_finalupdate& where
> main.SocialSecurityNumber)='222-22-2222'



 
Reply With Quote
 
 
 
 
Jack
Guest
Posts: n/a
 
      12-15-2004
Thanks again for your help Ray. And appreciate your last piece of advise
regarding the habit of having spaces on either side of & character. Regards.

"Ray Costanzo [MVP]" wrote:

> You have your literal variable name in your SQL [non]query.
>
> strsql = "UPDATE main SET main.FirstName ='" & l_firstname & "', " & _
> "main.LastName = '" & l_lastname & "', " & _
> "main.ColorPreference = '" & l_colorpreference & "', " &
> _
> "main.FoodPreference = '" & l_foodpreference & "', " &
> _
> "main.FinalUpdate = & " & CBool(l_finalupdate) & "
> where " & _
> "main.SocialSecurityNumber)='" & l_ss & "'
>
> Also, notice I put spaces on either side of the & characters above. That is
> a good habit to develop, because you'll wind up pulling your hair out one
> day trying to figure out why this:
>
> <%
> haircolor="brown"
> Response.write "<option value="""&haircolor&""">"&haircolor&"</option>"
> %>
>
> returns this:
>
> Expected end of statement
>
> Ray at work
>
> "Jack" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi,
> > I am trying to update database from asp. However, the finalupdate field is
> > not
> > working here. The finalupdate field is a Access Database field of type
> > yes/no. Any help/advise is appreciated. Regards
> >
> > THE DYNAMIC SQL STATEMENT:
> >
> > strsql = "UPDATE main SET main.FirstName ='" &l_firstname& "', " & _
> > "main.LastName = '" &l_lastname& "', " & _
> > "main.ColorPreference = '" &l_colorpreference& "', " &

> _
> > "main.FoodPreference = '" &l_foodpreference& "', " &

> _
> > "main.FinalUpdate = &l_finalupdate& where " & _
> > "main.SocialSecurityNumber)='" &l_ss& "'
> >
> > THE TRANSLATED SQL STATEMENT:
> >
> >
> > UPDATE main SET main.FirstName ='Cocoa', main.LastName = 'Jones',
> > main.ColorPreference = 'Choco', main.FoodPreference = 'Lamb',
> > main.FinalUpdate = &l_finalupdate& where
> > main.SocialSecurityNumber)='222-22-2222'

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with building sql statement to handle single quote in a fi Jack ASP General 4 01-10-2006 09:06 PM
Problems with building sql statement Jack ASP General 3 03-31-2005 07:05 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page David Browne ASP .Net 0 08-21-2003 10:43 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments