[cimpercee]

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)

[PsychoCoder]

Please post your code here, I cant help if I cant see what you're doing

This post has been edited by PsychoCoder: 07 August 2007 - 09:49 AM

[cimpercee]

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: 07 August 2007 - 11:05 AM

[cimpercee]

here is a sample project.


thanks for your time

Attached File(s)

•  test1.zip (175.76K)
  Number of downloads: 210

[PsychoCoder]

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)

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.

Happy Coding!

Attached File(s)

•  WindowsApplication1.zip (170.32K)
  Number of downloads: 196

[cimpercee]

thank you for the time. you just didnt solve my problem but opened my mind in a better one.

i really appreciate.