Mixing
How to use vb.net to update MSAccess Database
I am trying to write a method to accept a query as a parameter and then run that query to either add in a row into or edit the data in my database. I am using MS Access DB, Visual Studio to write my code, and VB.net as a coding language. I used a method that worked to query data from the database and modified it to modify data in the database. That is where things aren't quite working. Here is the code that I modified:
This is the query that i am passing into the method
query = "INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ([GOTIT], 121212)"
This is the method that i am trying to get working
Public Sub ExecEditQuery(Query As String)
'reset variables
Exception = ""
Try
'open a connection (already declared as private variable: Private DBCon As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" &
' "Data Source=Database.mdb;"))
DBCon.Open()
'create DB command
DBCmd = New OleDbCommand(Query, DBCon)
'execute command
DBCmd.ExecuteNonQuery()
Catch ex As Exception
Exception = ex.Message
End Try
'close DB connection
If DBCon.State = ConnectionState.Open Then DBCon.Close()
database vb.net ms-access
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
asked Jan 2 at 19:32
Mo WillzMo Willz
133
add a comment |
I am trying to write a method to accept a query as a parameter and then run that query to either add in a row into or edit the data in my database. I am using MS Access DB, Visual Studio to write my code, and VB.net as a coding language. I used a method that worked to query data from the database and modified it to modify data in the database. That is where things aren't quite working. Here is the code that I modified:
This is the query that i am passing into the method
query = "INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ([GOTIT], 121212)"
This is the method that i am trying to get working
Public Sub ExecEditQuery(Query As String)
'reset variables
Exception = ""
Try
'open a connection (already declared as private variable: Private DBCon As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" &
' "Data Source=Database.mdb;"))
DBCon.Open()
'create DB command
DBCmd = New OleDbCommand(Query, DBCon)
'execute command
DBCmd.ExecuteNonQuery()
Catch ex As Exception
Exception = ex.Message
End Try
'close DB connection
If DBCon.State = ConnectionState.Open Then DBCon.Close()
database vb.net ms-access
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
asked Jan 2 at 19:32
Mo WillzMo Willz
133
GOTIT needs to be in quotes, not a bracket. Use parameters to avoid sql injection and formatting errors. I would kill this method since it doesn't seem capable of handling parameters.
– LarsTech
Jan 2 at 19:42
add a comment |
I am trying to write a method to accept a query as a parameter and then run that query to either add in a row into or edit the data in my database. I am using MS Access DB, Visual Studio to write my code, and VB.net as a coding language. I used a method that worked to query data from the database and modified it to modify data in the database. That is where things aren't quite working. Here is the code that I modified:
This is the query that i am passing into the method
query = "INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ([GOTIT], 121212)"
This is the method that i am trying to get working
Public Sub ExecEditQuery(Query As String)
'reset variables
Exception = ""
Try
'open a connection (already declared as private variable: Private DBCon As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" &
' "Data Source=Database.mdb;"))
DBCon.Open()
'create DB command
DBCmd = New OleDbCommand(Query, DBCon)
'execute command
DBCmd.ExecuteNonQuery()
Catch ex As Exception
Exception = ex.Message
End Try
'close DB connection
If DBCon.State = ConnectionState.Open Then DBCon.Close()
database vb.net ms-access
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
asked Jan 2 at 19:32
Mo WillzMo Willz
133
I am trying to write a method to accept a query as a parameter and then run that query to either add in a row into or edit the data in my database. I am using MS Access DB, Visual Studio to write my code, and VB.net as a coding language. I used a method that worked to query data from the database and modified it to modify data in the database. That is where things aren't quite working. Here is the code that I modified:
This is the query that i am passing into the method
query = "INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ([GOTIT], 121212)"
This is the method that i am trying to get working
Public Sub ExecEditQuery(Query As String)
'reset variables
Exception = ""
Try
'open a connection (already declared as private variable: Private DBCon As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" &
' "Data Source=Database.mdb;"))
DBCon.Open()
'create DB command
DBCmd = New OleDbCommand(Query, DBCon)
'execute command
DBCmd.ExecuteNonQuery()
Catch ex As Exception
Exception = ex.Message
End Try
'close DB connection
If DBCon.State = ConnectionState.Open Then DBCon.Close()
database vb.net ms-access
database vb.net ms-access
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
asked Jan 2 at 19:32
Mo WillzMo Willz
133
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
asked Jan 2 at 19:32
Mo WillzMo Willz
133
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
edited Jan 2 at 19:40
jmcilhinney
26.5k32033
26.5k32033
asked Jan 2 at 19:32
Mo WillzMo Willz
133
asked Jan 2 at 19:32
Mo WillzMo Willz
133
asked Jan 2 at 19:32
Mo WillzMo Willz
133
133
GOTIT needs to be in quotes, not a bracket. Use parameters to avoid sql injection and formatting errors. I would kill this method since it doesn't seem capable of handling parameters.
– LarsTech
Jan 2 at 19:42
add a comment |
GOTIT needs to be in quotes, not a bracket. Use parameters to avoid sql injection and formatting errors. I would kill this method since it doesn't seem capable of handling parameters.
– LarsTech
Jan 2 at 19:42
GOTIT needs to be in quotes, not a bracket. Use parameters to avoid sql injection and formatting errors. I would kill this method since it doesn't seem capable of handling parameters.
– LarsTech
Jan 2 at 19:42
GOTIT needs to be in quotes, not a bracket. Use parameters to avoid sql injection and formatting errors. I would kill this method since it doesn't seem capable of handling parameters.
– LarsTech
Jan 2 at 19:42
add a comment |
1 Answer
1
active
oldest
votes
To connect to a database, you need to dim the following variables:
Public Filename As String = $"{AppDomain.CurrentDomain.BaseDirectory}YourDatabase.accdb"
Public ConStr As String = $"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA Source = {Filename}"
Public DBCon As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConStr)
Remember if you are inputting a string, remember to wrap it in quotation marks.
Also, if you are inputting in a table or field that is reserved by SQL for something else, it in back ticks (`)
For example:
DBCon.Open()
DBCmd = New OleDbCommand($"INSERT INTO `User` (Username) VALUES ('{Username}')", DBCon)
DBCmd.ExecuteNonQuery()
With respect to your query, try rewriting as
query = $"INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ('{GOTIT}', 121212)"
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012151%2fhow-to-use-vb-net-to-update-msaccess-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To connect to a database, you need to dim the following variables:
Public Filename As String = $"{AppDomain.CurrentDomain.BaseDirectory}YourDatabase.accdb"
Public ConStr As String = $"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA Source = {Filename}"
Public DBCon As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConStr)
Remember if you are inputting a string, remember to wrap it in quotation marks.
Also, if you are inputting in a table or field that is reserved by SQL for something else, it in back ticks (`)
For example:
DBCon.Open()
DBCmd = New OleDbCommand($"INSERT INTO `User` (Username) VALUES ('{Username}')", DBCon)
DBCmd.ExecuteNonQuery()
With respect to your query, try rewriting as
query = $"INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ('{GOTIT}', 121212)"
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
add a comment |
To connect to a database, you need to dim the following variables:
Public Filename As String = $"{AppDomain.CurrentDomain.BaseDirectory}YourDatabase.accdb"
Public ConStr As String = $"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA Source = {Filename}"
Public DBCon As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConStr)
Remember if you are inputting a string, remember to wrap it in quotation marks.
Also, if you are inputting in a table or field that is reserved by SQL for something else, it in back ticks (`)
For example:
DBCon.Open()
DBCmd = New OleDbCommand($"INSERT INTO `User` (Username) VALUES ('{Username}')", DBCon)
DBCmd.ExecuteNonQuery()
With respect to your query, try rewriting as
query = $"INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ('{GOTIT}', 121212)"
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
add a comment |
To connect to a database, you need to dim the following variables:
Public Filename As String = $"{AppDomain.CurrentDomain.BaseDirectory}YourDatabase.accdb"
Public ConStr As String = $"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA Source = {Filename}"
Public DBCon As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConStr)
Remember if you are inputting a string, remember to wrap it in quotation marks.
Also, if you are inputting in a table or field that is reserved by SQL for something else, it in back ticks (`)
For example:
DBCon.Open()
DBCmd = New OleDbCommand($"INSERT INTO `User` (Username) VALUES ('{Username}')", DBCon)
DBCmd.ExecuteNonQuery()
With respect to your query, try rewriting as
query = $"INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ('{GOTIT}', 121212)"
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
To connect to a database, you need to dim the following variables:
Public Filename As String = $"{AppDomain.CurrentDomain.BaseDirectory}YourDatabase.accdb"
Public ConStr As String = $"PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA Source = {Filename}"
Public DBCon As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConStr)
Remember if you are inputting a string, remember to wrap it in quotation marks.
Also, if you are inputting in a table or field that is reserved by SQL for something else, it in back ticks (`)
For example:
DBCon.Open()
DBCmd = New OleDbCommand($"INSERT INTO `User` (Username) VALUES ('{Username}')", DBCon)
DBCmd.ExecuteNonQuery()
With respect to your query, try rewriting as
query = $"INSERT INTO TABLE ([Column 1], [Column 2]) VALUES ('{GOTIT}', 121212)"
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
edited Jan 2 at 19:53
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
answered Jan 2 at 19:45
Mr PopoMr Popo
377116
377116
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012151%2fhow-to-use-vb-net-to-update-msaccess-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
GOTIT needs to be in quotes, not a bracket. Use parameters to avoid sql injection and formatting errors. I would kill this method since it doesn't seem capable of handling parameters.
– LarsTech
Jan 2 at 19:42