Acceess insert from vb.net generating "Data Type Mismatch" error 1

[DaveMac1960]

All,
I have the unfortunate circumstance of building a search/add/edit front end for an Access database. This is my first time doing this and it really makes me appreciate Microsoft SQL Server.

Anyway, I'm trying to insert a record and I keep getting the "data type mismatch" error. All the columns are defined as "text" and "Allow zero length" is set to yes on everything. I have looked and looked at my SQL statement and everything looks fine there (see below).

Can anyone steer me in the right direction?

Thanks in advance.

------------------------------------------------
sSQL = "INSERT INTO 2001_DBASE_KS " _
& "VALUES(" & "'" & FName & "', " & "'" & MI & "', " & "'" & LName & "', " _
& "'" & TCG & "', " & "'" & Suffix & "', " & "'" & Salutation & "', " & "'" & Company & "', " _
& "'" & Specialty & "', " & "'" & BusAddr1 & "', " _
& "'" & BusAddr2 & "', " & "'" & BusCity & "', " & "'" & BusState & "', " & "'" & BusCity & "', " _
& "'" & BusZip & "', " & "'" & BusPhone & "', " & "'" & BusFax & "', " & "'" & EMAIL & "', " _
& "'" & HAddr1 & "', " & "'" & HAddr2 & "', " & "'" & HCity & "', " & "'" & HState & "', " _
& "'" & HZip & "', " & "'" & HPhone & "', " & "'" & HFax & "', " & "'" & CountyCode & "', " _
& "'" & RefID & "', " & "'" & Contact & "', " & "'" & Active & "', " & "'" & upin_num & "', " _
& "'" & SendMail & "', " & "'" & Reason & "' " & ")" _
& "; "

Dim cnn As New OleDbConnection(CnnString)
cnn.Open()
cmd = New OleDbCommand(sSQL, cnn)
cmd.ExecuteNonQuery()
cnn.Close()

 

[DaveMac1960]

I left out what the SQL statement looks like before it hits the ExecuteNonQuery statement:

INSERT INTO 2001_DBASE_KS VALUES('John', 'Q', 'Test', 'Y', 'MD', 'Dr Test', 'Test Medical Group, LLC.', 'FP', '1313 Mockingbird Lane', 'Suite A', 'Indianapolis', 'IN', 'Indianapolis', '46268', '317 999 9999', '317 999-9999', 'jtest@testmedical.com', '666 Satan Drive', 'Apt. 666', 'Indianapolis', 'IN', '46260', '317 666-6666', '317 777-7777', '49', '', '', 'Y', '', 'Y', '' );

 

[DaveInIowa]

Have you considered what's going to happen (or is happening) when one of your values contains an embedded single quote? Try using parameters instead.

                With cmd
                    .CommandText = "INSERT INTO 2001_DBASE_KS VALUES(:FName, :MI, :LName, etc."
                    .Parameters.AddWithValue("FName", FName)
                    .Parameters.AddWithValue("LName", LName)
                End With

 

[DaveMac1960]

I always handle the single quotes when I pull the values off the web form using the "Replace" command.

Actually, I'm going to answer my own question here just in case someone else who's not familiar with building apps for use with Access and has the unfortunate circumstance of having to do so.

My original insert statement would work with Microsoft SQL, but Access requires that you list each field in the table and then the values. So my new insert statement looks like this:

sSQL = "INSERT INTO 2001_DBASE_KS " _
& "([First Name],[MI], [Last Name], [TCG MD], [Suffix], [Salutation], [Company], [Specialty], " _
& "[Bus Address 1], [Bus Address 2], [Bus City], [B-ST], [Bus Zip], [Bus Phone 1], [Bus Fax], , " _
& "[Home Add 1], [Home Add 2], [Home City], [H - ST], [Home Zip], [Home Phone], [Home Fax], " _
& "[County Code], [RefID], [ContactPref], [Active], [upin_num], [SendMail], [Reason])" _
& "VALUES(" & "'" & FName & "', " & "'" & MI & "', " & "'" & LName & "', " _
& "'" & TCG & "', " & "'" & Suffix & "', " & "'" & Salutation & "', " & "'" & Company & "', " _
& "'" & Specialty & "', " & "'" & BusAddr1 & "', " _
& "'" & BusAddr2 & "', " & "'" & BusCity & "', " & "'" & BusState & "', " _
& "'" & BusZip & "', " & "'" & BusPhone & "', " & "'" & BusFax & "', " & "'" & EMAIL & "', " _
& "'" & HAddr1 & "', " & "'" & HAddr2 & "', " & "'" & HCity & "', " & "'" & HState & "', " _
& "'" & HZip & "', " & "'" & HPhone & "', " & "'" & HFax & "', " & "'" & CountyCode & "', " _
& "'" & RefID & "', " & "'" & Contact & "', " & "'" & Active & "', " & "'" & upin_num & "', " _
& "'" & SendMail & "', " & "'" & Reason & "' " & ")" _
& "; "

Ugly isn't it? Access blows.

 

[DaveInIowa]

To save my eyesight and sanity when working with numerous quotes and concatenations, I use a technique like below. It may not be efficient, but it's a whole lot easier to maintain.

    Dim Values As String
    Values = "'<FName>', '<MI>', '<LName>', '<TCG>', '<Suffix>', '<Salutation>', '<Company>', " & _
        "'<Specialty>', '<BusAddr1>', ..., '<Reason>'"
    Values = Replace(Values, "<FName>", FName)
    ' ...
    Values = Replace(Values, "<Reason>", Reason)

    sSQL = "INSERT INTO 2001_DBASE_KS " & _
        "([First Name],[MI], [Last Name], [TCG MD], [Suffix], [Salutation], [Company], [Specialty], " & _
        "[Bus Address 1], [Bus Address 2], [Bus City], [B-ST], [Bus Zip], [Bus Phone 1], [Bus Fax], [EMAIL], " & _
        "[Home Add 1], [Home Add 2], [Home City], [H - ST], [Home Zip], [Home Phone], [Home Fax], " & _
        "[County Code], [RefID], [ContactPref], [Active], [upin_num], [SendMail], [Reason])" & _
        " VALUES(" & Values & ");"

 

[bboffin]

Since this is a VB.NET forum may I suggest using the more efficient StringBuilder for creating a long text string like this and to use the AppendFormat method to help with getting the values inserted correctly.

Dim lB as New StringBuilder("INSERT INTO 2001_DBASE_KS ")
With lB
	.Append("([First Name],[MI], [Last Name], [TCG MD], [Suffix], [Salutation], [Company], [Specialty], " & _
        "[Bus Address 1], [Bus Address 2], [Bus City], [B-ST], [Bus Zip], [Bus Phone 1], [Bus Fax], [EMAIL], " & _
        "[Home Add 1], [Home Add 2], [Home City], [H - ST], [Home Zip], [Home Phone], [Home Fax], " & _
        "[County Code], [RefID], [ContactPref], [Active], [upin_num], [SendMail], [Reason]) "
	.AppendFormat("VALUES (""{0}"",""{1}"",""{2}"",""{3}"",""{4}"",""{5}"",""{6}"",""{7}"",""{8}"",""{9}"",""{10}""," & _
		"""{11}"",""{12}"",""{13}"",""{14}"",""{15}"",""{16}"",""{17}"",""{18}"",""{19}"",""{20}""," & _
		"""{21}"",""{22}"",""{23}"",""{24}"",""{25}"",""{26}"",""{27}"",""{28}"",""{29}"");", & _
		FName, MI, LName, TCG, Suffix, Salutation, Company, Specialty, BusAddr1, BusAddr2, BusCity, & _
		BusState, BusZip, BusPhone, BusFax, EMAIL, HAddr1, HAddr2, HCity, HState, HZip, & _
		HPhone, HFax, CountyCode, RefID, Contact, Active, upin_num, SendMail, Reason)
	sSql = .ToString
End With

The AppendFormat method is especially useful if some of the values require quotes around them and some don't. It's much easier to spot a missing quote in the format string than using the & operator.

Bob Boffin