Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
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
Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
ASP.Net to Oracle Connectivity
Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers to develop an ASP.NET client…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse
While installing Eclipse in windows, got one error like above and unable to proceed with the installation.
This video describes how to successfully install Eclipse.
How to solve incompa…
Suggested Courses
Course of the Month9 days, 2 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.