Search, create or update a record  
Author Message
Joao Pinto





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

Hello,

I'm trying to do a search on a dataset and if I find a record matching a value, I want to update the record; If i don't find any record, I want to insert a new. I've the following code:

Imports System.Data.OleDb

Dim cmd As OleDbCommand

Dim dsNR As DataRow

Dim cb = New OleDb.OleDbCommandBuilder(da)

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DbSource & ";"

sSQL = "SELECT * FROM [Numeros_Dia] WHERE (Data= '" & DataRelatorio & "')"

da = New OleDb.OleDbDataAdapter(sSQL, con)

ds.Clear()

da.Fill(ds, "Numeros_Dia")

If ds.Tables(0).Rows.Count = 0 Then

dsNR = ds.Tables("Numeros_Dia").NewRow

dsNR.Item("Data") = DataRelatorio

dsNR.Item("M_Chefe_Equipa") = Nome

ds.Tables("Numeros_Dia").Rows.Add(dsNR)

da.Update(ds, "Numeros_Dia")

con.Close()

Else

cmd = New OleDbCommand()

cmd.Connection = con

cmd.CommandType = CommandType.Text

sSQL = "UPDATE Numeros_Dia SET [M_Chefe_Equipa]='" & Nome & "', [M_ProdOks]='" & txtOKsProduzidas.Text & "' WHERE (Data= '" & DataRelatorio & "')"

cmd.CommandText = sSQL

cmd.ExecuteNonQuery()

con.Close()

cmd.Dispose()

cmd = Nothing

End If

If no records are found (1st part of the code) there is no problem. It's adding a new record to the database. The problem I've is if there is already a record that matches the "DataRelatorio", because I'm not able to update it, i'm getting an error ("OleDbException was unhandled" - incorrect data typein criteria expression).

Can someone help me with my code please Or provide some code for me to do what I need

Thanks,

Joao Pinto

http://www.hide-link.com/



.NET Development32  
 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

One thing might be Data is a reserved work, try adding brackets around it like so:

"UPDATE Numeros_Dia SET [M_Chefe_Equipa]='" & Nome & "', [M_ProdOks]='" & txtOKsProduzidas.Text & "' WHERE ([Data]= '" & DataRelatorio & "')"



 
 
Joao Pinto





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

You are right, "Data" is a reserved word and it should be placed with brackets. But I still get the same error on the following line:

da.Fill(ds, "Numeros_Dia")

Does annyone know what is the problem

Thanks,

Joao Pinto

http://senhorpires.blogspot.com/


 
 
Bappi





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

Rewrite your else code like this:

Else

cmd = New OleDbCommand()

cmd.Connection = con

cmd.CommandType = CommandType.Text

sSQL = "UPDATE Numeros_Dia SET [M_Chefe_Equipa]='" & Nome & "', [M_ProdOks]='" & txtOKsProduzidas.Text & "' WHERE (Data= '" & DataRelatorio & "')"

cmd.CommandText = sSQL

con.Open()

cmd.ExecuteNonQuery()

con.Close()

cmd.Dispose()

cmd = Nothing

End If

DataCoomand object does not open its associate connection automatically like DataAdapter. You have to open the connection manually.



 
 
Joao Pinto





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

You are right also, I forgot the open command. But I still have the error before the else statment. As I mentioned before, the error comes up on the "da.fill(ds, "Numeros_Dia")" line of code. Is it a error because of wrong data type Because "Data" and "DataRelatorio" are dates.

Thanks,

Joao Pinto

http://senhorpires.blogspot.com/


 
 
Joao Pinto





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

I've managed to solve the previous problem that occured when I was updating a record (the "else" part of the code). Now I'm having problems with adding a new record to the database. This is my actual code:

Imports System.Data.OleDb

Dim cmd As OleDbCommand

Dim dsNR As DataRow

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DbSource & ";"

sSQL = "SELECT * FROM [Numeros_Dia] WHERE ([Data] LIKE # " & DataRelatorio & "#)"

da = New OleDb.OleDbDataAdapter(sSQL, con)

ds.Clear()

da.Fill(ds, "Numeros_Dia")

If ds.Tables("Numeros_Dia").Rows.Count = 0 Then

dsNR = ds.Tables("Numeros_Dia").NewRow

dsNR.Item("Data") = DataRelatorio

dsNR.Item("M_Chefe_Equipa") = Nome

ds.Tables("Numeros_Dia").Rows.Add(dsNR)

da.Update(ds, "Numeros_Dia")

con.Close()

Else

cmd = New OleDbCommand()

cmd.Connection = con

cmd.CommandType = CommandType.Text

sSQL = "UPDATE Numeros_Dia SET [M_Chefe_Equipa]='" & Nome & "', [M_ProdOks]='" & txtOKsProduzidas.Text & " ' "

cmd.CommandText = sSQL

cmd.ExecuteNonQuery()

con.Close()

cmd.Dispose()

cmd = Nothing

End If

I get the error on the line of code " da.Update(ds, "Numeros_Dia") ". The error that I'm having is "Update requires a valid InsertCommand when passed DataRow collection with new rows".

Can someone help me with this, please

Thanks,

Joao Pinto

http://senhorpires.blogspot.com/


 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

Yes, the error is very clear. The dataadapter is not magic, it will not make you impervious to bullets. (G)

The dataadapter works by examining the dataset and then pumping the changes to one of the DeleteCommand, InsertCommand or UpdateCommand. The way you constructed it you only have the SelectCommand (which is used to suck down the data into the dataset).

So you have to set all three if you want the thing to sync properly. One easy way to do this is use the OledbCommandBuilder class, look at the help topic for OledbCommandBuilder.



 
 
Joao Pinto





PostPosted: .NET Framework Data Access and Storage, Search, create or update a record Top

Thanks Matt! You are absolutly right! By mistake I've deleted a line of code (Dim cb = New OleDb.OleDbCommandBuilder(da) ) and that was the reason for my code not working.

Now it's OK.

Joao Pinto

http://senhorpires.blogspot.com/