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.

  • 4 Contributors
  • 3 Replies
  • 212 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by Naveed_786

Dani AI

Generated

— short, practical approach using only the variables you declared and suitable for putting inside your Next button handler. First confirm the data type of Smartid in Access: if it is a text field use an SQL test for NULL or empty string; if numeric test for NULL or 0. Replace "YourTable" and the Data Source path with your actual names.

' inside NextButton_Click (assumes con, cmd, cmd1, dr are declared at form/class level)
Try
    If con Is Nothing Then
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\to\your.mdb;")
    End If

    If con.State <> System.Data.ConnectionState.Open Then con.Open()

    cmd.Connection = con
    cmd.CommandText = "SELECT RegNo FROM YourTable WHERE Smartid IS NULL OR Smartid = ''"  ' use '' for text fields

    dr = cmd.ExecuteReader()
    While dr.Read()
        TextBox1.AppendText(dr("RegNo").ToString() & vbCrLf)   ' example: collect results in a multiline TextBox
    End While

Catch ex As Exception
    MessageBox.Show("Error: " & ex.Message)
Finally
    If dr IsNot Nothing AndAlso Not dr.IsClosed Then dr.Close()
    If con IsNot Nothing AndAlso con.State = System.Data.ConnectionState.Open Then con.Close()
End Try

Notes and troubleshooting

  • If Smartid is numeric, change the WHERE clause to "Smartid IS NULL OR Smartid = 0" to avoid type-mismatch errors.
  • If you need to bind results to a grid or reuse the data, follow and use an OleDbDataAdapter + DataSet; DataReader is lighter when you only need to stream values.
  • Use the ACE provider ("Provider=Microsoft.ACE.OLEDB.12.0;") for .accdb files and Jet for .mdb files.
  • : for date-range selects use a parameterized BETWEEN query and a new thread as advised (date handling and parameterization deserve a separate example).

Recommended Answers

All 3 Replies

Member Avatar for arjunsasidharan

so where are the attachments?
are you going to bind the rows in a datagrid or in a textbox?

So the picture i get from the information that you have provided is you have to get the regno when the Smartid is 0 or null

dim ldst as new dataset

declare a datset

the values that are returned should be stored in the dataset.. whether its gettin returned thru a textbox or a grid.

then try out

if ldst.Tables(0).Rows(0)(0) = 0 Then

'Checking whether the field SmartId is empty
ldst.Tables(1).Rows(0).Add.. and it goes something like that.. post in your code and will try to solve it out.

check this LINK

it has some basics of ADO.NET

Member Avatar for Aditi.sharma9

Please provide me code to access data in VB 2005 am using MS ACCESS as database.
I want to retrive data on the from date (field) means data of any particular date like from 27 to 31. Pls help me to know how to accecc data am new on this thanks....asap help req.

Member Avatar for Naveed_786

Please start new thread.....

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.