Web Calendar to Access Database

Discussion in 'ASP .Net' started by =?Utf-8?B?SHV0dHk=?=, Mar 10, 2005.

  1. 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
    --
    Hutty
     
    =?Utf-8?B?SHV0dHk=?=, Mar 10, 2005
    #1
    1. Advertising

  2. =?Utf-8?B?SHV0dHk=?=

    Scott M. Guest

    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" <> wrote in message
    news:...
    > 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
    > --
    > Hutty
     
    Scott M., Mar 10, 2005
    #2
    1. Advertising

  3. 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" <> wrote in message
    > news:...
    > > 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
    > > --
    > > Hutty

    >
    >
    >
     
    =?Utf-8?B?SHV0dHk=?=, Mar 11, 2005
    #3
  4. =?Utf-8?B?SHV0dHk=?=

    Scott M. Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> > 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
    >> > --
    >> > Hutty

    >>
    >>
    >>
     
    Scott M., Mar 11, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MSDN
    Replies:
    0
    Views:
    681
  2. Shevek
    Replies:
    3
    Views:
    6,044
    Shevek
    Jun 23, 2004
  3. Tony Johansson
    Replies:
    7
    Views:
    505
    Gregory A. Beamer
    Dec 23, 2009
  4. Shevek
    Replies:
    0
    Views:
    387
    Shevek
    Jun 23, 2004
  5. Parthiv Joshi
    Replies:
    1
    Views:
    688
    Samuel L Matzen
    Jul 6, 2004
Loading...

Share This Page