inserting new record

[mcfly]

hi there,

any one know why this code wont work for inserting a new record???

 Private Sub save_calendar_details()

        If txtPersonID.Text = "" Then

            Dim connetionString As String
            Dim oledbCnn As OleDbConnection
            Dim oledbCmd As OleDbCommand
            Dim sql As String

            'todo set password and user for database
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=H:\My Pictures\hotel_db.mdb;User Id=admin;Password=;"
            sql = "INSERT INTO tbl_calendar values(" & (txtCalendarID.Text) & ",'" & DateTimePicker1.Value & "','" & DateTimePicker1.Value & "')"

            oledbCnn = New OleDbConnection(connetionString)

            Try
                'open and prepare connection
                oledbCnn.Open()
                oledbCmd = New OleDbCommand(sql, oledbCnn)
                Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()


                oledbReader.Close()
                oledbCmd.Dispose()
                oledbCnn.Close()
            Catch ex As Exception
                'get error message
                MsgBox(ex.GetType.Name & " : " & ex.Message)
            End Try
        End If

    End Sub

as you can tell i am fairly new to this so help greatfully appricated...

 

[Hack]

What is the error message?

 

[mcfly]

hi there,

no error message just does nothing! which is a little confusing, am i calling/executing the SQL string correctly?!?

 

[Hack]

If you weren't calling it correctly, there would be an error.

I didn't see this at first. You aren't executing the INSERT.

Try: oledbCnn.ExecuteNonQuery()

 

[mcfly]

have tried entering the line below, but now it says

'ExecuteNonQuery' is not a member of 'System.Data.OleDb.OleDbConnection'.

Any ideas what this means?...

    Private Sub save_calendar_details()

        If txtPersonID.Text = "" Then

            Dim connetionString As String
            Dim oledbCnn As OleDbConnection
            Dim oledbCmd As OleDbCommand
            Dim sql As String

            'todo set password and user for database
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=H:\My Pictures\hotel_db.mdb;User Id=admin;Password=;"
            sql = "INSERT INTO tbl_calendar values(" & (txtCalendarID.Text) & ",'" & DateTimePicker1.Value & "','" & DateTimePicker1.value & "')"

            oledbCnn = New OleDbConnection(connetionString)

            Try
                'open and prepare connection
                oledbCnn.Open()
                oledbCmd = New OleDbCommand(sql, oledbCnn)
                Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

                oledbCnn.ExecuteNonQuery()

                oledbReader.Close()
                oledbCmd.Dispose()
                oledbCnn.Close()
            Catch ex As Exception
                'get error message
                MsgBox(ex.GetType.Name & " : " & ex.Message)
            End Try
        End If

    End Sub

 

[Hack]

That was my mistake...sorry. Try this

Dim command As New OleDbCommand(sql, oledbCnn)
command.ExecuteNonQuery()

 

[mcfly]

please no need to appologise you are doing me the favor, i now have the error message

OleDBException: No value given for one or more required parameters

I think SQL string is incorrect - I have no idea about SQL but would I better trying to insert variables rather than getting the text directly from the text box.

Also how do I know which column to insert which item of data into???...

 

[Hack]

Try this

sql = "INSERT INTO tbl_calendar (field1, field2, field3) " 'list the fields you will be putting data into
sql = sql & "values(@CalendarID, @FirstDate, @SecondDate) "
Dim command As New OleDbCommand(sql, oledbCnn)
command.Parameters.AddWithValue("@CalendarID", txtCalendarID.Text)
command.Parameters.AddWithValue("@FirstDate", DateTimePicker1.Value)
command.Parameters.AddWithValue("@SecondDate", DateTimePicker1.Value)
command.ExecuteNonQuery()

NOTE: I do not know what the names of your fields are, so you will need to change field1, field2, and field3 to whatever your table field names are.

 

[mcfly]

sorry for being a bit slow but i am just going around in circles...

Private Sub save_calendar_details()

        If txtPersonID.Text = "" Then

            Dim connetionString As String
            Dim oledbCnn As OleDbConnection
            Dim oledbCmd As OleDbCommand
            Dim sql As String

            sql = "INSERT INTO tbl_calendar (cal_date_id, date_from, date_to, notes) "

            sql = sql & "values(@CalendarID, @FirstDate, @SecondDate, @notes) "

            'todo set password and user for database
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=H:\My Pictures\hotel_db.mdb;User Id=admin;Password=;"

            oledbCnn = New OleDbConnection(connetionString)

            Try
                oledbCnn.Open()
                'open and prepare connection
                oledbCmd = New OleDbCommand(sql, oledbCnn)
                Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()


                Dim command As New OleDbCommand(sql, oledbCnn)

                command.Parameters.AddWithValue("@CalendarID", txtCalendarID.Text)

                command.Parameters.AddWithValue("@FirstDate", DateTimePicker1.Value)

                command.Parameters.AddWithValue("@SecondDate", DateTimePicker1.Value)

                command.Parameters.AddWithValue("@notes", txtEventNotes.Text)

                command.ExecuteNonQuery()

                oledbReader.Close()
                oledbCmd.Dispose()
                oledbCnn.Close()
            Catch ex As Exception
                'get error message
                MsgBox(ex.GetType.Name & " : " & ex.Message)
            End Try
        End If

    End Sub

I now get an error that says:

OleDbException : No value given for one or more required parameters

I think this may be something to do with the structure of my coding but I am not sure...they are the names of the columns

 

[Hack]

What line of code generates the error?

 

[mcfly]

just before this line...

Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

 

[Hack]

In other words, on this line

oledbCmd = New OleDbCommand(sql, oledbCnn)

??

If so, just comment that line out...you don't need it anymore

 

[mcfly]

error on this line now, 'data type mismatch' - i think this is because the value we are trying to pass is a 'date/time' in the database, how should i go about casting it before adding it too the database, ?...i would have thought it would just fit into the database as I am using the date time picker...but i'm not sure?

command.Parameters.AddWithValue("@FirstDate", DateTimePicker1.Value)

 

[JohnH]

'data type mismatch'
...
command.Parameters.AddWithValue("@CalendarID", txtCalendarID.Text)

I would guess 'id' is not a String type, perhaps Integer?

 

[mcfly]

id should be a string as it has a character at the front for example C14 then C15, C16 etc, it appears to pass the calendar id ok, its the date that has the problem? I'm not sure why, the column in the database is set as 'date/time so if we are using the date -time picker surly is shouldn't have a problem accepting the value we give it...?