Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Unanswered: sql data convert error for replication database in vb.net!!

    Hi..
    I'm starting using vb.net..i'm doing replication database from one server to another server. Really hope anybody can help me..Please...


    Here is the date sql coding :-

    SQL = "SELECT * FROM b_CODATxn WHERE day(dTxnDate) = 12 and month(dTxnDate)=3 and year(dTxnDate)=2008"

    HEre is the replication sql coding:-

    SQL = "INSERT INTO b_CODATxn (sIC,sSemester,sSeqNo,iTxnLineID,iLineID,dTxnDate, sTxnType,sAccNo, iDrCrInd,mAmt,sDesc,sFeeCode,iRecur,dProcessDate," + _
    " iID,sModFlag,sPostUser,iPeriod,iYear,mAmtPart ,sDepositSlip,sDoccode,sDocnum,sPayMode,sLinkcode, sComments,iLinkRunNo,iJournal,dDocDate) VALUES " + _
    "('" + myarray(i, 0) + "','" + myarray(i, 1) + "','" + myarray(i, 2) + "'," + myarray(i, 4) + "," + myarray(i, 4) + _
    ",'" + myarray(i, 5) + "','" + myarray(i, 6) + "','" + myarray(i, 7) + "'," + myarray(i, 8) + "," + myarray(i, 9) + _
    ",'" + myarray(i, 4) + "','" + myarray(i, 11) + "'," + myarray(i, 4) + ",'" + myarray(i, 13) + "'," + myarray(i, 14) + _
    ",'" + myarray(i, 18) + "','" + myarray(i, 5) + "'," + myarray(i, 17) + "," + myarray(i, 18) + "," + myarray(i, 19) + _
    ",'" + myarray(i, 4) + "','" + myarray(i, 4) + "','" + myarray(i, 4) + "','" + myarray(i, 4) + "','" + myarray(i, 24) + _
    "','" + myarray(i, 5) + "'," + myarray(i, 26) + "," + myarray(i, 27) + ",'" + myarray(i, 28) + "')"

    Here is the error occurs:-

    PROCESS FAIL !!!System.Data.Odbc.OdbcException : ERROR [22003][DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Arithmetic overflow during implicit conversion of VARCHAR value 13/03/2008 to a DATETIME field .

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by hanakimi
    SELECT * FROM b_CODATxn WHERE day(dTxnDate) = 12 and month(dTxnDate)=3 and year(dTxnDate)=2008
    This select will not be able to use an index on the dTxnDate column since you use a function on that column
    change to
    Code:
    WHERE dTxnDate>='20080312' 
      and dTxnDate<'20080313'
    Quote Originally Posted by hanakimi
    Arithmetic overflow during implicit conversion of VARCHAR value ‘13/03/2008’ to a DATETIME field
    The varchar is interpreted as 'mm/dd/ccyy' and overflow on month 13
    use 'ccyymmdd' to avoid confusion

  3. #3
    Join Date
    Nov 2008
    Posts
    8

    database select and insert

    first you select data from databse and loop through the data and insert it. Gere is the selct sample and also insert sample.

    http://vb.net-informations.com/dataa...-sqlserver.htm

    http://vb.net-informations.com/dataa...-sqlserver.htm

    Hope it will help you.

    yang.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •