Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using VB.Net to insert rows into a FoxPro table

Posted on 2005-04-19
14
Medium Priority
?
535 Views
Last Modified: 2008-01-09
Hi,

I'm writing an application that needs to amend some FoxPro tables.  So far, I can conenct to the database and view data no problems, but I get stuck when trying to use the INSERT command.

Here's what I have so far:

Dim AquaCmd As New OleDbCommand
Dim AquaTrans As OleDbTransaction
AquaTrans = cnAqua.BeginTransaction
AquaCmd.Connection = cnAqua
AquaCmd.Transaction = AquaTrans
AquaCmd.CommandText = "INSERT INTO cseadd (cadsid, csekey, txt1) VALUES ('19000', '21000', '001')"
AquaCmd.ExecuteNonQuery()
AquaTrans.Commit

The cseadd table is present, and the values 19000, 21000 and 001 are hard coded into the code so I don't have variables in things to begin with. I've tried it with the apostrophes in the VALUES section, without, and anything else I can come up with, still no joy.

This always fails at the ExecuteNonQuery() part, saying I get an "unhandled exception error"

Does anyone have any idea as to where I might be going wrong here?

Cheers,

Liteswitch
0
Comment
Question by:Liteswitch
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13813739
could you show us your connectionstring (is the connection open ?)
0
 

Author Comment

by:Liteswitch
ID: 13813785
This sub uses an openfiledialog box to let a user browse for the DBC file for FoxPro tables

Public Sub OpenAqua()
        If ofdAqua.ShowDialog = DialogResult.OK Then
            sAquaPath = ofdAqua.FileName
            sAquaConn = "Provider=vfpoledb.1;Data Source=" & sAquaPath & ";Collating Sequence=Machine"
            cnAqua = New OleDb.OleDbConnection(sAquaConn)
            cnAqua.ConnectionString = sAquaConn
            cnAqua.Open()

            GetAquaData()
        End If

    End Sub

This section now creates the SQL query for getting the data out of the database and into a datatable

   Public Sub GetAquaData()
        Dim sAquaSQL As String = "select * from course where cseinyr = '041' and csecode = '" & txtAccessCCode.Text & "'"
        Dim daAqua As New OleDb.OleDbDataAdapter(sAquaSQL, cnAqua)
        Dim dsAqua As New DataSet
        dsAqua.Clear()
        cbAqua = New OleDb.OleDbCommandBuilder(daAqua)
        daAqua.Fill(dsAqua, "041")

        dt_Aqua.Clear()
        daAqua.Fill(dt_Aqua)

        ShowAquaInfo()



    End Sub


All this works fine, ie the connection works, and I can browse through all returned recordsets, I just can't use the INSERT command properly..

I hope this helps things somewhat.

Ta!
0
 

Author Comment

by:Liteswitch
ID: 13813790
Oh yes, and the connection string never gets closed at the mo, so I know that isn't an issue.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13813853
I'm just guessing here but could you add

        AquaCmd.CommandType = Text
0
 

Author Comment

by:Liteswitch
ID: 13813874
No, that didn't work. I got integer conversion errors.  Cheers for the attempt though!
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13813885
???? integer conversion errors ? Where did you use that command
0
 

Author Comment

by:Liteswitch
ID: 13814027
I put it in after the

AquaTrans = cnAqua.BeginTransaction

the error I got was

"An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from string "Aqua Course Creator" to type 'Integer' is not valid."
0
 

Author Comment

by:Liteswitch
ID: 13814112
Seems like I didn't set the commandType properly.

AquaCmd.CommandType = CommandType.Text

is how to set the command type, but still ahd no joy.  Didn't get any errors on that specifically, but I do get the same error as I originally did before adding this line of code, ie the Unhandled Error Exception..
0
 
LVL 22

Accepted Solution

by:
Mohammed Nasman earned 500 total points
ID: 13814389
try using ODBCConnection instead of OleDBConnection
0
 

Author Comment

by:Liteswitch
ID: 13814463
Yes, ODBC worked instead of OleDb..  I'm puzzled as to why that would be the case!!

Perhaps it's because the database I'm trying to edit was written in FxPro 6, so the OleDb driver I had may not have been backwards compatible.  At least now it works, thanks!
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 13814905
For this line:
INSERT INTO cseadd (cadsid, csekey, txt1) VALUES ('19000', '21000', '001')"

Are cseadd.cadsid and csekey numeric or integer?  My guess is integer, so the line would be:

INSERT INTO cseadd (cadsid, csekey, txt1) VALUES (19000, 21000, '001')

Bob
0
 

Author Comment

by:Liteswitch
ID: 13819947
Cheers, that works to a point, but I actually wanted to use variables, and numeric ones at that.

I would've assumed this

"INSERT INTO cseadd (cadsid) VALUES ('" & numVariable & "')"

But that fails, because I suspect of the fact that the data I want to insert is numeric,so how would i put this into the VALUES section as a numeric variable called numVariable?

Cheers,

Liteswitch
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 13823425
The single quote (') is used as a delimiter for string text.  If you have numeric data, then leave off the string delimiter, or you'll get an error.

Bob
0
 

Author Comment

by:Liteswitch
ID: 13869219
Sorry for taking so long to answer this, I've been off work ill for a few days.  Thanks all for the help, and I've got it solved, apart from a few issues abaout data types, which I'd best post in another question.

Thanks again
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month12 days, 13 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question