VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-ASP Shopping Cart Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 General help me questions
 Web Forrm To SQL Database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmw000
VP-ASP New User

137 Posts

Posted - May 04 2004 :  16:05:03  Show Profile  Visit bmw000's Homepage  Reply with Quote
I know this is a little off topic, but I figured there are many talented people here who can help me.

I have a news section on my website. I enter the data in the text box and it submits what I type into an ms access database via and ODBC connection.

Can I use the connection string from vpasp that connects the shopping cart to the ms sql database, call the connection page from vpasp using the include statement?

I would like to get rid of the ms access database and the odbc connection since I am already using ms sql server for my shopping cart.

I am a beginner when it comes to .asp.

What can I do to convert my existing code to allow me to insert into the ms sql database in lieu of the ms access database.

Here is code for my current web form. I don't think anything has to be changed here.

<form name="Search" method="Post" action="adddb.asp">

<head>
<title>BMW Solutions</title>
</head>

<h1 align="center"><font size="+4">BMW Solutions</font>
</h1>
<h3 align="center"><br>

Add What's New</h3>
<h3 align="center"><br>
<textarea rows="18" cols="81" name="txtnew" wrap=virtual></textarea>

</h3>
<p align="center">
<input type="submit" name="btnSearch" value="Submit">
<input type="reset" name="btnSearch" value="Reset"></form>

</p>


Here is the code for my current .asp file which adds the data to my access database.

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#INCLUDE VIRTUAL="adovbs.inc" -->


<%
Dim MyConn, RS, strnew
strnew = Request.Form("txtnew")
strnew = Replace(strnew, vbCrLf, "<br>")
'grab the form contents

Set MyConn=Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")
'since you are working with the RecordSet you need to create an instance of the RecordSet Object

MyConn.Open "bmwsolutions_news"
'getdata is your DSN (data source name) you created in ODBC

RS.Open "Select * From new", MyConn, adOpenDynamic, adLockPessimistic, adCMDText
'open both RecordSets

RS.AddNew
RS("new")= strnew
RS("Date") = now
RS.Update
'Update the first RecordSet

'Clean up
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
Response.Redirect ( "http://www.bmwsolutions.com/staff/staff.asp" )
%>

What needs to be changed to get this insert into my ms sql database in lieu of my current access database?

Any help would be greatly appreciated.

Thanks In Advance,

Brian Weber

devshb
Senior Member

United Kingdom
1898 Posts

Posted - May 05 2004 :  03:03:19  Show Profile  Visit devshb's Homepage  Reply with Quote
i could be wrong, but i'm unsure if sqlserver has the same kind of add-blank-record facility allowed as access does, and you won't be able to do this if you've got any mandatory columns that aren't auto-set by the database or if you've got any unique indexes on columns other than the auto-generated id column.
it'd be much tidier/easier to do an explicit insert rather than "insert a blank record then update it".
in that case, all you'd need to do is open your sqlserver connection, execute the insert statement, and then close your sqlserver connection (ie you wouldn't need to open/process any cursors)

Simon Barnaby
Freelance Developer
Java-E UK
[email protected]

Edited by - devshb on May 05 2004 03:04:30
Go to Top of Page

bmw000
VP-ASP New User

137 Posts

Posted - May 05 2004 :  19:59:17  Show Profile  Visit bmw000's Homepage  Reply with Quote
Thanks for the info.

I got it working.

I have a new problem.

When there are multiple lines of text on the form it insterts it all into the database. but when it displays it on the wepage it shows it as one line.

Example:
I type this:

Hello

BMW

But when it displays it on the webpage it displays it as this:

Hello BMW

What can I do so it will display it correctly?

Here is my current code for inserting into the database

<% @ Language="VBScript" %>
<% Option Explicit %>
<%
Dim txtnew

Dim sConnString, connection, sSQL

txtnew = Request.Form("txtnew")

sSQL = "INSERT into bmwnews (new) values ('" & _
txtnew & "')"

sConnString="DRIVER={SQL Server};SERVER=**.**.**.**;DATABASE=****;UID=****;PWD=****"

Set connection = Server.CreateObject("ADODB.Connection")

connection.Open(sConnString)

connection.execute(sSQL)

connection.Close
Set connection = Nothing
Response.Redirect ( "http://www.bmwsolutions.com/staff/staff.asp" )
%>

Here is my current code for displaying the results.

<% @ Language="VBScript" %>
<% Option Explicit %>

<%
Dim connection,recordset
Dim sql_select,sConnString

sql_select="SELECT top 1 id, date, new FROM bmwnews ORDER BY id DESC"

Set connection = Server.CreateObject("ADODB.connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

sConnString="DRIVER={SQL Server};SERVER=**.**.**.**;DATABASE=****;UID=****;PWD=****"

Connection.Open sConnString

Recordset.Open sql_select,Connection

Do while Not recordset.eof
Response.Write "Date : " & recordset("date") & "<br>"
Response.Write "News : " & recordset("new") & "<br>"
recordset.MoveNext
loop

recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>

Thanks In Advance,
Brian Weber

Go to Top of Page

jonmadrid
VP-ASP New User

USA
192 Posts

Posted - May 05 2004 :  22:54:29  Show Profile  Visit jonmadrid's Homepage  Reply with Quote
Hi Brian,

It looks like you omitted a small bit of the code from your revised code that was in your original post. It's the bit that replaces the line feeds (vbCrLf) with HTML <br> tags so that the text is output properly to browers.

Look for this bit in your revised code:

*******
Dim sConnString, connection, sSQL
txtnew = Request.Form("txtnew")
*******

and just add this line after it:

*******
txtnew = Replace(txtnew , vbCrLf, "<br>")
*******

That's all you need to do. As I mentioned, it's just swapping all line feeds out for <br> tags.

If for some reason you don't want the HTML tags in the database, you can always call that replace function when you are outputting the text to the page. Using your revised code you would simply replace the the following line:

*******
Response.Write "News : " & recordset("new") & "<br>"
*******

with this:

*******
Response.Write "News : " & Replace(recordset("new"), vbCrLf, "<br>") & "<br>"
*******

Same trick as before -- its just replacing the line feeds with <br> tags, except this time its not changing the value of the record in the database; just the way its output on the page.

Either way would work, but it would make more sense to just replace it when its being inserted into the database. Even though its only a small bit of processing power to perform that replace, you'd be able to conserve more resources by not having to do that every time the data was being output. Do it once when saving the record to the database and be done with it. Thats the way I see it. It's up to what you need though.

I hope that helps!

All the best,

Jon Madrid
--------------------
Madrid Communications
Web Design, Development, and Hosting
www.madridcom.com
Go to Top of Page

bmw000
VP-ASP New User

137 Posts

Posted - May 06 2004 :  19:39:19  Show Profile  Visit bmw000's Homepage  Reply with Quote
Got it working.

Thanks for the help.

Brian Weber

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000