Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Storing check box into database

Posted on 2011-05-09
13
Medium Priority
?
309 Views
Last Modified: 2012-05-11
In my access database a have checkboxes

if its ticked its true... if not its false.

How can i store this in the database as i currently get the error Data type mismatch in criteria expression.

mportant As String, ByVal forumlock As String are the checkbox values

    Shared Function editpost(ByVal id As Integer, ByVal title As string, ByVal post As String, ByVal important As String, ByVal forumlock As String) As String

        Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
            Dim cmd As New OleDbCommand
            cmd.Connection = con

            cmd.CommandText = "Update forum SET Title ='" & title & "',Post= '" & post & "',forumlock ='" & forumlock & "',important ='" & important & "' WHERE ID = '" & id & "'"
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using

    End Function

Open in new window

0
Comment
Question by:runnerjp2005
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35720079
Hi, if text inside post, important and forumlock can read like true or false, you need to remove the ' from your sql, something like:
"Update forum SET Title ='" & title & "',Post= " & post & ",forumlock =" & forumlock & ",important =" & important & " WHERE ID = " & id

Open in new window

Also, I recommend to use parameters instead or this method.
0
 
LVL 8

Expert Comment

by:edemcs
ID: 35720140
I would cast the checkbox value to boolean before doing the update.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35720141
Hi, try with this:
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
    Dim cmd As New OleDbCommand(Nothing, con)
    cmd.CommandText = "Update forum SET Title = @pTitle, Post = @pPost, forumlock = @pForumLock, Important = @pImportant WHERE ID = @pID"
    cmd.Parameters.Add("@pTitle", OleDbType.VarWChar, 100).Value = title
    cmd.Parameters.Add("@pPost", OleDbType.Boolean).Value = post
    cmd.Parameters.Add("@pForumLock", OleDbType.Boolean).Value = forumlock
    cmd.Parameters.Add("@pImportant", OleDbType.Boolean).Value = important
    cmd.Parameters.Add("@pID", OleDbType.Integer).Value = id
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
End Using

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35720178
Try change the query to this


cmd.CommandText = "Update forum SET Title ='" & title & "',Post= '" & post & "',forumlock ='" & forumlock & "',important ='" & IIF(cbImportant.Checked, '1', '0') & "' WHERE ID = '" & id & "'"
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35720253
Oops! I did a mistake, Im asuming that post is a checkbox too, try this instead:
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
    Dim cmd As New OleDbCommand(Nothing, con)
    cmd.CommandText = "Update forum SET Title = @pTitle, Post = @pPost, forumlock = @pForumLock, Important = @pImportant WHERE ID = @pID"
    cmd.Parameters.Add("@pTitle", OleDbType.VarWChar, 100).Value = title
    cmd.Parameters.AddWithValue("@pPost", post)
    cmd.Parameters.Add("@pForumLock", OleDbType.Boolean).Value = forumlock
    cmd.Parameters.Add("@pImportant", OleDbType.Boolean).Value = important
    cmd.Parameters.Add("@pID", OleDbType.Integer).Value = id
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
End Using

Open in new window

0
 

Author Comment

by:runnerjp2005
ID: 35720566
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
    Dim cmd As New OleDbCommand(Nothing, con)
    cmd.CommandText = "Update forum SET Title = @pTitle, Post = @pPost, forumlock = @pForumLock, Important = @pImportant WHERE ID = @pID"
    cmd.Parameters.Add("@pTitle", OleDbType.VarWChar, 100).Value = title
    cmd.Parameters.AddWithValue("@pPost", post)
    cmd.Parameters.Add("@pForumLock", OleDbType.Boolean).Value = forumlock
    cmd.Parameters.Add("@pImportant", OleDbType.Boolean).Value = important
    cmd.Parameters.Add("@pID", OleDbType.Integer).Value = id
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
End Using

Open in new window


With that i get Failed to convert parameter value from a String to a Boolean.

I also tried cmd.CommandText = "Update forum SET Title ='" & title & "',Post= '" & post & "',forumlock ='" & forumlock & "',important ='" & IIF(cbImportant.Checked, '1', '0') & "' WHERE ID = '" & id & "'"

and get icImportant is not declaired
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35720581
>IIF(cbImportant.Checked, '1', '0')

I used cbImportant as an example checkbox.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35720669
Hi, I think that the best way to do this is to change your method parameters to (see the boolean type instead of string):
Shared Function editpost(ByVal id As Integer, ByVal title As string, ByVal post As String, ByVal important As Boolean, ByVal forumlock As Boolean) As String

Open in new window


Then when you call it, do that in this way:
editpost(MyID, MyTitle, MyPost, cbImportant.Checked, cbforumlock.Checked)

Open in new window

Then use the parameters approach, Im assuming that cbImportant and cbforumlock are you check boxes.
0
 

Author Comment

by:runnerjp2005
ID: 35720703
AHH important is a string though
0
 

Author Comment

by:runnerjp2005
ID: 35720792
yv989c i now get Data type mismatch in criteria expression.

Below is my fully code which may help

editpostasmin.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="editpostadmin.aspx.vb" Inherits="forums_editpostadmin" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Edit Post</title>
    </head>
<body>
    <form id="form1" runat="server">
 <div class="style1">
       <center>
          
        Edit Post<br />
        <asp:FormView ID="FormView1" runat="server" DataSourceID="AccessDataSource1">
        <ItemTemplate>
<asp:TextBox id="title" runat="server" Text='<%#Eval("Title") %>' /> <br />
<asp:TextBox id="post" runat="server" Text='<%#Eval("Post") %>' TextMode="MultiLine" /><br />
                       
        Important<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%#Eval("important") %>' />
      
           
          &nbsp; Locked 
            <asp:CheckBox ID="CheckBox2" runat="server" Checked='<%#Eval("forumlock") %>'/> <br />
            <asp:Button ID="submit" runat="server" Text="submit" CommandName="submit" /><br />
            <asp:Label ID="Label1" runat="server"
                Text="Label"></asp:Label>
          </ItemTemplate>
        </asp:FormView>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/forums/forum.mdb" SelectCommand="SELECT * FROM [forum] Where ID = @ID">
            <SelectParameters>
            <asp:QueryStringParameter Name="ID" QueryStringField="ID" />
            </SelectParameters>
        </asp:AccessDataSource></center>
    </div>
    
    </form>
</body>
</html>

Open in new window


editpostadmin.aspx.vb
Partial Class forums_editpostadmin
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim s As String
        s = Request.QueryString("id")


    End Sub




    Protected Sub FormView1_ItemCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewCommandEventArgs) Handles FormView1.ItemCommand



        If e.CommandName = "submit" Then
            Dim row As FormViewRow = FormView1.Row
            Dim label1 As Label = CType(row.FindControl("label1"), Label)
            Dim title As TextBox = CType(row.FindControl("title"), TextBox)
            Dim post As TextBox = CType(row.FindControl("post"), TextBox)
            Dim important As CheckBox = CType(row.FindControl("CheckBox1"), CheckBox)
            Dim forumlock As CheckBox = CType(row.FindControl("CheckBox2"), CheckBox)


            Dim s As String
            s = Request.QueryString("id")

            label1.Text = "updated"

            adminforum.editpost(s, title.Text, post.Text, important.Checked, forumlock.Checked)
        End If

    End Sub
End Class

Open in new window


and my class

Public Class adminforum
    Shared Function editpost(ByVal id As Integer, ByVal title As String, ByVal post As String, ByVal important As Boolean, ByVal forumlock As Boolean) As String

        Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
            Dim cmd As New OleDbCommand
            cmd.Connection = con

            cmd.CommandText = "Update forum SET Title ='" & title & "',Post= '" & post & "',forumlock ='" & forumlock & "',important ='" & important & "' WHERE ID = '" & id & "'"
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using

    End Function
End Class

Open in new window

0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 35720957
Hi buddy, update your adminforum class with this and try again:
Public Class adminforum
    Shared Function editpost(ByVal id As Integer, ByVal title As String, ByVal post As String, ByVal important As Boolean, ByVal forumlock As Boolean) As String
	Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
	    Dim cmd As New OleDbCommand(Nothing, con)
	    cmd.CommandText = "Update forum SET Title = @pTitle, Post = @pPost, forumlock = @pForumLock, Important = @pImportant WHERE ID = @pID"
	    cmd.Parameters.AddWithValue("@pTitle", title)
	    cmd.Parameters.AddWithValue("@pPost", post)
	    cmd.Parameters.Add("@pForumLock", OleDbType.Boolean).Value = forumlock
	    cmd.Parameters.Add("@pImportant", OleDbType.Boolean).Value = important
	    cmd.Parameters.Add("@pID", OleDbType.Integer).Value = id
	    con.Open()
	    cmd.ExecuteNonQuery()
	    con.Close()
	End Using
    End Function
End Class

Open in new window

0
 

Author Closing Comment

by:runnerjp2005
ID: 35721025
You are a champ!!!!
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35721047
Thanks buddy, we're here to help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question