SQL Insert Into command, Two Values for a SINGLE column

R

Rob W

Greetings,

I hope this is the correct forum for such a post as I'm writing a vb.net
application to an ms access database (accdb).

The code below fails on a syntax error.

From slowly building the code/SQL up, the error is because the syntax used
to insert TWO values for the single database Integer field cTelephone is
incorrect.

I want them to be concatenated together, can anyone please help or advise a
suitable resource or forum that could help.
I did try varying combinations using '+' and '&' without success.

Note: The sql work inserting string/integer values prior to trying to insert
the telephone number value.

Thanks
Rob

'Code to insert telphone value
" & Me.cboSTDCode.Text & Me.txtTelephone.Text & "


'Full SQL code
Dim sqlInsertMember As String = _
"INSERT INTO Members (cMembershipId, cTitle, CSurname, CforeNames,
cInitials, cWRorBranch, " _

+ "cHouseNum, cAddressLine1, cAddressLine2, cTown, cCity, cCounty, " _

+ "cPostcode, cDeceased, cMobile, cTelephone) VALUES ('" & Me.txtMemNum.Text
& "', " _

+ " '" & Me.cboTitle.Text & "', '" & Me.txtSurname.Text & "', " _

+ " '" & Me.txtForename.Text & "', '" & Me.lblInitialsReadable.Text & "', "
_

+ " '" & Me.txtBranch.Text & "', " _

+ " '" & Me.txtHouseOrFlatNum.Text & "', '" & Me.txtAddress1.Text & "', " _

+ " '" & Me.txtAddress2.Text & "', '" & Me.cboTown.Text & "', " _

+ " '" & Me.cboCity.Text & "', '" & Me.cboCounty.Text & "', " _

+ " '" & Me.txtPostCode.Text & "', '" & CType(Me.chkDeceased.Checked,
Integer) & "', " _

+ " " & Me.txtMobile.Text & ", " & Me.cboSTDCode.Text & Me.txtTelephone.Text
& ")"
 
A

Armen Stein

+ " " & Me.txtMobile.Text & ", " & Me.cboSTDCode.Text & Me.txtTelephone.Text
& ")"
Click to expand...

Just air code, but you seem to be missing the single quotes in that
last line:

+ " '" & Me.txtMobile.Text & "', '" & Me.cboSTDCode.Text &
Me.txtTelephone.Text
& "')"

Note that I added four of them in the quoted (" ") pieces:
"', '"
", '"
"')"

Another thing to try is stopping your code when the whole statement is
built, copy it from the immediate window, and pasting it into the SQL
query designer in Management Studio. It will indicate the syntax
errors.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
R

Rob W

I was rushing and didn't fully debug my code.

When I printed the string out to show the full insert statement and its
values from the form, the telephone number (STD code and remaining digits
are joined but with a SPACE between them).

Other than that the SQL statement looks fine, I believe I don't need single
quotes as they are for replacement of string values only.

So I will work out where the extra space is coming from and then take it
from there.

I should be less hesitant to post before properly debugging next time!
Thanks for the reply :)
 
K

KARL DEWEY

the syntax used to insert TWO values for the single database Integer field
cTelephone is incorrect.

Do not use Integer field for telephone data as it will never have math
functions performed on it.

Use a text field.
 
R

Rob W

I might just do that, use a text field.

From my debugging it failed due to an EMPTY string value could NOT be
converted into an integer value.

Having a text field would resolve this and I could simply check ascii code
values to ensure nothing unusual has been entered.

Thanks for the suggestion.
 
Top