Problem with SQL statement in VB

Misha_sh
Misha_sh used Ask the Experts™
on
For ,example I have a Table1 with two fields :Id ,Text1.
How can I add new record to the table which will contain values : 11(ID) ," 'Some'text "(Text1).
Result must be:
Id  Text1
---------
11  'Some'text

I don't know how to solve a problem with this sign --> '
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You escape it by using two.  When SQL encounters '' in a string, it treats it as a single quote within the string, hence your SQL becomes:

INSERT INTO Table1 (ID, Text1) VALUES (11, '''Some ''text')

Commented:
Try this:

Dim m_db as Database
Dim m_rs as Recordset

Set m_db = DBEngine.Workspaces(0).OpenDatabase("dbname")
Set m_rs = m_db.OpenRecordset("TableName", dbOpenDynaset)

m_rs.AddNew
m_rs![ID] = 11
m_rs![Text1] = "Some text"
m_rs.Update


This is using DAO.  

Commented:
Take a look at the QUOTENAME() intrinsic function.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
EasyAim, I have never saw the QUOTENAME() function. It is intrinsic to what?

Misha_sh, you can use this from VB:

strsql = "INSERT INTO Table1 (ID, Text1) VALUES (" & YourIDVariable & ", '" & replace(text1.text, ",", "''") & "')"
Top Expert 2012

Commented:
Eric,

>> I have never saw the QUOTENAME() function. It is intrinsic to what?<< It is T-SQL and you are right probably unrelated to the question.

Anthony
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Isn't it in VB?
Top Expert 2012

Commented:
>>Isn't it in VB? <<
Yes this is a VB Topic Area, but this function QUOTENAME() is strictly a T-SQL function.

Anthony

Commented:
hmmmm, help me out....  I thought SQL Server SQL used a Microsoft version of T-SQL.  "QUOTENAME()" isn't available in SQL Server?

Misha, whenever possible and appropriate, your code should strip out characters that may cause problems down the road.  For instance, the single quote, double quote, and all characters less than ASCII 32 can get into a database field and cause problems for either database administrators, developers debugging their code, and difficult to debug printing glitches.

But sometimes you just have to keep them, such as "O'Malley's Garage & Cafe".
Top Expert 2012

Commented:
>> I thought SQL Server SQL used a Microsoft version of T-SQL.  "QUOTENAME()" isn't available in SQL Server?<<
Actually the Microsoft SQL Server version of SQL language is called T-SQL and yes there is a QUOTENAME() function in T-SQL, but I do not believe it applies in this question.

Anthony

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial