Solved

How to insert multiple rows in one click

Posted on 2009-05-18
15
359 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

19 Experts available now in Live!

Get 1:1 Help Now