Page 1 of 1
Just a follow up with Customized SQL
5 Replies - 7910 Views - Last Post: 08 August 2007 - 07:11 AM
Posted 07 August 2007 - 09:43 AM
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)
Replies To: OledbDataadapter Problem
Re: OledbDataadapter Problem
Posted 07 August 2007 - 11:02 AM
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.
This post has been edited by cimpercee: 07 August 2007 - 11:05 AM
Re: OledbDataadapter Problem
Posted 07 August 2007 - 12:20 PM
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)
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
''' <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
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.
Number of downloads: 297