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!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to insert multiple rows in one click
Posted on 2009-05-18
Hi
I am using ASP.Net 2.0 code behind VB and SQL Server 2000. My problem is I want to insert multiple rows in one click. When I am trying to insert it will give me error like The name CQ is not permitted in this context.... My code is
Dim CQ As String = Request("CommentsAfterQues
Dim CID As Integer = Request("drpDLforCentreNam
Dim q12 As Integer = Request("q12")
Dim q13 As Integer = Request("q13")
Dim CD As String = Request("txtCallDate ")
Dim strCon As New SqlConnection("Data Source=d; Initial Catalog=NSQL; Integrated Security=True;")
Dim strCMD As New SqlCommand
strCon.Open()
Dim strSQL As String = ""
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, CQ, CID, q12, CD, 6), " & _
"(1, CQ, CID, q13, CD, 7)"
Try
strCMD = New SqlCommand(strSQL, strCon)
strCMD.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString
End Try
Please anyone help me how can I insert multiple rows.
Thanks in advance
I am using ASP.Net 2.0 code behind VB and SQL Server 2000. My problem is I want to insert multiple rows in one click. When I am trying to insert it will give me error like The name CQ is not permitted in this context.... My code is
Dim CQ As String = Request("CommentsAfterQues
Dim CID As Integer = Request("drpDLforCentreNam
Dim q12 As Integer = Request("q12")
Dim q13 As Integer = Request("q13")
Dim CD As String = Request("txtCallDate ")
Dim strCon As New SqlConnection("Data Source=d; Initial Catalog=NSQL; Integrated Security=True;")
Dim strCMD As New SqlCommand
strCon.Open()
Dim strSQL As String = ""
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, CQ, CID, q12, CD, 6), " & _
"(1, CQ, CID, q13, CD, 7)"
Try
strCMD = New SqlCommand(strSQL, strCon)
strCMD.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString
End Try
Please anyone help me how can I insert multiple rows.
Thanks in advance
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
8
7
15 Comments
You don't insert the values of your strings, but you insert "CQ" as string
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6), " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6), " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
now i am getting error System.Data.SqlClient.SqlE
Full error - System.Data.SqlClient.SqlE
strSQL value is -
1 -- this is SurveyTypeID
comments --- comments fiels
401 - centre id
12/05/2009 - date
8 -- this is q12
8 -- this is q13
1 -- this is SurveyTypeID
comments --- comments fiels
401 - centre id
12/05/2009 - date
8 -- this is q12
8 -- this is q13
Dim CQ As String = Request("CommentsAfterQues
Dim CID As Integer = Request("drpDLforCentreNam
Dim q12 As Integer = Request("q12")
Dim q13 As Integer = Request("q13")
Dim CD As String = Request("txtCallDate")
Dim strCon As New SqlConnection("Data Source=Test; Initial Catalog=NSQL; Integrated Security=True;")
Dim strCMD As New SqlCommand
strCon.Open()
Dim strSQL As String = ""
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", " & CD & ", 6) , " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", " & CD & ", 7)"
Try
strCMD = New SqlCommand(strSQL, strCon)
strCMD.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString
End Try
strCMD.Dispose()
strCon.Dispose()
strCon.Close()
Dim CID As Integer = Request("drpDLforCentreNam
Dim q12 As Integer = Request("q12")
Dim q13 As Integer = Request("q13")
Dim CD As String = Request("txtCallDate")
Dim strCon As New SqlConnection("Data Source=Test; Initial Catalog=NSQL; Integrated Security=True;")
Dim strCMD As New SqlCommand
strCon.Open()
Dim strSQL As String = ""
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", " & CD & ", 6) , " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", " & CD & ", 7)"
Try
strCMD = New SqlCommand(strSQL, strCon)
strCMD.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString
End Try
strCMD.Dispose()
strCon.Dispose()
strCon.Close()
and what is you sql-statement !!!!!
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", " & CD & ", 6) , " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", " & CD & ", 7)"
Messagebox.Show strsql
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", " & CD & ", 6) , " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", " & CD & ", 7)"
Messagebox.Show strsql
I think you have forgotten single quotes for your string-values
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"Values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
"(1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
still is getting error -
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) Values (1, 'comm', 401, 8, '12/12/2009', 6) , (1, 'comm', 401,8, '12/12/2009', 7)
System.Data.SqlClient.SqlE
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) Values (1, 'comm', 401, 8, '12/12/2009', 6) , (1, 'comm', 401,8, '12/12/2009', 7)
System.Data.SqlClient.SqlE
What version of sql are you using.
For sql2000/2005 try the following:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"select (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
" union all select (1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
For sql2008, try the following:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
"values (1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
http://www.dotnetspider.com/resources/19650-How-Insert-Multiple-Records-Using-Single-Insert-SQL-SERVER.aspx
For sql2000/2005 try the following:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"select (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
" union all select (1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
For sql2008, try the following:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"values (1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6) , " & _
"values (1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7)"
http://www.dotnetspider.com/resources/19650-How-Insert-Multiple-Records-Using-Single-Insert-SQL-SERVER.aspx
I am using sql 2000. but still same problem -
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) select (1, 'comments', 401, 8, '12/05/2009', 6) , union all select (1, 'comments', 401,8, '12/05/2009', 7)
System.Data.SqlClient.SqlE
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) select (1, 'comments', 401, 8, '12/05/2009', 6) , union all select (1, 'comments', 401,8, '12/05/2009', 7)
System.Data.SqlClient.SqlE
You'll need a small adjustment, because the following query will work:
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat)
select 1, 'comments', 401, 8, '12/05/2009', 6
union all
select 1, 'comments', 401,8, '12/05/2009', 7
In code:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"select 1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6 " & _
" union all select 1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7"
Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat)
select 1, 'comments', 401, 8, '12/05/2009', 6
union all
select 1, 'comments', 401,8, '12/05/2009', 7
In code:
strSQL = "Insert into QuestionDetails (SurveyTypeID, CommentsAfterQuestion, CentreID, CentreScore, EnquiryDate, QCat) " & _
"select 1, '" & CQ & "', " & CID & ", " & q12 & ", '" & CD & "', 6 " & _
" union all select 1, '" & CQ & "', " & CID & "," & q13 & ", '" & CD & "', 7"
Featured Post
Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Sometimes in DotNetNuke module development you want to swap controls within the same module definition. In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections. To get around this you need t…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found. I hope it will be useful.
Problem:
After closing a pop-up window, the parent page should be refreshed automaticall…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 13 hours left to enroll
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.