Updating FoxPro Memo field

19 Jan 2006 8:30 PM

jweesies

I'm using the oledbvfp.1 driver and trying to update a FoxPro table with data
in a asp.net application. The update works fine unless the data contains a
question mark, which produces a "no value given for one or more required
parameters" error. Any help would be appreciated.

20 Jan 2006 10:04 PM

Cindy Winegarden

Hi JWeesies,

Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .

I'm able to update with text that contains question marks using a
parameterized command:

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1

    Sub Main()

        Try

            Dim cn1 As New OleDbConnection( _
                "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
            cn1.Open()

            Dim cmd1 As New OleDbCommand( _
                "Create Table TestMemo (Field1 M)", cn1)
            cmd1.ExecuteNonQuery()

            Dim cmd2 As New OleDbCommand()
            cmd2.Connection = cn1
            cmd2.CommandType = CommandType.Text
            cmd2.CommandText = "Insert Into TestMemo (Field1) Values (?)"

            Dim p1 As New OleDbParameter()
            cmd2.Parameters.Add(p1)
            cmd2.Parameters(0).Value = "Hello? World?"
            cmd2.ExecuteNonQuery()

            Dim da1 As New OleDbDataAdapter( _
                "Select * From TestMemo", cn1)
            Dim ds1 As New DataSet()
            da1.Fill(ds1)
            Console.WriteLine(ds1.Tables(0).Rows(0).Item(0).ToString())
            Console.ReadLine()

        Catch e As Exception
            MsgBox(e.ToString())
        End Try

    End Sub

End Module

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegar***@msn.com www.cindywinegarden.com

Show quote

"jweesies" <jwees***@discussions.microsoft.com> wrote in message
news:297CEDD1-8FC2-4CAB-A397-7F0CFA64A259@microsoft.com...
> I'm using the oledbvfp.1 driver and trying to update a FoxPro table with
> data
> in a asp.net application. The update works fine unless the data contains
> a
> question mark, which produces a "no value given for one or more required
> parameters" error. Any help would be appreciated.

23 Jan 2006 4:11 PM

jweesies

Thanks for you reply Cindy. Downloading the updated driver did not help. I
was able to figure out what the problem is. If I enclose the text string in
brackets, I get the error if the text contains a question mark.

sql = "UPDATE Table SET Fields = ([" & FormObj.Text & "]) "

If I use single quotes around the value, it works just fine, even if it
contains a question mark.

sql = "UPDATE Table SET Fields = ('" & Replace(FormObj.Text, "'", "''") &
"') "

I used the double brackets so I didn't have to replace quotes within the
text string, but its not a big deal to change my code. Thanks!

Show quote

"Cindy Winegarden" wrote:

> Hi JWeesies,
>
> Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
> downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
>
> I'm able to update with text that contains question marks using a
> parameterized command:
>
> Imports System
> Imports System.Data
> Imports System.Data.OleDb
>
> Module Module1
>
>     Sub Main()
>
>         Try
>
>             Dim cn1 As New OleDbConnection( _
>                 "Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
>             cn1.Open()
>
>             Dim cmd1 As New OleDbCommand( _
>                 "Create Table TestMemo (Field1 M)", cn1)
>             cmd1.ExecuteNonQuery()
>
>             Dim cmd2 As New OleDbCommand()
>             cmd2.Connection = cn1
>             cmd2.CommandType = CommandType.Text
>             cmd2.CommandText = "Insert Into TestMemo (Field1) Values (?)"
>
>             Dim p1 As New OleDbParameter()
>             cmd2.Parameters.Add(p1)
>             cmd2.Parameters(0).Value = "Hello? World?"
>             cmd2.ExecuteNonQuery()
>
>             Dim da1 As New OleDbDataAdapter( _
>                 "Select * From TestMemo", cn1)
>             Dim ds1 As New DataSet()
>             da1.Fill(ds1)
>             Console.WriteLine(ds1.Tables(0).Rows(0).Item(0).ToString())
>             Console.ReadLine()
>
>         Catch e As Exception
>             MsgBox(e.ToString())
>         End Try
>
>     End Sub
>
> End Module
>
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegar***@msn.com www.cindywinegarden.com
>
>
> "jweesies" <jwees***@discussions.microsoft.com> wrote in message
> news:297CEDD1-8FC2-4CAB-A397-7F0CFA64A259@microsoft.com...
> > I'm using the oledbvfp.1 driver and trying to update a FoxPro table with
> > data
> > in a asp.net application. The update works fine unless the data contains
> > a
> > question mark, which produces a "no value given for one or more required
> > parameters" error. Any help would be appreciated.
>
>
>