locked
Filling Dataset Problem RRS feed

  • Question

  • Hi All,

    i have 50 tables.
    M trying to fill the table in dataset using a loop.
    no roblem for first 25 tables ,
    but at 26th table it gives the error "Input string was not in a correct format.Couldn't store <value> in "column_name" Column.  Expected type is UInt32.

    column type is varchar(50)
    changed it to TEXT ....but nothing happened.

    its taking numericals but no characters.

    Debugged one by one...
    at 26th table....
    adapter.fill(dataset)
    throws exception

    Please help me.

    regards,
    Amit.


    Friday, February 8, 2008 7:11 AM

Answers

  • Hi,

    The problem looks clear and simple:-)
    Monitoring solution can be slightly more complex.

    In simple words the ERROR is: Input string was not in a correct format for the target type. Couldn't store the value in target column.

    The problem : You have a value (one or more ) read from the original table that you are trying to put in the final table, but its type is not suitable for the type of the final table. In your case ,type of the target table column is Int32 and you're trying to insert a String type. Therefore, the operation fails.

    1. Obviously the problem was in table 26, but that is not saying that all the other id work as you wanted or as expected. But for monitoring the problem you should first focus on working with the table 26.

    Perform the same action only table 26!

    2. I see you're working with MySqlDataAdapter. This Adapter do not fit to SQL Server! morover you are working with MySqlDataReader which is a MySQL reader and again... is not fit to work with SQL Server, therefor the data is not reading currently and not processed / parsed correctly!

    ABOVE ALL YOU ARE WORKING WITH MySqlConnection WHICH IS AGAIN MySQL CONNECTION CLASS AND NOT SQL Server CONNECTION CLASS!

    This look like you are working with MySQL server, and if so you are not in the right forum, or you have just copy a code without understand what it is doing. You should look for a tutorial on basic working with SQL Server on your type of application!

    basically you should use:
    SqlConnection instead of MySqlConnection
    SqlDataAdapter instead of MySqlDataAdapter
    SqlDataReader instead of MySqlDataReader


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, January 22, 2014 2:45 PM

All replies

  • Well, its really hard to help you without knowing your code. But from the explanation given so far, you are using a typed dataset which expects a INT32, but the query for filling the dataset is querying a column having another datatype. You will simply have to rebuild your dataset in Visual Studio or make the query to align with the dataset again.

    Jens K. Suessmeyer


    ---
    http://www.sqlserver2005.de
    ---

    Saturday, February 9, 2008 10:18 PM
  • Hello,
    Thank you for the reply...
    m not getting wht you are saying....can u explain???

    regards,
    Amit.
    Monday, February 11, 2008 5:59 AM
  • sending source code....


    Dim MyCon As MySqlConnection

            Try

                Dim MyCmd As MySqlCommand
                Dim MyDs As DataSet
                Dim MyDs_Tables As DataSet
                Dim MyDa As MySqlDataAdapter
                Dim MyDr As MySqlDataReader
                Dim MyCol As Integer = 0
                Dim MyRow As Integer = 0
                Dim table_count As Integer = 0
                Dim table_row As Integer = 0
                Dim table_col As Integer = 0
                Dim count As Integer = 0

                MyCon = New MySqlConnection("Persist Security Info=true;server=loacalhost;database=user;user id=userid;password=password")
                MyCon.Open()

                MyCmd = New MySqlCommand
                MyCmd.CommandText = CommandType.Text
                MyCmd.Connection = MyCon

                MyCmd.CommandText = "show tables from user"

                MyDs_Tables = New DataSet

                MyDa = New MySqlDataAdapter(MyCmd)
                MyDa.Fill(MyDs_Tables)
                'MyDr = MyCmd.ExecuteReader

                MessageBox.Show("Total tables :" + MyDs_Tables.Tables(table_count).Rows.Count.ToString)
                lstvw.Clear()
                lstvw.View = View.List
              
                'MyDr.Close()
                MyRow = 0
                MyCol = 0
                table_count = 0
                MyDs = New DataSet
                '
                '*****************************WRITE XML*********************************
                Dim settings As New XmlWriterSettings()
                settings.Indent = True

                settings.NewLineOnAttributes = False

                Using writer As XmlWriter = XmlWriter.Create("C:\xmlfile.xml", settings)


                    writer.WriteStartDocument()
                    writer.WriteStartElement("Database")
                    writer.WriteStartElement("tables")

                    For table_row = 0 To MyDs_Tables.Tables(table_count).Rows.Count - 1

                        MyCmd.CommandText = "Select * from " + MyDs_Tables.Tables(table_count).Rows(table_row).Item(table_col).ToString
                        MyDs.Clear()
                        'If MyDs_Tables.Tables(table_count).Rows(table_row).Item(table_col).ToString = "pjvpmandal" Then
                        '    Dim str As String = ""
                        '    str = MyDs.Tables("pjvpmandal").Columns("q9ferfarno").DataType.Name.ToString()
                        'End If

                        MyDa.Fill(MyDs)

                        MsgBox(MyDs.Tables(table_count).TableName)
                       

                        MyDs.Tables(table_count).TableName = MyDs_Tables.Tables(table_count).Rows(table_row).Item(table_col).ToString

                       
                        writer.WriteStartElement("table")
                        writer.WriteStartAttribute("name")
                        writer.WriteValue(MyDs.Tables(table_count).TableName.ToString())
                        writer.WriteEndAttribute()
                        writer.WriteWhitespace(vbNewLine)

                        writer.WriteStartElement("rows")
                        writer.WriteWhitespace(vbNewLine)


                        For MyRow = 0 To MyDs.Tables(count).Rows.Count - 1
                            writer.WriteStartElement("row")
                            writer.WriteWhitespace(vbNewLine)
                            writer.WriteStartElement("fields")
                            writer.WriteWhitespace(vbNewLine)
                            For MyCol = 0 To MyDs.Tables(count).Columns.Count - 1

                                writer.WriteStartElement("field")
                                writer.WriteStartAttribute("name")
                                writer.WriteValue(MyDs.Tables(count).Columns(MyCol).ColumnName.ToString())
                                writer.WriteEndAttribute()

                                writer.WriteCData(MyDs.Tables(count).Rows(MyRow).Item(MyCol).ToString)

                                writer.WriteEndElement() '</field>
                                writer.WriteWhitespace(vbNewLine)
                            Next
                            writer.WriteEndElement() '</fields>
                            writer.WriteWhitespace(vbNewLine)
                            writer.WriteEndElement()     '</row>
                            writer.WriteWhitespace(vbNewLine)
                        Next

                        writer.WriteEndElement() '</rows>
                        writer.WriteWhitespace(vbNewLine)
                        writer.WriteEndElement() '</table>

                        'MyDr.Close()
                        lstvw.Items.Add(MyDs_Tables.Tables(table_count).Rows(table_row).Item(table_col))
                        count = 1
                    Next
                    writer.WriteEndElement() '</tables>
                    writer.WriteEndElement() '</Database>
                    writer.WriteEndDocument()
                    writer.Flush()
                    writer.Close()

                End Using

                MyDs.Clear()
                MyCon.Close()
                MsgBox("file written")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Exit Sub
            Finally
                MyCon.Close()
            End Try
    Monday, February 11, 2008 6:09 AM
  • Vengs,

    Is this still an issue?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, January 22, 2014 7:35 AM
  • Hi,

    The problem looks clear and simple:-)
    Monitoring solution can be slightly more complex.

    In simple words the ERROR is: Input string was not in a correct format for the target type. Couldn't store the value in target column.

    The problem : You have a value (one or more ) read from the original table that you are trying to put in the final table, but its type is not suitable for the type of the final table. In your case ,type of the target table column is Int32 and you're trying to insert a String type. Therefore, the operation fails.

    1. Obviously the problem was in table 26, but that is not saying that all the other id work as you wanted or as expected. But for monitoring the problem you should first focus on working with the table 26.

    Perform the same action only table 26!

    2. I see you're working with MySqlDataAdapter. This Adapter do not fit to SQL Server! morover you are working with MySqlDataReader which is a MySQL reader and again... is not fit to work with SQL Server, therefor the data is not reading currently and not processed / parsed correctly!

    ABOVE ALL YOU ARE WORKING WITH MySqlConnection WHICH IS AGAIN MySQL CONNECTION CLASS AND NOT SQL Server CONNECTION CLASS!

    This look like you are working with MySQL server, and if so you are not in the right forum, or you have just copy a code without understand what it is doing. You should look for a tutorial on basic working with SQL Server on your type of application!

    basically you should use:
    SqlConnection instead of MySqlConnection
    SqlDataAdapter instead of MySqlDataAdapter
    SqlDataReader instead of MySqlDataReader


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, January 22, 2014 2:45 PM