Error on my insert statement

If you lngpatientid is a varchar, you should put it in single-quotes:

Dim cmd As New SqlClient.SqlCommand("insert into tblArchive (lngpatientid, strpatientid, lnghouseid) SELECT lngpaientid, strpatientid, lnghouseid from tblPatient where lngpatientid = '" & userid & "'", Connection1)
            Connection1.Open()
            cmd.ExecuteNonQuery()
            connection1.close()

lngpatientid is declared as an int in the sql database.

You misspelled the lngpaientid in your SELECT.

Try this:

Dim cmd As New SqlClient.SqlCommand("insert into tblArchive (lngpatientid, strpatientid, lnghouseid) SELECT lngpatientid, strpatientid, lnghouseid from tblPatient where lngpatientid = " & userid, Connection1)
            Connection1.Open()
            cmd.ExecuteNonQuery()
            connection1.close()

Nope it still blows up when I try and execute the query.

What's the exact error message are you getting?  Can you try executing the statement in Query Analyzer?

Tried it and the layouts are not the same so I wrote this query but is says erro near select.

INSERT INTO tblArchive(strpatientid, lngpatientid, strfname, strmname, strlname,  strwphone, strophone, strgender, dtmdob, intage, strGender, ysninsmaid, ysninsmcare, ysninschild, ysninsmigrant, ysninsnone, ysninsother, meminsurance, ysnsignature, dtmsignature, bytlanguage, strlanguage, bytmarriage, bytliving, ysnpartner, ysndependent, ysnotherdep, ysnchild,
ysnother, bytemployment, ysnseasonal, byteducation, bytethinic, sretcinic, strrfname, strrlname, strraddress, strrcsz, strrphone, strrrelation, ysntransport, ysnchildcare, ysndisability, ysnlanguage, ysnfinancial, ysninformation,
ysnprogram, ysnnophone, ysnoterprob, strproblem, dtmcreated, dtmeditted, lngoldid, strdatabase, strprgentry, lnghear, strmedicaid, strmedicare,
strssnum, ysndoctor,lngorgid, lngcontactid, memnote, ysndontcount, strdontreason, straddress, strcity, strstate, strzip, strmaddress, strmcity, strymstate,strmzip, strhphone, inthousehold, curmonthly, memresources, memexpense, intcategory, bythouseing, ysncatergory, lnghouseid
select strpatientid, lngpatientid, strfname, strmname, strlname,  strwphone, strophone, strgender, dtmdob, intage, strGender, ysninsmaid, ysninsmcare, ysninschild, ysninsmigrant, ysninsnone, ysninsother, meminsurance, ysnsignature, dtmsignature, bytlanguage, strlanguage, bytmarriage, bytliving, ysnpartner, ysndependent, ysnotherdep, ysnchild,
ysnother, bytemployment, ysnseasonal, byteducation, bytethinic, sretcinic, strrfname, strrlname, strraddress, strrcsz, strrphone, strrrelation, ysntransport, ysnchildcare, ysndisability, ysnlanguage, ysnfinancial, ysninformation
ysnprogram, ysnnophone, ysnoterprob, strproblem, dtmcreated, dtmeditted, lngoldid, strdatabase, strprgentry, lnghear, strmedicaid, strmedicare
strssnum, ysndoctor,lngorgid, lngcontactid, memnote, ysndontcount, strdontreason, straddress, strcity, strstate, strzip, strmaddress, strmcity, strymstate,strmzip, strhphone, inthousehold, curmonthly, memresources, memexpense, intcategory, bythouseing, ysncatergory, lnghouseid
FROM         tblpatient, tblhousehold
inner join tblhousehold as H on tblpatient.lngpatientid = H.lngpatientid
WHERE     (lngPatientid = '21664')

Oops I didn't mean to post that.   Sorry scratch my last post

Got it.  Thanks