Different syntax writing to SQL Server DB

M

Mark A. Sam

The following ASP code snippet works when posting to an Access DB, but the
page hangs when I apply this to an SQL Database. I believe this code is the
issue. Is there some special property I am missing or different syntax that
needs to be applied here?

Thanks for any help and God Bless,

Mark A. Sam

if Request("mode") = "NEW" then
getfields()
Set rs = Server.CreateObject("ADODB.RecordSet")
q = "INSERT INTO CompanyInfo (Company,Contact,Address1,Address2," &_
"City,State,Zip,Country,Phone,Email,Key,DisplayEmail,DisplayPhone," &_
"Trucker,Shipper,TruckBroker,FoodBroker,Manufacturer,Merchandiser," &_
"Distributor,LTLCarrier,OtherType,trkNoUnits,trkStates,trkReefer,trkVan,"
&_
"trkFlatBed,trkDumpTruck,trkOther,shpLoads," &_

"shpStates,shpReefer,shpVan,shpFlatbed,shpDumpTruck,shpOther,Comments,Active
,howsitefound)" &_
" VALUES ('" & company & "','" & contact & "','" & address1 & "','" &
address2 &_
"','" & city & "','" & state & "','" & zip & "','" & country & "','" &
phone &_
"','" & email & "','" & key & "','" & displayemail & "','" & displayphone
&_
"'," & trucker & "," & shipper & "," & truckbroker & "," & foodbroker &
"," &_
manufacturer & "," & merchandiser & "," & distributor & "," & ltlcarrier
&_
",'" & othertype & "'," & trknounits & "," & trkstates & "," & trkreefer
&_
"," & trkvan & "," & trkflatbed & "," & trkdumptruck & ",'" & trkother &
"'," &_
shploads & "," & shpstates & "," & shpreefer & "," & shpvan & "," &
shpflatbed &_
"," & shpdumptruck & ",'" & shpother & "','" & comments & "',1,'" &
howsitefound & "')"
rs.Open q, "DSN=TruckloadsSQL;"
q = "SELECT Max(CompanyID) AS coid FROM CompanyInfo"
rs.Open q, "DSN=TruckloadsSQL;"
coid = rs("coid").value
Response.Cookies("coid") = coid
Response.Cookies("key") = key
d = Date
Response.Cookies("coid").expires = DateAdd("yyyy", 2, d)
Response.Cookies("key").expires = DateAdd("yyyy", 2, d)
mess = "Your Record has been added to the database. Your Company ID number
is " &_
coid & ". Your password is " & key & ". Please write them down. " &_
"You may edit your record now, if you wish"
rs.Close
Set rs = Nothing
 
K

Kevin Spencer

If the column "trkNUnits" is numeric, you need to remove the single quotes
around the value. It would help if you post the error message you get back
(if any).

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.
 
M

Mark A. Sam

Hello Kevin,

There was no error, just a Page Not Found message.

TrkNoUnits is numeric, but the Quotes are surrounding the OtherType field.

",'" & othertype & "'," <--- & trknounits & "," &

Its easy to look at it wrongly. There may be datatype problems. I had to
create the tables manually becuase the upsize wizard wouldn't go through all
its steps, so I may have problems. I didn't think of that, and it is a good
are to look at.

God Bless,

Mark
 
M

Mark A. Sam

How can I put an Alert in a function and within the HTML for debugging. I
tried this in the function:

Sub getfields()
alert('debug');

....


This caused procedure to bomb out right away.

Thanks

Mark
 
K

Kevin Spencer

If by "a Page Not Found message" you're referring to an HTTP 404 Not Found
message, the problem is not in your code, but in the location of the page.
That error comes from the web server when it can't find the URL you specify.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.
 
K

Kevin Spencer

The only "Alert function" you can execute in a web page is a JavaScript
function. Example:

<script type="text/javascript"><!--
function getfields()
{
alert("debug");
}
// --></script>

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.
 
M

Mark A. Sam

Sorry, my error. Here is the message:

The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot
be displayed.

--------------------------------------------------------------------------

Please try the following:

a.. Open the www.truckloads.net home page, and then look for links
to the information you want.
b.. Click the Refresh button, or try again later.

c.. Click Search to look for information on the Internet.
d.. You can also see a list of related sites.




HTTP 500 - Internal server error
Internet Explorer
 
M

Mark A. Sam

Kevin,

I'm not communicating well. Getfields is an existing subfunction in my
page. I was just demonstrating how I used the Alert method.
 
K

Kevin Spencer

I'm not following well! ;-)

You should be able to use Response.Write to view anything from the
server-side code. If your problem is that you are trying to use a Sub that
displays a client-side alert, it looks like you may be passing in a variable
with quotes around it. A string is one of 2 things: a string literal
("example") or a string variable, which is a token without quotes.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.
 
K

Kevin Spencer

Hi Mark,

That's not a "Page not Found" error, but a 500 server error, which indicates
a problem in your code, as you suspected. Check for any other numeric values
(I saw a "1" with single quotes around it, for example) that have single
quotes aournd them.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.
 
S

Stefan B Rusynko

For differences between Access and SQL see
http://www.aspfaq.com/show.asp?id=2214
For the reserved names in both see
http://www.aspfaq.com/show.asp?id=2080
You use key as a field name which is reserved in both
- change your field name

For debugging Add a test line like
response.write "q= " & q & "<br>"
before each
rs.Open q, "DSN=TruckloadsSQL;"
to examine the accuraracy of the statements

As for getfields() what is it supposed to return and I don't see it being used in your code snippet?

PS
Am presuming your IF in top of you code has a closing End If in page



| Kevin,
|
| I'm not communicating well. Getfields is an existing subfunction in my
| page. I was just demonstrating how I used the Alert method.
|
|
|
|
| | > The only "Alert function" you can execute in a web page is a JavaScript
| > function. Example:
| >
| > <script type="text/javascript"><!--
| > function getfields()
| > {
| > alert("debug");
| > }
| > // --></script>
| >
| > --
| > HTH,
| >
| > Kevin Spencer
| > Microsoft MVP
| > .Net Developer
| > Sometimes you eat the elephant.
| > Sometimes the elephant eats you.
| >
| > | > > How can I put an Alert in a function and within the HTML for debugging.
| I
| > > tried this in the function:
| > >
| > > Sub getfields()
| > > alert('debug');
| > >
| > > ...
| > >
| > >
| > > This caused procedure to bomb out right away.
| > >
| > > Thanks
| > >
| > > Mark
| > >
| > >
| > >
| > >
| > > | > >> The following ASP code snippet works when posting to an Access DB, but
| > >> the
| > >> page hangs when I apply this to an SQL Database. I believe this code is
| > > the
| > >> issue. Is there some special property I am missing or different syntax
| > > that
| > >> needs to be applied here?
| > >>
| > >> Thanks for any help and God Bless,
| > >>
| > >> Mark A. Sam
| > >>
| > >> if Request("mode") = "NEW" then
| > >> getfields()
| > >> Set rs = Server.CreateObject("ADODB.RecordSet")
| > >> q = "INSERT INTO CompanyInfo (Company,Contact,Address1,Address2," &_
| > >> "City,State,Zip,Country,Phone,Email,Key,DisplayEmail,DisplayPhone,"
| &_
| > >> "Trucker,Shipper,TruckBroker,FoodBroker,Manufacturer,Merchandiser,"
| &_
| > >>
| > >
| "Distributor,LTLCarrier,OtherType,trkNoUnits,trkStates,trkReefer,trkVan,"
| > >> &_
| > >> "trkFlatBed,trkDumpTruck,trkOther,shpLoads," &_
| > >>
| > >>
| > >
| "shpStates,shpReefer,shpVan,shpFlatbed,shpDumpTruck,shpOther,Comments,Active
| > >> ,howsitefound)" &_
| > >> " VALUES ('" & company & "','" & contact & "','" & address1 & "','" &
| > >> address2 &_
| > >> "','" & city & "','" & state & "','" & zip & "','" & country & "','"
| &
| > >> phone &_
| > >> "','" & email & "','" & key & "','" & displayemail & "','" &
| > > displayphone
| > >> &_
| > >> "'," & trucker & "," & shipper & "," & truckbroker & "," & foodbroker
| &
| > >> "," &_
| > >> manufacturer & "," & merchandiser & "," & distributor & "," &
| > >> ltlcarrier
| > >> &_
| > >> ",'" & othertype & "'," & trknounits & "," & trkstates & "," &
| > >> trkreefer
| > >> &_
| > >> "," & trkvan & "," & trkflatbed & "," & trkdumptruck & ",'" &
| trkother
| > >> &
| > >> "'," &_
| > >> shploads & "," & shpstates & "," & shpreefer & "," & shpvan & "," &
| > >> shpflatbed &_
| > >> "," & shpdumptruck & ",'" & shpother & "','" & comments & "',1,'" &
| > >> howsitefound & "')"
| > >> rs.Open q, "DSN=TruckloadsSQL;"
| > >> q = "SELECT Max(CompanyID) AS coid FROM CompanyInfo"
| > >> rs.Open q, "DSN=TruckloadsSQL;"
| > >> coid = rs("coid").value
| > >> Response.Cookies("coid") = coid
| > >> Response.Cookies("key") = key
| > >> d = Date
| > >> Response.Cookies("coid").expires = DateAdd("yyyy", 2, d)
| > >> Response.Cookies("key").expires = DateAdd("yyyy", 2, d)
| > >> mess = "Your Record has been added to the database. Your Company ID
| > > number
| > >> is " &_
| > >> coid & ". Your password is " & key & ". Please write them down. " &_
| > >> "You may edit your record now, if you wish"
| > >> rs.Close
| > >> Set rs = Nothing
| > >>
| > >>
| > >
| > >
| >
| >
|
|
 
M

Mark A. Sam

Thanks Stefan and Kevin...

I turned off the "Show friendly HTML messages" option in my browser internet
options, and it yielded the Error messages and line numbers I needed to get
the issues resolved.

God Bless,

Mark
 
K

Kevin Spencer

You're very welcome, and I can use all the Blessing I can get! ;-)

--

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.
 
Top