How could I use replace in this (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

chadb -> How could I use replace in this (6/17/2008 17:41:37)

I am needing to replace , in the desc field, but not sure how to do in the following code:


Thanks
Chad


<%
Dim DSN, RS, SQL, Conn

'This next line is your database connection line
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("../../fpdb/progress.mdb")
'This next line is your SQL for the data you want to use

sql = "SELECT job, suffix, item, desc, oper, wc, acthrs, stanhrs, diff, percdif, iscomplete FROM Item WHERE (lstjob = 'yes' and trandate >= #06/01/2008#)"

set Conn=server.createobject("adodb.connection")
Conn.open DSN
Set RS = Conn.Execute (SQL)

Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
Set RS = nothing
Conn.close
%>



rdouglass -> RE: How could I use replace in this (6/17/2008 23:15:50)

quote:

Response.Write RS.GetString(,,", ",vbCrLf,"")


Depending on what you're replacing, you're probably going to do it right there.

GetString does what the name implies; gets the whole result as a single string. So you'll probably need to do something like this:

Response.Write Replace(RS.GetString(,,", ",vbCrLf,""),"stringToFind","replacementString")

Hope it helps.



chadb -> RE: How could I use replace in this (6/23/2008 16:42:20)

I can't get that to work, I am trying to replace the comma's. I am trying this:
Here is my entire code:

<%
Dim DSN, RS, SQL, Conn

DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("../../fpdb/progress.mdb")

sql = "SELECT job, suffix, item, desc, oper, wc, acthrs, stanhrs, diff, percdif, iscomplete FROM Item WHERE (lstjob = 'yes' and trandate >= #06/01/2008#)"

set Conn=server.createobject("adodb.connection")
Conn.open DSN
Set RS = Conn.Execute (SQL)

Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write Replace(RS.GetString(,,", ",vbCrLf,""),";","-")
Set RS = nothing
Conn.close
%>



rdouglass -> RE: How could I use replace in this (6/23/2008 17:01:26)

quote:

Response.Write Replace(RS.GetString(,,", ",vbCrLf,""),";","-")


OK, so with that line, you're replacing semi-colons with dashes:

,";","-")

instead of commas but I don't think that's the real problem.

The issue you're probably going to run into is that GetString will send a comma separated string and you're exporting to a CSV as well so am I correct in assuming you want to replace the commas of an individual field with dashes?

Well if so, we can do some tricky stuff if we understand how GetString works. Refer to this link:

http://www.w3schools.com/ADO/met_rs_getstring.asp

and note the GetString 3rd Parameter 'coldel' or the column delimiter. Currently you're using a comma for your column delimiter:

RS.GetString(,,", ",vbCrLf,"")

See it? Actually you're using a comma and a space but that's irrelevant at this point and it suffices to say it's a comma. So since we want to replace commas in the resulting data and not in the column separator, why not substitute a different column delimiter / separator temporarily? Let's use "QQQQ" for instance. Now we *can* replace the commas in the data by using the Replace command like so:

Response.Write Replace(RS.GetString(,,"QQQQ",vbCrLf,""),",","-")

Notice that I replaced that comma and space delimiter with "QQQQ" to temporarily use as the 'coldel' in GetString. Now all I have to do is use replace again replacing the QQQQ with commas like so:

Response.Write Replace(Replace(RS.GetString(,,"QQQQ",vbCrLf,""),",","-"),"QQQQ",",")

That any closer?



chadb -> RE: How could I use replace in this (6/23/2008 17:09:07)

quote:

Response.Write Replace(Replace(RS.GetString(,,"QQQQ",vbCrLf,""),",","-"),"QQQQ",",")



That worked perfect. Thanks for the help and explanation!!



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
6.201172E-02