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...

 

[mcfly]

when we tell SQL we need it to store variables how does it know which are dates, strings, int' etc...?

i am new to vb.net but even newer to SQL

 

[JohnH]

It is set with SqlDbType property of the SqlParameter object, when you use AddWithValue it is automatically set based on the .Net type. This info is transferred to db with the other parameter information. Without parameters an unsafe call is made, db parses the command text and tries to match the type for target column.

 

[mcfly]

sorry it is now saying it's actually this line that is now the problem

command.ExecuteNonQuery()

...this is confusing?!?!

 

[mcfly]

says...


OleDbException : Data type mismatch in criteria expression

 

[mcfly]

this appears to work, however the data has been entered into the database but now wont display on a form some else in my program - very strange...will look into it further adn possible get back if i get stuck..thanks for all the help any way

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
                Dim command As New OleDbCommand(sql, oledbCnn)

                command.Parameters.AddWithValue("@CalendarID", OleDbType.VarChar).Value = txtCalendarID.Text
                command.Parameters.Add("@FirstDate", OleDbType.Date).Value = DateTimePicker1.Value
                command.Parameters.Add("@SecondDate", OleDbType.Date).Value = DateTimePicker1.Value
                command.Parameters.AddWithValue("@notes", OleDbType.VarChar).Value = txtEventNotes.Text
                command.ExecuteReader()

                command.Parameters.Clear()

                '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

 

[JohnH]

command.Parameters.AddWithValue("@CalendarID", OleDbType.VarChar).Value = txtCalendarID.Text

AddWithValue(name,value) Try this:

Dim c As New OleDb.OleDbCommand
Dim p As OleDb.OleDbParameter = c.Parameters.AddWithValue("blah", OleDb.OleDbType.VarChar)
Debug.WriteLine(p.OleDbType)
p.Value = "a string"
Debug.WriteLine(p.OleDbType)