Comment #1  (Posted by an unknown user on 08/01/2006)
Rating
Plain, simple and to the point for novices :-)
 

Comment #2  (Posted by Chad on 08/01/2006)
Rating
Very helpful, thankyou for not telling to use a wizard!
 

Comment #3  (Posted by an unknown user on 08/17/2006)
Rating
Easily understood, a great base on which beginners can build their knowledge. Keep up the good work.
 

Comment #4  (Posted by an unknown user on 09/04/2006)
Rating
Good technique
 

Comment #5  (Posted by Soundcluster on 09/06/2006)
Rating
Hi all:

I was trying to follow this tutorial, and it all makes sense, however I have encountered some problems when I get to Creating the code to Add new data to the Contacts table: I've written the code in the editor, no wavey lines there, so I assume it's all ok, but when I execute the code I get a run time error and the cursor stops on the ExecuteNonQuery, precisely on line 55 command.ExecuteNonQuery().

Has anyone encountered this problem?
I'm working with MS VS .net 2005.

Many thanks
 

Comment #6  (Posted by David Jeavons on 09/06/2006)
Rating
Hi Soundcluster

I am not sure why you should be receiving an exception when executing the query. Can you change that section of code so that it includes an exception handler and then post back what the exception message is, this will then help to identify the problem.

To add the exception handler, change the code so that it resembles the following:

Try
command.ExecuteNonQuery()
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Note that the above should only replace the existing Command.ExecuteNonQuery statement on line 55, all other code should stay as is.

Thanks
 

Comment #7  (Posted by Soundcluster on 09/06/2006)
Rating
Hi Dave:

Thanks ever so much for your exceptionally prompt reply, I shall try your suggestion later on when I'm back home from work, and post the result thereafter (around 9/10 GMT)

Thanks
 

Comment #8  (Posted by Soundcluster on 09/06/2006)
Rating
Hello again:

I have substituted the code as suggested, and when running the program I get a message box saying:

"The field is too small to accept the amount of data you attempted to add.Trying inserting or pasting less data"

Which is really strange since I get the same message no matter whether I'm writing a single letter or my name or anything else....

Many thanks in advance


 

Comment #9  (Posted by David Jeavons on 09/06/2006)
Rating
Hi Soundcluster

That is indeed very strange as the field size for the Title, FirstName and Lastname fields are set to 15, 50 and 60 characters respectively.

If you have downloaded the database from the first part of this article and haven't modified the design in any way then I am at a loss to explain what may be causing this.

Out of curiosity, which version of Access are you using?

Thanks
 

Comment #10  (Posted by Soundcluster on 09/06/2006)
Rating
Thanks David:

I'm Using Office XP, Access 10.0.2616.0, I don't know if it's of any help but here are the details of the OleDb exception:

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217833
Message="The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Address_Book.ManageData.btnSave_Click(Object sender, EventArgs e) in C:\Documents and Settings\Authorised User\My Documents\Visual Studio 2005\Projects\Address Book\Address Book\ManageData.vb:line 33
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at System.Windows.Forms.Form.ShowDialog()
at Address_Book.Form1.btnAdd_Click(Object sender, EventArgs e) in C:\Documents and Settings\Authorised User\My Documents\Visual Studio 2005\Projects\Address Book\Address Book\Form1.vb:line 28
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Address_Book.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()


 

Comment #11  (Posted by Soundcluster on 09/07/2006)
Rating
Hello again:

The problem I was experiencing was due to the fact that when I first downloaded the database from this site, I've opened up the database to have a look at it using Access....
As I've later remembered I was prompted to either convert or continue using the existing database...
I did click the default option convert and that's what caused the problem...

I've deleted all the copies of the database in the project folder, placed in a fresh copy of the database and it all worked fine...so far so good...

Thanks
 

Comment #12  (Posted by an unknown user on 09/30/2006)
Rating
Thanks very much for such detailed explanation and not use of Wizard
That's great for us novices!...Thanks!
 

Comment #13  (Posted by an unknown user on 10/05/2006)
Rating
This helped me a lot and the explanation is great as well.
 

Comment #14  (Posted by an unknown user on 10/10/2006)
Rating
actually the article is very easy to follow
 

Comment #15  (Posted by an unknown user on 10/17/2006)
Rating
Very good. Was straight to the point and didnt have any unnecessary information that serves to confuse people.

Helped me alot with my current project
 

Comment #16  (Posted by an unknown user on 11/09/2006)
Rating
Very Clear and Straight Forward
 

Comment #17  (Posted by David Jeavons on 11/16/2006)
Rating
Hi Henk van Andel (in Netherlands)

I believe you posted a comment (not on site but selected Send to Author) which I have received. Unfortunately, the devCity.NET website does not notify me of the posters email address due to security reasons, so I have no way of replying to your message until I know what your email address is.

Hopefully you will see this message and if you do, could you please click on my name at the top of this message which is a standard mailto link and send me an email. Then I will be able to help you further.

Thanks
 

Comment #18  (Posted by Tim on 11/29/2006)
Rating
Duh, how do I create a new form within a project? I'm going through part two here on the first page and am trying to create the ManageData form. I can't seem to figure out how to "create a new form" within the project. I can create a new project, but new form? Can someone help an ignorant yet humble newbie?
Thanks, Tim
 

Comment #19  (Posted by David Jeavons on 11/29/2006)
Rating
Hi Tim

There are a couple of ways of adding a new form to your project. The first and most straight forward is to select the Project Menu and from the menu select "Add Windows Form" which will then display the "Add New Item" dialog with the Windows Form already highlighted. Then all you need to do is change the name of the form to one you wish to use such as "ManageData.vb".

The other way of adding a form is to go to your Solution Explorer (usually the right side of your IDE) and right click on your project name and select the "Add" sub menu and then "Windows Form" to get the same dialog.

Later, when you come to add different types of objects to your project (such as modules, classes etc.) you will go through the same process but instead of stating that you want a Windows Form you can select "Add New Item" and choose the item from the same dialog window.

Hope this helps.
 

Comment #20  (Posted by an unknown user on 12/06/2006)
Rating
Thats what i want
 

Comment #21  (Posted by an unknown user on 12/06/2006)
Rating
This article useful for all .net beginers......
 

Comment #22  (Posted by Bobby on 12/30/2006)
Rating
Iam new to vb.net programming and iam using VS 2005. Iam
getting an error when executing the ADD. The error is
"oledb is unhandled , Syntax error in INSERT INTO statement." and the error is pointing to command.ExecuteNonQuery().

My insert statement is
sql = "insert into contacts (Title,FirstName,LastName)" & "values(" '&txtTitle.Text&' ","'&txtFirstName.Text&' "," '&txtLastName.Text&' ")"

 

Comment #23  (Posted by chris on 01/03/2007)
Rating
to comment 22, the sql statement should look like this:

sql = "insert into contacts(title,firstname,lastname) values('" & txtTitle.Text & "','" & txtFirstName.Text & "','" & txtLastName.Text & "')"

 

Comment #24  (Posted by David Jeavons on 01/04/2007)
Rating
Hi Bobby

My apologies on the delay in replying to your question.

The problem with the SQL statement is that you have your single and double apostraphe's the wrong way around. Basically, each string value in your SQL statement has to be enclosed in a single quote (apostraphe) but the whole SQL statement has to be contained within a string. This is one of the areas I struggled with when I first started with SQL. Anyhow, your statement should be:

sql = "insert into contacts (Title,FirstName,LastName) values('" & txtTitle.Text & "','" & txtFirstName.Text & "', '" & txtLastName.Text & "')"

I hope this helps.
 

Comment #25  (Posted by an unknown user on 01/07/2007)
Rating
Excellent Work! Waiting for new articles.
 

Comment #26  (Posted by an unknown user on 01/22/2007)
Rating
Very Good explanation and simple to understand. Cheers

Ahmad Al-Mutawa
Riyadh, Saudi Arabia
ufm99@hotmail.com
 

Comment #27  (Posted by Steve K on 01/26/2007)
Rating
I am receiving an invalid statement line on the following:

editform.ContactID = CType(dgContacts.Item(dgContacts.CurrentRow.Index, 0), Int32)


The message is
"Value of type 'System.Windows.Forms.Datagridviewcell' cannot be converted to Integer

Any clues why?

Additionally I suppose some of the VB has changed since the publishing of this article. I am currently using VS 2005 and have noticed that I have had to make some minor modifications.
Such as

"dgContacts.CurrentRow.Index" replaces the statement "dgcontacts.currentrowindex"


Thanks for your help

 

Comment #28  (Posted by N0MAD on 01/28/2007)
Rating
Hi this is really a nice tutorial but i have encounterd a problem on this line: sql= "UPDATE Contacts SET Title='" & txtTitle.Text "',"& "FirstName='" &txtFirstName.Text&"',LastName='"&txtLastName.Text&"' WHERE ContactID="_contactID
i get a End of statement expected error message and i don't know how to solve this since i am just beginning
vb.net. I have vs2005. thnx
 

Comment #29  (Posted by David Jeavons on 01/28/2007)
Rating
Hi Steve

To read the value of a DataGridView cell you can use:

CType(dgContacts.CurrentRow.Cells(0).Value.ToString(), Int32)


HTH
 

Comment #30  (Posted by Anthony Potts on 02/10/2007)
Rating
N0MAD,

Your problem here is in the sql statement, try this:

sql= "UPDATE Contacts SET Title='" & txtTitle.Text & "', FirstName='" &txtFirstName.Text&"', LastName='"&txtLastName.Text&"' WHERE ContactID=" & _contactID

One way to debug this is to dial it down a bit and try a simple update such as:

sql= "UPDATE Contacts SET Title='Test Title', FirstName='Testy', LastName='McTesterson' WHERE ContactID= 1"

Then you start putting in the txtTitle.Text, txtFirstName.Text, txtLastName.text, and _contactID one by one. This is especially helpful when beginning because it takes out one thing that may go wrong. Eventually, this technique is no longer necessary.

Hope this helps.
 

Comment #31  (Posted by an unknown user on 02/15/2007)
Rating
It's easy for me a chinese to understand what u're talking about?
Thanks alot!
 

Comment #32  (Posted by Gracie on 02/15/2007)
Rating
I was able to complete the tutorial without problems. Would it be possible to recreate this tutorial using Excel as the database, I have been able to find the syntax for the add button and save but I am having trouble with edit and delete. I am brand new to programming I like using Visual Basic 2005.
 

Comment #33  (Posted by an unknown user on 02/22/2007)
Rating
This is a great article; it really lays out the foundation for database applications. Thanks!!
 

Comment #34  (Posted by an unknown user on 02/28/2007)
Rating
this is a good article for the beginners to leaarn about database handling
 

Comment #35  (Posted by an unknown user on 02/28/2007)
Rating
good
 

Comment #36  (Posted by an unknown user on 03/02/2007)
Rating
EXACTLY what I was looking for! Thanks so much for your time in creating this.
 

Comment #37  (Posted by lucashii on 03/26/2007)
Rating
Hello,

I am getting a saw line for

dgContacts.CurrentRowIndex

with error reading
"CurrentRowIndex" is not a member of "system.Windows.Form.DataGridView"

Thanks~
 

Comment #38  (Posted by David Jeavons on 03/27/2007)
Rating
Hi lucashii

Please take a look at comment 29 above which shows the code that you need to read the value from a DataGridView as opposed to a DataGrid. I tried to make the article accessible to both 2003 and 2005 users which is why the DataGrid was used but I still as yet have not got around to modifying the article to note this particular difference between the two controls.


Dave
 

Comment #39  (Posted by an unknown user on 03/28/2007)
Rating
the conversion to int32's do not work i can not find how to fix it other then that it was a good start at database access so if any one can tell me why that did not work i will be happy to bump up my rating
 

Comment #40  (Posted by David Jeavons on 03/29/2007)
Rating
Hi

When you say that the conversion to Int32 does not work, could you be more specific. What exception are you getting? Could you provide the bit of code that isn't working? The value in the DataGrid that you are trying to convert is an Int32 value isn't it?

Sorry to answer your question with questions, but it's is difficult to help without this information.


Dave
 

Comment #41  (Posted by Allan on 03/30/2007)
Rating
editForm.ContactID = CType(dgContacts.Item( dgContacts.CurrentRow.Index, 0), Int32).

This part it comes up with a blue line saying it can not be converted to integer
 

Comment #42  (Posted by Rudedog on 05/31/2007)
Rating
Newbie. Got same ExecuteNonQuery Error. Inserted code as per comment 6 above. "No value given for one or more required parameters." It sounds like it is trying to tell me that I am ignoring the fact that ExecuteNonQuery returns data. If so, how would I read it. Using Access 2000 (9.0.3821 SR-1) and VB Express 2005. XPpro w/SP2.
 

Comment #43  (Posted by David Jeavons on 05/31/2007)
Rating
Hi Rudedog

The exception you are receiving is indicative of an error in your SQL statement or that values are conflicting with the data types specified in the design of the table. Are you working with the sample database or are you trying to apply the examples here to a different database?

If you could post the chunk of code that is not working correctly then hopefully we can pinpoint the cause of the exception.

As for reading the value of the ExecuteNonQuery method, this method simply returns an Integer value indicating how much data was affected by the query. So if you want to read the value you assign it to a variable, for example:

Dim recordsAffected As Int32 = Command.ExecuteNonQuery(.......)


HTH
 

Comment #44  (Posted by Rudedog on 06/01/2007)
Rating
Thanks for the quick reply. Saw that posted earlier so I figured this was a good learning project. That it is. I am using the same database, with different textbox fieldnames. They seem to be the problem for some unknown reason. Same names with different prefixes

Here's my actual SQL command, which causes the exception.
sql = "INSERT INTO Contacts(Title, FirstName, LastName)" & " VALUES(" & textboxTitle.Text & "," & textboxFirstName.Text & "," & textboxLastNameField.Text & ")"

This works.
sql = "INSERT INTO Contacts(Title) VALUES('Mr')"

This does not.
sql = "INSERT INTO Contacts(Title) VALUES(textboxTitle.Text)"

When I hover the mouse over textboxTitle in SQL, it shows me how it is defined. I assume no spelling errors on TextBox names. If I leave all fields empty and click "Save" it gives me SQL syntax error. Thanks ahead of time.
 

Comment #45  (Posted by Rudedog on 06/01/2007)
Rating
FIXED IT!

sql = "INSERT INTO Contacts(Title) VALUES(" & "'" & TextBoxTitle.Text & "'" & ")"

I set a breakpoint just prior to the excepion and looked at the SQL strings. When the SQL string was built by simply typing text into VALUE(), the text data values had quotes round them. When it retrieved the text data using textbox.text, the text data was there with no quotes.
Thanks. Now I know how to use the debugger...some.
 

Comment #46  (Posted by David Jeavons on 06/01/2007)
Rating
Hi Rudedog

I'm pleased you resolved your issue. Without sounding patronizing it is great to see new developers learn how to use the debugger, it is an invaluable tool and one that I'm afraid a lot of new developers fail to grasp until later in their developer lives.

Another tip when debugging any errors with SQL syntax is to set a breakpoint on the line containing your SQL and step over it, then in the immediate window write out the value of your variable that holds the SQL syntax and copy and paste it into your database tool (Access or SQL Server). This then allows you to study the SQL in more detail and run it directly against the database. If any errors are found you usually find that the database description of the error is more detailed than the Data Provider's error which usually allows you to pinpoint the problem quicker.

This approach also works in reverse if you are using inline SQL statements as in this tutorial, in that you can create your SQL statement using your database tool and then copy and paste it into your code and substitue hardcoded values for variable/control values.


HTH
 

Comment #47  (Posted by an unknown user on 06/08/2007)
Rating
Besides a few hiccups with your syntax (in VB 2005, such as:
<>
this tutorial is excellent and very explanatory for the beginner. I congratulate you and thank-you for an insight to Database Programming in VB.Net that I haven't been able to get anywhere else!
 

Comment #48  (Posted by an unknown user on 06/14/2007)
Rating
bravo! great article, thanks for taking the time to teach here. i love this site.
 

Comment #49  (Posted by an unknown user on 06/25/2007)
Rating
I have always been using the wizards to build the ADO.NET puzzle for me, but after reading this, I know the ADO.NET pieces well enough to do it myself. David, good job! When are you going to do a tutorial on object persistence? ;)
 

Comment #50  (Posted by an unknown user on 07/03/2007)
Rating
Very clear and concise tutorial. Exactly what I was looking for. I learned more from the 2 articles in less time than from any other source.
 

Comment #51  (Posted by Resty on 08/10/2007)
Rating
Hi, David!
I am doing a project very much like this one, almost copied everything, but I'm getting an error when I try to save something in the line of "command.ExecuteNonQuery()". It says "Data type mismatch in the criteria expression."
I'm using VS 2005 & my database is in Access of Windows XP Prof.
Please advice on this. Cheers!
 

Comment #52  (Posted by David Jeavons on 08/10/2007)
Rating
Hi Resty

It sounds like you have something wrong with the SQL statement you are running (this error usually indicates that). Can you post your SQL statement.


Thanks
 

Comment #53  (Posted by Resty Bautista on 08/13/2007)
Rating
You are right; it's my sql statement where I try to put string values to the date & number fields. Below is the code:
sql = "INSERT INTO tblPatient(HospitalNumber, PatientLastName, PatientFirstName, DateofBirth, Age, WoundType)VALUES('" & txtHospitalNumber.Text & "', '" & txtPatientLastName.Text & "', '" & txtPatientFirstName.Text & "', #" & txtDateofBirth.Text & "#, " & txtAge.Text & ", '" & txtWoundType.Text & "')"

But I have another problem: Sometimes, the user will not put the date of birth or age or wound type & when I do that, it gives me a "syntax error".

How do I go about it? Please advise! Cheers!
 

Comment #54  (Posted by an unknown user on 10/01/2007)
Rating
Very good writer, you explain things
so clear and direct. Thanks
 

Comment #55  (Posted by TC on 10/01/2007)
Rating
I comment twice, because your article is very very
clean and straight to the point.Everything is
so clear and the explanation is superb !! Thanks a lot !
 

Comment #56  (Posted by chemboy on 10/03/2007)
Rating
I am having a problem I cannot solve!

The line on Form1, btnEdit_Click: editForm.ContactID = CType(dgContacts.Item(dgContacts.CurrentRow.Index, 0), Int32) gives an error: "Value of type 'System.Windows.Forms.DataGridViewCell' cannot be converted to 'Integer'."

Any ideas?
 

Comment #57  (Posted by David Jeavons on 10/03/2007)
Rating
Hi

The code in this article uses the older DataGrid to try to keep it applicable to both .NET 1.1 and .NET 2.0 so if you are using the DataGridView you will need to change this line of code to read:

CType(dgContacts.CurrentRow.Cells(0).Value.ToString(), Int32)


HTH
 

Comment #58  (Posted by chemboy on 10/03/2007)
Rating
Hi David,

Thanks for the prompt reply! That worked. When i run the program, and try to edit I get this error: OleDbException was unhandled. Syntax error (missing operator) in query expression 'LastNameFrom Contacts WHERE ContactID=2'.
The line that is highlighted is from retrieveContactDetails():
Dim contactReader As OleDb.OleDbDataReader = command.ExecuteReader()

Does this mean that once it read the third row, there is no data, so it threw an error? How do I handle this?

Thank you so much
 

Comment #59  (Posted by David Jeavons on 10/03/2007)
Rating
Hi

It would appear that your SQL statement is missing a space between the LastName column and the FROM keyword:

'LastNameFrom Contacts WHERE ContactID=2'

Should be

LastName From Contacts WHERE ContactID=2


HTH
 

Comment #60  (Posted by chemboy on 10/03/2007)
Rating
That was highlighted and copied from the error itself. Here is what is actually written:
Dim command As New OleDb.OleDbCommand("SELECT Title, FirstName, LastName" & "From Contacts WHERE ContactID=" & _contactID, conn)
 

Comment #61  (Posted by David Jeavons on 10/03/2007)
Rating
Hi

Sorry, I didn't make myself clearer. I understand thet it was copied from the error message but the error looks like it was created due to their not being a space between the field name and the FROM keyword. Having now looked at your code, you should simply need to add a space in front of the FROM keyword and hopefully it should work.


HTH
 

Comment #62  (Posted by chemboy on 10/03/2007)
Rating
DOH!!! That did it!
Thank you so much again.
 

Comment #63  (Posted by an unknown user on 10/04/2007)
Rating
Very good article, just what I needed , thanks

 

Comment #64  (Posted by vb beginner on 10/23/2007)
Rating
I am using visual basic 2005 and have successfully tried your save function. I am having difficulties with the delete function.
I receive this error message evry time I try to delete a row from my drview:

"INVALID CAST EXCEPTION WAS UNHANDLED. CONVERSION FROM STRING "SANDRA" TO TYPE "INTEGER" IS NOT VALID.

i AM USING MY OWN DATABASE AND have (three) columns in my datagrdiview: "first name, last name, middle name"


this is my current code:
If dgvLastNames.CurrentRow.Index > -1 Then
Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.mdb;Jet OLEDB:Database Password=test")
Dim SQL As String = String.Empty
SQL = "DELETE from Clients where ClientID=" _
& CType(dgvLastNames.CurrentRow.Cells(0).Value.ToString(), Int32)
Conn.Open()
Dim command As New OleDbCommand(SQL, Conn)
command.ExecuteNonQuery()
Conn.Close()
retrievecontacts()
End If

This is the code that populates the datagridview:
Private Sub Populate_Information2(ByVal ClientLastName As String)

dgvLastNames.Columns.Clear()
OpenDB()
ClientLastName = "'%" & ClientLastName & "%'"
Dim Sql As String = "SELECT ClientFirstName, ClientMiddleName, ClientLastName FROM Clients"
Dim cmd As New OleDbCommand(Sql, Conn)
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
da.SelectCommand = cmd
Try
da.Fill(ds, "Data")
dt = ds.Tables("Data")
dgvLastNames.AutoGenerateColumns = False
Dim x As Integer
For x = 0 To dt.Columns.Count - 1
Dim dgc As New DataGridViewTextBoxColumn
With dgc
.Name = dt.Columns(x).ColumnName.ToString
.DataPropertyName = dt.Columns(x).ColumnName.ToString
.HeaderText = dt.Columns(x).ColumnName.ToString
.Width = 100
End With
dgvLastNames.Columns.Add(dgc)
Next
dgvLastNames.DataSource = dt
If x <= 0 Then
MessageBox.Show("No Record Found!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Catch ex As Exception
MsgBox("Error while executing: (" & cmd.CommandText & ")" & vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
Finally
da = Nothing
ds = Nothing
dt = Nothing
cmd.Dispose()
CloseDB()
End Try
End Sub

can you please suggest a resolution, it is definitely has something to do with the sql statement and the fact the I select a row with last name but my deletion code tries to delete the id of the client.
 

Comment #65  (Posted by David Jeavons on 10/23/2007)
Rating
Hi

Looking at the exception message and the code that you are using you are basically assigning the value of the FirstName column (from the Grid) to the ClientID within the WHERE Clause of your delete statement which is a string.

If your ClientID is a string value then you will need to enclose the value within single quotes. Also, if your ClientID does not contain values such as 'SANDRA' then you will need to grab the correct ClientID to perform the delete operation.


HTH
 

Comment #66  (Posted by vbbeginner on 10/24/2007)
Rating
Thank you very much for replying,
I still cannot get it to work after enclosing the "clientlastname" in single quotes.
when I put this dlete statement in,
"SQL = "DELETE from Clients", it successfully deletes all the clients, I do not understand still how I can get a particular client to be deleted.
thank you, can you please give a code example of how I can achieve that.
 

Comment #67  (Posted by David Jeavons on 10/24/2007)
Rating
Hi

Is your ClientID a string value? If it is and it is also the first field in the Grid then something like the following should work:

"DELETE FROM Clients WHERE ClientID='" & dgvLastNames.CurrentRow.Cells(0).Value.ToString() & "'"

Basically, the format of the DELETE statement needs to look like:

DELETE FROM Table WHERE FieldName = 'Value'

Assuming it is a string type that makes up the key for deleting. If it is a numeric type then simply omit the single quotes.


HTH
 

Comment #68  (Posted by vbbeginner on 10/24/2007)
Rating
David,

I apologize for not giving the whole picture.
I have only 3 columns in my dgview: last name, first name, and middle name.

it gives me an error that "data type mismatch in criteria expression".
so I do have ClientId declared as an integer. What should I do?
and ClientId is a long integer within the access 2007 database.

This is the code that I use to populate the datagridview by using a disconnected dataset:
Private Sub Populate_Information2(ByVal ClientLastName As String)

dgvLastNames.Columns.Clear()
OpenDB()
ClientLastName = "'%" & ClientLastName & "%'"
Dim Sql As String = "SELECT ClientFirstName, ClientMiddleName, ClientLastName FROM Clients"
Dim cmd As New OleDbCommand(Sql, Conn)
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
da.SelectCommand = cmd
Try
da.Fill(ds, "Data")
dt = ds.Tables("Data")
dgvLastNames.AutoGenerateColumns = False
Dim x As Integer
For x = 0 To dt.Columns.Count - 1
Dim dgc As New DataGridViewTextBoxColumn
With dgc
.Name = dt.Columns(x).ColumnName.ToString
.DataPropertyName = dt.Columns(x).ColumnName.ToString
.HeaderText = dt.Columns(x).ColumnName.ToString
.Width = 100
End With
dgvLastNames.Columns.Add(dgc)
Next
dgvLastNames.DataSource = dt
If x <= 0 Then
MessageBox.Show("No Record Found!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Catch ex As Exception
MsgBox("Error while executing: (" & cmd.CommandText & ")" & vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
Finally
da = Nothing
ds = Nothing
dt = Nothing
cmd.Dispose()
CloseDB()
End Try
End Sub

and I use your codes also:
Public Property ClientID() As Int32
Get
Return _ClientID
End Get
Set(ByVal value As Int32)
_ClientID = value
End Set
End Property

and
If dgvLastNames.CurrentRow.Index > -1 Then
Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\Admin\Desktop\Fixed\Fixed\UpdatedJune92007AnotherOne\AnotherOne\AnotherOne\AnotherOne\test.mdb;Jet OLEDB:Database Password=test")

Dim SQL As String = String.Empty
SQL = "DELETE FROM Clients WHERE ClientID='" & dgvLastNames.CurrentRow.Cells(0).Value.ToString() & "'"
'SQL = "DELETE from Clients"
Conn.Open()
Dim command As New OleDbCommand(SQL, Conn)
command.ExecuteNonQuery()
Conn.Close()
MessageBox.Show("Client Has been deleted!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)


retrievecontacts()
 

Comment #69  (Posted by David Jeavons on 10/24/2007)
Rating
Hi

The problem is that the value of dgvLastNames.CurrentRow.Cells(0).Value.ToString() will be the value of the ClientFirstName field but your ClientID field is a numeric field.

What you need to do is store the ClientID somewhere. Can you not display it in the Grid as the first column and change your Delete statement too:

DELETE FROM Clients WHERE ClientID=" & Convert.ToInt32(dgvLastNames.CurrentRow.Cells(0).Value)


HTH
 

Comment #70  (Posted by vbbeginner on 10/24/2007)
Rating
thanks david,
I did change the coding and it does delete, BUT I CANNOT HAVE THE ID'S listed next to clients. Is there another way to delete WITHOUT dispayng the id's.
please help
 

Comment #71  (Posted by David Jeavons on 10/24/2007)
Rating
Hi

Assuming this is a Windows Forms application and not an ASP.NET application then what you could do is output the ClientID to the Grid but then hide the column by setting it's Visible property to False. This will still allow you to access the value but the user will not see the column.


HTH
 

Comment #72  (Posted by vbbeginner on 10/25/2007)
Rating
Hi,
after I made the ClientID invisible, I receive this error: "format exception was unhandled" Input string was not in a correct format"
Please any suggestions.
 

Comment #73  (Posted by David Jeavons on 10/29/2007)
Rating
Hi

Have you tried referring to the column by name rather than using zero as it may be that by using zero you are still referring to the first visible column but if you explicitly refer to it by name then there should be no confusion.


HTH
 

Comment #74  (Posted by vbbeginner on 10/31/2007)
Rating
Hi,
can you please give me an example of setting the column name to 0? I am not sure I understand.
thank you
 

Comment #75  (Posted by David Jeavons on 11/01/2007)
Rating
Hi

To name a column you can do something similar too:

DataGridView1.Columns(0).Name = "ClientID"

Then when you want to refer to the column you can use:

MessageBox.Show(DataGridView1.CurrentRow.Cells("ClientID").Value.ToString)


HTH
 

Comment #76  (Posted by vbbeginner on 11/07/2007)
Rating
David,
I tried everything that you suggested in regards to deleting the record form the datagridview and it does not work.
I will leave that task for later on because I am having a problem with Updating the record. My saving works but then when I want to save after altering the record, it just creates another record.
This is my code which looks flawless to me:
Private Sub MenuItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click
Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.mdb;Jet OLEDB:Database Password=test")

Dim SQL As String = String.Empty
If _ClientId = 0 Then
'add a new record
SQL = "insert into Clients(ClientFirstName, ClientLastName, ClientMiddleName, ClientDateofBirth, ClientStreetAddress, ClientTelephoneNumberOne, ClientDateSeen, ClientComments, Reports, ClientEmail, ClientCompany, ClientCity, ClientState, ClientPostalCode, ClientContry,ClientTelephoneNumberTwo,)" & "values('" & txtFirst.Text & "', '" & txtLast.Text & "', '" & txtMiddle.Text & "', '" & txtDate.Text & "', '" & txtAddress.Text & "', '" & txtPhone.Text & "', '" & txtDateSeen.Text & "', '" & txtComments.Text & "','" & txtReports.Text & "', '" & txtEmail.Text & "', '" & txtCompany.Text & "', '" & txtCity.Text & "', '" & txtState.Text & "', '" & txtPostal.Text & "', '" & txtCountry.Text & "', '" & txtPhone2.Text & "', '" & txtWeight.Text & "', '" & txtFeet.Text & "', '" & txtInches.Text & "')"
Dim LastName_Selected As String = txtLast.Text
Dim firstname_selected As String = txtFirst.Text
Dim middlename_selected As String = txtMiddle.Text

MsgBox("You have successfully saved " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")

Else
SQL = "update Clients set ClientFirstName='" & txtFirst.Text & "', ClientLastName= '" & txtLast.Text & " ', ClientMiddleName='" & txtMiddle.Text & "', ClientDateofBirth='" & txtDate.Text & "', ClientStreetAddress='" & txtAddress.Text & "', ClientTelephoneNumberOne='" & txtPhone.Text & "', ClientDateSeen='" & txtDateSeen.Text & "', ClientComments='" & txtComments.Text & "', Reports='" & txtReports.Text & "',ClientEmail='" & txtEmail.Text & "',ClientCompany='" & txtCompany.Text & "', ClientCity='" & txtCity.Text & "',ClientState='" & txtState.Text & "',ClientPostalCode='" & txtPostal.Text & "',ClientContry='" & txtCountry.Text & "',ClientTelephoneNumberTwo='" & txtPhone2.Text & "' where ClientId=" & _ClientID

Dim LastName_Selected As String = txtLast.Text
Dim firstname_selected As String = txtFirst.Text
Dim middlename_selected As String = txtMiddle.Text
MsgBox("You have successfully updated " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Updated")
End If
Conn.Open()
Dim command As New OleDbCommand(SQL, Conn)
command.ExecuteNonQuery()
Conn.Close()

End Sub

 

Comment #77  (Posted by David Jeavons on 11/08/2007)
Rating
Hi

I haven't looked at the SQL statements too closely as I assume they are working for you. What I am curious about however is if you are ever updating your ClientID variable once you have added a new record. If I understand rightly, you will add a record with no problems but then as soon as you try to edit that record it is adding the record again. This would suggest to me that the ClientID variable is not being updated and so therefore the code is always processing the Insert statement.

Have you tried setting a breakpoint on this code and following the flow to determine what logic is fired etc.?
 

Comment #78  (Posted by vbbeginner on 11/09/2007)
Rating
add/update/delete works,
how do I set some of the columns within the datagrid to be Invisible, such as "clientId"?
 

Comment #79  (Posted by an unknown user on 01/15/2008)
Rating
Thank you for this wonderful tutorial.
 

Comment #80  (Posted by an unknown user on 02/17/2008)
Rating
This is Great Tutorial! Simple, Understandable. Thanks!!! more tutorial pls
 

Comment #81  (Posted by an unknown user on 03/17/2008)
Rating
understans easily & short & sweet
 

Comment #82  (Posted by David Jeavons on 04/14/2008)
Rating
Hi Alan

You submitted a comment (thank you) and asked a question, but unfortunately, as you didn't post it on site also, I have no way of knowing what your email address is.

Therefore, if you do see this comment, then please e-mail me directly rather than via devCity and I will help you out. I am currently away from home at the moment so may not get back to you straight away.

Alternatively, try with the following principle and if that doesn't help then mail me.

Using the DataReader approach that you learnt in this chapter, execute an SQL Select statement for your JobID and assign it to the DataReader and then something along the lines of the following should suffice:

Do While yourDataReader.Read
yourTextBox.Text = yourDataReader("FieldName").ToString
Loop

Assuming that your JobID field is unique, the above loop will only occur once if a match was found.


HTH
 

Comment #83  (Posted by Dharmit Thakore on 06/11/2008)
Rating
Hi,
This is excellent resource for begineers.
I am using the update statement and am getting error. The sql statement is as follows

SqlText = "UPDATE Emp SET Initials = '" & Me.txtInitials.Text & _
"', Group = '" & Me.cboGroup.Text & _
"', Designation = '" & Me.cboDesignation.Text & _
"', Status = '" & Me.cboStatus.Text & _
"' WHERE Name = '" & Me.cboEmpName.Text & "'"

I am trying to find the error since long now and am not able to get the answer.

the following sql query (which is for another update) works perfectly

SqlText = "UPDATE Project SET PM = '" & Me.cboPM.Text & _
"', Client_PM = '" & Me.txtClientPM.Text & _
"', Status = '" & Me.cboStatus.Text & _
"' WHERE Prj_No = '" & Me.txtPrjNo.Text & _
"' AND Prj_Name = '" & Me.cboPrjName.Text & _
"' AND Client = '" & Me.txtClient.Text & "'"

Any help would be appreciated

If you would like to answer me via email, I have added it too.
 

Comment #84  (Posted by Jerry on 07/06/2008)
Rating
Excellent+ tutorial. Best straighforward example with good explanations not making too many assumptions about prior knowledge.
I am using VB in VS 2003. Everything has worked fine up to now but when I run F5 it breaks and reports 'Nothing' at the following line -
Dim contactReader As OleDbDataReader = command.ExecuteReader
I cannot find out why. I have checked the syntax without success.
What have I done wrong?
 

Comment #85  (Posted by Wind on 08/12/2008)
Rating
Hi,

Your tutorial is really well done. Its totally fit for the level I am in currently.

But I got 1 question, what if I don't use datagrid and just wanna use textbox to show data?

Especially when I want to find specific data like when I entered the first name to search and the data will be shown in the textbox.

Thanks for answering
 

Comment #86  (Posted by David Jeavons on 08/14/2008)
Rating
Hi Wind

If you take a look through this part of the tutorial, you will come by a section where we create a manage form to show and modify three fields in the database. This form utilises a method called RetrieveContactDetails which retrieves a single row of data and uses an OleDbDataReader to populate three text boxes with the data retrieved.

Hopefully this part of the tutorial will answer your question. If you need any further help, just shout.


Thanks
 

Comment #87  (Posted by Wind on 08/17/2008)
Rating
Hi David,

Thanks for telling me that. I have tried and its worked. Thanks alot.
 

Comment #88  (Posted by Wind on 08/26/2008)
Rating
Hi,

I have encountered another problem. I published the application, installed and runs it, its said the Access can't be found. I must place the file at the specific location the system said then able to run the programme.

Is there anyway to solve this problem? As in when I publish the application and install it, then I no need to copy the access file to the destinated location?
 

Comment #89  (Posted by David Jeavons on 08/26/2008)
Rating
Hi Wind

As long as you output the Access database file in the same location as the executable file then everything should be fine.


HTH
 

Comment #90  (Posted by Wind on 08/26/2008)
Rating
As in? Can give an example?
Sorry for the trouble.
 

Comment #91  (Posted by David Jeavons on 08/27/2008)
Rating
Hi Wind

It's been a while since I have created any installation applications so my mind is a bit foggy in this area.

However, From what I remember, there is a target path where your executable file will be outputted to. When you add your database file to the installation package, you specify that it should be deployed to the same target path so that both executable and database file reside in the same path.

As long as you are then using the Application.StartupPath command to locate your database file, it should run fine.


HTH
 

Comment #92  (Posted by an unknown user on 09/24/2008)
Rating
Excellent tutorial. Thank you! GM
 

Comment #93  (Posted by an unknown user on 10/20/2008)
Rating
Very well explained for beginners like me. Is what I was looking for since some time ago.
 

Comment #94  (Posted by Fan on 12/23/2008)
Rating
Hi David,

Hey, sorry for such late reply.

How do you add the database into the installation package?
 

Comment #95  (Posted by zibex on 01/21/2009)
Rating
xg5gYd hi! nice site!
 

Comment #96  (Posted by Ricky on 02/23/2009)
Rating
Im confused, iv done the code exactly as you said for adding the contacts but when i run the program i get an OleDbException saying 'Could not find installable ISAM' what can i do? as this code could really help with my current computing project
 

Comment #97  (Posted by Ricky on 02/23/2009)
Rating
No matter, its sorted now, great tutorial btw =]
 

Comment #98  (Posted by Ricky on 02/24/2009)
Rating
When i try to add a new person to the database it gives an exception in 'command.ExecuteNonQuery()' saying "No value given for one or more required parameters."

whats wrong with it?
 

Comment #99  (Posted by an unknown user on 03/03/2009)
Rating
good explanations, although most of the code brings up errors
 

Comment #100  (Posted by on 10/11/2009)
Rating

 

Comment #101  (Posted by Tramadol is marketed as a racemic mixture with on 11/09/2009)
Rating
competitor plavix competitor plavix competitor plavix . competitor plavix competitor plavix . competitor plavix competitor plavix . competitor plavix recreational carisoprodol use recreational carisoprodol use . recreational carisoprodol use recreational carisoprodol use . recreational carisoprodol use recreational carisoprodol use . recreational carisoprodol use recreational carisoprodol use recreational carisoprodol use . august book guest info order site ambien august book guest info order site ambien august book guest info order site ambien august book guest info order site ambien august book guest info order site ambien . august book guest info order site ambien august book guest info order site ambien august book guest info order site ambien august book guest info order site ambien august book guest info order site ambien .
 

Comment #102  (Posted by is the principal agonist at ?-opioid receptors, on 11/25/2009)
Rating
hi, you have super site.: URLsWithHREF
 

Comment #103  (Posted by albeit not nearly as intense. on 11/27/2009)
Rating
Keep up this great resource: URLsWithURL
 

Comment #104  (Posted by Polh on 11/28/2009)
Rating
archy it i, and i like you!: URLsWithURL
 

Comment #105  (Posted by cheap oem software on 02/10/2012)
Rating
hOWJwC I serched through the internet and got here. What a wonderful invention of the mankind. With the help of the network you communicate, learn, read !... That helped us to get acquainted!....
 

Comment #106  (Posted by oem software on 02/10/2012)
Rating
tGKH4e This article is for professionals..!!