I'm fairly new to vb.net so any input is appreciated. I'm getting errors when I try to update the database using the following code: -
sql = "SELECT * FROM Employees WHERE Employee = '" & lblUser.Text & "'"
-
Global.SetupDs(ds1)
-
-
If txtOldPW.Text <> ds1.Tables(0).Rows(0).Item("Password") Then
-
MsgBox("Old password does not match employee records.")
-
txtOldPW.Text = ""
-
txtNewPW.Text = ""
-
txtConfirmNewPW.Text = ""
-
Exit Sub
-
End If
-
-
ds1.Tables(0).Rows(0).Item("Password") = txtNewPW.Text
-
da.Update(ds1)
-
ds1.Clear()
-
MsgBox("Password Updated.")
-
SetupDs is a sub in my global module that uses the SQL string I provide and a connection I've setup to retrieve the data I want to put in my dataset. I've also publicly declared a commandbuilder in the global module: -
Public da As OleDb.OleDbDataAdapter
-
Public con As New OleDb.OleDbConnection
-
Public sql As String
-
Public cb As New OleDb.OleDbCommandBuilder(da)
-
I'm getting the following when I try to update:
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
Any help would be great... I haven't gotten a resposne from anywhere else I've tried :-/
8 2445
After you instantiate your data adapter [after you say da = new OleDbDataAdapter(selectCommand,connection)] you should include these lines. -
da.UpdateCommand = cb.GetUpdateCommand()
-
da.InsertCommand = cb.GetInsertCommand()
-
da.DeleteCommand = cb.GetDeleteCommand()
-
You made the command builder, but didn't use it to build the commands.
Thanks for the reply. I inserted the lines you mentioned, but now I'm getting errors about initializing the select command:
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: The DataAdapter.SelectCommand property needs to be initialized."
I was hoping it'd be as easy as the other 3, (i.e. "da.selectcommand = cb.getselectcommand()") but there was no getselectcommand(), under cb. I'm guessing this is because select has to do with retrieving data instead of updating it?
Thanks for the reply. I inserted the lines you mentioned, but now I'm getting errors about initializing the select command:
"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: The DataAdapter.SelectCommand property needs to be initialized."
I was hoping it'd be as easy as the other 3, (i.e. "da.selectcommand = cb.getselectcommand()") but there was no getselectcommand(), under cb. I'm guessing this is because select has to do with retrieving data instead of updating it?
See, you already wrote the select command. It's in the variable "sql"
You need to understand the process here. You are getting things out of order. Here's what I usually do: (check connectionstrings.com for your connection string; make sure to add the "Imports" statement at the top of your code page.) -
Imports System.Data.OleDb
-
.
-
.
-
.
-
'this connstr for MS SQL 2005, find correct one
-
connStr = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
-
Dim con As New OleDbConnection(connStr)
-
Dim sql As String
-
sql = "SELECT * FROM Employees WHERE Employee = '" & lblUser.Text & "'"
-
Dim da As New OleDbDataAdapter(sql, con)
-
Dim cb As New OleDbCommandBuilder(da)
-
If you do things in this order, you should be able to do what you need to do.
Thanks for your continued help and patience, as I mentioned I'm new to .net so some of the basic concepts are a bit puzzling for me. I changed my code so that the connection, dataAdapter, and command builder come in the order you mentioned, but I'm still getting errors on the "daEmployee.update(dsEmployee)" line. -
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
-
Dim MainForm As New Main
-
Dim con As New OleDb.OleDbConnection
-
con.ConnectionString = "PROVIDER = Microsoft.Jet.Oledb.4.0;Data Source = C:\Documents and Settings\CZ\My Documents\Databases\QSD_be.mdb"
-
Dim sqlEmployee As String
-
sqlEmployee = "SELECT * FROM Employees WHERE Employee = '" & lblUser.Text & "'"
-
Dim daEmployee As New OleDb.OleDbDataAdapter(sqlEmployee, con)
-
Dim dsEmployee As New DataSet
-
Dim cb As New OleDb.OleDbCommandBuilder(daEmployee)
-
-
'fill datasets
-
con.Open()
-
daEmployee.Fill(dsEmployee)
-
con.Close()
-
-
'setup da commands
-
daEmployee.UpdateCommand = cb.GetUpdateCommand()
-
daEmployee.InsertCommand = cb.GetInsertCommand()
-
daEmployee.DeleteCommand = cb.GetDeleteCommand()
-
-
If Not Len(txtOldPW.Text) > 0 Then
-
MsgBox("Please enter old password.")
-
Exit Sub
-
End If
-
-
If Not Len(txtNewPW.Text) > 0 Then
-
MsgBox("Please enter new password.")
-
Exit Sub
-
End If
-
-
If Not Len(txtConfirmNewPW.Text) > 0 Then
-
MsgBox("Please confirm new password.")
-
Exit Sub
-
End If
-
-
If txtNewPW.Text <> txtConfirmNewPW.Text Then
-
MsgBox("New password does not match confirm new password.")
-
txtNewPW.Text = ""
-
txtConfirmNewPW.Text = ""
-
Exit Sub
-
End If
-
-
If Len(txtNewPW.Text) < 5 Or Len(txtNewPW.Text) > 10 Then
-
MsgBox("New password must be between 5 and 10 characters")
-
txtNewPW.Text = ""
-
txtConfirmNewPW.Text = ""
-
Exit Sub
-
End If
-
-
If txtOldPW.Text <> dsEmployee.Tables(0).Rows(0).Item("Password") Then
-
MsgBox("Old password does not match employee records.")
-
txtOldPW.Text = ""
-
txtNewPW.Text = ""
-
txtConfirmNewPW.Text = ""
-
Exit Sub
-
End If
-
-
dsEmployee.Tables(0).Rows(0).Item("Password") = txtNewPW.Text
-
-
daEmployee.Update(dsEmployee)
-
MsgBox("Password Updated.")
-
-
MainForm.Show()
-
Me.Close()
-
dsEmployee.Clear()
-
End Sub
-
As you can see I moved everything local so i can avoid confusion when dealing with public variables.
The error is "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" with no additional data.
I'm sorry, tonight I don't have time to answer this. But don't worry, tomorrow I'll help you out.
Don't think that I'm ignoring you. :)
No problem, I'll take help whenever I can get it :)
Ok, you're going about this in an inefficient way. The method you're using is better for updating multiple records and fields at once. Also, you seem to be doing some things out of order. So I put together a bit of a tutorial for you. This is the way I would do what you are doing. (Ok, so if I were doing it, I'd do it in C#, but this is the VB.NET translation version :D )
Remember to put the "Imports" statmement as the first line of the code page. -
Imports System.Data.OleDb
-
.
-
.
-
.
-
Dim sqlSelect, sqlUpdate, connStr As String
-
'set up select command
-
sqlSelect = String.Format("select Password from employees where Employee = '{0}'", lblUser.Text)
-
'set up connection string
-
connStr = "PROVIDER=Microsoft.Jet.Oledb.4.0;Data Source = C:\Documents and Settings\CZ\My Documents\Databases\QSD_be.mdb"
-
'set up connection object
-
Dim conn As New OleDbConnection(connStr)
-
'set up command object
-
Dim cmd As New OleDbCommand(sqlSelect, conn)
-
'set up string to hold existing password and new password
-
Dim oldPass, newPass As String
-
-
'try to open the conn and execute the command
-
Try
-
conn.Open()
-
oldPass = Convert.ToString(cmd.ExecuteScalar())
-
conn.Close()
-
Catch ex As Exception
-
MessageBox.Show("SQL Error")
-
Finally 'if there were errors, make sure that conn is closed
-
If (conn.State <> ConnectionState.Closed) Then
-
conn.Close()
-
End If
-
End Try
-
-
'do all your logic to see if you need a new password
-
'...
-
'...
-
'set up update command
-
sqlUpdate = String.Format("update Employees set Password = '{0}' where Employee = '{1}'", newPass, lblUser.Text)
-
'add the update string to the command object
-
cmd.CommandText = sqlUpdate
-
'try to execute the command
-
Try
-
conn.Open()
-
oldPass = Convert.ToString(cmd.ExecuteScalar())
-
conn.Close()
-
Catch ex As Exception
-
MessageBox.Show("SQL Error")
-
Finally 'if there were errors, make sure that conn is closed
-
If (conn.State <> ConnectionState.Closed) Then
-
conn.Close()
-
End If
-
End Try
-
If you have any questions about what is going on here, just reply and ask. I'll be happy to explain it.
P.S. Sorry it took me so long to get back to you, I've been working really hard.
Thanks for the response. This is the first project where I've tried to split the database up and manipulate the data from the front end. I'm used to having the data in one file, so this whole business of connections and commands and such is a bit new to me.
I'm still struggling with the update command. I followed your advice and wrote sqlcommands for the select and update, but I'm getting the same error on the updatecmd.executescalar(). -
Dim selectSQL, updateSQL As String
-
selectSQL = "SELECT Password FROM Employees WHERE Employee = '" & lblUser.Text & "';"
-
updateSQL = "UPDATE Employees SET Password = '" & txtNewPW1.Text & "' WHERE Employee = '" & lblUser.Text & "';"
-
Dim selectCmd As New OleDbCommand(selectSQL, con)
-
Dim updateCmd As New OleDbCommand(updateSQL, con)
-
Dim oldPass As String
-
-
'bunch of code where I check string lengths etc
-
-
'Try
-
con.Open()
-
oldPass = selectCmd.ExecuteScalar()
-
If txtPassword.Text <> oldPass Then
-
MsgBox("Password provided does not match current employee password.")
-
txtPassword.Text = ""
-
txtNewPW1.Text = ""
-
txtNewPW2.Text = ""
-
Exit Sub
-
End If
-
-
updateCmd.ExecuteScalar()
-
-
'Catch ex As Exception
-
'MsgBox("Error in password update process")
-
'Exit Sub
-
'Finally
-
If con.State <> ConnectionState.Closed Then
-
con.Close()
-
End If
-
'End Try
-
-
MsgBox("Password Updated.")
-
I commented out the try/catch to see what the exact error was, its the same one I had earlier
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Test Person |
last post by:
Hi
I'm having trouble with updating my database which is SqlServer. I'm using
the update command
and only the dataset is being changed not the actual row in the database.
Best Regards
|
by: Foxster |
last post by:
in access i have two tables (tblplayer, tblpoints)
in tblplayer i have field totalpoint (number)
in tlbpoints i have field pointgame (number)
the two files are related via idplayer
now i made a...
|
by: Mark Kolber |
last post by:
I just migrated to Office 2003. When I opened an important Access
database, Access 2003 promptly destroyed it.
Unfortunately, there was a glitch in my backup routine and two
folders, including...
|
by: Commander Ace |
last post by:
Hi, I'm having some problems with updating an SQL Database with modified
fields from a datatable. I got the code from a Wrox book, 'Professional
VB.NET 2003', the same code can be found here:...
|
by: Greg |
last post by:
I'm using the Framework 1.1, so I don't have access to the new
DataGridView object.
What I'm wondering is, is there a really simple way to bind a plain
datagrid to a database in such a way that...
|
by: Dot Net Daddy |
last post by:
Hello,
I have some textareas assigned some values which is taken from the
database on page_load. Also there is an update button.
But when I change the text and click the update button it does...
|
by: bpluo |
last post by:
Hi,
I have a specific problem.
`How can one programmtically (using Visual basic, either in access or in .net) replace some tables of a 2003 Access database by the tables in an another 2003...
|
by: joel |
last post by:
hi guys...
i am a student, i am studying and i am a newbie in VB.net and i am
using SQL Server 2000.
I have this datatable which i insert rows (may take up to 2 or more
rows because i would...
|
by: abdulhadi |
last post by:
I have a table contains the results of matches
and table contains the statistics
how could I fill the statistics table by getting information from the result table
the statistics table columns...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |