14 Replies - 985 Views - Last Post: 02 March 2011 - 05:42 PM Rate Topic: -----

#1 chocolateprincess   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 20-February 11

connection problem

Posted 20 February 2011 - 06:13 AM

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Along\Documents\Visual Studio 2008\Projects\LCRS\LCRS\Lcrsd.mdb;")
        Dim myCommand As OleDbCommand
        Dim mySQLString As String
        Try
            myConnection.Open()
            mySQLString = "INSERT INTO REKOD_PELAJAR VALUES('" + TextBox1.Text + "','" + ComboBox1.Text + " ','" + DTP.Value + "','" + TextBox2.Text + "')"
            myCommand = New OleDbCommand(mySQLString, myConnection)
            myCommand.CommandType = CommandType.Text
            myCommand.ExecuteNonQuery()
        Catch ex As Exception

        Finally
            myConnection.Close()
        End Try
        MessageBox.Show("rekod anda telah disimpan")

    End Sub

End Class


Edited by Dogstopper: :code:

Is This A Good Question/Topic? 0
  • +

Replies To: connection problem

#2 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: connection problem

Posted 20 February 2011 - 06:46 AM

So directly after Catch ex As Exception put the following code:
MessageBox.Show(ex.Message)


Run your program and post back with what that error you're getting is.

By not handling or displaying anything in the Catch block, you're essentially just burying any error you get in the Try Block.

This post has been edited by CharlieMay: 20 February 2011 - 06:46 AM

Was This Post Helpful? 0
  • +
  • -

#3 chocolateprincess   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 20-February 11

Re: connection problem

Posted 20 February 2011 - 06:52 AM

other error occured, it says that number of query values and destination fields are not the same

i also try this way, but i dont understand about TableAdapter.Update

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SQL As String
        Dim Con As OleDbConnection

        SQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Along\Documents\Visual Studio 2008\Projects\LCRS\LCRS\Lcrsd.mdb"

        Con = New OleDbConnection(SQL)
        Con.Open()

        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet


        da = New OleDbDataAdapter("Select * from REKOD_PELAJAR", Con)
        da.Fill(ds, "REKOD_PELAJAR")

        Dim dr As DataRow
        dr = ds.Tables("REKOD_PELAJAR").NewRow

        dr("NAMA") = TextBox1.Text
        dr("KELAS") = ComboBox1.Text
        dr("TARIKH") = DTP.Value
        dr("NO_PENDAFTARAN") = TextBox2.Text
        ds.Tables("REKOD_PELAJAR").Rows.Add(dr)

        da = New OleDbDataAdapter("Select * from REKOD_PELAJAR", Con)

        Dim builder As New OleDbCommandBuilder(da)
        builder.GetUpdateCommand()

        TableAdapter.Update(ds, "REKOD_PELAJAR")

    End Sub



Edited by Dogstopper: :code:
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: connection problem

Posted 20 February 2011 - 06:56 AM

OK, you need to specify your fields then...

A typical INSERT statement is in the following format
INSERT INTO yourtable (Field1, Field2, etc...) VALUES ('value1', 'value2', etc...)


You currently have

INSERT INTO yourtable VALUES ('value1', 'value2', etc...)


Notice the left side of the values where you specify what fields that values go into?
Was This Post Helpful? 2
  • +
  • -

#5 chocolateprincess   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 20-February 11

Re: connection problem

Posted 20 February 2011 - 07:35 AM

It's working.... thank you very much..... really2 appreciate this..... i've been trying to solve this problem from the morning.... no one really helps....
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: connection problem

Posted 20 February 2011 - 07:45 AM

So out of curiosity can you answer something for me?

I've been seeing more and more people using insert statement with that syntax [il] INSERT INTO table VALUES (...)

Is this being taught in schools? because it just appears that a lot of people are doing it.
Was This Post Helpful? 0
  • +
  • -

#7 chocolateprincess   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 20-February 11

Re: connection problem

Posted 20 February 2011 - 07:50 AM

i refers to lots of internet tutorials..... and most of them use it....rather than using command Fill,
maybe because it is shorter
Was This Post Helpful? 0
  • +
  • -

#8 chocolateprincess   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 20-February 11

Re: connection problem

Posted 20 February 2011 - 07:56 AM

i learn this code from w3school sqlcommands
Was This Post Helpful? 0
  • +
  • -

#9 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: connection problem

Posted 20 February 2011 - 08:00 AM

Thanks,
It just appears a lot and although it's valid, it just causes confusion when an error occurs due to a database requirement that isn't being followed. Like switching two values or forgetting one.

Too much room for error just for a shortcut in my opinion. ;)
Was This Post Helpful? 0
  • +
  • -

#10 chocolateprincess   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 20-February 11

Re: connection problem

Posted 20 February 2011 - 09:53 AM

i can't agree more... it makes my head spin
Was This Post Helpful? 0
  • +
  • -

#11 Dogstopper   User is offline

  • The Ninjaducky
  • member icon

Reputation: 2972
  • View blog
  • Posts: 11,223
  • Joined: 15-July 08

Re: connection problem

Posted 20 February 2011 - 10:50 AM

chocolateprincess, I don't know if you can see the rules or background, but if you would, please :code: from now on. :)

Thanks!
Was This Post Helpful? 0
  • +
  • -

#12 js20   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 02-March 11

Re: connection problem

Posted 02 March 2011 - 11:42 AM

View PostCharlieMay, on 20 February 2011 - 06:56 AM, said:

OK, you need to specify your fields then...

A typical INSERT statement is in the following format
INSERT INTO yourtable (Field1, Field2, etc...) VALUES ('value1', 'value2', etc...)


You currently have

INSERT INTO yourtable VALUES ('value1', 'value2', etc...)


Notice the left side of the values where you specify what fields that values go into?






I am having a similar problem trying to INSERT a few values. I have looked at a few different techniques but I can't figure out the problem. I have a program that will fetch the correct data so my connection is correct. I did notice a difference in my connection and the one listed above... Mine is Microsoft.ACE.OlEDB not Microsoft.Jet... not sure if this will cause a problem, but here goes.
i am just trying to INSERT into my database but get a J-I-T error, I added the TRY to fix that and my error messsage is now "Syntax error in INSERT INTO statement." Here is the code for it...

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        ''DB connection
        Dim dbName As String = "G:\blah\blah\blah.mdb"
        Dim myConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" & dbName
        Dim mySelectQuery As String
        ''form values
        Dim stID As String = cbState.Text
        Dim Disc As String = cbDiscipline.Text
        Dim City As String = tbCity.Text
        Dim Comment As String = tbComment.Text
        Dim SubBy As String = tbSubBy.Text

        If cbState.Text = "" Then
            MsgBox("Please choose a State")
        ElseIf cbDiscipline.Text = "" Then
            MsgBox("Please choose a Discipline")
        ElseIf tbCity.Text = "" Then
            tbCity.Text = "ALL"
        ElseIf tbComment.Text = "" Then
            MsgBox("Please fill out the Comment Box")
        ElseIf tbSubBy.Text = "" Then
            MsgBox("Please put your initials in the ""Submitted By"" box")
        End If

        Dim myconnection As New OleDbConnection(myConnString)
        Dim myCommand As OleDbCommand
        Try
            myconnection.Open()
            mySelectQuery = ("INSERT INTO Local_Codes(State_ID, City, Discipline, Comment, Submitted_By) VALUES('" + stID + "','" + Disc + "','" + City + "','" + Comment + "','" + SubBy + "'")
            myCommand = New OleDbCommand(mySelectQuery, myconnection)
            myCommand.CommandType = CommandType.Text
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            myconnection.Close()
        End Try
       End Sub




Any idea on what is causing this error?
Thanks, JS
Was This Post Helpful? 0
  • +
  • -

#13 js20   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 02-March 11

Re: connection problem

Posted 02 March 2011 - 12:19 PM

Figured out the problem, didn't have the correct amount of ")" in my sql.
Thanks for the original posts though CharlieMay, they helped!
JS
Was This Post Helpful? 0
  • +
  • -

#14 BobRodes   User is offline

  • Product Manager
  • member icon

Reputation: 604
  • View blog
  • Posts: 3,085
  • Joined: 19-May 09

Re: connection problem

Posted 02 March 2011 - 05:08 PM

View PostCharlieMay, on 20 February 2011 - 02:45 PM, said:

So out of curiosity can you answer something for me?

I've been seeing more and more people using insert statement with that syntax [il] INSERT INTO table VALUES (...)

Is this being taught in schools? because it just appears that a lot of people are doing it.

Charlie, I'm not sure if this is your question, but I believe you're asking why people aren't using the (fieldlist) part of the insert statement. If you're inserting one value into each field in the target table in the order that they occur in the target table, that's the default and you don't need to specify a fieldlist. It's been that way since forever, but is probably not considered terribly robust, given that table structures have a way of changing over time.
Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: connection problem

Posted 02 March 2011 - 05:42 PM

Bob,
Yea, I knew that but I guess I consider it a shortcut for people who understand the rules of using it. As far as teaching it to new students, I feel like it really causes confusion. To me it would be better to teach
INSERT INTO MyTable (afield, anotherfield) VALUES ('avalue', 'anothervalue')
. I was just seeing it a lot lately and was wondering if that's how they were teaching it.

I mean, one little mistake and you're putting the data in the wrong place ;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1