1 Replies - 375 Views - Last Post: 15 October 2013 - 06:51 AM Rate Topic: -----

#1 hendrikbez  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 20-August 08

Number of query values and destination fileds are not the same

Posted 14 October 2013 - 11:39 PM

I am getting a error "Number of query values and destination fileds are not the same"

 Private Sub Add_Info_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        cn.SetExcelConnectionString(IO.Path.Combine(Application.StartupPath, "Backup tapes.xlsx"), UseHeader.Yes, ExcelImex.TryScan)


        Dim cmd As OleDbCommand = New OleDbCommand(" SELECT [Tape#], [Container#], [ContainerRef#], [Date out], [Date to be back], [Date tapes did Return], [Frequently], [Type of Backup],[Recieved Back], [System], [Day Back] FROM [Tapes$]", cn)
        'Dim cmd As OleDbCommand = New OleDbCommand(" SELECT [Tape#] As TapeNumber, [Container#] As ContainerNumber FROM [Tapes$]", cn)

        ExcelAdapter.SelectCommand = cmd

        cn.Open()

        ExcelAdapter.Fill(DataSet1, "Tapes")
        bsData.DataSource = DataSet1.Tables("Tapes").DefaultView



        DataGridView1.DataSource = bsData

        ExcelAdapter.InsertCommand = New OleDbCommand With {.CommandText = "INSERT INTO [Tapes$] VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)", .Connection = cn}
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P1", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P2", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P3", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P4", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P5", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P6", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P7", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P8", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P9", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P10", .OleDbType = OleDbType.WChar})
        ExcelAdapter.InsertCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P11", .OleDbType = OleDbType.WChar})

        ExcelAdapter.UpdateCommand = New OleDbCommand With _
                             { _
                                 .CommandText = _
                                 <SQL>
                                     UPDATE [Tapes$] 
                                     SET Tapes=@P1, Containere =@P2, ContainerRef=@P3, DateOut=@P4, DateBack=@P5, DateReturn=@P6, Freq=@P7, Type=@P8, ReceivedBack=@P9, System=@P10, DayBack=@P11
                                     WHERE Tapes=@P12 AND Containere = @P13 AND ContainerRef =@P14 AND DateOut=@P15 AND DateBack=@P16 AND DateReturn=@P17 AND freq=@P18 AND Type=@P19 AND RecievedBack=@P20, AND System=@P21 AND DayBack=@P22   
                        </SQL>.Value, _
                                 .Connection = cn
                             }

        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P1", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P2", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P3", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P4", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P5", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P6", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P7", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P8", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P9", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P10", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P11", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P12", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P13", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P14", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P15", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P16", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P17", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P18", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P19", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P20", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P21", .OleDbType = OleDbType.WChar})
        ExcelAdapter.UpdateCommand.Parameters.Add(New OleDbParameter With {.ParameterName = "@P22", .OleDbType = OleDbType.WChar})

        'DataGridView1.Columns(3).DefaultCellStyle.Format = "dd MMMM yyyy"
        'DataGridView1.Columns(4).DefaultCellStyle.Format = "dd MMMM yyyy"
        'DataGridView1.Columns(5).DefaultCellStyle.Format = "dd MMMM yyyy"


        DataGridView1.Columns("Tape#").HeaderText = "Tape#"
        DataGridView1.Columns("Container#").HeaderText = "Container#"
        DataGridView1.Columns("Containerref#").HeaderText = "Containerref#"
        DataGridView1.Columns("Date Out").HeaderText = "Date Out"
        DataGridView1.Columns("Date to be back").HeaderText = "Date to be back"
        DataGridView1.Columns("Date tapes did Return").HeaderText = "Date tapes did Return"
        DataGridView1.Columns("Frequently").HeaderText = "Frequently"
        DataGridView1.Columns("Type of Backup").HeaderText = "Type of Backup"
        DataGridView1.Columns("Recieved Back").HeaderText = "Recieved Back"
        DataGridView1.Columns("System").HeaderText = "System"
        DataGridView1.Columns("Day Back").HeaderText = "Day Back"

    End Sub

    Private Sub btbSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btbSave.Click

        If Not String.IsNullOrWhiteSpace(txtTape.Text) AndAlso Not String.IsNullOrWhiteSpace(TxtContainer.Text) AndAlso Not String.IsNullOrWhiteSpace(txtcontainerRef.Text) AndAlso Not String.IsNullOrWhiteSpace(txtDateOut.Text) AndAlso Not String.IsNullOrWhiteSpace(txtDateBack.Text) AndAlso Not String.IsNullOrWhiteSpace(txtDateRetrun.Text) AndAlso Not String.IsNullOrWhiteSpace(txtFrequently.Text) AndAlso Not String.IsNullOrWhiteSpace(txtType.Text) AndAlso Not String.IsNullOrWhiteSpace(txtReceived.Text) AndAlso Not String.IsNullOrWhiteSpace(txtSystem.Text) AndAlso Not String.IsNullOrWhiteSpace(txtDay.Text) Then

            ExcelAdapter.InsertCommand.Parameters(0).Value = txtTape.Text
            ExcelAdapter.InsertCommand.Parameters(1).Value = TxtContainer.Text
            ExcelAdapter.InsertCommand.Parameters(2).Value = txtcontainerRef.Text
            ExcelAdapter.InsertCommand.Parameters(3).Value = txtDateOut.Text
            ExcelAdapter.InsertCommand.Parameters(4).Value = txtDateBack.Text
            ExcelAdapter.InsertCommand.Parameters(5).Value = txtDateRetrun.Text
            ExcelAdapter.InsertCommand.Parameters(6).Value = txtFrequently.Text
            ExcelAdapter.InsertCommand.Parameters(7).Value = txtType.Text
            ExcelAdapter.InsertCommand.Parameters(8).Value = txtReceived.Text
            ExcelAdapter.InsertCommand.Parameters(9).Value = txtSystem.Text
            ExcelAdapter.InsertCommand.Parameters(10).Value = txtDay.Text
            Try
                Dim Affected = ExcelAdapter.InsertCommand.ExecuteNonQuery()
                If Affected = 1 Then
                    CType(bsData.DataSource, DataView).Table.Rows.Add(New Object() _
                        {txtTape.Text, TxtContainer.Text, txtcontainerRef.Text, txtDateOut.Text, txtDateBack.Text, txtDateRetrun.Text, txtFrequently.Text, txtType.Text, txtReceived.Text, txtSystem.Text, txtDay.Text})
                    MessageBox.Show("Record added")
                    ActiveControl = DataGridView1
                Else
                    MessageBox.Show("Failed to add record.")
                End If
            Catch ex As Exception
                MessageBox.Show(
                    String.Format("System error adding record.{0}{1}", Environment.NewLine, ex.Message))
            End Try
        Else
            MessageBox.Show("Please enter Tape # and Container # and Container Ref # and Date out and Date Back and Date Received and Frequently and Received and System and Day")
        End If
    End Sub




Is This A Good Question/Topic? 0
  • +

Replies To: Number of query values and destination fileds are not the same

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9261
  • View blog
  • Posts: 34,750
  • Joined: 12-June 08

Re: Number of query values and destination fileds are not the same

Posted 15 October 2013 - 06:51 AM

020	       ExcelAdapter.InsertCommand = New OleDbCommand With {.CommandText = "INSERT INTO [Tapes$] VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)", .Connection = cn}


Typically you need to tell the insert statement your columns so it explicitly knows where to put what data

INSERT INTO (col1, col2, ..., coln)
VALUES (val1, val2, ..., valn)

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1