Database + SQL Help

I'm having a few issues with an MS Database I'm making. I have the following lines to insert a record into the database:

strInsert = "INSERT INTO Clients VALUES ('" & strFName & "', '" & strLName & "', '" & strAdd & "', '" & strCity & "', '" & strState & "', '" & strZip & "', '" & strPhone & "', '" & strEmail & "', '" & strComments & "')"
Data1.Database.Execute strInsert

To clarify, anything with "str" (sans quotes) is a string variable. The goal is that each field has a string in it taken from text boxes, and the Data1 line will insert the record into the DB. My issues comes when the last field (Comments, or any field for that matter I guess) has an apostrophe or a comma in it. Since those are characters used by SQL it thinks I'm ending the command prematurely.

For anyone still with me here, I need a way to be able to avoid that issue. In other words, I need a way to use apostrophes and commas in my fields without VB throwing a syntax error...and I would like the answer using VB + SQL. Thanks.

================================================================
10 World Records and Counting:
2 Atari 2600 (Mouse Trap and Zoo Fun)
2 Game Boy (Kirby&;#39;s Dreamland - Points, Qix - Points)
2 NES (Rescue Rangers - Speed, Mega Man - Points)
2 N64 (2x Mario Golf)
2 MAME (Vs. Dr. Mario - Default Settings, Vs. Dr. Mario - TGTS)

home.graffiti.net/NeoScyther:graffiti.net - My new website
neoscyther.livejournal.com - My Internet Journal

"With a keen eye for detail, one truth prevails!"
- Case Closed

"Gentlemen. This is the M12LRV. I like to call it the Warthog."
"Why Warthog sir?"
"'Cause M12LRV is too hard to say in conversations, son."
- Red vs. Blue

__________________________________________________________
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/

[Non-text portions of this message have been removed]

Messages in this topic (1) Reply (via web post) | Start a new topic

.

You will have to use Replace command and change all the apostrophe and
commas into something else.
strFName=Replace(strFName,",",";") 'Changes all commas into semi-colons

Then you can add the string to the database. If you would like, you can
always change it back when you retrieve the record.

Rick Rose

-------Original Message-------

From: Russell Archey
Date: 3/19/2007 8:55:11 PM
To: visualbasic6programming%40yahoogroups.com">visualbasic6programmingyahoogroups.com
Subject: [Visual Basic 6 programming] Database + SQL Help

I'm having a few issues with an MS Database I'm making. I have the
following lines to insert a record into the database:

strInsert = "INSERT INTO Clients VALUES ('" & strFName & "', '" & strLName &
"', '" & strAdd & "', '" & strCity & "', '" & strState & "', '" & strZip & "
, '" & strPhone & "', '" & strEmail & "', '" & strComments & "')"
Data1.Database.Execute strInsert

To clarify, anything with "str" (sans quotes) is a string variable. The
goal is that each field has a string in it taken from text boxes, and the
Data1 line will insert the record into the DB. My issues comes when the
last field (Comments, or any field for that matter I guess) has an
apostrophe or a comma in it. Since those are characters used by SQL it
thinks I'm ending the command prematurely.

For anyone still with me here, I need a way to be able to avoid that issue.
In other words, I need a way to use apostrophes and commas in my fields
without VB throwing a syntax error...and I would like the answer using VB +
SQL. Thanks.

================================================================
10 World Records and Counting:
2 Atari 2600 (Mouse Trap and Zoo Fun)
2 Game Boy (Kirby&;#39;s Dreamland - Points, Qix - Points)
2 NES (Rescue Rangers - Speed, Mega Man - Points)
2 N64 (2x Mario Golf)
2 MAME (Vs. Dr. Mario - Default Settings, Vs. Dr. Mario - TGTS)


home.graffiti.net/NeoScyther:graffiti.net - My new website
neoscyther.livejournal.com - My Internet Journal

"With a keen eye for detail, one truth prevails!"
- Case Closed

"Gentlemen. This is the M12LRV. I like to call it the Warthog."
"Why Warthog sir?"
"'Cause M12LRV is too hard to say in conversations, son."
- Red vs. Blue






__________________________________________________________
______
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/

[Non-text portions of this message have been removed]



------------------------ Yahoo! Groups Sponsor --------------------~-->
Something is new at Yahoo! Groups. Check out the enhanced email design.
http://us.click.yahoo.com/kOt0.A/gOaOAA/yQLSAA/k7folB/TM
----------------------------------------------------------~->


Yahoo! Groups Links




[Non-text portions of this message have been removed]

Messages in this topic (2) Reply (via web post) | Start a new topic

.