4 Replies - 1469 Views - Last Post: 01 July 2014 - 06:16 AM Rate Topic: -----

#1 puuts   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 13

INSERT ALL ITEMS OF LISTVIEW TO ACCESS DATABASE

Posted 30 June 2014 - 03:19 AM

Hello Gurus,

I've trying to insert all items of my listview to access database using the below code, but I'm having an error "SYNTAX error in INSERT INTO statement...."


 If ListView6.Items.Count > 0 Then

            Try
                For i = 0 To ListView6.Items.Count - 1


                    Dim SqlQuery As String = "INSERT INTO tblComplete (ReportName,Function,Region,AssignTo,Timestamp) VALUES ('" & ListView6.Items(i).SubItems(1).ToString & "', '" & ListView6.Items(i).SubItems(2).ToString & "', '" & ListView6.Items(i).SubItems(3).Text.ToString & "', '" & ListView6.Items(i).SubItems(4).Text.ToString & "','" & Date.Now& "')"
                    Dim SqlCommand As New OleDbCommand
                    Dim dbRdr As OleDbDataReader

                    With SqlCommand
                        .CommandText = "SELECT count(*) FROM tblComplete WHERE [email protected]"
                        .Parameters.AddWithValue("@field1", ListView6.Items(i).SubItems(1).ToString)

                    End With


                    SqlCommand.Connection = conn

                    dbRdr = SqlCommand.ExecuteReader
                    If dbRdr.HasRows = True Then
                        dbRdr.Read()

                        If dbRdr.Item(0) = 0 Then
                            dbRdr.Close()

                            With SqlCommand
                                .CommandText = SqlQuery
                                .Connection = conn
                                .ExecuteNonQuery()

                            End With

                        End If
                        MsgBox("Add")
                    End If

                Next

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try




Could you please advise what/where wrong with my code?
Thanks in advance!

Is This A Good Question/Topic? 0
  • +

Replies To: INSERT ALL ITEMS OF LISTVIEW TO ACCESS DATABASE

#2 jimzcoder   User is offline

  • D.I.C Regular

Reputation: 57
  • View blog
  • Posts: 351
  • Joined: 14-November 12

Re: INSERT ALL ITEMS OF LISTVIEW TO ACCESS DATABASE

Posted 30 June 2014 - 05:16 AM

check line 12 and 13
you are suppose to use the the parameter name"@Reportname"
but you added a different parameter name on 13th line "@field1"

just change it into "@Reportname" and it should be fine.

Good luck
Was This Post Helpful? 0
  • +
  • -

#3 puuts   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-May 13

Re: INSERT ALL ITEMS OF LISTVIEW TO ACCESS DATABASE

Posted 30 June 2014 - 10:51 PM

Hi Jim,

I've change "@field1" to "@ReportName" but still same error occurs "Syntax error in INSERT INTO statement"

 If ListView6.Items.Count > 0 Then

            Try
                For i = 0 To ListView6.Items.Count - 1


                    Dim SqlQuery As String = "INSERT INTO tblComplete (ReportName,Function,Region,AssignTo,Timestamp) VALUES ('" & ListView6.Items(i).SubItems(1).ToString & "', '" & ListView6.Items(i).SubItems(2).ToString & "', '" & ListView6.Items(i).SubItems(3).Text.ToString & "', '" & ListView6.Items(i).SubItems(4).Text.ToString & "','" & fname & "')"
                    Dim SqlCommand As New OleDbCommand
                    Dim dbRdr As OleDbDataReader

                    With SqlCommand
                        .CommandText = "SELECT count(*) FROM tblComplete WHERE [email protected]"
                        [b].Parameters.AddWithValue("@ReportName", ListView6.Items(i).SubItems(1).ToString)[/b]

                    End With


                    SqlCommand.Connection = conn

                    dbRdr = SqlCommand.ExecuteReader
                    If dbRdr.HasRows = True Then
                        dbRdr.Read()

                        If dbRdr.Item(0) = 0 Then
                            dbRdr.Close()

                            With SqlCommand
                                .CommandText = SqlQuery
                                .Connection = conn
                                .ExecuteNonQuery()

                            End With

                        End If
                        MsgBox("Add")
                    End If

                Next

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try


        End If



Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • blow up my boots
  • member icon

Reputation: 6544
  • View blog
  • Posts: 26,527
  • Joined: 12-December 12

Re: INSERT ALL ITEMS OF LISTVIEW TO ACCESS DATABASE

Posted 01 July 2014 - 02:53 AM

Print out SqlQuery using a MessageBox or Debug.Print so that you can check it.

I gave the same advice in your other topic.

This post has been edited by andrewsw: 01 July 2014 - 02:55 AM

Was This Post Helpful? 0
  • +
  • -

#5 jimzcoder   User is offline

  • D.I.C Regular

Reputation: 57
  • View blog
  • Posts: 351
  • Joined: 14-November 12

Re: INSERT ALL ITEMS OF LISTVIEW TO ACCESS DATABASE

Posted 01 July 2014 - 06:16 AM

yeah.. try following andrewsw's advice.
meanwhile.
check this line here:
Dim SqlQuery As String = "INSERT INTO tblComplete (ReportName,Function,Region,AssignTo,Timestamp) VALUES ('" & ListView6.Items(i).SubItems(1).ToString & "', '" & ListView6.Items(i).SubItems(2).ToString & "', '" & ListView6.Items(i).SubItems(3).Text.ToString & "', '" & ListView6.Items(i).SubItems(4).Text.ToString & "','" & fname & "')"


Based on what i know timestamp usually represents DateTime DataType.
Assuming timeStamp is a DataTime DataType on your Database Table.
you must remove the single "'" quote at the beginning and end of your value.

your code should now look like this:
Dim SqlQuery As String = "INSERT INTO tblComplete (ReportName,Function,Region,AssignTo,Timestamp) VALUES ('" & ListView6.Items(i).SubItems(1).ToString & "', '" & ListView6.Items(i).SubItems(2).ToString & "', '" & ListView6.Items(i).SubItems(3).Text.ToString & "', '" & ListView6.Items(i).SubItems(4).Text.ToString & "'," & DateTime.Now & ")"



Hope it helped. Good luck.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1