|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 28th, 2003, 03:26 PM
|
|||
|
|||
|
change from Access to MS SQL
At the moment I have an MS Access database and is getting full very quickly.
I want to upgrade to a MS SQL database provided by my hosting provider. My current site is written in ASP and SQL, I want to know would I have to allter any SQL or ASP coding to make it compatible with the MS SQL database instead of access. This is an example of the coding that I am using at the moment to perform searches. Code:
<%
' Open the Database Connection
SET Conn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("\db\jobdatabase.mdb") & ";" & _
"Persist Security Info=False"
Conn.Open(sConnection)
%>
<%
if (Request.form("search") = "" ) or (Request.form("search") = " " ) then
SQL1 = "SELECT * FROM jobs"
set Rs = Conn.Execute(SQL1)
Else
strSplit = split(Request.form ("search"),",") ' value to search for in title
Dim iNext
For i = 0 To UBound(strSplit)
if (strSplit(i) <> "") and (strSplit(i) <> " ") Then
if (i=0) then
Booltxt="WHERE ( "
Else
Booltxt="OR "
End If
sql_search1 = sql_search1 & Booltxt & " description like '%" & strSplit(i) & "%' "
End If
Next 'i
sql_search1 = sql_search1 & ")"
SQL = "SELECT * FROM jobs " & sql_search1
set Rs = Conn.Execute(SQL)
End If
%>
Thanks in advance.
|
|
#2
October 31st, 2003, 04:50 PM
|
||||
|
||||
|
The above SQL statement will work unmodified on MSSQL. You'll need to change the sConnection string though, to connect to MSSQL instead of Access.
__________________
Up the Irons What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home. "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest Down with Sharon Osbourne Puzzle of the Month solved by Keath and KevinADC, superior perl programmers of the month Looking for a perl job with kick-*** programmers in a well-known NASDAQ listed tech company with branches in the US and Europe? We're hiring. PM me for details. Requirements
|
|
#3
October 31st, 2003, 11:51 PM
|
|||
|
|||
|
There are other differences you'll have to accomodate in your code. For example, in Access the delimiter for date fields is #, but in sql server it's '
There is no boolean datatype in sql server There is a page on the MS website that describes the differences between the two databases.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > change from Access to MS SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
