Code not working???

[Jacksparrow87]

Hi people,

I was hoping someone could help me out here please, basically I have an application which is linked to a database.

Now if the combobox status is set to 'closed', I want to Update my database on EVERY row where the lognumber matches lbllogno.

So far I have the following coding:

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim cmd As New OleDbCommand
        With cmd
            .CommandType = CommandType.Text
            .Connection = conn
            .CommandText = "UPDATE [Log] SET Status = Status " & Val(cmbstatus.Text) & " WHERE LogNumber = lbllogno.text"
            .Parameters.Add("@p1", Me.cmbstatus.SelectedValue)
        End With
        conn.Open()
        cmd.ExecuteNonQuery()

Now when I run the above coding, it breaks on the cmd.ExecuteNonQuery() line.

Please help, thanks

 

[SgtJarrow]

Assuming LogNumber is a numeric value and not a text value:

.CommandText = "UPDATE [Log] SET Status = 'Status " & Val(cmbstatus.Text) & "' WHERE LogNumber = " & lbllogno.text

You were missing a couple of single quotes and had lbllogo on the inside of the double quotes defining the SQL statement.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[Jacksparrow87]

Hi, thanks for your help.

Your coding did do half the job as all the status's of the same lognumber were changed, however they cahnged to 'status 0' and not to the text that was displayed in cmbstatus.text.

Hopefully you understand what Im trying to say.

 

[SgtJarrow]

If you want the text in the displayed in the text box replace:

Val(cmbstatus.Text) with cbmstatus.Text

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[rjoubert]

Your CommandText statement is appending the word "Status" to the value in the combo box. That's why you have values like "status0".

Is your Status field numeric or text? If it's text, use the following...

.CommandText = "UPDATE [Log] SET Status = '" & cmbstatus.Text & "' WHERE LogNumber = " & lbllogno.text

If your Status field is numeric, use this...

.CommandText = "UPDATE [Log] SET Status = " & cmbstatus.Text & " WHERE LogNumber = " & lbllogno.text

 

[Jacksparrow87]

Thanks buddy, problem resolved.

 

[Jacksparrow87]

Sorry to reopen this thread but im experiencing a similiar problem, now the above coding works perfect however the problem is with the first form.

The above coding goes in the 2nd form and after the above coding has finished I have me.close.

Now on the first form I have some coding which loads the current datagrid, however this only works when I click on the refresh button. But when I add the same coding after frm2.showdialogue
frm2Dispose()
'coding to refresh

It does not work, anyone know maybe why?

Is it because I never added the Insert before the update? I have other coding elsewhere and I have used Insert and Update there and that works perfect (It updates the datagrid straightaway.)

Thanks

 

[SgtJarrow]

How are you refreshing the datagrid?? Is the datagrid bound and if so, how?? And is it a datagrid or a datagridview??

Can you post the code you have for updating the datagrid??

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[Jacksparrow87]

Hi,

Its a datagrid and I have the following coding:

   Private Sub LoadLog()
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter("Select * from Log Order By LogNumber DESC, NoteNumber DESC; ", conn)
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False
    End Sub

Then in the refresh button I have

LoadLog

Which shows the updated data, however after the frm2.showdialogue
frm2.dispose
LoadLog

Does not make the data refresh.

Hope that helps.

 

[mansii]

Try to LoadLog first then ShowDialogue the frm2.

 

[Jacksparrow87]

Thanks for your help, I tried that however it did not work.

 

[Sorwen]

Show your code that loads the form and where your LoadLog is then called and anything that happens after.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!