[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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()
0
running32
Asked:
running32
  • 5
  • 5
1 Solution
 
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
 
rafranciscoCommented:
You misspelled the lngpaientid in your SELECT.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
rafranciscoCommented:
You are missing the closing parenthesis before the SELECT.
0
 
running32Author Commented:
Got it.  Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now