Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Web Calendar to Access Database

Reply
Thread Tools

Web Calendar to Access Database

 
 
=?Utf-8?B?SHV0dHk=?=
Guest
Posts: n/a
 
      03-10-2005
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,PersonResp onsible)
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,
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
--
Hutty
 
Reply With Quote
 
 
 
 
Scott M.
Guest
Posts: n/a
 
      03-10-2005
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

"Hutty" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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,PersonResp onsible)
> 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,
>
> 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
> --
> Hutty



 
Reply With Quote
 
 
 
 
=?Utf-8?B?SHV0dHk=?=
Guest
Posts: n/a
 
      03-11-2005
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." wrote:

> 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
>
> "Hutty" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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,PersonResp onsible)
> > 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,
> >
> > 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
> > --
> > Hutty

>
>
>

 
Reply With Quote
 
Scott M.
Guest
Posts: n/a
 
      03-11-2005
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.


"Hutty" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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." wrote:
>
>> 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
>>
>> "Hutty" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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,PersonResp onsible)
>> > 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,
>> >
>> > 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
>> > --
>> > Hutty

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
How to Synchronise Hotmail Calendar with Standard Outlook Calendar using Outlook Connector ?? Synapse Syndrome Computer Support 0 12-02-2007 04:19 AM
Thunderbird Calendar with Exchange 2003 Calendar and Public FoldersCalendar jincmcse Firefox 1 09-03-2005 02:46 AM
Calendar Control - Programatically set the calendar to a date range Shevek ASP .Net 3 06-23-2004 01:41 PM



Advertisments