Storing check box into database

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

runnerjp2005Asked:
Who is Participating?
 
Carlos VillegasConnect With a Mentor Full Stack .NET DeveloperCommented:
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
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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
 
edemcsCommented:
I would cast the checkbox value to boolean before doing the update.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
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
 
CodeCruiserCommented:
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
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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
 
runnerjp2005Author Commented:
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
 
CodeCruiserCommented:
>IIF(cbImportant.Checked, '1', '0')

I used cbImportant as an example checkbox.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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
 
runnerjp2005Author Commented:
AHH important is a string though
0
 
runnerjp2005Author Commented:
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
 
runnerjp2005Author Commented:
You are a champ!!!!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Thanks buddy, we're here to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.