11 Replies - 674 Views - Last Post: 15 January 2015 - 02:32 AM Rate Topic: -----

#1 PNJLj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 237
  • Joined: 18-May 09

Error In SQLConnection

Posted 12 January 2015 - 10:28 PM

Unsure what this message is but still not saving data, any ideas? Please see attached error file.
Here is the code - Just tried running a Try/Catch statement before the shown area, and its saying
"Keyword Not Supported: 'Provider'."

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim strEmployee As String = lstNames.SelectedItem.ToString()
        Dim strDate As String = datePicker.Text
        Dim strItem As String = lstItems.SelectedItem.ToString()
        Dim strNumItems As String = numUD.Value
        Dim strReason As String = cmbReason.SelectedItem.ToString()
        Dim strNotes As String = txtNotes.text
        Dim RowAdded As Integer

        Dim sQuery As String = String.Empty

        sQuery &= "INSERT INTO tblDetails (colDate/Time, colEmployee, colItem, colNumber, colReason, colNotes) ""VALUES (@colDate/Time, @colEmployee, @colItem, @colNumber, @colReason, @colNotes)"
        Using Conn As New SqlConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Information.mdb")
            Using Comm As New SqlCommand()
                With Comm
                    .Connection = Conn
                    .CommandType = CommandType.Text
                    .CommandText = sQuery
                    .Parameters.AddWithValue("@colDate/Time", strDate)
                    .Parameters.AddWithValue("@colEmployee", strEmployee)
                    .Parameters.AddWithValue("@colItem", strItem)
                    .Parameters.AddWithValue("@colNumber", strNumItems)
                    .Parameters.AddWithValue("@colReason", strReason)
                    .Parameters.AddWithValue("@colNotes", strNotes)
                End With
                Try
                    Conn.Open()
                    Comm.ExecuteNonQuery()
                    MessageBox.Show("New Row Instered" & RowAdded)
                Catch ex As SqlException
                    MessageBox.Show(ex.Message.ToString(), "Error In SQL")
                    Conn.Close()
                End Try
            End Using
        End Using

    End Sub

End Class


I am trying to insert data into a database. Any suggestions welcome.

Attached image(s)

  • Attached Image

This post has been edited by PNJLj: 12 January 2015 - 10:32 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Error In SQLConnection

#2 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Error In SQLConnection

Posted 13 January 2015 - 01:17 AM

Hi

It would appear that you are using an Access database (given the .mdb extension of your file name) but you are using SqlCommand which leads me to believe that you are the System.Data.SqlClient objects when in fact you should be using OleDb.

So your connection should be an OleDbConnection object and your command should be an OleDbCommand object.

Also, please note that for OleDb, while named parameters are generally not supported (rather you should use ? in your SQL statement) they can be used (and I prefer the readability), just be careful as you must ensure that when adding your parameter values that you do so in the order that they appear in the SQL statement. So, if you modify your SQL statement with additional criteria, be sure to add your additional parameters in the correct order

HTH

This post has been edited by djjeavons: 13 January 2015 - 01:23 AM

Was This Post Helpful? 1
  • +
  • -

#3 PNJLj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 237
  • Joined: 18-May 09

Re: Error In SQLConnection

Posted 15 January 2015 - 12:45 AM

ok, i understand the oledbconection issue and am fixing that up as we speak, however i am unsure about the named paramaters issue you are talking about, could you please show a basic example. Thanks in advance. and for the help.
Was This Post Helpful? 0
  • +
  • -

#4 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Error In SQLConnection

Posted 15 January 2015 - 01:00 AM

Hi

What you have now is fine as your parameters are added in the same order as they appear in the SQL statement.

What I mean by this is consider the following:

"SELECT Field1, Field2, Field3 FROM Table WHERE Field1 = @Field1Parameter AND Field2 = @Field2Parameter"

Command.Parameters.AddWithValue("@Field1Parameter", "SomeValue1")
Command.Parameters.AddWithValue("@Field2Parameter", "SomeValue2")



The above is fine as the parameters are added in the same order as they appear. However if you had:

"SELECT Field1, Field2, Field3 FROM Table WHERE Field1 = @Field1Parameter AND Field2 = @Field2Parameter"

Command.Parameters.AddWithValue("@Field2Parameter", "SomeValue2")
Command.Parameters.AddWithValue("@Field1Parameter", "SomeValue1")



Then while the code will still run the data returned may not be as expected as "SomeValue2" will be passed as @Field1Parameter (as that is the first parameter in the SQL statement).

Hope that makes sense.
Was This Post Helpful? 0
  • +
  • -

#5 PNJLj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 237
  • Joined: 18-May 09

Re: Error In SQLConnection

Posted 15 January 2015 - 01:06 AM

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim strEmployee As String = lstNames.SelectedItem.ToString()
        Dim strDate As String = datePicker.Text
        Dim strItem As String = lstItems.SelectedItem.ToString()
        Dim strNumItems As String = CStr(numUD.Value)
        Dim strReason As String = cmbReason.SelectedItem.ToString()
        Dim strNotes As String = txtNotes.Text
        Dim RowAdded As Integer

        Dim sQuery As String = String.Empty
        sQuery = "INSERT INTO Details (Date/Time, Employee, Item, Number, Reason, Notes) ""VALUES (@colDate/Time, @colEmployee, @colItem, @colNumber, @colReason, @colNotes)"

        Try
            Using Conn As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Information.mdb")
                Using Comm As New OleDbCommand()
                    With Comm
                        .Connection = Conn
                        .CommandType = CommandType.Text
                        .CommandText = sQuery
                        .Parameters.AddWithValue("@colDate/Time", strDate)
                        .Parameters.AddWithValue("@colEmployee", strEmployee)
                        .Parameters.AddWithValue("@colItem", strItem)
                        .Parameters.AddWithValue("@colNumber", strNumItems)
                        .Parameters.AddWithValue("@colReason", strReason)
                        .Parameters.AddWithValue("@colNotes", strNotes)
                    End With
                    Try
                        Conn.Open()
                        Comm.ExecuteNonQuery()
                        MessageBox.Show("New Row Instered" & RowAdded)
                    Catch ex As OleDbException
                        MessageBox.Show(ex.Message.ToString(), "Error In OLEDB", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Conn.Close()
                    End Try
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString(), "Error In OLEDB Connection", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub


I have this but i am getting an error message from the "Error In OLEDB" Try/Catch area. I am thinking its the Comm.ExecuteNonQuery. Any suggestion.

BTW, i am going to change to how you described and i will show shortly to see if its correct. Thanks heaps.
Was This Post Helpful? 0
  • +
  • -

#6 PNJLj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 237
  • Joined: 18-May 09

Re: Error In SQLConnection

Posted 15 January 2015 - 01:15 AM

  sQuery = "INSERT INTO Details (Date/Time, Employee, Item, Number, Reason, Notes) ""VALUES WHERE (Date/Time = @colDate/Time AND Employee = @colEmployee AND Item = @colItem AND Number = @colNumber AND Reason = @colReason AND Notes = @colNotes)"


^^^ Is that what you mean?

 Using Comm As New OleDbCommand()
                    With Comm
                        .Connection = Conn
                        .CommandType = CommandType.Text
                        .CommandText = sQuery
                        .Parameters.AddWithValue("@colDate/Time", datePicker.Text)
                        .Parameters.AddWithValue("@colEmployee", lstNames.SelectedItem.ToString())
                        .Parameters.AddWithValue("@colItem", lstItems.SelectedItem.ToString())
                        .Parameters.AddWithValue("@colNumber", CStr(numUD.Value))
                        .Parameters.AddWithValue("@colReason", cmbReason.SelectedItem.ToString())
                        .Parameters.AddWithValue("@colNotes", txtNotes.Text)
                    End With


My error is saying Syntax error in INSERT INTO statement.

This post has been edited by PNJLj: 15 January 2015 - 01:17 AM

Was This Post Helpful? 0
  • +
  • -

#7 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Error In SQLConnection

Posted 15 January 2015 - 01:31 AM

Hi

Sorry, I may have confused you with my example as you have now added a WHERE criteria to your sql statement. I was just simply pointing out the parameters piece.

Your INSERT statement should be as you had it (but with some changes that I will note):

'Your Original
sQuery = "INSERT INTO Details (Date/Time, Employee, Item, Number, Reason, Notes) ""VALUES (@colDate/Time, @colEmployee, @colItem, @colNumber, @colReason, @colNotes)"

'Should be
sQuery = "INSERT INTO Details ([Date/Time], Employee, Item, [Number], Reason, Notes) VALUES (@colDate/Time, @colEmployee, @colItem, @colNumber, @colReason, @colNotes)"



In the above I have removed the additional "" characters you had prior to the VALUES keyword. I have also enclosed Date/Time and Number fields in square brackets. Number is a reserved word in Access and shouldn't be used as a field name but if it must then enclose it in square brackets. Date/Time may or may not be a reserved word (DATETIME definitely is) but as it contains a slash character it is safer to enclose it in square brackets. Again, consider renaming this field.
Was This Post Helpful? 1
  • +
  • -

#8 PNJLj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 237
  • Joined: 18-May 09

Re: Error In SQLConnection

Posted 15 January 2015 - 02:03 AM

this is frustraing! i have done what you said and reverted back to old way, now im getting an
No value given for one or more required paramaters.

any suggestions? could it be the "Comm.ExecuteNonQuery()" section?
Was This Post Helpful? 0
  • +
  • -

#9 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Error In SQLConnection

Posted 15 January 2015 - 02:14 AM

Hi

The error is suggesting that your database expects data to be supplied for one or more columns but you have not supplied it. For example, if Reason is a required field but no reason has been entered on your UI then you will get this error when executing the statement.

It has nothing to do with the ExecuteNonQuery section and in fact you have moved a step forward. Ensure that you supply a piece of information for each field and test it. If you are still getting an error after that, can you provide the structure of your Details table (maybe as a screen shot).
Was This Post Helpful? 1
  • +
  • -

#10 deery5000  Icon User is offline

  • D.I.C Lover

Reputation: 87
  • View blog
  • Posts: 1,097
  • Joined: 09-May 09

Re: Error In SQLConnection

Posted 15 January 2015 - 02:27 AM

The reason your getting the provider error at this line is simply to do with the & not being allowed. Create a variable to hold the value and pass this to the call

form this
("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Information.mdb"



to this
dim connectionString as string = ("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Information.mdb"
 Using Conn As New SqlConnection(connectionString)


Was This Post Helpful? 0
  • +
  • -

#11 PNJLj  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 237
  • Joined: 18-May 09

Re: Error In SQLConnection

Posted 15 January 2015 - 02:28 AM

OK, So i worked it out and its now saving data ... :) YAY!
Thanks so much. Now for finer details. Cannot express how much you helped, Thanks again.
Was This Post Helpful? 0
  • +
  • -

#12 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: Error In SQLConnection

Posted 15 January 2015 - 02:32 AM

View Postdeery5000, on 15 January 2015 - 02:27 AM, said:

The reason your getting the provider error at this line is simply to do with the & not being allowed. Create a variable to hold the value and pass this to the call

form this
("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Information.mdb"



to this
dim connectionString as string = ("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Information.mdb"
 Using Conn As New SqlConnection(connectionString)



But this is an Access database and the original post and yours both use an SqlConnection therefore the connection string is invalid, hence the error.

View PostPNJLj, on 15 January 2015 - 02:28 AM, said:

OK, So i worked it out and its now saving data ... :)/> YAY!
Thanks so much. Now for finer details. Cannot express how much you helped, Thanks again.

Excellent, and your welcome.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1