Error on my insert statement

My query keeps blowing up on the insert statement can someone tell me what is wrong with my syntax.

Thanks


            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()
running32Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
You are missing the closing parenthesis before the SELECT.
0
 
rafranciscoCommented:
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()
0
 
running32Author Commented:
lngpatientid is declared as an int in the sql database.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rafranciscoCommented:
You misspelled the lngpaientid in your SELECT.
0
 
rafranciscoCommented:
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()
0
 
running32Author Commented:
Nope it still blows up when I try and execute the query.
0
 
rafranciscoCommented:
What's the exact error message are you getting?  Can you try executing the statement in Query Analyzer?
0
 
running32Author Commented:
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')
0
 
running32Author Commented:
Oops I didn't mean to post that.   Sorry scratch my last post
0
 
running32Author Commented:
Got it.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.