Member Avatar for DATABASE

:lol: need help!!!

how can i change the value of an item of (Yes/No) datatype in a DB Using VB.NET??

assume that the column is named (a)

  • 3 Contributors
  • 6 Replies
  • 219 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by zmind

Dani AI

Generated

Short version: you were calling the String.Insert method on the CommandText (that only changes the in-memory string) instead of executing a SQL update against the database. For a login/status scenario you should run an UPDATE that targets the specific staff row (by StaffID or username) and set the Yes/No field to a boolean value. As noted, Insert on a string won’t touch the DB, and was right to point toward using a boolean value — do that via parameters and ExecuteNonQuery (or set an UpdateCommand on your DataAdapter).

Example (safe, parameterized VB.NET pattern — uses UPDATE, not INSERT):

Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\yourdb.accdb;"

Using cn As New OleDbConnection(connString)
    Using cmd As New OleDbCommand("UPDATE StaffAccount SET S_Status = ? WHERE StaffID = ?", cn)
        cmd.Parameters.Add(New OleDbParameter("S_Status", OleDbType.Boolean)).Value = True
        cmd.Parameters.Add(New OleDbParameter("StaffID", OleDbType.Integer)).Value = staffId
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Using

Notes and troubleshooting

  • Use UPDATE (change existing row) rather than INSERT (creates a new row).
  • Use a boolean parameter (True/False). The provider translates this for Access (internally Access uses -1 for True and 0 for False).
  • With OleDb the ? placeholders are positional: add parameters in the same order as the placeholders.
  • If you use a DataAdapter, set its UpdateCommand or use DataAdapter.Update on a changed DataSet/DataTable.
  • Always parameterize to avoid SQL injection, open/close connections (Using is handy), and refresh your online-staff query (e.g. SELECT Username FROM StaffAccount WHERE S_Status=TRUE) after the update.
  • For multi-user scenarios Access has limits — consider a server DB if many simultaneous users are expected.

Recommended Answers

All 6 Replies

Member Avatar for zmind

If you use the ADO.Net in your application you can use the the DBCommand object to update your database. The DBCommand object
has a property named CommandText that can be used to set the SQL command to be executed.

Member Avatar for DATABASE

hi

i wrote this code, but it give me an error i do not know where s the error

Me.OleDbDataAdapter1.SelectCommand.CommandText.Insert("S_Status", "Yes")


S_Status .... the column name
it can take values (Yes/No)
i am using MS Access DB

:rolleyes: can you help me with it plz

Member Avatar for zmind

I think you get it wrong with the Insert method here. The Insert method here is from the String class and is for you to insert a string at a give position in the string instance. So it will not actually insert anything to your database but instead, insert a string to the CommandText property which expect a SQL command string . For you to insert a value to a YES/NO to your db column, you can use the following...

This assumed you have defined and initialised the connection and command object.

Me.OleDbDataAdapter1.SelectCommand.CommandText="INSERT INTO YourTableName(S_Status) Values(1)"


Note : Put 1 if you want to set the value to YES and 0 if it is a NO

Hope this will help

Member Avatar for DATABASE

thanks Zmind

i try it

Me.OleDbDataAdapter1.SelectCommand.CommandText = "INSERT INTO StaffAccount(S_Status) values(1)"

but i think there is something missing

* the problem is.... i have a login form if a staff login to the system his status should changed... and when we show the second form his name will be written in the list of Online staff

is the problem understandable? :?:

Member Avatar for williamrojas78

I am not sure, but i think you need to add the rest of the commands to complete the commandset of the oledbadapter.

the way i do it, i create an adodb.connection and then set the value of that field to a boolean varible = true.

Hope it helps

Member Avatar for zmind

sorry for my late reply.

The code provided is a code snippet only. You have to have the connection and command object declared and instantiated first before action can be perform to write to the Access database via the SQL command. If you still need to get help, please send me an email or private message. I will provide you a full source code of reading and setting the user login status.

cheers

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.