checkbox into database table

Question

0.00/5 (No votes)

See more:

This Query is written in Add button and m using ODBC connection. i need to add check box value into one column in database table ie. designation. the below cade is giving an error.
I am a beginner so i want full explanation pls.
Dim con As New Odbc.OdbcConnection("dsn=dem;uid=root;pwd=root")

Dim str As String

Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
If chkincharge.Checked Then
a = chkincharge.Text.ToString
ElseIf chkadditionalincharge.Checked Then
b = chkadditionalincharge.Text.ToString
ElseIf chkmember.Checked Then
c = chkmember.Text.ToString

End If

con.Open()
Dim cmd As New Odbc.OdbcCommand("insert into checkbox values('" + DateTimePicker1.Value.Date.ToString("yyyy/mm/dd") + "','" + cmbcommitteename.Text + "','" + cmbmembers.Text + "','" + a + "','" + b + "','" + c + "')", con)
cmd.ExecuteNonQuery()
con.Close()
MsgBox("Record added successfully")
'txteventtitle.Text = ""
'rchdescription.Text = ""
'DateTimePicker1.Text = Date.Now

End Sub

thank u in advance

Posted 24-Apr-13 21:55pm

Shraddha Shikerkar

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-04-24T22:05:00

Solution 1

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

VB

Dim cmd As New Odbc.OdbcCommand("INSERT INTO checkbox VALUES(@DT, @CN, @MB, @A, @B, @C)", con)
cmd.Parameters.AddWithValue("@DT", DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("@CN", cmbcommitteename.Text)
cmd.Parameters.AddWithValue("@MB", cmbmembers.Text)
cmd.Parameters.AddWithValue("@A", a)
cmd.Parameters.AddWithValue("@B", b)
cmd.Parameters.AddWithValue("@C", c)
cmd.ExecuteNonQuery()
con.Close()

And name your fields! If you don't, then you are risking problems if a column is added or removed.

Posted 24-Apr-13 22:05pm

OriginalGriff

Comments

Shraddha Shikerkar 25-Apr-13 4:30am

i tired this code before . but it is nt working .. it ws giving a prob in cmd.executenonquery()

OriginalGriff 25-Apr-13 4:40am

And what's the problem? What error did you get?

Shraddha Shikerkar 25-Apr-13 4:46am

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''04/01/13'' at line 1

this is wt m getting..

OriginalGriff 25-Apr-13 5:01am

No, that's the error you got with your original version.
Did you try the version I suggested?

Shraddha Shikerkar 26-Apr-13 3:08am

yes m trying ... ill see the result.
I have one another query.
And it is :
i have fields on my forms are title , description and calender..
and in the table have this three fields along with one primary key which is auto generated..
So nw say if i want to search and delete particular record it should not delete other data.. as i ll search a record on title and calender basis..
and both of this fields are not primary key.
What is the solution for this??

Shraddha Shikerkar 26-Apr-13 3:23am

Hey m getting this error after using ur suggested query..
ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]Column count doesn't match value count at row 1

OriginalGriff 26-Apr-13 3:28am

What did I say about naming your fields?
When you say
INSERT INTO MyTable VALUES(1,2,3)
It tries to insert the values into column1, column2, and column3. If column1 is an auto generated field, then you can't set it, but it will still try becuas eyou haven't given it anything else to work with.
Instead, say this:
INSERT INTO MyTable (Title, Description, Calender) VALUES ('tit', 'desc', '2013-04-26')
and it knows what field to put what info into.

Shraddha Shikerkar 26-Apr-13 3:43am

yes i have done like that only.

Shraddha Shikerkar 26-Apr-13 3:56am

Your first query i have modified with this.

con.Open()
Dim cmd As New Odbc.OdbcCommand("INSERT INTO checkbox(from1,committee,members,a,b,c) VALUES(@DT, @CN, @MB, @A, @B, @C)", con)
cmd.Parameters.AddWithValue("@DT", DateTimePicker1.Value.Date.ToString("yyyy/mm/dd"))
cmd.Parameters.AddWithValue("@CN", cmbcommitteename.Text)
cmd.Parameters.AddWithValue("@MB", cmbmembers.Text)
cmd.Parameters.AddWithValue("@A", a)
cmd.Parameters.AddWithValue("@B", b)
cmd.Parameters.AddWithValue("@C", c)
cmd.ExecuteNonQuery()
con.Close()
MsgBox("Record added successfully")

Now i get successfull record added msg but it stores null value in the database.

OriginalGriff 26-Apr-13 3:58am

Why are you storing dates as strings? That is a very poor idea - it makes math, comparisons and so forth much, much harder. Store dates as dates!
And which value does it store as a null? Which column?

Shraddha Shikerkar 26-Apr-13 4:01am

oh is it i dint have any idea abt date sorry..
yup the column which stores null values are
from1, committeename,members,designation field.
Hey is it ok if i try to store three checkbox value in one column of my table i.e designation???

OriginalGriff 26-Apr-13 5:17am

You could - but I wouldn't. You would have to convert them from bool to string, or int to store them, and then reverse the process to use them again. And it is very likely that that would use more DB space than storing three separate one-bit values.

Shraddha Shikerkar 27-Apr-13 1:27am

Its very difficult for me to understand abt conversion..If U can explain it with an example then i would be very thank ful to u.

OriginalGriff 27-Apr-13 3:55am

If you want to store three bools in one column, then you can't use a bool field - so you need to use an int or a string.
Which means that you have to decide what part of the field is which bool.
For example, in an int, bit 0 might be the first bool, bit 1 the second and so on.
For a string, the first character might be the first bool, and so forth. Then you decide if '0' and '1' are false / true, or 't' and 'f', or ' ' and 'x', or... it's up to you (and not very obvious) - I'd go with three bool fields, myself.

checkbox into database table

1 solution

Solution 1

Add your solution here

Preview 0

checkbox into database table

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us