Member Avatar for Ruch

hello all please help me out,I have designed a database in access having two fields(1.Regno 2.Smartid).I hav also kept some dummy values in RegNo,But the Smartid is empty.
Now what code should I write to retrive the RegNo for which the Smartid is empty.

I hav used only these three declarations:-
Dim con As System.Data.OleDb.OleDbConnection
Dim cmd, cmd1 As OleDbCommand
Dim dr As OleDbDataReader
Note:please answer me only using these declarations.
I have also attached the vb.net source file .please find the same in the attachment>>>>

the code is to be written in the next button action.

  • 3 Contributors
  • 2 Replies
  • 341 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by AV Manoharan

Dani AI

Generated

Two different things can make Smartid look "empty" in Access: a true NULL (no value) or an empty/whitespace string. Check both. already has the right set of declarations; was on the right track about using a reader, but the DataReader is returned by OleDbCommand.ExecuteReader() (you do not "open" the reader with the SQL string). The example below uses only the declared variables and shows a practical query that treats NULL and whitespace as empty.

' Next button click — con, cmd, cmd1, dr are assumed declared
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\YourDb.mdb;"
cmd = New OleDbCommand("SELECT RegNo FROM [YourTable] WHERE Smartid IS NULL OR Trim(Smartid) = ''", con)
Try
    con.Open()
    dr = cmd.ExecuteReader()
    ComboBox1.Items.Clear()
    While dr.Read()
        ComboBox1.Items.Add(dr("RegNo").ToString())
    End While
Finally
    If dr IsNot Nothing Then dr.Close()
    If con.State = ConnectionState.Open Then con.Close()
End Try

Notes and troubleshooting:

  • If your DB is .accdb use the ACE provider (e.g. "Microsoft.ACE.OLEDB.12.0/16.0") or compile the app to x86 when using Jet (Jet is 32-bit only).
  • If Smartid is numeric, test only IS NULL (no empty-string check).
  • Use square brackets around names with spaces: [Reg No].
  • To skip duplicates add DISTINCT to the SELECT.
  • Always close the reader and connection in Finally; check for DBNull when reading fields if you later read other columns.

This expands and corrects earlier replies by showing the correct ExecuteReader pattern, handling NULL vs empty, and calling out provider/bitness pitfalls.

Recommended Answers

All 2 Replies

Member Avatar for Ancient Dragon

moved

Member Avatar for AV Manoharan

The VBcode remains same whether you use the .Net or not. Only thing changes is the connection and data stream. here you have declared dr as the data reader. First use the con to get the connection established to your Access Data base. Then
con.Open
bind you dr with the activeconnection con and what property your dr should have.
dr.Open "select regno from hell where smartid IS null;"
fill your combo or list inside a loop with the Additem(dr.fields("regno"))

that's all... and don'be too smart. Because when you can Dimension the OLEDB codes, I think the question is absolutely out of place.

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.