Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

INSERT INTO .... PROBLEMS !!!


Hi,

I am tring to insert a lot of records (about 10.000) in a local table on PPC
with SqlCE 2.0 and an application written with VB.NET 2003
I retrieve data from a SQL Server 2000 via Internet and parking them on
datatables. Then I use a sqlCeCommand to execute a bulk operation (insert
records in a empty table).
For many table all it's ok but when I find a Date field start problems. I
receive an error with no message (a sqlCeException).
The core code is:

'strLocalTable is the name of the TableName in Dataset and the table name in
the SqlCE db

 Dim cmdHardL As SqlCeCommand = conL.CreateCommand
 Dim strListField As String 'string with field name
 Dim strListArg As String 'list of ?

 'strListField creation
 For i = 0 To dsR.Tables(strLocalTable).Columns.Count - 1
       strListField = strListField &
dsR.Tables(strLocalTable).Columns(i).ColumnName & ", "
 Next

'I need to add a field named FLAG that exist only in table on local db
strListField = strListField & "FLAG"

'strListArg creation
For i = 0 To dsR.Tables(strLocalTable).Columns.Count
       strListArg = strListArg & ", ?"
Next
strListArg = Trim(Mid(strListArg, 2))

cmdHardL.CommandText = "INSERT INTO " & strLocalTable & " (" & strListField
& ") VALUES " & "(" & strListArg & ")"
cmdHardL.Parameters.Clear()

'Note that ConvertDBtoSQLtype is a function that return a SqlDbType in
according with Microsoft table conversion type
For s = 0 To dsR.Tables(strLocalTable).Columns.Count - 1
    'Qualifing input parameters
    If dsR.Tables(strLocalTable).Columns(s).DataType.FullName =
"System.String" Then
        If dsR.Tables(strLocalTable).Columns(s).MaxLength > 0 Then

cmdHardL.Parameters.Add(dsR.Tables(strLocalTable).Columns(s).ColumnName,
ConvertDBtoSQLtype(dsR.Tables(strLocalTable).Columns(s)),
dsR.Tables(strLocalTable).Columns(s).MaxLength)
        Else

cmdHardL.Parameters.Add(dsR.Tables(strLocalTable).Columns(s).ColumnName,
ConvertDBtoSQLtype(dsR.Tables(strLocalTable).Columns(s)))
        End If
    ElseIf dsR.Tables(strLocalTable).Columns(s).DataType.FullName =
"System.DateTime" Then

cmdHardL.Parameters.Add(dsR.Tables(strLocalTable).Columns(s).ColumnName,
System.Data.SqlDbType.DateTime)
    Else

cmdHardL.Parameters.Add(dsR.Tables(strLocalTable).Columns(s).ColumnName,
ConvertDBtoSQLtype(dsR.Tables(strLocalTable).Columns(s)))
    End If
Next

cmdHardL.Parameters.Add("FLAG", System.Data.SqlDbType.NVarChar)
cmdHardL.Prepare()

'And now let's start to import data
For i = 0 To dsR.Tables(strLocalTable).Rows.Count - 1
    For s = 0 To dsR.Tables(strLocalTable).Columns.Count - 1
        'Value for input parameters
        If IsDBNull(dsR.Tables(strLocalTable).Rows(i)(s)) Then
            cmdHardL.Parameters(s).Value =
DefaultDataValue(dsR.Tables(strLocalTable).Columns(s))
        Else
            If dsR.Tables(strLocalTable).Columns(s).DataType.FullName =
"System.String" Then
                cmdHardL.Parameters(s).Value =
Convert.ToString(Replace(dsR.Tables(strLocalTable).Rows(i)(s), "'", "''"))
            ElseIf dsR.Tables(strLocalTable).Columns(s).DataType.FullName =
"System.DateTime" Then
                cmdHardL.Parameters(s).Value =
Convert.ToDateTime(dsR.Tables(strLocalTable).Rows(i)(s))
            Else
                cmdHardL.Parameters(s).Value =
dsR.Tables(strLocalTable).Rows(i)(s)
            End If
        End If
    Next
    cmdHardL.Parameters(s).Value = Convert.ToString("0")

    cmdHardL.ExecuteNonQuery()
Next

'Here I've omitted the Try Catch routine

What's wrong ?

The problems is with date or what else ?

PLEASE HELP ME !!!!

Daniel

Hi

Please post the error message

Regards
VT
Knowledge is power, share it....
http://oneplace4sql.blogspot.com/
"Daniele Livio Dainesi" <ddain@infinito.it> wrote in message
news:OhVdnldoHHA.4428@TK2MSFTNGP06.phx.gbl...

Hi VT

I've seen that the problem is "Requested conversion in not supported" or
"Data conversion failed. OLE DB Status ,,,,," and this happen every time
there is a Date field and sometimes without date fields.

But, first of all: how can i pass a date parameter for a date field ?

I've writed so:

cmd.Parameters(0).Value = Convert.ToDateTime(ds.Tables(0).Rows(i)(s))

I've tried also

cmd.Parameters(0).Value = ds.Tables(0).Rows(i)(s)

or

cmd.Parameters(0).Value = Format(ds.Tables(0).Rows(i)(s),"yyyy/MM/dd
hh:mm:ss"

but ...nothing! Ialways get an error !!!

I've tried also to make a text parameter and pass a date field within a
string but nothing !!

What's wrong ???

"vt" <vinu.t.1@gmail.com> ha scritto nel messaggio
news:%23OUJ4odoHHA.1776@TK2MSFTNGP05.phx.gbl...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc