Liteswitch
asked on
Using VB.Net to insert rows into a FoxPro table
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
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
could you show us your connectionstring (is the connection open ?)
ASKER
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(sAqu
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(sAq
Dim dsAqua As New DataSet
dsAqua.Clear()
cbAqua = New OleDb.OleDbCommandBuilder(
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!
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(sAqu
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(sAq
Dim dsAqua As New DataSet
dsAqua.Clear()
cbAqua = New OleDb.OleDbCommandBuilder(
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!
ASKER
Oh yes, and the connection string never gets closed at the mo, so I know that isn't an issue.
I'm just guessing here but could you add
AquaCmd.CommandType = Text
AquaCmd.CommandType = Text
ASKER
No, that didn't work. I got integer conversion errors. Cheers for the attempt though!
???? integer conversion errors ? Where did you use that command
ASKER
I put it in after the
AquaTrans = cnAqua.BeginTransaction
the error I got was
"An unhandled exception of type 'System.InvalidCastExcepti
Additional information: Cast from string "Aqua Course Creator" to type 'Integer' is not valid."
AquaTrans = cnAqua.BeginTransaction
the error I got was
"An unhandled exception of type 'System.InvalidCastExcepti
Additional information: Cast from string "Aqua Course Creator" to type 'Integer' is not valid."
ASKER
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
Bob
ASKER
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
Thanks again