Just too many table adapters

[pachjo]

Hi,

This code does not error but simply fills the combobox with loads of rows of the name of the row type rather than the contents?

[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboNewAutoTransaction.DataSource = glb_dtTransactionDesc.Select([/SIZE][SIZE=2][COLOR=#800000]"auto_tran = 'False'"[/COLOR][/SIZE][SIZE=2])
[/SIZE]

I am trying to use one table adapter rather than creating a new one everytime I need to access the data in the table a different way

Am I going down the wrong road?

Thanks

 

[pachjo]

Aha now I see!

So firstly again thanks for the tips, excellent especially using the object browser!

The reason for the error is two fold!

1. I neglected the use of ME.

[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmDeleteVoidTo.ToString([/SIZE][SIZE=2][COLOR=#800000]"yyyyMMdd"[/COLOR][/SIZE][SIZE=2])
[/SIZE]

2. Me.dtmDeleteVoidTo is a DateTimePicker control not a DateTime variable, hence the error on the .ToString("yyyyMMdd")

Therefore that is why I probable originally used, with me. included

[I]me.dtmDeleteVoidTo.Value.Date.ToString.Substring(0, 10)[/I]

But in saying that the original problem still exists where I dont get any output from the .SELECT statement

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQLString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"void = 'True' AND tran_date >= '"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmDeleteVoidFrom.Value.Date & [/SIZE][SIZE=2][COLOR=#800000]"' AND tran_date <= '"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmDeleteVoidTo.Value.Date.ToString.Substring(0, 10) & [/SIZE][SIZE=2][COLOR=#800000]" 23:59:59'"
[/COLOR][/SIZE][SIZE=2]rwRows = glb_dtTransactions.Select(strSQLString)
MessageBox.Show(rwRows.Length.ToString)
[/SIZE]

I have tried not enclosing the dates in single quotes but get a error about missing operand after 23

If I enclose in quotes I don't get an error but dont get any output either

 

[cjard]

1. I neglected the use of ME.

Not *strictly* necessary..

2. Me.dtmDeleteVoidTo is a DateTimePicker control not a DateTime variable, hence the error on the .ToString("yyyyMMdd")

Ah. If i'd made it it would have been called dtpDelete.. rather than dtm, but there isnt a rule for prefixing

dateTimePicker.Value gets the date value the dtp represents, so its:

dateTimePicker.Value.ToString("yyyyMMddHHmmssffffff") ...

output from the .SELECT statement

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQLString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"void = 'True' AND tran_date >= '"[/COLOR][/SIZE][SIZE=2] & [/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmDeleteVoidFrom.Value.Date & [/SIZE][SIZE=2][COLOR=#800000]"' AND tran_date <= '"[/COLOR][/SIZE][SIZE=2] & [/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dtmDeleteVoidTo.Value.Date.ToString.Substring(0, 10) & [/SIZE][SIZE=2][COLOR=#800000]" 23:59:59'"[/COLOR][/SIZE]
[SIZE=2]rwRows = glb_dtTransactions.Select(strSQLString)[/SIZE]
[SIZE=2]MessageBox.Show(rwRows.Length.ToString)[/SIZE]

I have tried not enclosing the dates in single quotes but get a error about missing operand after 23

If I enclose in quotes I don't get an error but dont get any output either

I'll give you a little pointer in the modern direction, with the hope that it will change the way you do db access forever:

Dont ever use string concatenation to build an SQL. We just had a major, public financial website fall over because a guy came along and put his name in as Michael O'Connor. 6 months ago, I gave up arguing with the nitwit that wrote the site, as to why he should use parameterized statements.. he didnt, and some user input broke his code.
It would have been impossible for that to happen with a properly written SQL


Your SQL should look like this:

myDbCommand.CommandText = "SELECT * FROM table WHERE tran_date >= @start_date AND tran_date <= @end_date"


you then add parameters to the command that you run:

myDbCommand.Parameters.Clear()
myDbCommand.Parameters.Add("@start_date", DbType.DateTime)
myDbCommand.Parameters.Add("@end_date", DbType.DateTime)


now you have it set up, you never ever need to touch anything again with this statement. Just change the values and run it:

myDbCommand.Parameters("@start_date").Value = dtpFromDate.Value
myDbCommand.Parameters("@end_date").Value = dtpToDate.Value

next time you want to run it, dont clear the parameters and add them again etc.. just changes the values and run it..
-

Now that you know about stuff like this, and youre using 2.0, take a read of DW2 link in my signature, and youll go through some tutorials to a whole ne, proper way of doing database access that is good OO design too.. it will stop all the messy, ugly db code in button handlers etc that plagues vb programs the world over..

 

[pachjo]

You are indeed a star Thanks once again for the tips re SQL.

I shall now scan through my app and replace all, will take a while, but as you say........time to move on into the 'modern' way

Will let you know what happens....

 

[pachjo]

Hi,

OK so this is what I have attached to my button handler:

 Dim taTableAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM transactions WHERE void = 'True' AND tran_date BETWEEN @DateFrom AND @DateTo", glb_cnMM2007)

Dim dtTable As New DataTable

taTableAdapter.SelectCommand.Parameters.Add("@DateFrom", SqlDbType.DateTime)

taTableAdapter.SelectCommand.Parameters.Add("@DateTo", SqlDbType.DateTime)

                taTableAdapter.SelectCommand.Parameters("@DateFrom").Value = Me.dtmDeleteVoidFrom.Value.Date

taTableAdapter.SelectCommand.Parameters("@DateTo").Value = Me.dtmDeleteVoidTo.Value.Date

taTableAdapter.Fill(dtTable)

MessageBox.Show(dtTable.Rows.Count.ToString)

Now this works fine and allows me to deal with the records that I want, however it does require me to create a new tableadapter, datatable and setup etc.

It does not allow me to usethe previous attempt to obtain an array of datarows from an existing tableadapter?

Also if i hav my main tableadapter and i start adding parameters this will modify the tabladapter so it does not remain intact for the rest of the application.

therefore should i be using independant tableadapters for each starnd of processsing?


thanks