[RESOLVED] [2005] Problem inserting data to database
Hi!
I'm trying to add some data from textboxes to my database. I had it working in another application using the same code just the other day. But no, niether the new app or the old is functioning. All I get is my error message, and the truth told, I'm not very good at error searching through my code.
Any help?
This is in the top of the main form.
Code:
Imports System.Text
Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class StartForm
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String
And here is the sub that is my trouble.
Code:
Private Sub AddMovieBtn_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddMovieBtn.Click
Dim addresponce As Integer
addresponce = MsgBox(" Do you want to add the following object? " + "Movie Title: " + Title.Text, MsgBoxStyle.YesNo)
If addresponce = vbYes Then
If Title.Text = "" And Actor.Text = "" And Descrip.Text = "" And Length.Text = "" Then
MsgBox("You have to fill at least 1 field!")
Else
Try
[B] cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=movies.mdb;")
cn.Open()
str = "INSERT INTO moviedata VALUES('" & Title.Text & "' , '" & Length.Text & "' , '" & Actor.Text & "' , '" & Descrip.Text & "' , '" & Rating.SelectedItem & "' , '" & Genre.SelectedItem & " ' , '" & FileLocation.Text & "')"[/B]
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn)
icount = cmd.ExecuteNonQuery
'MsgBox(icount)
'displays number of records inserted
MsgBox("The Movie Has Been Added To The Database")
'Clear all data
Title.Text = ""
Length.Text = ""
Actor.Text = ""
Descrip.Text = ""
Rating.Text = ""
Genre.Text = ""
FileLocation.Text = ""
Catch
MsgBox("An error was reported! Please try again!")
End Try
cn.Close()
End If
Else
If addresponce = vbNo Then
Me.Select()
End If
End If
Me.MoviedataTableAdapter.Fill(Me.MoviesDataSet.moviedata)
End Sub
Just one more quick question. When I use ComboBoxes, and I want the value I've choosen in it to go to the database. What should I select? Text, SelectedValue or SelectedItem?
Thanks for the help!
Re: [2005] Problem inserting data to database
Quote:
Originally Posted by maco
All I get is my error message
And what error message is that? If the IDE gives you an error message and you want help with the error then always pass the error message on to us.
Re: [2005] Problem inserting data to database
All I get is my own error message, the one that is catched.
vb Code:
Catch
MsgBox("An error was reported! Please try again!")
End Try
And I dunno what to do more, I'm trying to learn but error searching isn't my stron side.
Re: [2005] Problem inserting data to database
Print the error message to have more information about your error
vb Code:
catch ex as Exception
MessageBox.Show(ex.ToString())
End Try
Re: [2005] Problem inserting data to database
http://img80.imageshack.us/img80/8659/errrcx1.th.jpg
I Guess that means there is something with the number of cells in my database. Does the SQL query count the first cell which is a counter to give the record an ID?
Re: [2005] Problem inserting data to database
While what you have used is legal syntax when inserting a value to EVERY column in the table, e.g.
Code:
INSERT INTO MyTable VALUES (Value1, Value2, Value3)
it is certainly not legal if you are omitting values for one or more columns. In that case how would the system know what values are for what columns.
Even if you are specifying a value for every column that is still considered bad practice. You should consider ALWAYS specifying the columns explicitly regardless, e.g.
Code:
INSERT INTO MyTable (Column1, Column2, Column3) VALUES (Value1, Value2, Value3)
Re: [2005] Problem inserting data to database
vb Code:
Private cn as OleDbConnection = New OleDbConnection
Private ds as DataSet = New DataSet()
Private da as OleDbDataAdapter
Private Sub Form_Load(sender as Object, e as EventArgs)
'Load the table movieData
dim selectQuery as String = "SELECT ID, Name FROM movieData"
dim insertQuery as String = "INSERT INTO movieData (ID, Name) VALUES(@ID, @Name)"
dim SelectCmd as OleDbCommand = New OleDbCommand(selectQuery, cn)
dim InsertCmd as OleDbCommand = New OleDbCommand(insertQuery, cn)
InsertCmd.Parameters.Add("@ID", OleDbType.Integer, 'something', Me.TextBoxID.text)
InsertCmd.Parameters.Add("@Name", OleDbType.Varchar, 50, Me.TextBoxName.text)
da.SelectCommand = SelectCmd
da.InsertCommand = InsertCmd
Try
cn.Open
da.Fill(ds, "movieData")
cn.Close
Catch ex as Execption
MessageBox.Show(ex.ToStrong())
End Try
End Sub
Private Sub ButtonAddMovie_Click(sender, e)
'CorrectInput = Check if the the content of the TextBox are correct
If CorrectInput Then
dim result as DialogResult = MessageBox.Show("Add Movie?", MessageBox.vbYesNo)
If result = MessageBox.vbYes Then
dim row as ds.Tables("movieData").NewRow()
row("ID") = Me.TextBoxID.Text
row("Name") = Me.TextBoxName.Text
ds.Tables("movieData").Rows.Add(row)
End If
End If
End Sub
Private Sub buttonUpdateDatabase_Click(sender, e)
try
cn.Open()
ds.Update()
cn.Close()
MessageBox.Show("Update successfull")
Catch ex as Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Re: [2005] Problem inserting data to database
Thanks guys! I got it working now, defining what columns to insert to worked out fine!