?
Solved

How To do a  search and Output to a datagrid Using VB.net

Posted on 2006-04-18
12
Medium Priority
?
266 Views
Last Modified: 2010-04-23

Hey guys how is everyone today?. Well I'm facing a small problem with my VB connection to Access.
I'm sure its simple, but I just can't see the error or why the error is occuring.

Anyhow my objective is to have a user input an ID from the 1 textbox, then have it Query to the Access DB
and display the information to a datagrid.

Keep in mind Im a noob with this kinda stuff since I barely know how to insert data to Access LOL

Well anyhow here is the code and tell me what you guys think of it?


The error Im getting is this ///// * An unhandled exception of type 'System.NullReferenceException' occurred in AlphaDesign.exe

Additional information: Object reference not set to an instance of an object.*///////


The code is this :





        If txtUserID.Text = "" Then
            MessageBox.Show("User ID Text Box is Empty!!", "Error", MessageBoxButtons.OK _
           , MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
            lblUserIDError.Visible = True

        Else
            lblUserIDError.Visible = False

        End If


        '/////////////////////////////Here we will now try to FIND first the USer

        Dim connString As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = X:\members.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim insertCmd As OleDbCommand ' This stores my Command for the DB


        Dim UserID = txtUserID.Text.Trim

        'Opening the DB connection
        myConnection.ConnectionString = connString
        myConnection.Open()

        'NOw to create a Select Command to search the DB
        'Trying to put a search command with the user input from the text box

        With insertCmd
            .Connection = myConnection                   //////****This is where the error occurs/////
            .CommandText = "Select User_ID From UserTable" & _
                           "Where User_ID = UserID "
            .CommandType = CommandType.Text
           
            .Parameters("@User_ID").Value = UserID  ' This is the textBox.



        End With

        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = insertCmd




        Try ' The reason we use try is to see if the database can open and what can happen

            insertCmd.ExecuteNonQuery()
            Adapter.Fill(DataSet)

            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

           
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        insertCmd.Dispose()
        insertCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing
        myConnection.Close()

///////////////////////////////////////////////////////////


Also I just wanted to know if my SQL is good or is that also messed up  :(
Thankyou in advance as always and please let me know what you guys think.

0
Comment
Question by:masterat03
  • 6
  • 4
  • 2
12 Comments
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16481583
Dim insertCmd As New OleDbCommand

You didn't create the instance, just specified the type
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16481609
Oh, and also:
"Select User_ID From UserTable" & _
                           "Where User_ID = UserID "
should be:
"Select User_ID From UserTable" & _
                           "Where User_ID  = " & UserID

that is if user_ID is a number

if it's a string:
"Select User_ID From UserTable" & _
                           "Where User_ID ='" & UserID & "'"


or also possible : String.Format("SELECT User_ID FROM UserTable  WHERE User_ID = '{0}'", UserID)
assuming it's a string again


0
 

Author Comment

by:masterat03
ID: 16481791
Yes it is a string, I just wanted to know out of curiosity why would  '///Dim insertCmd As OleDbCommand ' This stores my Command for the DB
that effect the connection to the database?.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:masterat03
ID: 16481887
Well I can see now why I set it like this...perhaps I could be wrong

Dim insertCmd As OleDbCommand = New OLEDB Command

that seem to fix the error plus what I did was I changed the other code
to do this :

"Select User_ID From UserTable" & _
                           "Where User_ID  = " & UserID

Which also seem to work, now the only problem is that I can't receive anything on the grid... or any output what so ever

I will try the other SQL query to see if it works , being that User ID is a string , although it does accept numbers, but as string
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16484469
If it's a number in your database, you better provide a number here, if it's a string, provide a string :D

Dim insertCmd As OleDbCommand = New OLEDB Command i
s the same as
Dim insertCmd As New OleDbCommand
0
 
LVL 6

Expert Comment

by:cubixSoftware
ID: 16485320
Hi

Try this...


        Dim connString As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = X:\members.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim insertCmd As OleDbCommand = New OleDbCommand  ' This stores my Command for the DB


        Dim UserID = txtUserID.Text.Trim

        'Opening the DB connection
        myConnection.ConnectionString = connString
        myConnection.Open()

        'NOw to create a Select Command to search the DB
        'Trying to put a search command with the user input from the text box

        With insertCmd
            .Connection = myConnection                   //////****This is where the error occurs/////
            .CommandText = "Select User_ID From UserTable " & _
                           " Where User_ID = @User_ID "
            .CommandType = CommandType.Text
           
            .Parameters.Add(New OleDbParameter"@User_ID", OleDbType.Char, 10))  ' change length to correct value
           .Parameters("@User_ID").Value = UserID  ' This is the textBox.



        End With

        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = insertCmd




        Try ' The reason we use try is to see if the database can open and what can happen

            Adapter.Fill(DataSet)

            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

           
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        insertCmd.Dispose()
        insertCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing
        myConnection.Close()

///////////////////////////////////////////////////////////
0
 

Author Comment

by:masterat03
ID: 16488488
Hey whats up Cubix...well I tried the correction you told me , but I still can't get data to go into the Grid.
Could there be something else that I'm missing.

Also I looked at the SQL code you corrected and it makes more sense that way than the other way I had it, because before
the WHERE clause would have nothing in it and now including the @USER_ID to it makes sense because of the paramater(@USER_ID).Value = UserID
is being added to it.

Little by little I'm getting the hang of this...also is there a book you can recommend for SQL but using VB.net . Because I notice I have alot of trouble
when it comes to that.


Anyhow I tried to put it like you said :

////////////////////////////////

Dim UserID = txtUserID.Text.Trim

        'Opening the DB connection
        myConnection.ConnectionString = connString
        myConnection.Open()

        'NOw to create a Select Command to search the DB
        'Trying to put a search command with the user input from the text box

        With insertCmd
            .Connection = myConnection
            .CommandText = "Select User_ID From UserTable" & _
                           "Where User_ID =@User_ID"
            .CommandType = CommandType.Text

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters("@User_ID").Value = UserID ' This is from the text box




        End With

        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = insertCmd




        Try ' The reason we use try is to see if the database can open and what can happen


            Adapter.Fill(DataSet)
            ' insertCmd.ExecuteNonQuery()

            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black


        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        insertCmd.Dispose()
        insertCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing

////////////////////////////////

But there is no response from my Database to the Grid. I triple checked that the value I insert to the text box is actually in the database already.
Example either ( a number or string ) so I know that the user_ID has these values already inserted in the database.

Please let me know what you think it could be or even anyone else that may have an idea of why this is not responding correctly I would highly appreciate
your input as well :).

0
 
LVL 6

Accepted Solution

by:
cubixSoftware earned 2000 total points
ID: 16488939
Hi

becareful with

          .CommandText = "Select User_ID From UserTable" & _
                           "Where User_ID =@User_ID"

as there needs to be a space between "UserTable" and "Where" within your sql statement (see my previous code)

If you step through the code, at the point of Adapter.Fill(Dataset) can you check the value of the parameter @User_ID to make absolutely sure it has a value within it.

Also immediately after that statement, check the Dataset.Tables(0).Rows.Count value to see how many you have returned.
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16489007
 .Connection = myConnection
            .CommandText = "Select User_ID From UserTable" & _
                           "Where User_ID =@User_ID"
            .CommandType = CommandType.Text

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters("@User_ID").Value = UserID ' This is from the text box

Sorry, but this part doesn't make much sense to me.
It's not a stored procedure you are executing, but just a select query.
Keep it simple, use the select query.
If your data is not returning anything, copy the commandText you created in a query analyzer and see if you actually have data.

What you need to do:

Use a function like this:
 Public Function GetDataTableForQuery(byVal connectionString As String, ByVal selectStatement As String) As DataTable

        'initialize
        Dim cmd As OleDbCommand = Nothing
        Dim da As OleDbDataAdapter = Nothing
        Dim ds As DataSet = Nothing
Dim db as OleDbConnection


        Try
           db= new OleDbConnection (connectionString )
            cmd = New OleDbCommand(selectStatement, db)
            da = New OleDbDataAdapter(cmd)
            ds = New DataSet
            da.Fill(ds)
            'return the first table
            Return ds.Tables(0)
        Catch ex As Exception
            'might implement real error handling here
        Finally
            If Not da Is Nothing Then da.Dispose()
            If Not cmd Is Nothing Then cmd.Dispose()
           if not db is nothing then db.Close()

        End Try
        'no success, no data :S
        Return Nothing
    End Function



Feed this function with the query I showed you before and attach the returning datable to your datasource.
Let me know if you have any progress.
0
 

Author Comment

by:masterat03
ID: 16489704
Hey Cubix You were right again on the Money lol.
It was the spaces in the  SQL Statement


 .CommandText = "Select User_ID From UserTable" & _
                           "Where User_ID =@User_ID"


As soon as I space it out a bit it work like a charm :)

Thankyou again for your help and you to PockyMaster

As you know Cubix Im still a big noob when it comes to ADO and connecting access to VB.net
I took your advice to deal with datagrid and I'm still trying to get the hang on all the stuff required to make the technology work for me :).

As you can see from my last topic I 'm trying to jump from 1 topic to another because I really want to learn this thing as best as possible
The last one if you remember was inserting 1 data row into Access

This one is dealing with retrieving the data and displaying it

I will jump into deletion and updating soon....Although you did say you can do deletion with dataset and updating...how I still dont have a clue??? LOL

Keep a look out for the other 2 topics because I'm sure I will have trouble with deletion and Updating...but as always I will try to keep it
simple to insert just 1 box and 1 row like that many NOOBS!! like myself can educate themselves and try to understand
how this technology works!!

As always You guys are on top of your game!!

Take care and thankyou very very much.

:)

0
 

Author Comment

by:masterat03
ID: 16491271
Hey I was also going to say I decided to switch a few things...instead of looking for User_ID I switch it to their last name, but I notice
it doesnt work...the user ID is primary key, but when I tried another field...well nothing happen..

Is are these searches dependent on Primary Keys only?
0
 

Author Comment

by:masterat03
ID: 16491356
Opps Ok nevermind I fix that one lol
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question