| Author |
 Topic  |
|
|
bmw000
VP-CART New User
137 Posts |
 Posted - May 04 2004 : 16:05:03
|
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
1904 Posts |
Posted - May 05 2004 : 03:03:19
|
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 |
 |
|
|
bmw000
VP-CART New User
137 Posts |
Posted - May 05 2004 : 19:59:17
|
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
|
|
|
|
jonmadrid
VP-CART New User
USA
192 Posts |
Posted - May 05 2004 : 22:54:29
|
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
|
|
|
|
bmw000
VP-CART New User
137 Posts |
Posted - May 06 2004 : 19:39:19
|
Got it working.
Thanks for the help.
Brian Weber
|
|
|
| |
Topic |
|
All Forums
Web Forrm To SQL Database
New Topic
Printer Friendly
TrustGuard - PCI Security Scanner
