CODEWITHSUNDEEP
sqlms-accessasp-classic
Seitam
Seitam

Reputation: 388

Inserting alphanumeric characters into ms-access database using asp classic

I am having trouble when I try to introduce alphanumeric characters into ms-access database. I am able to do it with numerical and date characters, but it seems to be a problem with alphanumerical ones,

Here is the code I use:

Dim adoCon         
Dim strSQL        

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


adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("basededades.mdb")


'Here I am getting all the values previously passed by a form.

stringEntrada=Request.Form("stringEntrada")
stringSortida=Request.Form("stringSortida")
valoridHabitaciolliure=Request.Form("valoridHabitaciolliure")
numeropersones=Request.Form("numeroPersones")
nom=Request.Form("nom")
dni=Request.Form("dni")
tlf=Request.Form("tlf")
mail=Request.Form("mail")
ciutat=Request.Form("ciutat")
tipusH=Request.Form("tipusH")
diaReserva=Request.Form("diaReserva")

mail,nom,ciutat,tipusH,dni,valoridHabitaciolliure are alphanumerical characters from a text input form. diaReserva,stringSortida,stringEntrada, are dates form a text input form. tlf is a integer variable.

strSQL="INSERT INTO Reserva (dni,tlf,diaReserva,inici,fi,tipusHabitacio) VALUES ("&dni&","&tlf&",'"&diaReserva&"','"&stringEntrada&"','"&stringSortida&"'," "&tipusH&")" 
adoCon.Execute(strSQL)

When I see the values inserted into the database I realise that the date variables like "diaReserva" or "stringSortida" and the numerical ones like "tlf" are inserted correctly.

To insert date variables I use a simple ' surrounded by double " in the sql query: '"&stringEntrada&"'

To insert numerical ones I only use the double: "&tlf&"

If I try to use simple ' when I am trying to insert an alphanumerical, like: '"mail"' I do not recieve any error, but the database records a blank value.

If I try to use double ", like: "mail" I am getting an error.

How I could insert alphanumerical variables without having trouble?

Thank you for your time, and sorry for my bad english.

Upvotes: 1

Views: 1173

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123839

You can avoid your "quoting problem" and also avoid SQL Injection vulnerabilities by using a parameterized query similar to this one:

Dim con  '' As ADODB.Connection
Dim cmd  '' As ADODB.Command
Dim stringName, longSponsorID, datetimeDateJoined

Const adCmdText = 1
Const adVarWChar = 202, adInteger = 3, adDate = 7
Const adParamInput = 1

'' test data
stringName = "Gord"
longSponsorID = 5
datetimeDateJoined = Now

Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Public\mdbTest.mdb;"
Set cmd = CreateObject("ADODB.Command")
cmd.CommandType = adCmdText
cmd.ActiveConnection = con
cmd.CommandText = _
        "INSERT INTO Members " & _
            "(memberName, sponsorID, dateJoined) " & _
        "VALUES " & _
            "(?, ?, ?)"
'' parameter for [memberName]
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, stringName)
'' parameter for [sponsorID]
cmd.Parameters.Append cmd.CreateParameter("?", adInteger, adParamInput, , longSponsorID)
'' parameter for [dateJoined]
cmd.Parameters.Append cmd.CreateParameter("?", adDate, adParamInput, , datetimeDateJoined)
cmd.Execute
Set cmd = Nothing
con.Close
Set con = Nothing

Upvotes: 0

Related Questions