[RESOLVED] Edit Database Entry

[dethredic]

So when I export a database entry I want to change a value from either Null or 0 to 1. I have found tons of example on how to add entries, but I am not sure how to edit one.

I currently I am thinking about just checking each value to see if it matches then deleting the record and adding a new one with the 1 instead of null/0. I just think there has to be a better way.

I am using a MSSQL database.

[BrianS]

Sorry, misread your post, I thought you wanted help with the UPDATE function.

Guess I'm not sure what you mean by Export a Database Entry

[dethredic]

Well I have something in my database like:

JobNumber    Hours   Employee  Exported
1            3       Joe       0
3            5       Bob       0

I read that and and write it to a .csv file.
Once I write the first record to a text file I want to change it to:

JobNumber    Hours   Employee  Exported
1            3       Joe       1
3            5       Bob       0

[BrianS]

And how do you export? Do you do a SQL command and then save the results as a .txt/.csv file?

If so, you can do a CASE and change the 0 to a 1.

[sapator]

You want to export and write the 0 to 1 on the CSV, correct?
Yeah what Brians says,how you do it? But to run a little ahead you can have your sql results to a datareader and loop to each row , put the result to the csv and change 0 to 1.Again on the next row, read datareader next line put results to csv, change 0 to 1.

[dethredic]

Sorry, I am a little confused. I read the database like this:

Using connection1 As New SqlConnection(Form1.ConnectionString)
                            Using command1 As New SqlCommand("SELECT JobNumber, NameFirst, Hours FROM Employees", _
                                                          connection1)
                                connection1.Open()
                                Using reader1 As SqlDataReader = command1.ExecuteReader()
                                    While reader1.Read()
                                        If JobNumber =  reader1("JobNumber") Then
                                            aNameFirst = reader1("NameFirst")
                                            aHours= reader1("Hours")
                                        End If
                                    End While
                                End Using
                            End Using
                        End Using

I then use those variables to write the data to the csv. That part works fine.

There is another column that I didn't read called exported. I just want to put a 0 there.

[BrianS]

Where do you read the Exported column?

[dethredic]

Where do you read the Exported column?

Sorry, I should have given you my actual code, not just something similar.

For i = 0 To (ListView1.CheckedItems.Count - 1)
            description = Form1.RemoveWhiteSpace(ListView1.CheckedItems(i).Text) & " " & Form1.RemoveWhiteSpace(ListView1.CheckedItems(i).SubItems(1).Text)
 
            Using connection As New SqlConnection(Form1.ConnectionString)
                Using command As New SqlCommand("SELECT TimeSheetID, ProjectNumber, CostCode, HoursTotal, Date FROM TimesheetDetails", _
                                              connection)
                    connection.Open()
                    Using reader As SqlDataReader = command.ExecuteReader()
                        While reader.Read()
                            If reader("TimeSheetID") = ListView1.CheckedItems(i).SubItems(5).Text Then
                                jobnumber = reader("ProjectNumber")
                                costcode = reader("costcode")
                                accountingdate = reader("Date")
                                units = reader("HoursTotal")
 
                                Using connection1 As New SqlConnection(Form1.ConnectionString)
                                    Using command1 As New SqlCommand("SELECT EmployeeID, Rate FROM EmployeeRate", _
                                                                  connection1)
                                        connection1.Open()
                                        Using reader1 As SqlDataReader = command1.ExecuteReader()
                                            While reader1.Read()
                                                If ListView1.CheckedItems(i).SubItems(4).Text = reader1("EmployeeID") Then
                                                    unitcost = reader1("Rate")
                                                End If
                                            End While
                                        End Using
                                    End Using
                                End Using
 
                                amount = units * unitcost
                                exportstring = "DC," & jobnumber & ",," & costcode & "," & "600" & "," & "2" & "," & transactiondate & "," & accountingdate & "," & description & "," & units & "," & unitcost & "," & amount & "," & "" & "," & ""
                                oWrite.WriteLine(exportstring)
 
                                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                '  The vlaues have been written, time to change the value of exported to a 1
                                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                Using connection1 As New SqlConnection(Form1.ConnectionString)
                                    Using command1 As New SqlCommand("SELECT EmployeeID, TimesheetID, Exported FROM Timesheet", _
                                                                  connection1)
                                        connection1.Open()
                                        Using reader1 As SqlDataReader = command1.ExecuteReader()
                                            While reader1.Read()
                                                If ListView1.CheckedItems(i).SubItems(4).Text = reader1("EmployeeID") Then
                                                    If ListView1.CheckedItems(i).SubItems(5).Text = reader1("TimesheetID") Then
                                                        Dim exported = reader1("Exported")
                                                        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                                        '  I can read the value with reader1("Exported") but I don't think that helps me
                                                        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                                    End If
                                                End If
                                            End While
                                        End Using
                                    End Using
                                End Using
 
                            End If
                        End While
                    End Using
                End Using
            End Using
 
        Next

[BrianS]

Try changing

"SELECT TimeSheetID, ProjectNumber, CostCode, HoursTotal, Date, Exported  FROM TimesheetDetails"

to

"SELECT TimeSheetID, ProjectNumber, CostCode, HoursTotal, Date, case Exported when 0 then 1 when null then 1 else Exported end  FROM TimesheetDetails"

[dethredic]

My ninja edit was a little slow. Anyways I figured it out:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                '  The vlaues have been written, time to change the value of exported to a 1
                                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                Using connection1 As New SqlConnection(Form1.ConnectionString)
                                    Using command1 As New SqlCommand("SELECT EmployeeID, TimesheetID, Exported FROM Timesheet", _
                                                                  connection1)
 
                                        connection1.Open()
                                        Using reader1 As SqlDataReader = command1.ExecuteReader()
                                            While reader1.Read()
                                                If ListView1.CheckedItems(i).SubItems(4).Text = reader1("EmployeeID") Then
                                                    If ListView1.CheckedItems(i).SubItems(5).Text = reader1("TimesheetID") Then
 
                                                        Using connection2 As New SqlConnection(Form1.ConnectionString)
 
                                                            Using command2 As SqlCommand = New SqlCommand("UPDATE Timesheet SET Exported = 1 where EmployeeID = " & reader1("EmployeeID") & "and TimesheetID = " & reader1("TimesheetID"), connection2)
                                                                connection2.Open()
                                                                command2.ExecuteNonQuery()
                                                                connection2.Close()
                                                            End Using
                                                        End Using
 
                                                    End If
                                                End If
                                            End While
                                        End Using
                                    End Using
                                End Using

Thanks for the help.