Solved

How to insert multiple rows in one click

Posted on 2009-05-18
15
370 Views
Last Modified: 2012-05-07
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("CommentsAfterQuestion")
        Dim CID As Integer = Request("drpDLforCentreName")
        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
0
Comment
Question by:arifpervez
  • 8
  • 7
15 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410216
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)"
0
 

Author Comment

by:arifpervez
ID: 24410258
now i am getting error System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection......
0
 

Author Comment

by:arifpervez
ID: 24410278
Full error - System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Lexus.Frm_AddQuestionDetails.btnSubmit_Click(Object sender, EventArgs e) in C:\Documents and Settings\Test\My Documents\Visual Studio 2008\Projects\Test\Frm_AddQuestionDetails.aspx.vb:line 51
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410295
Can you print the value of strSQL after you filled it (before your try)
0
 

Author Comment

by:arifpervez
ID: 24410337
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
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410358
I mean your complete sql-statement !
0
 

Author Comment

by:arifpervez
ID: 24410416
Dim CQ As String = Request("CommentsAfterQuestion")
        Dim CID As Integer = Request("drpDLforCentreName")
        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()
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 53

Expert Comment

by:Dhaest
ID: 24410466
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
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410476
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)"
0
 

Author Comment

by:arifpervez
ID: 24410523
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.SqlException: Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 24410564
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
0
 

Author Comment

by:arifpervez
ID: 24410580
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.SqlException: Line 1: Incorrect syntax near ','. Line 1: Incorrect syntax near ','. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
0
 

Author Comment

by:arifpervez
ID: 24410615
I remove the , before union all. though same error
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 24410621
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"
0
 

Author Comment

by:arifpervez
ID: 24410631
Thanks for your help. Its working.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now