Writing Record To FoxPro from VB 
Author Message
 Writing Record To FoxPro from VB

I'm trying to write a new record to a FoxPro table from VB.  My SQL has the
usual INSERT syntax, and worked previously with an Access table.  I got the
following error:

Run-time error '-2147217913 (80040e07)':

[Microsoft][ODBC Visual FoxPro Driver]Data Type Mismatch.

I thought maybe the SQL statement was messed up, so I copied it from the
immediate window and ran it from the command window in FoxPro 7.0.  I kept
getting the "Data Type Mismatch" error.  I removed each insert variable and
its value one at a time until there was only one left.  I STILL got the
error.

I have a feeling this has something to do with how the FoxPro engine handles
SQL.  Does anyone have any ideas?



Sat, 03 Sep 2005 08:49:29 GMT  
 Writing Record To FoxPro from VB
Hi Curt,

It would really help if you posted the code you are running. Otherwise we
can't begin to help, except to guess -

FoxPro True and False are written .T. and .F. (not True/False and not 0/-1)
FoxPro dates are written in curly braces: {03/17/2003}
FoxPro DateTime values are also in curly braces: {03/17/2003 20:56:29}

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> I'm trying to write a new record to a FoxPro table from VB.  My SQL has
> the
> usual INSERT syntax, and worked previously with an Access table.  I got
> the
> following error:

> Run-time error '-2147217913 (80040e07)':

> [Microsoft][ODBC Visual FoxPro Driver]Data Type Mismatch.

> I thought maybe the SQL statement was messed up, so I copied it from the
> immediate window and ran it from the command window in FoxPro 7.0.  I kept
> getting the "Data Type Mismatch" error.  I removed each insert variable
> and
> its value one at a time until there was only one left.  I STILL got the
> error.

> I have a feeling this has something to do with how the FoxPro engine
> handles
> SQL.  Does anyone have any ideas?



Sat, 03 Sep 2005 09:29:45 GMT  
 Writing Record To FoxPro from VB
The reason I didn't post the code is because the problem also existed on a
simple insert query in the command window.  I thought maybe there was
something fundamentally different between insert queries in FoxPro and those
in other engines.  Since that is apparently not the case, here's the code:

****************************************************************************
*******
Sub WriteBatchRowsToTable()

    Dim nRow As Integer
    Dim nTotalRows As Integer

    Dim sCompanyCode As String
    Dim sCode As String
    Dim sBatch As String
    Dim nAmount As Double
    Dim sType As String
    Dim nGridRate As Currency       ' value for rate in Batch grid row
    Dim nRate As Currency           ' value that is written to the table
    Dim sGridDepartment As String   ' value for department in Batch grid row
    Dim sDepartment As String       ' value that is written to the table
    Dim sDescription As String

    sCompanyCode = cboCompanyCode.Text

    nTotalRows = gridBatch.Rows - 1     ' Take away 1 row for fixed row.

    ' Write record to batch table
    For nRow = 1 To nTotalRows
        gridBatch.Row = nRow

        gridBatch.Col = mnBATCH
        sBatch = gridBatch.Text

        gridBatch.Col = mnCODE
        sCode = gridBatch.Text

        gridBatch.Col = mnAMOUNT
        If Not gridBatch.Text = "" Then
            nAmount = CDbl(gridBatch.Text)
        End If

        gridBatch.Col = mnH_E_TYPE
        sType = gridBatch.Text

        gridBatch.Col = mnTEMP_RATE
        If gridBatch.Text = "" Then
            nRate = 0
        Else
            nGridRate = CCur(gridBatch.Text)
            If nGridRate <> mnEmployeeRate Then
                nRate = nGridRate
            Else
                nRate = mnEmployeeRate
            End If
        End If

        gridBatch.Col = mnTEMP_DEPT
        sGridDepartment = gridBatch.Text
        If sGridDepartment <> "" Then
            sDepartment = sGridDepartment
        Else
            sDepartment = ""
        End If

        gridBatch.Col = mnH_E_TYPE_DESC
        sDescription = gridBatch.Text

        gsSQL = "INSERT INTO
Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript
ion,File_No) "
        gsSQL = gsSQL + "VALUES('" + CStr(gnUserID) + "'," +
sSQLString(msCurrentBatch) + "," + sSQLString(sCompanyCode) + ","
        gsSQL = gsSQL + sSQLString(sCode) + "," + CStr(nAmount) + "," +
sSQLString(sType) + "," + CStr(nRate) + ","
        gsSQL = gsSQL + sSQLString(sDepartment) + "," +
sSQLString(sDescription) + "," + sSQLString(msEmployeeFileNumber) + ")"

        ' VB Data   gdbJFLTime.Execute gsSQL

        Set gcn = New ADODB.Connection
        gcn.Open (gsFoxProConnectionString)
        gcn.Execute gsSQL
        gcn.Close

    Next nRow

End Sub

****************************************************************************
**************************************************************

I'm not sure how much this helps, because of all those variables that build
the query.  On that note, here's how the query looks when I rip it out of
the immediate window right before it's executed:

****************************************************************************
**********
INSERT INTO
Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript
ion,File_No) VALUES('1','000950','H6U','E',55,'H',34,NULL,NULL,'101727')

****************************************************************************
*********
It looks like a normal insert statement to me.  As I mentioned, I
systematically removed each field/value pair, starting with the last (in the
command window), and still kept getting the error.

Any help you can give me would be much appreciated.


Quote:
> Hi Curt,

> It would really help if you posted the code you are running. Otherwise we
> can't begin to help, except to guess -

> FoxPro True and False are written .T. and .F. (not True/False and not
0/-1)
> FoxPro dates are written in curly braces: {03/17/2003}
> FoxPro DateTime values are also in curly braces: {03/17/2003 20:56:29}

> --
> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



> > I'm trying to write a new record to a FoxPro table from VB.  My SQL has
> > the
> > usual INSERT syntax, and worked previously with an Access table.  I got
> > the
> > following error:

> > Run-time error '-2147217913 (80040e07)':

> > [Microsoft][ODBC Visual FoxPro Driver]Data Type Mismatch.

> > I thought maybe the SQL statement was messed up, so I copied it from the
> > immediate window and ran it from the command window in FoxPro 7.0.  I
kept
> > getting the "Data Type Mismatch" error.  I removed each insert variable
> > and
> > its value one at a time until there was only one left.  I STILL got the
> > error.

> > I have a feeling this has something to do with how the FoxPro engine
> > handles
> > SQL.  Does anyone have any ideas?



Sat, 03 Sep 2005 11:32:43 GMT  
 Writing Record To FoxPro from VB
This link has some documentation on data and field types for visual foxpro
that may or may not be helpful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7...
ml/conData_and_Field_Types.asp
Without knowing the data types of the fields in the foxpro table I cannot
tell where in the SQL statement there
is the mismatch however I would be looking at any numeric fields in the
table (should not have single quotes around
them in the immediate window) and the currency fields (I'm not sure but I
think you have to put a $ sign in front
of them). Also if you can have a search on your computer for the visual
foxpro ODBC driver help file (DRVVFP.HLP)
that may also help. Good luck.

Eraser


Quote:
> The reason I didn't post the code is because the problem also existed on a
> simple insert query in the command window.  I thought maybe there was
> something fundamentally different between insert queries in FoxPro and
those
> in other engines.  Since that is apparently not the case, here's the code:

****************************************************************************
Quote:
> *******
> Sub WriteBatchRowsToTable()

>     Dim nRow As Integer
>     Dim nTotalRows As Integer

>     Dim sCompanyCode As String
>     Dim sCode As String
>     Dim sBatch As String
>     Dim nAmount As Double
>     Dim sType As String
>     Dim nGridRate As Currency       ' value for rate in Batch grid row
>     Dim nRate As Currency           ' value that is written to the table
>     Dim sGridDepartment As String   ' value for department in Batch grid
row
>     Dim sDepartment As String       ' value that is written to the table
>     Dim sDescription As String

>     sCompanyCode = cboCompanyCode.Text

>     nTotalRows = gridBatch.Rows - 1     ' Take away 1 row for fixed row.

>     ' Write record to batch table
>     For nRow = 1 To nTotalRows
>         gridBatch.Row = nRow

>         gridBatch.Col = mnBATCH
>         sBatch = gridBatch.Text

>         gridBatch.Col = mnCODE
>         sCode = gridBatch.Text

>         gridBatch.Col = mnAMOUNT
>         If Not gridBatch.Text = "" Then
>             nAmount = CDbl(gridBatch.Text)
>         End If

>         gridBatch.Col = mnH_E_TYPE
>         sType = gridBatch.Text

>         gridBatch.Col = mnTEMP_RATE
>         If gridBatch.Text = "" Then
>             nRate = 0
>         Else
>             nGridRate = CCur(gridBatch.Text)
>             If nGridRate <> mnEmployeeRate Then
>                 nRate = nGridRate
>             Else
>                 nRate = mnEmployeeRate
>             End If
>         End If

>         gridBatch.Col = mnTEMP_DEPT
>         sGridDepartment = gridBatch.Text
>         If sGridDepartment <> "" Then
>             sDepartment = sGridDepartment
>         Else
>             sDepartment = ""
>         End If

>         gridBatch.Col = mnH_E_TYPE_DESC
>         sDescription = gridBatch.Text

>         gsSQL = "INSERT INTO

Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript

- Show quoted text -

Quote:
> ion,File_No) "
>         gsSQL = gsSQL + "VALUES('" + CStr(gnUserID) + "'," +
> sSQLString(msCurrentBatch) + "," + sSQLString(sCompanyCode) + ","
>         gsSQL = gsSQL + sSQLString(sCode) + "," + CStr(nAmount) + "," +
> sSQLString(sType) + "," + CStr(nRate) + ","
>         gsSQL = gsSQL + sSQLString(sDepartment) + "," +
> sSQLString(sDescription) + "," + sSQLString(msEmployeeFileNumber) + ")"

>         ' VB Data   gdbJFLTime.Execute gsSQL

>         Set gcn = New ADODB.Connection
>         gcn.Open (gsFoxProConnectionString)
>         gcn.Execute gsSQL
>         gcn.Close

>     Next nRow

> End Sub

****************************************************************************
Quote:
> **************************************************************

> I'm not sure how much this helps, because of all those variables that
build
> the query.  On that note, here's how the query looks when I rip it out of
> the immediate window right before it's executed:

****************************************************************************
Quote:
> **********
> INSERT INTO

Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript
Quote:
> ion,File_No) VALUES('1','000950','H6U','E',55,'H',34,NULL,NULL,'101727')

****************************************************************************

- Show quoted text -

Quote:
> *********
> It looks like a normal insert statement to me.  As I mentioned, I
> systematically removed each field/value pair, starting with the last (in
the
> command window), and still kept getting the error.

> Any help you can give me would be much appreciated.



> > Hi Curt,

> > It would really help if you posted the code you are running. Otherwise
we
> > can't begin to help, except to guess -

> > FoxPro True and False are written .T. and .F. (not True/False and not
> 0/-1)
> > FoxPro dates are written in curly braces: {03/17/2003}
> > FoxPro DateTime values are also in curly braces: {03/17/2003 20:56:29}

> > --
> > Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> > http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



> > > I'm trying to write a new record to a FoxPro table from VB.  My SQL
has
> > > the
> > > usual INSERT syntax, and worked previously with an Access table.  I
got
> > > the
> > > following error:

> > > Run-time error '-2147217913 (80040e07)':

> > > [Microsoft][ODBC Visual FoxPro Driver]Data Type Mismatch.

> > > I thought maybe the SQL statement was messed up, so I copied it from
the
> > > immediate window and ran it from the command window in FoxPro 7.0.  I
> kept
> > > getting the "Data Type Mismatch" error.  I removed each insert
variable
> > > and
> > > its value one at a time until there was only one left.  I STILL got
the
> > > error.

> > > I have a feeling this has something to do with how the FoxPro engine
> > > handles
> > > SQL.  Does anyone have any ideas?



Sat, 03 Sep 2005 13:32:44 GMT  
 Writing Record To FoxPro from VB

Where you have

        NULL

it should possibly be

        .NULL.

--
* -
* - We get these rules to follow ... that and this, these and those ...
* -



Sat, 03 Sep 2005 17:19:48 GMT  
 Writing Record To FoxPro from VB
Have those two fields been set up to accept NULL? If they hade a DEFAULT
value of NULL you wouldn't have to include them at all.
If they don't accept NULL or haven't got NULL as default, they will default
automatically to '' , and consequently you don't have to include them in an
INSERT.
-Anders


Quote:
> The reason I didn't post the code is because the problem also existed on a
> simple insert query in the command window.  I thought maybe there was
> something fundamentally different between insert queries in FoxPro and
those
> in other engines.  Since that is apparently not the case, here's the code:

****************************************************************************
Quote:
> *******
> Sub WriteBatchRowsToTable()

>     Dim nRow As Integer
>     Dim nTotalRows As Integer

>     Dim sCompanyCode As String
>     Dim sCode As String
>     Dim sBatch As String
>     Dim nAmount As Double
>     Dim sType As String
>     Dim nGridRate As Currency       ' value for rate in Batch grid row
>     Dim nRate As Currency           ' value that is written to the table
>     Dim sGridDepartment As String   ' value for department in Batch grid
row
>     Dim sDepartment As String       ' value that is written to the table
>     Dim sDescription As String

>     sCompanyCode = cboCompanyCode.Text

>     nTotalRows = gridBatch.Rows - 1     ' Take away 1 row for fixed row.

>     ' Write record to batch table
>     For nRow = 1 To nTotalRows
>         gridBatch.Row = nRow

>         gridBatch.Col = mnBATCH
>         sBatch = gridBatch.Text

>         gridBatch.Col = mnCODE
>         sCode = gridBatch.Text

>         gridBatch.Col = mnAMOUNT
>         If Not gridBatch.Text = "" Then
>             nAmount = CDbl(gridBatch.Text)
>         End If

>         gridBatch.Col = mnH_E_TYPE
>         sType = gridBatch.Text

>         gridBatch.Col = mnTEMP_RATE
>         If gridBatch.Text = "" Then
>             nRate = 0
>         Else
>             nGridRate = CCur(gridBatch.Text)
>             If nGridRate <> mnEmployeeRate Then
>                 nRate = nGridRate
>             Else
>                 nRate = mnEmployeeRate
>             End If
>         End If

>         gridBatch.Col = mnTEMP_DEPT
>         sGridDepartment = gridBatch.Text
>         If sGridDepartment <> "" Then
>             sDepartment = sGridDepartment
>         Else
>             sDepartment = ""
>         End If

>         gridBatch.Col = mnH_E_TYPE_DESC
>         sDescription = gridBatch.Text

>         gsSQL = "INSERT INTO

Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript

- Show quoted text -

Quote:
> ion,File_No) "
>         gsSQL = gsSQL + "VALUES('" + CStr(gnUserID) + "'," +
> sSQLString(msCurrentBatch) + "," + sSQLString(sCompanyCode) + ","
>         gsSQL = gsSQL + sSQLString(sCode) + "," + CStr(nAmount) + "," +
> sSQLString(sType) + "," + CStr(nRate) + ","
>         gsSQL = gsSQL + sSQLString(sDepartment) + "," +
> sSQLString(sDescription) + "," + sSQLString(msEmployeeFileNumber) + ")"

>         ' VB Data   gdbJFLTime.Execute gsSQL

>         Set gcn = New ADODB.Connection
>         gcn.Open (gsFoxProConnectionString)
>         gcn.Execute gsSQL
>         gcn.Close

>     Next nRow

> End Sub

****************************************************************************
Quote:
> **************************************************************

> I'm not sure how much this helps, because of all those variables that
build
> the query.  On that note, here's how the query looks when I rip it out of
> the immediate window right before it's executed:

****************************************************************************
Quote:
> **********
> INSERT INTO

Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript
Quote:
> ion,File_No) VALUES('1','000950','H6U','E',55,'H',34,NULL,NULL,'101727')

****************************************************************************

- Show quoted text -

Quote:
> *********
> It looks like a normal insert statement to me.  As I mentioned, I
> systematically removed each field/value pair, starting with the last (in
the
> command window), and still kept getting the error.

> Any help you can give me would be much appreciated.



> > Hi Curt,

> > It would really help if you posted the code you are running. Otherwise
we
> > can't begin to help, except to guess -

> > FoxPro True and False are written .T. and .F. (not True/False and not
> 0/-1)
> > FoxPro dates are written in curly braces: {03/17/2003}
> > FoxPro DateTime values are also in curly braces: {03/17/2003 20:56:29}

> > --
> > Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> > http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



> > > I'm trying to write a new record to a FoxPro table from VB.  My SQL
has
> > > the
> > > usual INSERT syntax, and worked previously with an Access table.  I
got
> > > the
> > > following error:

> > > Run-time error '-2147217913 (80040e07)':

> > > [Microsoft][ODBC Visual FoxPro Driver]Data Type Mismatch.

> > > I thought maybe the SQL statement was messed up, so I copied it from
the
> > > immediate window and ran it from the command window in FoxPro 7.0.  I
> kept
> > > getting the "Data Type Mismatch" error.  I removed each insert
variable
> > > and
> > > its value one at a time until there was only one left.  I STILL got
the
> > > error.

> > > I have a feeling this has something to do with how the FoxPro engine
> > > handles
> > > SQL.  Does anyone have any ideas?



Sat, 03 Sep 2005 18:06:00 GMT  
 Writing Record To FoxPro from VB
Curt:

As of yesterday, you were using ADO. Now today you're using SQL. Why dont
you try the ADO AddNew method. It will give you more control. Fox is very
obstinate in using SQL, especially if you're trying to write it from
"outside" of fox.

Also read up on variable types in fox and vb and see the differences.

JL


Quote:
> I'm trying to write a new record to a FoxPro table from VB.  My SQL has
the
> usual INSERT syntax, and worked previously with an Access table.  I got
the
> following error:

> Run-time error '-2147217913 (80040e07)':

> [Microsoft][ODBC Visual FoxPro Driver]Data Type Mismatch.

> I thought maybe the SQL statement was messed up, so I copied it from the
> immediate window and ran it from the command window in FoxPro 7.0.  I kept
> getting the "Data Type Mismatch" error.  I removed each insert variable
and
> its value one at a time until there was only one left.  I STILL got the
> error.

> I have a feeling this has something to do with how the FoxPro engine
handles
> SQL.  Does anyone have any ideas?



Sat, 03 Sep 2005 20:41:26 GMT  
 Writing Record To FoxPro from VB
Hi Curt,

If the advice posted here doesn't work for you, maybe you can post the
actual gsSQL string as it looks when it's sent off to the server, and we'll
catch something we haven't thought of yet.

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:

>         gsSQL = "INSERT INTO
> Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript
> ion,File_No) "
>         gsSQL = gsSQL + "VALUES('" + CStr(gnUserID) + "'," +
> sSQLString(msCurrentBatch) + "," + sSQLString(sCompanyCode) + ","
>         gsSQL = gsSQL + sSQLString(sCode) + "," + CStr(nAmount) + "," +
> sSQLString(sType) + "," + CStr(nRate) + ","
>         gsSQL = gsSQL + sSQLString(sDepartment) + "," +
> sSQLString(sDescription) + "," + sSQLString(msEmployeeFileNumber) + ")"



Sat, 03 Sep 2005 22:36:50 GMT  
 Writing Record To FoxPro from VB
Cindy,

My last post has that at that bottom.  I'll try some of the suggestions made
by other members and see what happens.

Curt


Quote:
> Hi Curt,

> If the advice posted here doesn't work for you, maybe you can post the
> actual gsSQL string as it looks when it's sent off to the server, and
we'll
> catch something we haven't thought of yet.

> --
> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



> >         gsSQL = "INSERT INTO

Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript

- Show quoted text -

Quote:
> > ion,File_No) "
> >         gsSQL = gsSQL + "VALUES('" + CStr(gnUserID) + "'," +
> > sSQLString(msCurrentBatch) + "," + sSQLString(sCompanyCode) + ","
> >         gsSQL = gsSQL + sSQLString(sCode) + "," + CStr(nAmount) + "," +
> > sSQLString(sType) + "," + CStr(nRate) + ","
> >         gsSQL = gsSQL + sSQLString(sDepartment) + "," +
> > sSQLString(sDescription) + "," + sSQLString(msEmployeeFileNumber) + ")"



Sun, 04 Sep 2005 01:36:18 GMT  
 Writing Record To FoxPro from VB
Oops!  The answer was right under my nose.  The first field was an int, but
I was making it a string.  I hope I didn't waste everyone's time.  Thanks
for your help!


Quote:
> Cindy,

> My last post has that at that bottom.  I'll try some of the suggestions
made
> by other members and see what happens.

> Curt



> > Hi Curt,

> > If the advice posted here doesn't work for you, maybe you can post the
> > actual gsSQL string as it looks when it's sent off to the server, and
> we'll
> > catch something we haven't thought of yet.

> > --
> > Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> > http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



> > >         gsSQL = "INSERT INTO

Batch(User_ID,Batch_Id,Co_Code,Code,Amount,Type,Temp_Rate,Temp_Dept,Descript

- Show quoted text -

Quote:
> > > ion,File_No) "
> > >         gsSQL = gsSQL + "VALUES('" + CStr(gnUserID) + "'," +
> > > sSQLString(msCurrentBatch) + "," + sSQLString(sCompanyCode) + ","
> > >         gsSQL = gsSQL + sSQLString(sCode) + "," + CStr(nAmount) + ","
+
> > > sSQLString(sType) + "," + CStr(nRate) + ","
> > >         gsSQL = gsSQL + sSQLString(sDepartment) + "," +
> > > sSQLString(sDescription) + "," + sSQLString(msEmployeeFileNumber) +
")"



Sun, 04 Sep 2005 01:45:43 GMT  
 Writing Record To FoxPro from VB
Sorry I missed that.

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> Cindy,

> My last post has that at that bottom.  I'll try some of the suggestions
> made
> by other members and see what happens.



Sun, 04 Sep 2005 06:02:21 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Read/Write FoxPro 2.6 in VB .NET 2003

2. Oops, I should write FOXPRO instead of VB. Sorry

3. Write current date everytime a record is written?

4. FoxPro ODBC insert incomplete record written

5. Writing new records to a cursor

6. Help: Error 1502, Cannot write to the record because it is in use

7. Determining if a record exist before writing to table

8. Writing records from unbound forms

9. 1502 Cannot write to the record because it is in use

10. Fox 2.5 DOS - writing memos to wrong record

11. Writing new records to a cursor

12. Cannot write to the record because it is in use

 

 
Powered by phpBB® Forum Software