-
Apr 16th, 2008, 07:02 AM
#1
Thread Starter
Member
Command text was not set for the command object.
Hello
I worked with this program couple of weeks ago and it worked properly but now I don't know why i get this error message when i try to insert new record. However it inserts and adds new record to the table it stops running and displays an error message"Command text was not set for the command object."
the arrow points to line started by >>>dataAdapter.Fill(studentTable)
this is not the completed code and in access database all field types are text.
any suggestion would be appreciated.
Thanks
global variables
Code:
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim studentTable As New DataTable
Dim moduleTable As New DataTable
Dim optionTable As New DataTable
Dim searchTable As New DataTable
Dim markTable As New DataTable
Dim resultTable As New DataTable
Dim sqlStr As String
Dim connStr As String
Dim recordCount As Integer
Code:
'INSERT
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim IdNumber As String
Dim studentName As String
Dim DOB As String
Dim year As String
Dim route As String
Dim optionTaken As String
Dim connection As New OleDb.OleDbConnection(connStr)
Dim insertOledbCommand As New OleDb.OleDbCommand
IdNumber = InputBox("Type in the ID Number")
studentName = InputBox("type in the Student Name")
DOB = InputBox("type in the Date of Birth")
year = InputBox("type in the Year")
route = InputBox("type in the Route type")
optionTaken = InputBox("type in the Option Taken")
sqlStr = "INSERT INTO tblStudentsDetails VALUES('" & IdNumber & "', '" & studentName & "', '" & DOB & "', '" & year & "', '" & route & "', '" & optionTaken & "')"
txtsqlCommand.Text = sqlStr
' setup for insertion
connection.Open()
insertOledbCommand.Connection = connection
insertOledbCommand.CommandText = sqlStr
' do the insertion
dataAdapter.InsertCommand = insertOledbCommand
dataAdapter.InsertCommand.ExecuteNonQuery()
connection.Close()
'display the insertion on datagrid
studentTable.Clear()
dataAdapter.Fill(studentTable)
DataGridView1.DataSource = studentTable
End Sub
-
Apr 16th, 2008, 07:26 AM
#2
Lively Member
Re: Command text was not set for the command object.
where is your dataadapter select statement defined?
-
Apr 16th, 2008, 09:29 AM
#3
Re: Command text was not set for the command object.
You have an insert command set for the data adapter, but you don't have a select command. Since you haven't told it how to fill it yet, it errors out when you try to do so.
(VB/C#) is clearly superior to (C#/VB) because it (has/doesn't have) <insert trivial difference here>.
-
Apr 17th, 2008, 04:05 AM
#4
Thread Starter
Member
Re: Command text was not set for the command object.
how can I do it? I don't know. Could you please explain more.
Code:
Private Sub displayByOption_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dataAdapter = New OleDb.OleDbDataAdapter(sqlStr, connStr)
End Sub
do you mean this line?
Thanks
Last edited by jojo13; Apr 17th, 2008 at 04:24 AM.
-
Apr 17th, 2008, 04:38 AM
#5
Lively Member
Re: Command text was not set for the command object.
yes, as long as you have defined sqlSTR and ConnStr.
Your sqlSTR must be the sql query which returns the data you want when you type dataAdapter.Fill(studentTable)
-
Apr 17th, 2008, 04:43 AM
#6
Thread Starter
Member
Re: Command text was not set for the command object.
I have this line but I still got this error message!!!
Oh I don't know why but the error message differ from the last one I did not make any changes,error is "data type mismatch in criteria expression" and points to line dataAdapter.InsertCommand.ExecuteNonQuery()
any ideas???
Last edited by jojo13; Apr 17th, 2008 at 04:46 AM.
-
Apr 17th, 2008, 04:52 AM
#7
Thread Starter
Member
Re: Command text was not set for the command object.
oh my god I now have the same error for my Delete record codes!!!!
dataAdapter.InsertCommand.ExecuteNonQuery()
what is wrong with these codes??
Code:
'DELETE
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim connection As New OleDb.OleDbConnection(connStr)
Dim deleteOledbCommand As New OleDb.OleDbCommand
Dim deleteRows As Integer
Dim deleteId As String
deleteId = InputBox("type in the ID Number to delete")
sqlStr = "DELETE FROM tblStudentsDetails WHERE IDNumber = " _
& " '" & deleteId & " ' "
txtsqlCommand.Text = sqlStr
connection.Open()
' make a sql delete command
deleteOledbCommand.Connection = connection
deleteOledbCommand.CommandText = sqlStr
' activate the deletion
dataAdapter.DeleteCommand = deleteOledbCommand
deleteRows = dataAdapter.DeleteCommand.ExecuteNonQuery
If deleteRows = 0 Then
MessageBox.Show("Deletion Not Done!! problems . . .")
Else
MessageBox.Show("Delection Successfull!!")
sqlStr = "SELECT * FROM tblStudentsDetails"
txtsqlCommand.Text = sqlStr
dataAdapter.SelectCommand.CommandText = sqlStr
studentTable.Clear()
dataAdapter.Fill(studentTable)
End If
End Sub
I really need help for it
-
Apr 17th, 2008, 05:17 AM
#8
Lively Member
Re: Command text was not set for the command object.
to use a dataadapter you need to define the select command first. try
Code:
'DELETE
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim connection As New OleDb.OleDbConnection(connStr)
Dim deleteOledbCommand As New OleDb.OleDbCommand
Dim deleteRows As Integer
Dim deleteId As String
'Assign select commad here
dataAdapter.SelectCommand.CommandText = "SELECT * FROM tblStudentsDetails"
deleteId = InputBox("type in the ID Number to delete")
sqlStr = "DELETE FROM tblStudentsDetails WHERE IDNumber = " _
& " '" & deleteId & " ' "
txtsqlCommand.Text = sqlStr
connection.Open()
' make a sql delete command
deleteOledbCommand.Connection = connection
deleteOledbCommand.CommandText = sqlStr
' activate the deletion
dataAdapter.DeleteCommand = deleteOledbCommand
deleteRows = dataAdapter.DeleteCommand.ExecuteNonQuery
If deleteRows = 0 Then
MessageBox.Show("Deletion Not Done!! problems . . .")
Else
MessageBox.Show("Delection Successfull!!")
sqlStr = "SELECT * FROM tblStudentsDetails"
txtsqlCommand.Text = sqlStr
studentTable.Clear()
dataAdapter.Fill(studentTable)
End If
End Sub
-
Nov 14th, 2013, 11:11 PM
#9
Registered User
Re: Command text was not set for the command object.
Mr. Jrogers can u help me too.. im having the same problem but it is data set..
-
Apr 18th, 2008, 08:06 AM
#10
Thread Starter
Member
Re: Command text was not set for the command object.
Thanks jroger
but I have a problem and that is the program works on saturdays and doesn't work on sundays. you know what I mean sometimes work properly even with the last code but sometime shows that error message and now it works but does not delete it it says deletion successful but doesn't reload the table. and I don't know what is the relation between insert and delete they both have the same problem.
any suggestions?
Thanks
-
Apr 18th, 2008, 08:36 AM
#11
Lively Member
Re: Command text was not set for the command object.
you have dataadapter and odataadapter what is the difference.. can you post the complete code and I will take a look
-
Apr 18th, 2008, 08:51 AM
#12
Thread Starter
Member
Re: Command text was not set for the command object.
here is the code for form1, I mean main form, in 2 posts.I have different form for some action and they work properly. at the moment the problem is insert and delete.they borh work but for insert it display the run time error"Command text was not set for the command object." and for deelete it does say deletion successful but doesn't reload the table.
thanks for your support
Code:
Public Class Form1
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim studentTable As New DataTable
Dim moduleTable As New DataTable
Dim optionTable As New DataTable
Dim searchTable As New DataTable
Dim markTable As New DataTable
Dim resultTable As New DataTable
Dim sqlStr As String
Dim connStr As String
Dim recordCount As Integer
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Form2.Visible = False
chart.Visible = False
connStr = "PROVIDER = Microsoft.jet.OLEDB.4.0;" _
& "Data Source = dataBase.mdb"
dataAdapter = New OleDb.OleDbDataAdapter(sqlStr, connStr)
End Sub
'Display Table and options
Private Sub btnDisplayGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayGrid.Click
Form3.Show()
End Sub
'COUNTER
Private Sub btnRecordCount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRecordCount.Click
Dim recCount As Integer
studentTable.Clear()
sqlStr = "SELECT * FROM tblStudentsDetails"
txtSqlCommand.Text = sqlStr
dataAdapter.SelectCommand.CommandText = sqlStr
recCount = dataAdapter.Fill(studentTable)
MessageBox.Show("There are " & recCount & " records")
End Sub
'UPDATE
Private Sub btnUpdateGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateGrid.Click
Dim changes As Integer
Dim dataAdapter1 As New OleDb.OleDbDataAdapter(sqlStr, connStr)
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter1)
changes = dataAdapter1.Update(studentTable)
dataAdapter1.Dispose()
If changes > 0 Then
MessageBox.Show(changes & "modification in database!!")
Else
MessageBox.Show("No changes!!")
End If
End Sub
'FIND
Private Sub btnFindStudent_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFindStudent.Click
Find.Show()
End Sub
'INSERT
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim IdNumber As String
Dim studentName As String
Dim DOB As String
Dim year As String
Dim route As String
Dim optionTaken As String
Dim connection As New OleDb.OleDbConnection(connStr)
Dim insertOledbCommand As New OleDb.OleDbCommand
IdNumber = InputBox("Type in the ID Number")
studentName = InputBox("type in the Student Name")
DOB = InputBox("type in the Date of Birth")
year = InputBox("type in the Year")
route = InputBox("type in the Route type")
optionTaken = InputBox("type in the Option Taken")
sqlStr = "INSERT INTO tblStudentsDetails VALUES('" & IdNumber & "', '" & studentName & "', '" & DOB & "', '" & year & "', '" & route & "', '" & optionTaken & "')"
txtsqlCommand.Text = sqlStr
' setup for insertion
connection.Open()
insertOledbCommand.Connection = connection
insertOledbCommand.CommandText = sqlStr
' do the insertion
dataAdapter.InsertCommand = insertOledbCommand
dataAdapter.InsertCommand.ExecuteNonQuery()
connection.Close()
'display the insertion on datagrid
studentTable.Clear()
dataAdapter.Fill(studentTable)
DataGridView1.DataSource = studentTable
End Sub
'DELETE
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim connection As New OleDb.OleDbConnection(connStr)
Dim deleteOledbCommand As New OleDb.OleDbCommand
Dim deleteRows As Integer
Dim deleteId As String
'Assign select commad here
dataAdapter.SelectCommand.CommandText = "SELECT * FROM tblStudentsDetails"
deleteId = InputBox("type in the ID Number to delete")
sqlStr = "DELETE FROM tblStudentsDetails WHERE IDNumber = " _
& " '" & deleteId & " ' "
txtsqlCommand.Text = sqlStr
connection.Open()
' make a sql delete command
deleteOledbCommand.Connection = connection
deleteOledbCommand.CommandText = sqlStr
' activate the deletion
dataAdapter.DeleteCommand = deleteOledbCommand
deleteRows = dataAdapter.DeleteCommand.ExecuteNonQuery
If deleteRows = 0 Then
MessageBox.Show("Deletion Not Done!! problems . . .")
Else
MessageBox.Show("Delection Successfull!!")
sqlStr = "SELECT * FROM tblStudentsDetails"
txtsqlCommand.Text = sqlStr
studentTable.Clear()
dataAdapter.Fill(studentTable)
End If
End Sub
'SORT BY OPTION
Private Sub btnDisplayByOption_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayByOption.Click
displayByOption.Show()
End Sub
'EXIT
Private Sub btnExitProgram_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExitProgram.Click
dataAdapter.Dispose()
End
End Sub
'MENU>VIEW
Private Sub StudentsDetailsTableToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StudentsDetailsTableToolStripMenuItem.Click
studentTable.Clear()
DataGridView1.Visible = True
sqlStr = "SELECT * FROM tblStudentsDetails"
txtSqlCommand.Text = sqlStr
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(studentTable)
dataAdapter.Dispose()
DataGridView1.DataSource = studentTable
End Sub
Private Sub DisplayBySpecToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisplayBySpecToolStripMenuItem.Click
Dim particularOption As String
Dim recCount As Integer
DataGridView2.DataSource = searchTable
particularOption = InputBox("type in the Option to search")
sqlStr = "SELECT * FROM tblStudentsDetails WHERE " _
& "OptionTaken = '" & particularOption & "' "
txtSqlCommand.Text = sqlStr
dataAdapter.SelectCommand.CommandText = sqlStr
searchTable.Clear()
recCount = dataAdapter.Fill(searchTable)
If recCount <> 0 Then
DataGridView2.Visible = True
Else
MessageBox.Show(" Option NOT found!!")
DataGridView2.Visible = False
End If
End Sub
Private Sub RecordCountToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RecordCountToolStripMenuItem.Click
Dim recCount As Integer
studentTable.Clear()
sqlStr = "SELECT * FROM tblStudentsDetails"
txtSqlCommand.Text = sqlStr
dataAdapter.SelectCommand.CommandText = sqlStr
recCount = dataAdapter.Fill(studentTable)
MessageBox.Show("There are " & recCount & " records")
End Sub
'MENU>SEARCH
Private Sub FindRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FindRecordToolStripMenuItem.Click
Dim findIDNumber As String
Dim recCount As Integer
DataGridView2.DataSource = searchTable
findIDNumber = InputBox("type in the ID Number to search")
sqlStr = "SELECT * FROM tblStudentsDetails WHERE " _
& "IDNumber = '" & findIDNumber & "' "
txtSqlCommand.Text = sqlStr
dataAdapter.SelectCommand.CommandText = sqlStr
searchTable.Clear()
recCount = dataAdapter.Fill(searchTable)
If recCount <> 0 Then
DataGridView2.Visible = True
Else
MessageBox.Show(" Record NOT found!!")
DataGridView2.Visible = False
End If
End Sub
-
Apr 18th, 2008, 08:52 AM
#13
Thread Starter
Member
Re: Command text was not set for the command object.
(codes continued)
Code:
'MENU>FILE
Private Sub InsertRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertRecordToolStripMenuItem.Click
Dim IdNumber As String
Dim studentName As String
Dim DOB As String
Dim year As String
Dim route As String
Dim optionTaken As String
Dim connection As New OleDb.OleDbConnection(connStr)
Dim insertOledbCommand As New OleDb.OleDbCommand
IdNumber = InputBox("Type in the ID Number")
studentName = InputBox("type in the Student Name")
DOB = InputBox("type in the Date of Birth")
year = InputBox("type in the Year")
route = InputBox("type in the Route type")
optionTaken = InputBox("type in the Option Taken")
sqlStr = "INSERT INTO tblStudentsDetails VALUES('" & IdNumber & "', '" & studentName & "', '" & DOB & "', '" & year & "', '" & route & "', '" & optionTaken & "')"
txtSqlCommand.Text = sqlStr
' setup for insertion
connection.Open()
insertOledbCommand.Connection = connection
insertOledbCommand.CommandText = sqlStr
' do the insertion
dataAdapter.InsertCommand = insertOledbCommand
dataAdapter.InsertCommand.ExecuteNonQuery()
connection.Close()
'display the insertion on datagrid
studentTable.Clear()
dataAdapter.Fill(studentTable)
DataGridView1.DataSource = studentTable
End Sub
Private Sub DeleteRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteRecordToolStripMenuItem.Click
Dim connection As New OleDb.OleDbConnection(connStr)
Dim deleteOledbCommand As New OleDb.OleDbCommand
Dim deleteRows As Integer
Dim deleteId As String
deleteId = InputBox("type in the ID Number to delete")
sqlStr = "DELETE FROM tblStudentsDetails WHERE IDNumber = " _
& " '" & deleteId & " ' "
txtSqlCommand.Text = sqlStr
connection.Open()
' make a sql delete command
deleteOledbCommand.Connection = connection
deleteOledbCommand.CommandText = sqlStr
' activate the deletion
dataAdapter.DeleteCommand = deleteOledbCommand
deleteRows = dataAdapter.DeleteCommand.ExecuteNonQuery
If deleteRows = 0 Then
MessageBox.Show("Deletion Not Done!! problems . . .")
Else
MessageBox.Show("Delection Successfull!!")
sqlStr = "SELECT * FROM tblStudentsDetails"
txtSqlCommand.Text = sqlStr
dataAdapter.SelectCommand.CommandText = sqlStr
studentTable.Clear()
dataAdapter.Fill(studentTable)
End If
End Sub
Private Sub UpdateGridToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateGridToolStripMenuItem.Click
Dim changes As Integer
Dim dataAdapter1 As New OleDb.OleDbDataAdapter(sqlStr, connStr)
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter1)
changes = dataAdapter1.Update(studentTable)
dataAdapter1.Dispose()
If changes > 0 Then
MessageBox.Show(changes & "modification in database!!")
Else
MessageBox.Show("No changes!!")
End If
End Sub
Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
dataAdapter.Dispose()
End
End Sub
Private Sub ModuleTableToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ModuleTableToolStripMenuItem.Click
moduleTable.Clear()
DataGridView1.Visible = True
sqlStr = "SELECT * FROM tblModuleTable"
txtSqlCommand.Text = sqlStr
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(moduleTable)
dataAdapter.Dispose()
DataGridView1.DataSource = moduleTable
End Sub
Private Sub OptionTableToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OptionTableToolStripMenuItem.Click
optionTable.Clear()
DataGridView1.Visible = True
sqlStr = "SELECT * FROM qry_Places"
txtSqlCommand.Text = sqlStr
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(optionTable)
dataAdapter.Dispose()
DataGridView1.DataSource = optionTable
End Sub
Private Sub btnDisplayChart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayChart.Click
chart.Show()
End Sub
Private Sub btnMark_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMark.Click
markTable.Clear()
DataGridView1.Visible = True
sqlStr = "SELECT * FROM qry_Mark"
txtSqlCommand.Text = sqlStr
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(markTable)
dataAdapter.Dispose()
DataGridView1.DataSource = markTable
End Sub
Private Sub btnResult_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnResult.Click
resultTable.Clear()
DataGridView1.Visible = True
sqlStr = "SELECT * FROM qry_Pass"
txtSqlCommand.Text = sqlStr
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapter.Fill(resultTable)
dataAdapter.Dispose()
DataGridView1.DataSource = resultTable
End Sub
End Class
-
Apr 18th, 2008, 09:26 AM
#14
Thread Starter
Member
Re: Command text was not set for the command object.
I add this line"dataAdapter.SelectCommand.CommandText = "SELECT * FROM tblStudentsDetails"" to the insert function and it works now,but deletion still has problem it doesn't reload the table
Thanks
-
Apr 18th, 2008, 09:34 AM
#15
Lively Member
Re: Command text was not set for the command object.
If I were you I would definitely have one dataadapter per datatable, and I would define them all to start with (including parameters) then when you want the data you can convcentrate on calling the required method of the relevant dataadapter.
-
Apr 18th, 2008, 09:36 AM
#16
Lively Member
Re: Command text was not set for the command object.
add
Code:
datagridview1.datasource = nothing
datagridview1.datasource = studenttable
-
Apr 18th, 2008, 09:53 AM
#17
Thread Starter
Member
Re: Command text was not set for the command object.
Thanks jrogers
It works now. thanks for your advise as well.
Cheers
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|