• Welcome to Overclockers Forums! Join us to reply in threads, receive reduced ads, and to customize your site experience!

Need Coding Help (Visual Studio 2010 - Access 2013)

Overclockers is supported by our readers. When you click a link to make a purchase, we may earn a commission. Learn More.

darkblitz05

Member
Joined
Feb 18, 2013
Location
Philippines
Guys I have this program that I want to use for my small library room
and Im using Visual Studio 2010
and the DB is Access 2013

just need to have a little help on Adding records, Delete, and Update
 
Last edited:
A little google search that directed me to the Microsoft website weilding this page.
https://msdn.microsoft.com/en-us/library/ms233812.aspx

It should be helpful in guiding you in how to access the access database using VB.net

I am not going to write your code for you, and with where you are currently this is the most i know how to help.
Once you try to write the code and have questions with what might be going wrong I can help more.
 
If you have a specific question, feel free to post a snippet of the code and whatever relevant error you're receiving, otherwise you're not going to get much help. Use the MSDN link above and look for how to connect to an Access database. Once you've connected successfully, look up how to retrieve rows, then update, insert, delete, search, etc. The best way to approach a problem like this is to break it into small, manageable chunks and try to solve them one at a time.

For what it's worth, if you don't have much (or any) programming experience, a quicker solution might be to make use of forms and reports in Access, you can most likely accomplish the same functionality you're after. That being said, Access sucks so the less dependent you are on it, the better. :)
 
guys i have this code already its a function save record but in the function also is the update
i am having troubles with it, although there are no errors during debug but the updated record
didnt go to the right column or row...


Private Sub Save_Record()
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim sSQL As String = String.Empty


Try
'get connection string declared in the Module1.vb and assing it to conn variable
conn = New OleDbConnection(Get_Constring)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
'I just use the textbox tag property to idetify if the data is new or existing.
If Me.BooknameTextBox.Tag = 0 Then
sSQL = "INSERT INTO libinventory (bookname, author, date_published, category, location)"
sSQL = sSQL & " VALUES(@bookname, @author, @date_published, @category, @location)"
cmd.CommandText = sSQL
Else
sSQL = "UPDATE libinventory set bookname=@bookname where ID=" & Me.BooknameTextBox.Tag
'sSQL = "UPDATE libinventory set bookname = @bookname, author = @author, date_published = @date_published, category = @category, location = @location where ID=" & Me.BooknameTextBox.Tag
cmd.CommandText = sSQL
End If


cmd.Parameters.Add("@bookname", OleDbType.VarChar).Value = IIf(Len(Trim(Me.BooknameTextBox.Text)) > 0, Me.BooknameTextBox.Text, DBNull.Value)
cmd.Parameters.Add("@author", OleDbType.VarChar).Value = IIf(Len(Trim(Me.AuthorTextBox.Text)) > 0, Me.AuthorTextBox.Text, DBNull.Value)
If Me.Date_publishedDateTimePicker.Text = " " Then
MsgBox("Input Date pls")
End If
cmd.Parameters.Add("@date_published", OleDbType.Date).Value = IIf(Me.Date_publishedDateTimePicker.Text <> " ", Me.Date_publishedDateTimePicker.Value, DBNull.Value)
cmd.Parameters.Add("@date_published", OleDbType.Date).Value = Me.Date_publishedDateTimePicker.Text

'set paramaters
cmd.Parameters.Add("@category", OleDbType.VarChar).Value = IIf(Len(Trim(Me.CategoryTextBox.Text)) > 0, Me.CategoryTextBox.Text, DBNull.Value)
cmd.Parameters.Add("@location", OleDbType.VarChar).Value = IIf(Len(Trim(Me.LocationTextBox.Text)) > 0, Me.LocationTextBox.Text, DBNull.Value)
cmd.Parameters.Add("@Id", OleDbType.Numeric).Value = Me.BooknameTextBox.Tag
cmd.ExecuteNonQuery()

'If the record is new then we have to get its ID so that we can edit it rightaway after the insertion.
If Me.BooknameTextBox.Tag = 0 Then
cmd.CommandText = "Select @@Identity"
'Set textbox tag property with the ID of new record
Me.BooknameTextBox.Tag = cmd.ExecuteScalar()
End If
MsgBox("Data has been save.")

Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub

help.jpg
 
Updating the wrong column and row are two different issues. Updating the wrong row means your search "where x = y" is wrong on either side of the comparison operator. Updating the wrong column means you likely switched a few values in your SQL statement.

Here is where I would start: have it show you the unique key (you do have this, right?) for the row it located in the search results, to verify you have the correct row. Once you get that figured out, have it show you the full SQL statement as you would execute. Reading the final SQL code to be executed should make any errors in your code obvious.
 
Updating the wrong column and row are two different issues. Updating the wrong row means your search "where x = y" is wrong on either side of the comparison operator. Updating the wrong column means you likely switched a few values in your SQL statement.

Here is where I would start: have it show you the unique key (you do have this, right?) for the row it located in the search results, to verify you have the correct row. Once you get that figured out, have it show you the full SQL statement as you would execute. Reading the final SQL code to be executed should make any errors in your code obvious.

well to be honest im already confused.. i tried my best hahaha
im still up to the update and delete functions...
is it ok if i upload the project here so that you can view really the codes that I made.. ?
im just really somehow starting the VB and trying it with the simple access db..
 
I don't have time to look through it now, but I had time to skim through it. This looks like an assignment to me, so I'm only going to offer limited help.

Do your SQL search and create a popup to show what "ID" (the column name) it currently has. This will tell you what row you have. The column is likely a typo in your SQL statement, where you mix up two values. Have the program output the full SQL query so you can review it.
 
well now the problem is solved..
BUT i got a new one.. when i was publishing it.. and i included already the access db..
i got an error... it cannot find the access db...
 
What is the exact error. The program is either not copying a clean one to the compile folder, or its in a different location than expected.
 
What is the exact error. The program is either not copying a clean one to the compile folder, or its in a different location than expected.

this problem was solved, sorry for the late reply... what I did was to put install Inno Setup, i forgot that the Studio 2010 removed the publish feature.

NOW...
what i want to try is to make my database into like a server database... (1 database but many machines who can add/edit the data)
How to do it?
 
this problem was solved, sorry for the late reply... what I did was to put install Inno Setup, i forgot that the Studio 2010 removed the publish feature.

NOW...
what i want to try is to make my database into like a server database... (1 database but many machines who can add/edit the data)
How to do it?

As far as I know you cannot access an Access database from outside the network that it exists on (either physical or virtual). If you're on the same network, then it should be just a matter of individuals opening your database from network share, or accessing it through a web application (ie, sharepoint).
 
if you want it as a server database you can either host it and have clients link to it using the connection string, which if i know access correctly would be a direct URL to the access database file itself. in which case you simply need to "install" the client to all systems and ensure they can find the database, this works if everyone is on the same local network.

Otherwise you can do an N-Teir client/server setup in which case you build a webservice that actually accesses the database and the clients would simply call the web service to do everything they need to do.

The easiest way is to keep it on the local network. While webservices would obviously require a webserver.

Those are simply 2 options based on 2 completely different ideals for releasing it.
What is your final target of users, be specific as to how you want them to be able to access it.
Local network access only, offline capabilities, over the internet so they can access it anywhere?

With more informaiton we can give you more advice.
 
Last edited:
just 3 users who can access it, and its offline.. it is just in a local network.. because I have 10 machines in the network and I wanted 3 users who can edit or delete the data from the database.. yes maybe just put the access db in the network but my problem is I dont know where to start hehe (yes maybe by putting the DB in the network) but I dont know how to like the application to find the DB into the network.. any code to help me? also what script compiler to use because the Publish of the VS 2010 is already removed from its features.
 
What I tried was this:

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=\\server\share\folder\myAccessFile.accdb;


and in my code project its like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\RMMS-PC6\Users\Public\Database1.accdb"

BUT after making a setup of the project using INNO SETUP

I got this errror...:confused: :confused: :confused:

prob.jpg
 
It seems you code is still pointing to C:\program files\bookfinder\database1 somewhere rather than the server its hosted on.
 
my suggestion to you on the connection string. Don't hardcode it into your application. Have it configured in the app.config file and reference that for your connection. It will allow it to be changed on a client by client basis if needed, and it allows you to setup multiple conenctions strings and have them all in the same place.

Otherwise yes, it does sound like you still have an incorrect reference somewhere in your code.
 
Not sure if this is applicable to your case but another alternative to hard coding the file location is, the ../ moniker. You can say "../ProgramName/FileName, and the program will look inside the program folder for the file you need. It gets ride of the C://stuff. At least thats the case when using C++ and the VC++ compiler, I'm not sure if it transfers over to other Microsoft supported languages but worth a try :D
 
Back