Web Calendar to Access Database

[Guest]

I'm trying create a calendar control that updates an access database with
events. I have some code I managed to piece together. First error I'm
running into is the Mycommand.ExecuteNonQuery(). I get an error on this line
as it looks for the query. Here's the first piece of the code where I'm able
to get display calendar and the input boxes. Just trying to write back to
database. Thanks in advance.

Public Sub Page_Load(ByVal Sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
'Add the items to the _Type DropDownList Web Control
_Type.Items.Add(New ListItem("Mailing", "1"))
_Type.Items.Add(New ListItem("House ad", "2"))
_Type.Items.Add(New ListItem("Trade show", "3"))
_Type.Items.Add("Test")

_Date.SelectedDate = _Date.TodaysDate
End If
End Sub
Sub Do_Insert(ByVal Sender As Object, ByVal e As EventArgs)

'Only update the database if the user entered valid inputs
If Not Page.IsValid Then Exit Sub

'Add the event to the database

Dim myConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Inetpub\wwwroot\vacation.MDB")
Dim myCommand As New OleDb.OleDbCommand("Insert into
tbl_Marketing(_Date,Type,Title,Audience,PersonResponsible)
values(_Date,Type,Title,Audience,PersonResponsible)", myconnection)

myCommand.CommandType = CommandType.StoredProcedure

Dim parameterTitle As New OleDbParameter("@Title",
OleDbType.VarChar, 50)
parameterTitle.Value = Title.Text
myCommand.Parameters.Add(parameterTitle)

'I used the simplest version of the calendar control to allow the
'user to pick the date. So, to retrieve the value from the control,
'I needed to get the value from the SelectedDate property
Dim parameterDate As New OleDbParameter("@_Date", OleDbType.Date, 8)
parameterDate.Value = _Date.SelectedDate
myCommand.Parameters.Add(parameterDate)

Dim parameterAudience As New _
OleDbParameter("@Audience", OleDbType.VarChar, 50)
parameterAudience.Value = Audience.Text
myCommand.Parameters.Add(parameterAudience)

Dim parameterPResponsible As New _
OleDbParameter("@PersonResponsible", OleDbType.VarChar,
50)
parameterPResponsible.Value = PResponsible.Text
myCommand.Parameters.Add(parameterPResponsible)

Dim parameterType As New OleDbParameter("@Type", OleDbType.Integer, 4)
parameterType.Value = _Type.SelectedItem.Value
myCommand.Parameters.Add(parameterType)


myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Response.Redirect("default.aspx") 'Redirect the user to the calendar
End Sub

 

[Scott M.]

Wow, I see a lot of code that is out of whack here.

Why are you setting myCommand.CommandType = CommandType.StoredProcedure when
you are using a SQL statement, not a stored procedure? By the way, Access
doesn't use stored procedures anyway.
Why are you using Access as your data repsitory for a web page? Access has
limited concurrent user capabilities and will become unstable as you get
past about 10 concurrent connections.
Why are you creating all those parameters and adding them to your command,
but not using them in your INSERT statement?
Why aren't you using Try...Catch to catch your exception(s) and troubleshoot
from there?

It seems like you have pieced together this code from snippets you picked up
here and there, but I would strongly recommend going back and researching
how to create and configure a command object.

-Scott

 

[Guest]

Actually, the code comes from this site
http://aspnet.4guysfromrolla.com/articles/041603-1.aspx

Since I'm not using SQL server I have to use an access database. I would
expect a total of 30 users totally for what i have in mind. I was trying to
substitute oledb where sql was in the code below. In attempt that I could
achieve same results from above website. Any information in obtaining task
at hand is helpful. thanks

 

[Scott M.]

The code changes you've made make the original code incorrect.

The original code used SQL Server and Stored Procedures. You've substituted
Access (which doesn't use SP's), so you need to change the commandType of
the command to CommandText, or better yet, just remove the line altogether
since text is the default.

You've switched the code from stored procedures to a SQL statement but not
taken advantage of all the parameters created in the code. You should
change your INSERT statement to include these parameters, rather than using
variables.

The opening of the connection and the ExecuteNonQuery should absolutely 100%
be enclosed in a Try...Catch statement with the closing of the connection in
the Finally section. I don't care if the code came form Bill Gates himself,
this is a must.

Lastly (as stated before), you really shouldn't be using Access for this.
You should look into using the MSDE or SQL Server.