.NET (Core and Framework)

13-Apr-09 19:20

I am not getting my program to update my access 2003 database. What the hek am I doing wrong.

This is the only problem that I still have in my program

Here is my code (function)

Public Function Edit() As String
       Try

           Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
           OleDbConn.Open()

           Dim MyOledbCommand As OleDbCommand = New OleDbCommand()

           Dim String1, String2, String3, String4, String5, string6, string7, string8, string9, string10, string11, string12, string13 As String

           String1 = TxtLuidraad.Text
           String2 = txtA1.Text()
           String3 = txtA2.Text()
           String4 = txtA3.Text()
           String5 = txtA4.Text()
           string6 = txtA5.Text()
           string7 = txtA6.Text()
           string8 = txtA7.Text()
           string9 = txtA8.Text()
           string10 = txtA9.Text()
           string11 = txtA10.Text()
           string12 = txtA11.Text()
           string13 = txtA12.Text()

           MyOledbCommand.CommandText = "UPDATE Tblokkies SET WHERE leidraad = ?, A1 = ?, A2 = ?, A3 = ?,A4 = ?, A5 = ?, A6 = ?, A7 = ?, A8 = ?, A9 = ?, A10 = ?, A11 = ?, A12 = ?"

           MyOledbCommand.Parameters.AddWithValue("@string1", Me.TxtLuidraad.Text)
           MyOledbCommand.Parameters.AddWithValue("@string2", Me.txtA1.Text)
           MyOledbCommand.Parameters.AddWithValue("@string3", Me.txtA2.Text)
           MyOledbCommand.Parameters.AddWithValue("@string4", Me.txtA3.Text)
           MyOledbCommand.Parameters.AddWithValue("@string5", Me.txtA4.Text)
           MyOledbCommand.Parameters.AddWithValue("@string6", Me.txtA5.Text)
           MyOledbCommand.Parameters.AddWithValue("@string7", Me.txtA6.Text)
           MyOledbCommand.Parameters.AddWithValue("@string8", Me.txtA7.Text)
           MyOledbCommand.Parameters.AddWithValue("@string9", Me.txtA8.Text)
           MyOledbCommand.Parameters.AddWithValue("@string10", Me.txtA9.Text)
           MyOledbCommand.Parameters.AddWithValue("@string11", Me.txtA10.Text)
           MyOledbCommand.Parameters.AddWithValue("@string12", Me.txtA11.Text)
           MyOledbCommand.Parameters.AddWithValue("@string13", Me.txtA12.Text)


           MyOledbCommand.Connection = OleDbConn

           MyOledbCommand.ExecuteNonQuery()
           OleDbConn.Close()

           DisableTextboxes()
           NeroBar1.Value = 0

           FillDataGrid("Select * from Tblokkies")
           FillListBox("Select * from Tblokkies")

           btnaddNew.Enabled = True
           btnDelete.Enabled = True
           ' btnupdate.Enabled = True

           SaveOrEdit = "Cancel"

           'StatusLabel.Text = " Inligtin Verander."
           StatusLabel.Text = "Leidraad   :" & TxtLuidraad.Text & ": Verander."

       Catch err As System.Exception
           StatusLabel.Text = err.Message
       End Try


   End Function

My update button

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

        StatusLabel.Text = " Kies Leidraad om te verander"

        If ListBox1.SelectedIndex <> -1 Then

            SaveOrEdit = "Edit"
        Else
            StatusLabel.Text = " Bestaande leidraad is nie verander nie"
        End If

            End Sub

My save button

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

       If SaveOrEdit = "Save" Then

           Dim add As DialogResult

           add = Cls_MessageBbox.Show("Is jy Seker jy will Leidraad" & Environment.NewLine & TxtLuidraad.Text & Environment.NewLine & "Byvoeg", "Byvoeg.", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

           AddNew()

           'add = Cls_MessageBbox.Show("Leidraad" & Environment.NewLine & TxtLuidraad.Text & Environment.NewLine & "is Bygevoeg", "Byvoeg.", MessageBoxButtons.OK, MessageBoxIcon.Information)

       ElseIf SaveOrEdit = "Edit" Then

           Edit()
       Else

           StatusLabel.Text = " Geen Leidraad is bygevoeg"

       End If

   End Sub

Pete O'Hanlon13-Apr-09 21:55

Pete O'Hanlon

13-Apr-09 21:55

hendrikbez wrote:
MyOledbCommand.CommandText = "UPDATE Tblokkies SET WHERE leidraad = ?, A1 = ?, A2 = ?, A3 = ?,A4 = ?, A5 = ?, A6 = ?, A7 = ?, A8 = ?, A9 = ?, A10 = ?, A11 = ?, A12 = ?" MyOledbCommand.Parameters.AddWithValue("@string1", Me.TxtLuidraad.Text)

Okay - at least part of your problem lies here. You use positional parameters (?) in your statement, but named parameters in your parameters list. They need to be consistent.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

hendrikbez13-Apr-09 22:26

13-Apr-09 22:26

even if I use this

MyOledbCommand.CommandText = "UPDATE Tblokkies SET leidraad = @string1, A1 = @string2, A2 = @string3, A3 = @string4,A4 = @string5, A5 = @string6, A6 = @string7, A7 = @string8, A8 = @string9, A9 = @string10, A10 = @string11, A11 = @string12, A12 = @string13"

I can not get it to save to the database

Eddy Vluggen13-Apr-09 23:11

13-Apr-09 23:11

I think that the WHERE part would be moved to the end of the statement, and you might want to check the parameters;

"UPDATE Tblokkies 
   SET A1  = @string2, 
       A2  = @string3, 
       A3  = @string4, 
       A4  = @string5, 
       A5  = @string6, 
       A6  = @string7, 
       A7  = @string8, 
       A8  = @string9, 
       A9  = @string10, 
       A10 = @string11, 
       A11 = @string12, 
       A12 = @string13 
 WHERE leidraad = @string1"

Enjoy

I are troll Smile | :)

hendrikbez13-Apr-09 23:59

13-Apr-09 23:59

Thanks, now I am getting a answer back, that it did not save it to database,
before, I did not get anyting back

Edit

Ps sometimes it says that is have saved the info, but there it did not sdaved it in the data base

Eddy Vluggen14-Apr-09 1:13

14-Apr-09 1:13

hendrikbez wrote:
Ps sometimes it says that is have saved the info, but there it did not sdaved it in the data base

That might happen when there are no records matching the parameter @string1; in that case it will return with succes, since it updated all records matching the criteria (iow, no records at all).

Try the query outside of your code, and verify whether it works as intended Smile | :)

I are troll Smile | :)

hendrikbez14-Apr-09 1:33

14-Apr-09 1:33

My string1 and string2 will always have info in, I only want to add string3 today, and later string4 and so on, so when I click on my listbox it chooses the info and put it in my textboxers, then I add a new word to string3, then it must update, but it don't

Eddy Vluggen14-Apr-09 6:16

14-Apr-09 6:16

It will only update the row in the table that has "@string1". That means that the row has to exist before you can update it. Can you put a breakpoint there and verify that "string1" is filled with the correct key?

In other words, I'm interested in the values that you're writing to the database.

I are troll Smile | :)

hendrikbez14-Apr-09 23:24

14-Apr-09 23:24

With my Breakpoint.

1. Click on button "Edit info"
2. Say I must choose a leidraad to edit
3. Choose number 4 from listbox

On this code

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

        Dim SqlStr As String

        SqlStr = "Select * from tblokkies where leidraad = """ & ListBox1.Items(ListBox1.SelectedIndex) & """"

        FillTextBox(SqlStr)
    End Sub

On F8

SqlStr = "Select * from tblokkies where leidraad = """ & ListBox1.Items(ListBox1.SelectedIndex) & """"
SqlStr ========"Sqltr "Select *from tblokkies where leidraad = "'n uitroep""
ListBox1.SelectedIndex)=========="Listbox1.Seletectedindex 3

FillTextBox(SqlStr) ========"Filltextbox Nothing

It then fills my textboxes with this info

4. info in this from row in my table ====Luidraad ='n uitroep" A1=gits, A2=aag,A3=SA (My textboxes luidraad,A1,A2,A3,)
Want to put info now in A4=test" (textbox A4)
4. Click on Save button.

On this code after F8

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

      If SaveOrEdit = "Save" Then

          Dim add As DialogResult

          add = Cls_MessageBbox.Show("Is jy Seker jy will Leidraad" & vbCrLf & vbCrLf & TxtLuidraad.Text & vbCrLf & vbCrLf & "Byvoeg", "Byvoeg.", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

          If add = Windows.Forms.DialogResult.Yes Then
              AddNew()
          Else
              ListBox1.Text = "Geen leidraad is Bygevoeg nie"

          End If

      ElseIf SaveOrEdit = "Edit" Then

          Edit()
      Else

          StatusLabel.Text = " Geen Leidraad is bygevoeg"

      End If

  End Sub

If SaveOrEdit = "Save" Then =====Saveoredit ""
Then jumbs to
ElseIf SaveOrEdit = "Edit" Then =======Saveordeit ""
StatusLabel.Text = " Geen Leidraad is bygevoeg"=========Statuslabel.text "Leidradd :'n Uiroep: geselkteer"
After F8
StatusLabel.Text = " Geen Leidraad is bygevoeg"=========Statuslabel.text "Geen Leidraad is bygevoeg"

This is where it stops.

Eddy Vluggen15-Apr-09 0:11

Connecting C++.NET with WebService..

15-Apr-09 0:11

I understood that the btnAdd_Click handler is the save-button. Somehow it does trigger this for the first three textboxes (as you said they were saved correctly), but number four is out.

The variable SaveOrEdit hasn't got the value "Edit", which would trigger the Edit() function. The question remains why it works for the first three textboxes differently then for the fourth.

It 'feels' like there's something wrong with the program-flow, but I can't point where the pain precisely is Sigh | :sigh:

I are troll Smile | :)

shaina223112-Apr-09 21:13

shaina2231

12-Apr-09 21:13

How its possible to Connect the Vc+.NET application with webservice.
i want to Post the Values of Some fields on Submit button clicking To WebService( designed in c#.NET)
What type of project should i take either MFC or else plz guide

Database connection

EmZan11-Apr-09 2:21

11-Apr-09 2:21

i'm wondoring which is better !!

1.openning the connection to the database and leaving it opened untill the application is closed.

2.closing that connection and openning it again when i a need to connect
,which means i will open and close the connection many times while the application is running !!

thanks in advance

0x3c011-Apr-09 2:41

0x3c0

11-Apr-09 2:41

Just a technical point: assuming that the connection is by the same program to the same file, the connection doesn't get closed and opened lots of times. Most database drivers use connection pooling, which caches the database connection. This increases the speed considerably

Personally, I would go with the second option. I don't write database programs often, but with the advent of connection pooling, the speed reduction is virtually eliminated. On the other hand, a program may terminate abnormally; an example of this would be the 'End Process' button in Task Manager. If that happens, I don't know if the connection would get terminated

This choice is just my opinion really. It may not be right, but it goes with my general method of retrieving data: get in, read it as quickly and efficiently as possible, get out, deal with the extracted data

EmZan11-Apr-09 3:01

11-Apr-09 3:01

thanks for ur reply ,

but i didn't get the meaning of connection pooling !
and what is the advantage?

i thought about it ,
i think if the program depends on a database which is on pc other than the application pc , it may be better if i use option 2 because of network traffic and security issues

beside if the database is on the application pc itself i would use option 1
that is my opinion and am not sure if it is better

0x3c011-Apr-09 3:17

0x3c0

11-Apr-09 3:17

Connection pooling basically caches the connection. It removes most of the overhead from creating a connection. Most of the database drivers use it. The main advantage of this is speed; there's no need to create a connection and read the data when the database driver's got some of the data already cached

EmZan11-Apr-09 6:15

11-Apr-09 6:15

oh yee i got it

it remembers me deferred execution Linq in .net3.5
deferred execution doesn't have connection pooling Smile | :)

thanks a lot

*Developer Day Scotland - Free community conference Delegate Registration Open

Colin Angus Mackay11-Apr-09 6:25

Colin Angus Mackay

11-Apr-09 6:25

Member 4697348 wrote:
it [connection pooling] remembers me deferred execution Linq in .net3.5

What has deferred execution got to do with connection pooling?

EmZan12-Apr-09 20:58