Problem in Saving List Item To the DATABASE

Question

x2fair -2 Light Poster

13 Years Ago

Everyone..please help me...
I cannot save my list item to the database with this code.

Dim my_cmd As MySqlCommand
                        my_cmd = New MySqlCommand()
                        my_cmd.CommandText = "insert into documenttable values(route1,route2,route3,route4,route5,route6,route7,route8,route9,route10,route11,route12)"
                        rs.Open(query, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                        my_cmd.Parameters.AddWithValue(rs("route1").Value, .ListBox1.Items(0))
                        my_cmd.Parameters.AddWithValue(rs("route2").Value, .ListBox1.Items(1))
                        my_cmd.Parameters.AddWithValue(rs("route3").Value, .ListBox1.Items(2))
                        my_cmd.Parameters.AddWithValue(rs("route4").Value, .ListBox1.Items(3))
                        my_cmd.Parameters.AddWithValue(rs("route5").Value, .ListBox1.Items(4))
                        my_cmd.Parameters.AddWithValue(rs("route6").Value, .ListBox1.Items(5))
                        my_cmd.Parameters.AddWithValue(rs("route7").Value, .ListBox1.Items(6))
                        my_cmd.Parameters.AddWithValue(rs("route8").Value, .ListBox1.Items(7))
                        my_cmd.Parameters.AddWithValue(rs("route9").Value, .ListBox1.Items(8))
                        my_cmd.Parameters.AddWithValue(rs("route10").Value, .ListBox1.Items(9))
                        my_cmd.Parameters.AddWithValue(rs("route11").Value, .ListBox1.Items(10))
                        my_cmd.Parameters.AddWithValue(rs("route12").Value, .ListBox1.Items(11))

vb.net

Edited 13 Years Ago by kvprajapati because: Added [code] tags.

5 Contributors
8 Replies
158 Views
21 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by kvprajapati

nytro 0 Newbie Poster

13 Years Ago

your insert command should be something like:

Dim my_cmd As MySqlCommand
my_cmd = New MySqlCommand()
my_cmd.CommandText = "insert into documenttable(?,?,?,?,?,?,?,?,?,?,?,?) values(route1,route2,route3,route4,route5,route6,route7,route8,route9,route10,route11,route12)"
rs.Open(query, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
my_cmd.Parameters.AddWithValue(rs("route1").Value, .ListBox1.Items(0))
my_cmd.Parameters.AddWithValue(rs("route2").Value, .ListBox1.Items(1))
my_cmd.Parameters.AddWithValue(rs("route3").Value, .ListBox1.Items(2))
my_cmd.Parameters.AddWithValue(rs("route4").Value, .ListBox1.Items(3))
my_cmd.Parameters.AddWithValue(rs("route5").Value, .ListBox1.Items(4))
my_cmd.Parameters.AddWithValue(rs("route6").Value, .ListBox1.Items(5))
my_cmd.Parameters.AddWithValue(rs("route7").Value, .ListBox1.Items(6))
my_cmd.Parameters.AddWithValue(rs("route8").Value, .ListBox1.Items(7))
my_cmd.Parameters.AddWithValue(rs("route9").Value, .ListBox1.Items(8))
my_cmd.Parameters.AddWithValue(rs("route10").Value, .ListBox1.Items(9))
my_cmd.Parameters.AddWithValue(rs("route11").Value, .ListBox1.Items(10))
my_cmd.Parameters.AddWithValue(rs("route12").Value, .ListBox1.Items(11))

Edited 13 Years Ago by kvprajapati because: Added [code] tags.

kruxena 1 Newbie Poster

13 Years Ago

HI...

? means the field in your table in your database. The command in your code cannot add because it doesn't know what field is he adding to.

my_cmd.CommandText = "insert into documenttable(fieldroute1,fieldroute2,fieldroute3,fieldroute4,fieldroute5,fieldroute6,fieldroute7,fieldroute8,fieldroute9,fieldroute210) values(route1,route2,route3,route4,route5,route6,route7,route8,route9,route10,route11,route12)"

try this one and let us know if it answers your question.
ty

Edited 13 Years Ago by kvprajapati because: Added [code] tags.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

x2fair -2 Light Poster · Answer 1 · 2011-01-15T06:24:26+00:00

x2fair -2 Light Poster

13 Years Ago

whats the"?" means?

x2fair -2 Light Poster · Answer 2 · 2011-01-15T09:11:09+00:00

my original code look like this

my_cmd.CommandText = "Insert into documenttable(route1,route2,route3,route4,route5,route6,route7,route8,route9,route10,route11,route12) values (route1,route2,route3,route4,route5,route6,route7,route8,route9,route10,route11,route12)"           
 my_cmd.Parameters.AddWithValue("route2", .ListBox1.Items.Item(1))
                                  my_cmd.Parameters.AddWithValue("route3", .ListBox1.Items.Item(2))
                                  my_cmd.Parameters.AddWithValue("route4", .ListBox1.Items.Item(3))
                                 my_cmd.Parameters.AddWithValue("route5", .ListBox1.Items.Item(4))
                                my_cmd.Parameters.AddWithValue("route6", .ListBox1.Items.Item(5))
                               my_cmd.Parameters.AddWithValue("route7", .ListBox1.Items.Item(6))
                              my_cmd.Parameters.AddWithValue("route8", .ListBox1.Items.Item(7))
                             my_cmd.Parameters.AddWithValue("route9", .ListBox1.Items.Item(8))
                            my_cmd.Parameters.AddWithValue("route10", .ListBox1.Items.Item(9))
                           my_cmd.Parameters.AddWithValue("route11", .ListBox1.Items.Item(10))
                          my_cmd.Parameters.AddWithValue("route12", .ListBox1.Items.Item(11))

zinnqu 7 Junior Poster in Training · Answer 3 · 2011-01-15T10:10:41+00:00

A easier way is this, contencate the statement with the information in it

my_cmd.CommandText = "Insert into TableName _
      (route1,route2,route3,route4,route5,route6,route7,route8,route9,route10,route11,route12) Values " _ 
      & ListBox1.Items.Item(0) _
      & "," _
      & ListBox1.Items.Item(1) _
      ...
      & ListBox1.Items.Item(11) & ") "

then call the command. Less confusion and it will fill the objects that you need in one shot

x2fair -2 Light Poster · Answer 4 · 2011-01-15T11:19:36+00:00

this is my whole code kindly check it...

Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        conn = New ADODB.Connection
        rs = New ADODB.Recordset

        With conn
            .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=" & MySQL_SVR & ";" _
            & "DATABASE=DocumentDB;" _
            & "UID=" & MySQL_UID & ";" _
            & "PWD=" & MySQL_PWD & ";" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .Open()
            .Close()
        End With

        conn.Open()

        With rs
            Try
                query = "SELECT * FROM Documenttable WHERE documentID= '" & frmDocumentDetails.txtBarcodeID.Text & "'"

                rs.Open(query, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

                If rs.RecordCount <> 0 Then

                    MsgBox("Please click again the GENERATE ID NUMBER button because that BARCODE NUMBER already exist. Thank you.", vbCritical, "ERROR BARCODE ID : : EVSU Document Tracking")
                    frmDocumentDetails.Show()
                    frmDocumentDetails.cmdGenerate.Focus()

                Else
                    .AddNew()

                    With frmDocumentDetails
                   
                        rs(0).Value = .txtBarcodeID.Text
                        rs(1).Value = .lblDateToday.Text
                        rs(2).Value = .txtDocumentCreator.Text
                        rs(3).Value = .txtTitle.Text
                        rs(4).Value = .cmbtype.Text
                        rs(5).Value = .txtDesc.Text
                        rs(6).Value = .txtCurrent.Text

                        Dim my_cmd As MySqlCommand
                        For j As Integer = 0 To .ListBox1.Items.Count.ToString - 1
                            my_cmd = New MySqlCommand()
                            my_cmd.CommandText = ("Insert into documenttable (route1, route2, route3, route4, route5, route6, route7, route8, route9, route10, route11, route12) values " _
                                                & .ListBox1.Items.Item(0) _
                                                      & "," _
                                                & .ListBox1.Items.Item(1) _
                                                      & "," _
                                                & .ListBox1.Items.Item(2) _
                                                      & "," _
                                                & .ListBox1.Items.Item(3) _
                                                      & "," _
                                                & .ListBox1.Items.Item(4) _
                                                      & "," _
                                                & .ListBox1.Items.Item(5) _
                                                      & "," _
                                                & .ListBox1.Items.Item(6) _
                                                 & "," _
                                                & .ListBox1.Items.Item(7) _
                                                      & "," _
                                                & .ListBox1.Items.Item(8) _
                                                      & "," _
                                                & .ListBox1.Items.Item(9) _
                                                      & "," _
                                                & .ListBox1.Items.Item(10) _
                                                      & "," _
                                                & .ListBox1.Items.Item(11) & ") ")

                            my_cmd.ExecuteNonQuery()
                        Next

                    End With
                    .Update()
                    MsgBox("Details has been save.!", vbInformation, "EVSU Document Tracking")
                    .Close()

                    MsgBox("This will continue to Document Barcode Printing.", vbInformation, "EVSU Document Tracking")
                    frmDocumentDetails.Hide()
                    frmConfirmationWindow.Show()
                    frmConfirmationWindow.txtInput.Text = (frmDocumentDetails.txtBarcodeID.Text)

                    With frmDocumentDetails

                        Dim str1 As String = .txtTitle.Text
                        .txtBarcodeID.Text = ""
                        .txtDesc.Text = ""
                        .cmbName.Text = ""
                        .txtTitle.Text = ""
                        .cmbtype.Text = ""
                        .ListBox1.Items.Clear()
                        .txtCurrent.Text = ""

                    End With
                End If
            Catch ex As Exception
                Exit Try
            End Try
        End With
        conn.Close()

kruxena 1 Newbie Poster · Answer 5 · 2011-01-15T11:45:13+00:00

Hi i think you miss one open parenthesis there.

my_cmd.CommandText = ("Insert into documenttable (route1, route2, route3, route4, route5, route6, route7, route8, route9, route10, route11, route12) values (" _  <---Here
& .ListBox1.Items.Item(0) _
& "," _
& .ListBox1.Items.Item(1) _
& "," _
& .ListBox1.Items.Item(2) _
& "," _
& .ListBox1.Items.Item(3) _
& "," _
& .ListBox1.Items.Item(4) _
& "," _
& .ListBox1.Items.Item(5) _
& "," _
& .ListBox1.Items.Item(6) _
& "," _
& .ListBox1.Items.Item(7) _
& "," _
& .ListBox1.Items.Item(8) _
& "," _
& .ListBox1.Items.Item(9) _
& "," _
& .ListBox1.Items.Item(10) _
& "," _
& .ListBox1.Items.Item(11) & ") ")

Is there any error? can you post it here? ty

kvprajapati 1,826 Posting Genius Team Colleague · Answer 6 · 2011-01-15T14:16:47+00:00

Double thread - http://www.daniweb.com/forums/thread339772.html

Thread Locked.