-
Jul 1st, 2018, 04:24 AM
#1
Thread Starter
Junior Member
-
Jul 1st, 2018, 04:37 AM
#2
Re: Syntax error VB/MS Access
First off please refrain from using all upper case in your post. In regards to your issue, it's unwise to string concatenate a query of any kind as you have done. Instead use OleDbParameters as shown below. The last parameter to the function requires a Integer declared, passed in and on success returns the new primary key (that is if there is a primary key that is auto-incrementing). How the SQL statement is created requires targeting Framework 3.5 or higher using XML Literals, otherwise place the statement into a string. Lastly, the transaction is optional but is good to use more so when working with relational data, I left this in as it's a stock code sample I use for answering forum questions.
Code:
Public Function AddNewRow(
ByVal pName As String,
ByVal pContact As String,
ByVal pContactTitle As String,
ByVal pJoinDate As Date,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Using cn As New OleDbConnection("TODO")
Dim transaction As OleDbTransaction = Nothing
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate)
Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)
</SQL>.Value
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
Try
cn.Open()
transaction = cn.BeginTransaction()
cmd.Transaction = transaction
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
Catch sqlex As OleDbException
transaction.Rollback()
Catch ex As Exception
mHasException = True
mLastException = ex
Success = False
End Try
End Using
End Using
Return Success
End Function
EDIT: Without XML literals
Code:
Public Function AddNewRow(
ByVal pName As String,
ByVal pContact As String,
ByVal pContactTitle As String,
ByVal pJoinDate As Date,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Using cn As New OleDbConnection("TODO")
Dim transaction As OleDbTransaction = Nothing
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)"
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cmd.Parameters.AddWithValue("@JoinDate", pJoinDate)
Try
cn.Open()
transaction = cn.BeginTransaction()
cmd.Transaction = transaction
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
Catch sqlex As OleDbException
transaction.Rollback()
Catch ex As Exception
Success = False
End Try
End Using
End Using
Return Success
End Function
Last edited by kareninstructor; Jul 1st, 2018 at 06:02 AM.
Reason: added second code sample
-
Jul 1st, 2018, 04:52 AM
#3
Thread Starter
Junior Member
Re: Syntax error VB/MS Access
thanks for the fast response. but can I use SQL while using access database.?
-
Jul 1st, 2018, 04:57 AM
#4
Re: Syntax error VB/MS Access
You can, and you already were... INSERT INTO is an SQL statement.
As for the <SQL> tag, that has the limitations Karen mentioned (you need to be targeting .Net Framework 3.5 or higher). Parameters do not have that limitation.
For further information on why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).
By the way, I notice that you are using "Classic" ADO with recordsets etc (which was current 20 years ago), ideally you should switch to ADO.Net as Karen showed (which is still current).
-
Jul 1st, 2018, 04:59 AM
#5
Re: Syntax error VB/MS Access
Originally Posted by shammis
thanks for the fast response. but can I use SQL while using access database.?
Of course you can. You already are. You're just not doing it properly. If you had actually looked at your SQL code, rather than just the code that constructed it, you probably would have seen the actual issue. Use parameters a demonstrated and the issue should go away.
-
Jul 1st, 2018, 06:03 AM
#6
Re: Syntax error VB/MS Access
Originally Posted by shammis
thanks for the fast response. but can I use SQL while using access database.?
See second code sample in my post
-
Jul 1st, 2018, 06:27 AM
#7
Thread Starter
Junior Member
Re: Syntax error VB/MS Access
Originally Posted by kareninstructor
See second code sample in my post
Thanks again karen. But i still cant understand how to get text boxes value to @CompanyName . example: How insert TextBox1.Text to CompanyName . Sorry I am still new for these things. I am self learner. watching videos and try to learn my self. when i face a some error i try to fix it if couldn't i ask in here. so anyone can explain a little bit. sorry for the my bad English.
-
Jul 1st, 2018, 06:58 AM
#8
Re: Syntax error VB/MS Access
When you call Parameters.AddWithValue, you specify the parameter name and the value. If the value is in TextBox1.Text then that's what you provide.
If you're going to use the AddNewRow method that kareninstructor has written then when you call that method you pass the values you want to insert as arguments when you call it. Again, if TextBox1.Text is one of those values then that's what you pass when you call the method. This is simply a case of calling a method and passing an argument. If you don't know how to do that then you should probably spend some time on the fundamentals first.
-
Jul 1st, 2018, 09:39 AM
#9
Thread Starter
Junior Member
Re: Syntax error VB/MS Access
Originally Posted by jmcilhinney
When you call Parameters.AddWithValue, you specify the parameter name and the value. If the value is in TextBox1.Text then that's what you provide.
If you're going to use the AddNewRow method that kareninstructor has written then when you call that method you pass the values you want to insert as arguments when you call it. Again, if TextBox1.Text is one of those values then that's what you pass when you call the method. This is simply a case of calling a method and passing an argument. If you don't know how to do that then you should probably spend some time on the fundamentals first.
i think i found way how to do it. But i cant understand what is pIdentfier and what was doing it.?
Code:
Public Function AddNewRow(
ByVal Name1 As String,
ByVal Contact As String,
ByVal ContactTitle As String,
ByVal JoinDate As Date,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Using cn As New OleDbConnection("TODO")
Dim transaction As OleDbTransaction = Nothing
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle,JoinDate) Values (@CompanyName,@ContactName,@ContactTitle,@JoinDate)"
cmd.Parameters.AddWithValue("@CompanyName", Name1)
cmd.Parameters.AddWithValue("@ContactName", Contact)
cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
cmd.Parameters.AddWithValue("@JoinDate", JoinDate)
Try
cn.Open()
transaction = cn.BeginTransaction()
cmd.Transaction = transaction
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
Catch sqlex As OleDbException
transaction.Rollback()
Catch ex As Exception
Success = False
End Try
End Using
End Using
Return Success
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim tst As New Form1
tst.AddNewRow(Name1.Text, Contact.Text, ContactTitle.Text, JoinDate.Text, pIdentfier)
End Sub
-
Jul 1st, 2018, 05:25 PM
#10
Re: Syntax error VB/MS Access
You probably don't need pIdentfier
It is there so that if the database table has an AutoNumber/Identity field, you can automatically detect the value for the record you just added.
As that doesn't seem relevant in this case, you can remove it from the parameters list:
Code:
ByVal JoinDate As Date) As Boolean
...and remove the two lines for using it:
Code:
cmd.ExecuteNonQuery()
Catch sqlex As OleDbException
-
Jul 1st, 2018, 07:42 PM
#11
Thread Starter
Junior Member
Re: Syntax error VB/MS Access
Originally Posted by si_the_geek
You probably don't need pIdentfier
It is there so that if the database table has an AutoNumber/Identity field, you can automatically detect the value for the record you just added.
As that doesn't seem relevant in this case, you can remove it from the parameters list:
Code:
ByVal JoinDate As Date) As Boolean
...and remove the two lines for using it:
Code:
cmd.ExecuteNonQuery()
Catch sqlex As OleDbException
Finally all errors gone.. Thanks to all . but now when i click on button it was nothing happened. i checked the database. it was not updated.
here is the codes i used.
Code:
Public Function AddNewRow(
ByVal ID As String,
ByVal Name1 As String,
ByVal Contact As String,
ByVal ContactTitle As String,
ByVal JoinDate As Date) As Boolean
Dim Success As Boolean = True
Using cn As New OleDbConnection
cn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\Database1.accdb"
Dim transaction As OleDbTransaction = Nothing
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText = "INSERT INTO Customer (ID,CompanyName,ContactName,ContactTitle,JoinDate) Values (@ID,@CompanyName,@ContactName,@ContactTitle,@JoinDate)"
cmd.Parameters.AddWithValue("@ID", ID)
cmd.Parameters.AddWithValue("@CompanyName", Name1)
cmd.Parameters.AddWithValue("@ContactName", Contact)
cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
cmd.Parameters.AddWithValue("@JoinDate", JoinDate)
Try
cn.Open()
transaction = cn.BeginTransaction()
cmd.Transaction = transaction
cmd.ExecuteNonQuery()
transaction.Rollback()
Catch ex As Exception
Success = False
End Try
End Using
End Using
Return Success
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
AddNewRow(ID.Text, Name1.Text, Contact.Text, ContactTitle.Text, JoinDate.Text)
End Sub
-
Jul 1st, 2018, 07:48 PM
#12
Re: Syntax error VB/MS Access
How would you even know whether it's working or not? In that AddNewRow method you simply return False if an exception is thrown but you never actually test what value is returned when you call AddNewRow. An exception could be being thrown and you'd never know. Have you stepped through the code in the debugger to see whether it succeeds or not? That is the first thing to do.
If it does succeed, you should then test the value returned by that ExecuteNonQuery call. If that's not zero then the code is working exactly as it should and you're just not looking for the data properly, which is not unusual. In that case, follow the first link in my signature to learn how local data files are managed.
-
Jul 1st, 2018, 08:33 PM
#13
Thread Starter
Junior Member
Re: Syntax error VB/MS Access
Originally Posted by jmcilhinney
How would you even know whether it's working or not? In that AddNewRow method you simply return False if an exception is thrown but you never actually test what value is returned when you call AddNewRow. An exception could be being thrown and you'd never know. Have you stepped through the code in the debugger to see whether it succeeds or not? That is the first thing to do.
If it does succeed, you should then test the value returned by that ExecuteNonQuery call. If that's not zero then the code is working exactly as it should and you're just not looking for the data properly, which is not unusual. In that case, follow the first link in my signature to learn how local data files are managed.
I try step by step as you instruct and found error in database format . then i create new one as database format as .mdb then again run debugger and its run to
Code:
transaction.Rollback()
and show the form1 and nothing happened and nothing to added to database.
-
Jul 1st, 2018, 08:42 PM
#14
Re: Syntax error VB/MS Access
You need to start making an effort to understand the code you're using. Do you know what transactions do or how they work? If not, have you tried to find out? Have you read the relevant documentation and/or searched the web for information?
You've changed how the one in your code is being used and broken it as a result. The point of a transaction is to allow you to ensure that multiple database operations are either all performed successfully or none of them are, so you don't end up with an intermediate, indeterminate state. The original code rolled back the transaction, i.e. undid any database changes, if and only if there was an OleDbException thrown. You've removed that Catch statement but retained the Rollback call, which means that you're always going to undo your changes if they are made successfully. Does that make sense? Of course not. You only want to rollback a transaction if something goes wrong partway through. You were told in the first place that the transaction was optional. Don't use it if it's not appropriate to do so.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|