VB.NET School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Welcome to Dream.In.Code
Become a VB.NET Expert!

Join 300,475 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,735 people online right now. Registration is fast and FREE... Join Now!




OledbDataadapter Problem

 

OledbDataadapter Problem, Just a follow up with Customized SQL

cimpercee

7 Aug, 2007 - 08:43 AM
Post #1

D.I.C Head
**

Joined: 10 Aug, 2006
Posts: 127



Thanked: 1 times
My Contributions
I posted before regarding this on topic customized SQL.


I actually added the items on my toolbox oledbdataadapter so i could work on the commandtext. but still i got errors.

Please teach me how to do it. I am building on VB.2005 now. my database is Access 2007.

I wanted to build my own query. Not on the query builder but i dont know how to overwrite(while my program is running)


User is offlineProfile CardPM
+Quote Post


PsychoCoder

RE: OledbDataadapter Problem

7 Aug, 2007 - 08:48 AM
Post #2

Dyslexics Untie!
Group Icon

Joined: 26 Jul, 2007
Posts: 14,714



Thanked: 501 times
Dream Kudos: 11450
Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net, jQuery

My Contributions
Please post your code here, I cant help if I cant see what you're doing smile.gif


This post has been edited by PsychoCoder: 7 Aug, 2007 - 08:49 AM
User is offlineProfile CardPM
+Quote Post

cimpercee

RE: OledbDataadapter Problem

7 Aug, 2007 - 10:02 AM
Post #3

D.I.C Head
**

Joined: 10 Aug, 2006
Posts: 127



Thanked: 1 times
My Contributions
i did what you told me about oledbdataadapter but on my project i dont have that one.
i only have the tabledataadapter + bindingsource + my dataset.

wanted to change the tabledataadapter.Fill method. as default, the query on the fill method was generated.(generated sql statement)

what i want to do is to change the generated sql with mine.i am changing it depending on the user input on a textbox.if i change it on query builder, it does not function. like Select Username from Accounts where Username = '"+textbox1.text+"'. on the other hand, on vb2003, where i was using oledbconnection and oledbadapter, i can change it using oledbadapter.commandtext = mystring.on the solution explorer, when i explor my tableadapter, i see the commandtext.but when i manipulate it on code, tableadapter."i cant see the commandtext anymore here"

my application is kinda big now, but you still need a piece of it, i will make another application and just put my problem.

Thanks again.

This post has been edited by cimpercee: 7 Aug, 2007 - 10:05 AM
User is offlineProfile CardPM
+Quote Post

cimpercee

RE: OledbDataadapter Problem

7 Aug, 2007 - 10:14 AM
Post #4

D.I.C Head
**

Joined: 10 Aug, 2006
Posts: 127



Thanked: 1 times
My Contributions
here is a sample project.


thanks for your time





Attached File(s)
Attached File  test1.zip ( 175.76k ) Number of downloads: 103
User is offlineProfile CardPM
+Quote Post

PsychoCoder

RE: OledbDataadapter Problem

7 Aug, 2007 - 11:20 AM
Post #5

Dyslexics Untie!
Group Icon

Joined: 26 Jul, 2007
Posts: 14,714



Thanked: 501 times
Dream Kudos: 11450
Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net, jQuery

My Contributions
Ok, I didn't realize you had a physical TableAdapter in your application, I assumed you were doing it in the code, so I apologize for that. I personally prefer to do it all in the code not with dragging a TableAdapter to the application (the .xsd File).

If you want to do it in the code instead of through the XSD file try this, add a class to your application, name it DataAccess, (make sure to add this statement at the top of the class)

CODE

Imports System.Data.OleDb
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings


You may have to click on Project, Select Add Reference then scroll down and select those to have them in the project. Then add this function to it., for returning a BindingSource for your DataGridView

CODE

''' <summary>
''' Returns a BindingSource, which is used with, for example, a DataGridView control
''' </summary>
''' <param name="cmdSql">"pre-Loaded" command, ready to be executed</param>
''' <returns>BindingSource</returns>
''' <remarks>Use this function to ease populating controls that use a BindingSource</remarks>
Public Shared Function GetBindingSource(ByVal cmdSql As SqlCommand) As BindingSource
    'declare our binding source
    Dim oBindingSource As New BindingSource()
    ' Create a new data adapter based on the specified query.
    Dim daGet As New SqlDataAdapter(cmdSql)
    ' Populate a new data table and bind it to the BindingSource.
    Dim dtGet As New DataTable()
    'set the timeout of the SqlCommandObject
    cmdSql.CommandTimeout = 240
    dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
    Try
        'fill the DataTable with the SqlDataAdapter
        daGet.Fill(dtGet)
    Catch ex As Exception
        'check for errors
        MsgBox(ex.Message, "Error in GetBindingSource")
        Return Nothing
    End Try
    'set the DataSource for the BindingSource to the DataTable
    oBindingSource.DataSource = dtGet
    'return the BindingSource to the calling method or control
    Return oBindingSource
End Function



Then in your button click procedure change it to this

CODE

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'create your OleDb Objects
        Dim cnConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\MAOL.mdb;")
        Dim cmdCommand As New OleDbCommand()
        Dim daAdapter As New OleDbDataAdapter
        Dim dtSet As New DataTable

        'set your OledBCommand Properties
        With cmdCommand
            .CommandText = ("SELECT * FROM Accounts WHERE Username = 'Admin'") 'sql statement to execute
            .CommandType = CommandType.Text 'let it know you're executing a string not a stored procedure
            .Connection = cnConnection 'set its connection
        End With

        Try
            Dim oBinding As BindingSource = DataAccess.GetBindingSource(cmdCommand)
            'Now bind your grid
            dgvAccounts.DataSource = oBinding
            'catch any errors
        Catch ex As Exception
            'if theres an error fisplay it to the user
            MessageBox.Show(ex.Message, "Error: Retrieving Data", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub


I always suggest to do your data access in code anyways, not using the TableAdapter Control. I had to delete your original Form as it was really acting freaky, and also delete the reference to the .xls file and connection in the Settings of MyProject.

I am attaching the updated project for you to look at. If you insist on using the BindingSource Control and the .xls file I will have to look into these as I've never used them before.

Happy Coding!


Attached File(s)
Attached File  WindowsApplication1.zip ( 170.32k ) Number of downloads: 81
User is offlineProfile CardPM
+Quote Post

cimpercee

RE: OledbDataadapter Problem

8 Aug, 2007 - 06:11 AM
Post #6

D.I.C Head
**

Joined: 10 Aug, 2006
Posts: 127



Thanked: 1 times
My Contributions
thank you for the time. you just didnt solve my problem but opened my mind in a better one.

i really appreciate.
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/8/09 03:25AM

Live VB.NET Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

VB.NET Tutorials

Reference Sheets

VB.NET Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month