I am about to go crazy over trying to insert a row of data in a new MS ACCESS database. The database has 4 columns, "Email", "Name", "CompanyName", & "Date". The Date field is set as DATE/TIME-General Date in MS ACCESS, and all other fields are TEXT.
Here is my code below. Hopefully, someone can help me find the error in my code.
Dim conCoaxis As OleDbConnection
Dim strInsert As String
Dim cmdInsert As OleDbCommand
Dim dtmDate As DateTime
dtmDate = DateTime.Now()
conCoaxis = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=D:\WWWRoot\DOMAINNAME\database\email.mdb")
strInsert = "INSERT INTO List (Email, Name, CompanyName, Date) Values (@Email, @Name, @CompanyName, @Date)"
cmdInsert = New OleDbCommand( strInsert, conCoaxis )
cmdInsert.Parameters.Add( "@Email", txtEmail.Text )
cmdInsert.Parameters.Add( "@Name", txtName.Text )
cmdInsert.Parameters.Add( "@CompanyName", txtCompany.Text )
cmdInsert.Parameters.Add( "@Date", OleDbType.Date).Value = dtmDate.ToString("g")
Try
conCoaxis.Open()
cmdInsert.ExecuteNonQuery()
conCoaxis.Close()
Response.Write("Updated Successfully!<p> </p><p> </p><p> </p>")
Catch
conCoaxis.Close()
End Try
Thanks for the reply. I got it all figured out. You we're right about the reserved words. NAME is also a reserved word. However, I DIDN'T know that you could enclose the column name in brackets ( [NAME], [DATE] ) to make it work. That is very helpful
information.
Need help on this using .NET 2.0 . I think the code applies to .NET 1.x . I have this code that I cannot figure out the cause of the problem:
I want to insert values from textbox-based input and upon
clicking the Create button, these command lines will run. It didn't work! It
points to the "test.Insert()" line that indicates that "Keyword not
supported: 'provider'". See the code behind below:
Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnCreate.Click
Dim test As SqlDataSource = New SqlDataSource()
test.ConnectionString =
ConfigurationManager.ConnectionStrings("dbaccountsCS").ToString
test.InsertCommand = "INSERT INTO [tblaccounts] ([UserName],
[FName], [LName], [Email], [Department],[Password]) VALUES (@UserName,
@FName, @LName, @Email, @Dept, @Password)"
[quote user="mpMS"] It points to the "test.Insert()" line that indicates that "Keyword not supported: 'provider'"
I guess that's because SqlDataSource.ConnectionString doesn't accept the Provider keyword, instead, you need to set the SqlDataSource.ProviderName to System.Data.OleDb. For more information, you can take a look at this link:
aspnet_novic...
Participant
1002 Points
216 Posts
Simple Database Question...INSERT INTO...MS ACCESS
Sep 01, 2006 04:12 PM|LINK
I am about to go crazy over trying to insert a row of data in a new MS ACCESS database. The database has 4 columns, "Email", "Name", "CompanyName", & "Date". The Date field is set as DATE/TIME-General Date in MS ACCESS, and all other fields are TEXT. Here is my code below. Hopefully, someone can help me find the error in my code.
Dim conCoaxis As OleDbConnection Dim strInsert As String Dim cmdInsert As OleDbCommand Dim dtmDate As DateTime dtmDate = DateTime.Now() conCoaxis = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=D:\WWWRoot\DOMAINNAME\database\email.mdb") strInsert = "INSERT INTO List (Email, Name, CompanyName, Date) Values (@Email, @Name, @CompanyName, @Date)" cmdInsert = New OleDbCommand( strInsert, conCoaxis ) cmdInsert.Parameters.Add( "@Email", txtEmail.Text ) cmdInsert.Parameters.Add( "@Name", txtName.Text ) cmdInsert.Parameters.Add( "@CompanyName", txtCompany.Text ) cmdInsert.Parameters.Add( "@Date", OleDbType.Date).Value = dtmDate.ToString("g") Try conCoaxis.Open() cmdInsert.ExecuteNonQuery() conCoaxis.Close() Response.Write("Updated Successfully!<p> </p><p> </p><p> </p>") Catch conCoaxis.Close() End Trywww.adventerprises.net
deokule2003
Participant
1786 Points
356 Posts
Re: Simple Database Question...INSERT INTO...MS ACCESS
Sep 02, 2006 07:18 PM|LINK
Hello,
"Date" is a reserved word in MS Access. To know more reserved words in Access refer following link:
http://support.microsoft.com/kb/286335/
So, either use other column name in place of "date". Or modify your insert statement as given below:
Square brakets around "Date"
Regards
Kuldeep Deokule
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.
aspnet_novic...
Participant
1002 Points
216 Posts
Re: Simple Database Question...INSERT INTO...MS ACCESS
Sep 03, 2006 07:44 PM|LINK
Thanks for the reply. I got it all figured out. You we're right about the reserved words. NAME is also a reserved word. However, I DIDN'T know that you could enclose the column name in brackets ( [NAME], [DATE] ) to make it work. That is very helpful information.
Thanks for your help,
Matt
www.adventerprises.net
mpMS
Member
10 Points
2 Posts
Re: Simple Database Question...INSERT INTO...MS ACCESS
Sep 05, 2006 12:59 AM|LINK
Need help on this using .NET 2.0 . I think the code applies to .NET 1.x . I have this code that I cannot figure out the cause of the problem:
I want to insert values from textbox-based input and upon
clicking the Create button, these command lines will run. It didn't work! It
points to the "test.Insert()" line that indicates that "Keyword not
supported: 'provider'". See the code behind below:
Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnCreate.Click
Dim test As SqlDataSource = New SqlDataSource()
test.ConnectionString =
ConfigurationManager.ConnectionStrings("dbaccountsCS").ToString
test.InsertCommand = "INSERT INTO [tblaccounts] ([UserName],
[FName], [LName], [Email], [Department],[Password]) VALUES (@UserName,
@FName, @LName, @Email, @Dept, @Password)"
test.InsertParameters.Add("UserName", txtUserName.Text)
test.InsertParameters.Add("FName", txtFName.Text)
test.InsertParameters.Add("LName", txtLName.Text)
test.InsertParameters.Add("Email", txtEmail.Text)
test.InsertParameters.Add("Dept", ddlDept.Text)
test.InsertParameters.Add("Password", txtPass.Text)
test.Insert() <<<<<<<<------ This line has an error
Label7.Visible = True
End Sub
Hope you might help...
Iori_Jay
Star
12940 Points
2450 Posts
Re: Simple Database Question...INSERT INTO...MS ACCESS
Sep 07, 2006 09:36 AM|LINK
[quote user="mpMS"] It points to the "test.Insert()" line that indicates that "Keyword not supported: 'provider'"
I guess that's because SqlDataSource.ConnectionString doesn't accept the Provider keyword, instead, you need to set the SqlDataSource.ProviderName to System.Data.OleDb. For more information, you can take a look at this link:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.connectionstring.aspx
http://51up.org/bbs/forumdisplay.php?fid=38
anant718
Member
4 Points
3 Posts
Re: Simple Database Question...INSERT INTO...MS ACCESS
Sep 03, 2011 04:34 AM|LINK
Can you please give the above code in C#?