Results 1 to 7 of 7

Thread: [RESOLVED] Inserting combo box values?

  1. #1

    Thread Starter
    Registered User
    Join Date
    Dec 2014
    Posts
    7

    Resolved [RESOLVED] Inserting combo box values?

    Hello! I'm trying to get my system to insert combo box values into my access database. I always get this very long error whenever I try to click my 'add' button and I somehow get this feeling that it's because of my INSERT statement. This is my code(Sorry if it's all confusing, I'm just a beginner in VB). Any help will be greatly appreciated! Thank you
    Code:
    Imports System.Data.OleDb
    Public Class AdmMain
        Public connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Thesis\Thesis\Database1.accdb"
        Public conn As New OleDbConnection
    
        Sub fillcombo()
            strsql = " select yrgr from yearandgrade"
            Dim acscmd As New OleDb.OleDbCommand
            acscmd.CommandText = strsql
            acscmd.Connection = acsconn
            acsdr = acscmd.ExecuteReader
    
            While (acsdr.Read())
                cboyr.Items.Add(acsdr("yrgr"))
            End While
            acscmd.Dispose()
            acsdr.Close()
        End Sub
    
        Sub comb2()
            strsql = " select sections from sectio"
            Dim acscmd As New OleDb.OleDbCommand
            acscmd.CommandText = strsql
            acscmd.Connection = acsconn
            acsdr = acscmd.ExecuteReader
    
            While (acsdr.Read())
                cbosec.Items.Add(acsdr("sections"))
            End While
            acscmd.Dispose()
            acsdr.Close()
        End Sub
    
        Private Sub LinkLabel1_LinkClicked(sender As System.Object, e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
            If MessageBox.Show("Are you sure you want to logout?", "Logout", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                MessageBox.Show("You have successfully logged out of VCM's Library Information System!", "Logout Confirmed")
                Me.Close()
                LoginUser.Show()
            End If
        End Sub
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Me.txtFName.Text = ""
            Me.txtMName.Text = ""
            Me.txtLName.Text = ""
            Me.cboyr.Text = ""
            Me.cbosec.Text = ""
            Me.txtFName.Focus()
        End Sub
    
        Private Sub AdmMain_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Module1.connect()
            Me.fillcombo()
            Me.comb2()
            conn.ConnectionString = connstring
    
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                MsgBox("open")
            Else
                MsgBox("close")
    
            End If
        End Sub
    
    
        Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
            Try
                Dim SqlQuery As String = "INSERT INTO students (StudentID,FirstName,MiddleName,LastName,Yr,Section) VALUES ('" & TxtID.Text & "', '" & txtFName.Text & "', '" & txtMName.Text & "', '" & txtLName.Text & "', '" & cboyr.SelectedItem & "', '" & cbosec.SelectedItem & "')"
                Dim SqlCommand As New OleDbCommand
    
                With SqlCommand
                    .CommandText = SqlQuery
                    .Connection = conn
                    .ExecuteNonQuery()
                End With
                MsgBox("One record successfully added!")
            Catch ex As Exception
                MsgBox(ex.Tostring)
            End Try
            
        End Sub
    
    End Class
    This is the error I get whenever I click my add button.
    Name:  popopopop.png
Views: 344
Size:  46.2 KB

  2. #2
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: Inserting combo box values?

    Have you added a watch to the 'SqlQuery' and inspect the string before you call '.ExecuteNonQuery()' ? Post the value of that string, I bet something is off with it. Also, you might want to use parameters instead of concatenating text (for formatting and security purposes).

    http://www.codeguru.com/columns/vb/u...plications.htm

  3. #3
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Inserting combo box values?

    Are Yr and Section set up as varchar (or any char variation) in your database or are these some number type? It might be that you have a data-type mismatch... I'm esp. thinking of Yr which seems like it'd make sense as a DateTime and (unless MS has changed this in the last decade or so) DateTime values have to be surrounded by #'s instead of single-quotes. I'd also second that you should use parameters just for this purpose of trying to figure out the correct syntax for entering in a particular format for its corresponding data-type. Also you may consider something like "cboyr.SelectedItem.ToString" for a similar reason....

  4. #4

    Thread Starter
    Registered User
    Join Date
    Dec 2014
    Posts
    7

    Re: Inserting combo box values?

    Quote Originally Posted by jayinthe813 View Post
    Have you added a watch to the 'SqlQuery' and inspect the string before you call '.ExecuteNonQuery()' ? Post the value of that string, I bet something is off with it. Also, you might want to use parameters instead of concatenating text (for formatting and security purposes).

    http://www.codeguru.com/columns/vb/u...plications.htm
    How do you add a watch? I decided to change my code and add parameters but i still get a very long error.

    Here's the state of my new code

    Code:
    Imports System.Data.OleDb
    
    Public Class AdmMain
    
    
        Sub fillcombo()
            strsql = " select yrgr from yearandgrade"
            Dim acscmd As New OleDb.OleDbCommand
            acscmd.CommandText = strsql
            acscmd.Connection = acsconn
            acsdr = acscmd.ExecuteReader
    
            While (acsdr.Read())
                cboyr.Items.Add(acsdr("yrgr"))
            End While
            acscmd.Dispose()
            acsdr.Close()
        End Sub
    
        Sub comb2()
            strsql = " select sections from sectio"
            Dim acscmd As New OleDb.OleDbCommand
            acscmd.CommandText = strsql
            acscmd.Connection = acsconn
            acsdr = acscmd.ExecuteReader
    
            While (acsdr.Read())
                cbosec.Items.Add(acsdr("sections"))
            End While
            acscmd.Dispose()
            acsdr.Close()
        End Sub
    
        Private Sub LinkLabel1_LinkClicked(sender As System.Object, e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
            If MessageBox.Show("Are you sure you want to logout?", "Logout", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                MessageBox.Show("You have successfully logged out of VCM's Library Information System!", "Logout Confirmed")
                Me.Close()
                LoginUser.Show()
            End If
        End Sub
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Me.txtFName.Text = ""
            Me.txtMName.Text = ""
            Me.txtLName.Text = ""
            Me.cboyr.Text = ""
            Me.cbosec.Text = ""
            Me.txtFName.Focus()
        End Sub
    
        Private Sub AdmMain_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Module1.connect()
            Me.fillcombo()
            Me.comb2()
    
        End Sub
    
    
        Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
            Dim rbdtext As String = cboyr.SelectedItem.ToString
            Dim uno As String = cbosec.SelectedItem.ToString
            Try
                Using conn = New System.Data.OleDb.OleDbConnection()
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Thesis\Thesis\Database1.accdb"
                    conn.Open()
                    Dim sqlquery As String = "INSERT INTO students (StudentID, FirstName,MiddleName,LastName,Yr, Section) " & _
                                            "VALUES (@studid, @fname,@mname,@lname,@yr, @sec)"
                    Dim SqlCommand As New System.Data.OleDb.OleDbCommand
                    SqlCommand.Parameters.AddWithValue("@studid", TxtID.Text)
                    SqlCommand.Parameters.AddWithValue("@fname", txtFName.Text)
                    SqlCommand.Parameters.AddWithValue("@mname", txtMName.Text)
                    SqlCommand.Parameters.AddWithValue("@lname", txtLName.Text)
                    SqlCommand.Parameters.AddWithValue("@yr", rbdtext)
                    SqlCommand.Parameters.AddWithValue("@sec", uno)
                    SqlCommand.Connection = conn
    
                    Dim sqlRead As System.Data.OleDb.OleDbDataReader = SqlCommand.ExecuteReader()
    
                    MsgBox("One record successfully added!", "Added!")
    
                End Using
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            
        End Sub
    Attached Images Attached Images  

  5. #5

    Thread Starter
    Registered User
    Join Date
    Dec 2014
    Posts
    7

    Re: Inserting combo box values?

    Quote Originally Posted by Pyth007 View Post
    Are Yr and Section set up as varchar (or any char variation) in your database or are these some number type? It might be that you have a data-type mismatch... I'm esp. thinking of Yr which seems like it'd make sense as a DateTime and (unless MS has changed this in the last decade or so) DateTime values have to be surrounded by #'s instead of single-quotes. I'd also second that you should use parameters just for this purpose of trying to figure out the correct syntax for entering in a particular format for its corresponding data-type. Also you may consider something like "cboyr.SelectedItem.ToString" for a similar reason....
    No. Both are not set up as varchar. I also tried using parameters but still get errors.

  6. #6
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: Inserting combo box values?

    The latest error is easy to solve... When you get an error in VB.net, it is usually fairly long. But if you start at the top, see if that gives you a clue as to where the actual problem lies. In this case, the first error message is "System.Data.OleDb.OleDb.Exception(0x80040E0C): Command text was not set for the command object.". And in your code, you never do set the SQL query string to the .CommnadText of your command object. You just need to add that line as such:
    Code:
                    Dim SqlCommand As New System.Data.OleDb.OleDbCommand
                    SqlCommand.CommandText = SqlQuery
                    SqlCommand.Parameters.AddWithValue("@studid", TxtID.Text)
                    SqlCommand.Parameters.AddWithValue("@fname", txtFName.Text)
                    SqlCommand.Parameters.AddWithValue("@mname", txtMName.Text)
                    SqlCommand.Parameters.AddWithValue("@lname", txtLName.Text)
                    SqlCommand.Parameters.AddWithValue("@yr", rbdtext)
                    SqlCommand.Parameters.AddWithValue("@sec", uno)
                    SqlCommand.Connection = conn
    BTW, before you made the changes with using parameters, you did use the With structure for setting up the different properties for the command object. I'd suggest to continue using that just to aid in reading your code and for helping to organize that code.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,242

    Re: Inserting combo box values?

    The initial error was probably due to wrapping numeric fields in single quotes. Only string fields get single quotes in Access. Dates get enclosed in # signs (though you don't have any of those), and numeric fields, such as possibly the StudentID and the Yr, are not wrapped in anything. Using parameters will generally remove any such issues, as the parameters will format things better. This is also true for some interesting names. For example O'Brien would do something bad due to the apocalytpic apostrophe. Parameters take care of that issue, as well.

    Of course, you can still make typos. Nobody can fix that. As a general rule, if the error changes....that's a good thing. You may have introduced a new error, but at least you can feel assured that you are making progress.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width