Thread: Maybe more of a VB question, but here goes
Good Morning Everyone, I'm having a bit of a VB problem, and I don't know quite how to solve this as my VB knowledge is minimal at best. The problem is with quotes and apostrophes in variables. What I'm doing is pulling some really really really bad data from an Access database and trying to insert it into my handy dandy Postgres db. Here's the insert statement: pg_cn.Execute "INSERT into itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h, d,itemno," & _ "finish,mtostatus) values('" & rs!jobno & "','" & rs!area & "','" & rs!floor & "','" & rs!building & "','" & _ rs!room & "','" & rs!elevation & "','" & rs!phase & "','" & rs!cycle & "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _ rs!d & "','" & rs!itemno & "','" & rs!finish & "','" & rs!mtostatus & "')" rs is an adodb recordset Now, if rs!d="7'" (I said it was bad data ;)) that tickmark throws off the whole insert statement. as they're interpreted as the genuine ticks. Has anyone come across this or can anyone point me in the right direction to solve this problem short of removing the users "-' key? thanks in advance corey
In this article there is some functions that maybe can help you http://support.microsoft.com/default.aspx?scid=kb;EN-US;q178070 Regards Zlatko Talic ----- Original Message ----- From: Corey W. Gibbs <cgibbs@westmarkproducts.com> To: <pgsql-odbc@postgresql.org> Sent: Wednesday, March 13, 2002 7:14 PM Subject: [ODBC] Maybe more of a VB question, but here goes > Good Morning Everyone, > > I'm having a bit of a VB problem, and I don't know quite how to solve this > as my VB knowledge is minimal at best. The problem is with quotes and > apostrophes in variables. What I'm doing is pulling some really really > really bad data from an Access database and trying to insert it into my > handy dandy Postgres db. > > Here's the insert statement: > pg_cn.Execute "INSERT into > itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h, > d,itemno," & _ > "finish,mtostatus) values('" & rs!jobno & "','" & rs!area & "','" & > rs!floor & "','" & rs!building & "','" & _ > rs!room & "','" & rs!elevation & "','" & rs!phase & "','" & rs!cycle & > "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _ > rs!d & "','" & rs!itemno & "','" & rs!finish & "','" & rs!mtostatus & > "')" > > rs is an adodb recordset > > Now, if rs!d="7'" (I said it was bad data ;)) that tickmark throws off the > whole insert statement. as they're interpreted as the genuine ticks. Has > anyone come across this or can anyone point me in the right direction to > solve this problem short of removing the users "-' key? > > thanks in advance > corey > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Try: pg_cn.Execute "INSERT INTO itemlist (.....) VALUES (..., " & Replace(rd!d, "'", "''") & ", ...)" Regards, Dave. > -----Original Message----- > From: Corey W. Gibbs [mailto:cgibbs@westmarkproducts.com] > Sent: 13 March 2002 18:14 > To: pgsql-odbc@postgresql.org > Subject: [ODBC] Maybe more of a VB question, but here goes > > > Good Morning Everyone, > > I'm having a bit of a VB problem, and I don't know quite how > to solve this > as my VB knowledge is minimal at best. The problem is with > quotes and > apostrophes in variables. What I'm doing is pulling some > really really > really bad data from an Access database and trying to insert > it into my > handy dandy Postgres db. > > Here's the insert statement: > pg_cn.Execute "INSERT into > itemlist(jobno,area,floor,building,room,elevation,phase,cycle, > model,w,h, > d,itemno," & _ > "finish,mtostatus) values('" & rs!jobno & "','" & rs!area > & "','" & > rs!floor & "','" & rs!building & "','" & _ > rs!room & "','" & rs!elevation & "','" & rs!phase & "','" > & rs!cycle & > "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _ > rs!d & "','" & rs!itemno & "','" & rs!finish & "','" & > rs!mtostatus & > "')" > > rs is an adodb recordset > > Now, if rs!d="7'" (I said it was bad data ;)) that tickmark > throws off the > whole insert statement. as they're interpreted as the genuine > ticks. Has > anyone come across this or can anyone point me in the right > direction to > solve this problem short of removing the users "-' key? > > thanks in advance > corey > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Try double quoteing (ie '' for '). Also you can escape it using \'. Of course bveign able to use \ as an escape character means that when they really have one you have to give \\ :) Cheers, - Stuart > -----Original Message----- > From: Corey W. Gibbs [mailto:cgibbs@westmarkproducts.com] > Sent: 13 March 2002 18:14 > To: pgsql-odbc@postgresql.org > Subject: Maybe more of a VB question, but here goes > > > Good Morning Everyone, > > I'm having a bit of a VB problem, and I don't know quite how > to solve this > as my VB knowledge is minimal at best. The problem is with > quotes and > apostrophes in variables. What I'm doing is pulling some > really really > really bad data from an Access database and trying to insert > it into my > handy dandy Postgres db. > > Here's the insert statement: > pg_cn.Execute "INSERT into > itemlist(jobno,area,floor,building,room,elevation,phase,cycle, > model,w,h, > d,itemno," & _ > "finish,mtostatus) values('" & rs!jobno & "','" & rs!area > & "','" & > rs!floor & "','" & rs!building & "','" & _ > rs!room & "','" & rs!elevation & "','" & rs!phase & "','" > & rs!cycle & > "','" & rs!model & "','" & rs!w & "','" & rs!h & "','" & _ > rs!d & "','" & rs!itemno & "','" & rs!finish & "','" & > rs!mtostatus & > "')" > > rs is an adodb recordset > > Now, if rs!d="7'" (I said it was bad data ;)) that tickmark > throws off the > whole insert statement. as they're interpreted as the genuine > ticks. Has > anyone come across this or can anyone point me in the right > direction to > solve this problem short of removing the users "-' key? > > thanks in advance > corey > >
For those that like ready made function type of things, here's something for you. This functions converts a string to be suitable for handing over to the back end, with appropriate characters escaped and single quotes surrounding if necessary. It also handles NULL. I escape using \ so: input output ---------------- hello 'hello' bla'h 'bla\'h' c:\linux 'c:\\linux' <null> NULL Here's the listing: Function escapeString(text As Variant) As String 'Converts a string to be suitable for handing over to the back end, with all appropriate characters escaped ' and single quotes surrounding if necessary Dim pos As Integer, ret As String If IsNull(text) Then escapeString = "NULL" Else ret = text pos = 0 Do pos = InStr(pos + 1, ret, "\") 'we must do \ first If pos > 0 Then ret = Left(ret, pos - 1) & "\" & Mid(ret, pos) pos = pos + 1 End If Loop Until pos = 0 pos = 0 Do pos = InStr(pos + 1, ret, "'") If pos > 0 Then ret = Left(ret, pos - 1) & "\" & Mid(ret, pos) pos = pos + 1 End If Loop Until pos = 0 escapeString = "'" & ret & "'" End If End Function For Corey's case, it would probably be useful to make a wrapper function to add a comma on the end, eg.. Function wrapper(text As Variant) As String escapeString = escapeString(text) & ", " End Function And then just do this: > Here's the insert statement: pg_cn.Execute "INSERT into itemlist(jobno,area,floor,building,room,elevation,phase,cycle,model,w,h,d,itemno," & _ "finish,mtostatus) values(" & _ wrapper(rs!jobno) & wrapper(rs!area) & _ wrapper(rs!floor) & wrapper(rs!building) & _ wrapper(rs!room) & wrapper(rs!elevation) & _ wrapper(rs!phase) & wrapper(rs!cycle) & _ wrapper(rs!model) & wrapper(rs!w) & _ wrapper(rs!h) & wrapper(rs!d) & _ wrapper(rs!itemno) & wrapper(rs!finish) & _ escapeString(rs!mtostatus) & ")" Remember to call escapeString instead of wrapper for the last one.. Someone may want to add this to a howto or something (hint, hint).. -Cedar On Thu, 14 Mar 2002, Henshall, Stuart - WCP wrote: > Try double quoteing (ie '' for '). > Also you can escape it using \'. > Of course bveign able to use \ as an escape character means that when they > really have one you have to give \\ :) > Cheers, > - Stuart
> -----Original Message----- > From: Cedar Cox [mailto:cedarc@visionforisrael.com] > Sent: 14 March 2002 21:10 > To: Henshall, Stuart - WCP > Cc: 'cgibbs@westmarkproducts.com'; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Maybe more of a VB question, but here goes > > > > For those that like ready made function type of things, > here's something for you. This functions converts a string > to be suitable for handing over to the back end, with > appropriate characters escaped and single quotes surrounding > if necessary. It also handles NULL. I escape using \ so: > > input output > ---------------- > hello 'hello' > bla'h 'bla\'h' > c:\linux 'c:\\linux' > <null> NULL > > Here's the listing: > > Function escapeString(text As Variant) As String > 'Converts a string to be suitable for handing over to the > back end, with all appropriate characters escaped > ' and single quotes surrounding if necessary > Dim pos As Integer, ret As String > If IsNull(text) Then > escapeString = "NULL" > Else > ret = text > pos = 0 > Do > pos = InStr(pos + 1, ret, "\") 'we must do \ first > If pos > 0 Then > ret = Left(ret, pos - 1) & "\" & Mid(ret, pos) > pos = pos + 1 > End If > Loop Until pos = 0 > pos = 0 > Do > pos = InStr(pos + 1, ret, "'") > If pos > 0 Then > ret = Left(ret, pos - 1) & "\" & Mid(ret, pos) > pos = pos + 1 > End If > Loop Until pos = 0 > escapeString = "'" & ret & "'" > End If > End Function Is this quicker than something like: Function escapeString(text As Variant) As String If IsNull(text) Then escapeString = "NULL" else escapeString = "'" & Replace(Replace(text, "\", "\\"), "'", "''") & "'" end if End Function (BTW, in pgAdmin II we occasionally ran into problems using \' so we use '' - that tended to be in CREATE/ALTER statements though rather than simple INSERTs). > Remember to call escapeString instead of wrapper for the last one.. > Someone may want to add this to a howto or something (hint, > hint).. -Cedar Hint taken. Shall we get some concencus on the best method first? Mine is obviously simpler, but if it's not a efficient or has another problem I've not considered... Regards, Dave.