[RESOLVED] [2005] Problem inserting data to database

[maco]

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.

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.

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!

[jmcilhinney]

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.

[maco]

All I get is my own error message, the one that is catched.

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.

[talkro]

Print the error message to have more information about your error

catch ex as Exception
     MessageBox.Show(ex.ToString())
End Try

[maco]

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?

[jmcilhinney]

While what you have used is legal syntax when inserting a value to EVERY column in the table, e.g.

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.

INSERT INTO MyTable (Column1, Column2, Column3) VALUES (Value1, Value2, Value3)

[talkro]

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

[maco]

Thanks guys! I got it working now, defining what columns to insert to worked out fine!