|
Hi Ritesh,
Thanks for the advice!
I tried the convert, but I'm getting this error...
ERROR: Undefined Function 'Convert' in Expression
OleDbCommand("SELECT convert(nvarchar,NameID) + ',' + convert(nvarchar,IsMarried) AS Name_IsMarried, myConnection);
Any idea why I would get this error?
Thanks!
Ron
|
|
|
|
|
You can access more than just the 2 values of the combobox, every item shown in the combobox is a datarow (if binded to a datatable), if you need another columnvalue just get the current row with
Dim r As DataRow = Me.cboArticulos.SelectedItem
then you can access any column of the current row with r.item("Columnname")
im not sure if this is what you are looking for
Alexei AR
Theres always a better way
Look for it 
|
|
|
|
|
Hi Alexie,
Thanks for the advice, I'm new to programming and havn't tried binding yet.
I'm trying to set it up, but I use C#. Are you able to convert your code
to C#? I tried this, but it did not work.
comboBox1.DataBindings.Add("SelectedValue", myDataTable, "Store.Supplier");
//This code must be incorrect, it crashes the event (item selected)
DataRow myRow = (DataRow)myDataTable.SelectedItem;
Thanks for your help,
Ron
|
|
|
|
|
ComboBox1.DataSource = myDataTable;
ComboBox1.DisplayMember = "Name";
ComboBox1.ValueMember = "NameID";
that code is correct, you can bind it like that
Just whenever you want to acces any other value of the current row (the one in the combobox), just declare a datarow with the combobox.selecteditem:
dim R as datarow = me.combobox.selecteditem, then just access any column you need with R.item("ColumnName")
Alexei AR
Theres always a better way
Look for it
|
|
|
|
|
hi, my Date field in the database formated as 2/15/2006 11:40:46 AM . i try to manually give a date (no) but it give me error. the error come from myReader!. help me to correct, thanks
no = "2152006"
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String
myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()
Author = myReader("Author")
myReader.Close()
myConn.Close()
lblShow.Text = Subject
-- modified at 4:41 Sunday 19th February, 2006
|
|
|
|
|
First and foremost: Don't inject values into the SQL string. here's why[^]
Second: You would be better using an ExecuteScalar for this than using ExecuteReader. This is because you are only receiving one value and ExecuteScalar is designed for retrieving one value (the first column of the first row).
Thirdly: You may wish to consider layering your application. It is bad practice to have database code in the same method as something setting user interface controls. For more information search the internet for the Layering Pattern[^]
sebastian yeok wrote: my Date field in the database formated as 2/15/2006 11:40:46 AM .
No it isn't. What you are seeing is a localised version of the date coming from the SQL Server. This has nothing to do with the way it is stored. So....
Lastly, use a proper DateTime object and pass it to SQL Server as a parameter. That way you do not have to deal with the formatting of the date.
Dim no As DateTime = New DateTime(2006, 2, 15)
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String
myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE [Date] = @date";
myCmd.CommandText = strSQL
' The parameter is added so that (1) you don't inject the value into the SQL String and
' (2) you don't have to worry about the formatting of the date as the framework will
' do that for you. Notice how @date corresponds to the @date in the strSQL above.
myCmd.Parameters.Add("@date", no)
myConn.Open()
myCmd.Connection = myConn
' Use execute scalar. Remember to check for DBValueNull in case no rows were found
' and to cast the result to what ever type Author is.
Author = myCmd.ExecuteScalar()
myReader.Close()
myConn.Close()
lblShow.Text = Subject
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay,help me more.
1) How to display the "author" using the lblShow.
2) in this, it retrive one record, how about retrive more records? example SELECT * FROM Booktbl WHERE Date = @date". let say 10 records, how to display it?
3) how about retrive data between 2005/5/15 and 2006/5/15
what my problem is 2) and 3), retrive data from Booktbl between the date and display all the selected data.
very appreaciate
|
|
|
|
|
sebastian yeok wrote: 1) How to display the "author" using the lblShow.
If you are referring to how the layering pattern works then you put all your data access in one class (or a group of classes dedicated to data access) and call a method on the class. This removes the data access code from the user interface class. If you design it correctly it also means that at some point in the future you can swap out the data access classes and implement a new set of classes (that have the same public interface) that access a different database.
sebastian yeok wrote: in this, it retrive one record, how about retrive more records?
Go back to using the data reader. From the code you originally supplied you were only ever reading one column and one row from the database - so it would appear to be a good candidate for using ExecuteScalar()
sebastian yeok wrote: let say 10 records, how to display it?
I don't know enough about your intended user interface to answer that.
sebastian yeok wrote: how about retrive data between 2005/5/15 and 2006/5/15
Change you SQL to handle a date range. For example:
SELECT Columns
FROM MyTable
WHERE DateColumn >= @startDate
AND DateColumn <= @endDate
And pass in the parameters for @startDate and @endDate. Remember that a DateTime object for a date only sets the time to midnight. So, for the end date, you might want to set the time element as well to 23:59 to ensure you get results that occur on the end date.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
myCmd.Parameters.Add("@startDate", sDate)
myCmd.Parameters.Add("@endDate", eDate)
is't correct i do like this?
10 records means, retrive 10 rows of record or more.
author date
a ....
b ...
and so on
|
|
|
|
|
sebastian yeok wrote: myCmd.Parameters.Add("@startDate", sDate)
myCmd.Parameters.Add("@endDate", eDate)
is't correct i do like this?
Assuming your SQL Statement now contains a WHERE clause that uses the parameters @startDate and @endDate then I would assume it is correct.
sebastian yeok wrote: 10 records means, retrive 10 rows of record or more.
 The context of this statement has been lost. What does this refer to? (You may find it useful to use the "Quote Selected Text" button to insert a quote from the previous post so that the context of statements is not lost.)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hello,
I have made a search but could not find how to indent correctly SQL statements.
For instance;
SELECT * FROM a WHERE att1 IN ( SELECT att8 FROM b WHERE att2 = 10 AND att3 = 20 AND att4 = 30 GROUP BY att8 HAVING count(*) > 3 )
How this sample statement can be indented correctly(standard way)?
Any opinion or any article would be appriciated.
Kind Regards,
Sarp
|
|
|
|
|
I've not come across a standard way of doing this. My particular scheme is something like this:
SELECT Column1, Column2, Column3
FROM MyTable AS mt
INNER JOIN SomeOtherTable AS sot ON mt.pk = sot.fk
INNER JOIN (SELECT fk, ColumnA, ColumnB, ColumnC
FROM InnerTable
WHERE ColumnD = 10) AS it ON mt.pk = it.fk
WHERE mt.Column4 IS NOT NULL
ORDER BY Column2;
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I write my SQL like this:
SELECT
filed1,
field2
FROM
table1,
table2
WHERE
condition 1
AND
contidion 2
ORDER BY
field
LIMIT
1
DESC
Q:What does the derived class in C# tell to it's parent?
A:All your base are belong to us!
|
|
|
|
|
Colin Angus Mackay wrote: My particular scheme is something like
I do the same here. My old Oracle book from my undergraduate database course does it this way.
PJC
|
|
|
|
|
Thanks all for your opinions.
|
|
|
|
|
hi, i have to question here hope someone can help me.
1) INSERT INTO table2 (col1, col2, col3....)
SELECT col1,col2, col3
FROM table1
WHERE someting.....
the select statement used the .ExecuteReader and the insert statement used .ExecuteNonQuery how to join this both used in question (1)? guide me step by step.
2) by refer to question (1), how to move 1000 or more rocord from table1 to table2 ?. how to i store the 1000 records from table1. is there any solution to this problem.
anyone help appreciate! thanks
|
|
|
|
|
sebastian yeok wrote: the select statement used the .ExecuteReader and the insert statement used .ExecuteNonQuery how to join this both used in question (1)?
A better way of looking at is is:
I expect results back so I use ExecuteReader()
I don't expect results back so I use ExecuteNonQuery()
The SQL you supply is correct, you don't expect results back to the calling application. You therefore use ExecuteNonQuery()
sebastian yeok wrote: how to move 1000 or more rocord from table1 to table2 ?. how to i store the 1000 records from table1. is there any solution to this problem.
I don't understand what the problem is that you are having. In (1) you already manage to copy the rows from one table to another. If you don't want the rows to continue to exist in the originating table you perform a DELETE operation with the same criteria in the WHERE clause.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi, I have two identical tables in 2 seperate databases. 1 is the "central" DB and its contents can change. Basically I want to write a poece of code to run periodically to ensure the contents of table 2 are up to date with that of table 1. Id imagine this is a relativaly common task so I was wondering are there any smart ways of doing it or any existing code knocking around? Thanks in advance,
|
|
|
|
|
You could try something like this:
SELECT a.pk, b.pk
FROM FirstDatabase.dbo.TableName AS a
FULL OUTER JOIN SecondDatabase.dbo.TableName AS b WHERE a.pk = b.pk
WHERE a.pk IS NULL
OR b.pk IS NULL
pk = primary key, if you have a compound key then you will need all the columns that make up the primary key.
The results of the query should (I haven't tested it) return any rows that exist in one database, but don't in the other.
If you want to return all rows that have differences then you might want to add to the WHERE clause:
OR a.column1 <> b.column1
OR a.column2 <> b.column2 ...and so on for each of the columns.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Sorry I should have mentioned the two DB's are actually running on 2 different servers
|
|
|
|
|
That's okay. The naming convention extends to servers. Just add in the server name like this:
ServerName.DatabaseName.SchemaName.TableName
SchemaName is dbo, unless you've set it up otherwise.
You will also have to link the two servers together. You might find this useful: MSDN: Configuring Linked Servers[^]
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I'm having performance issue with this ASP.NET app. From SQL Server log there're MANY MANY log which resembles the following:
SQL Server log: Login succeeded for user 'APPL_ACCOUNT'. Connection: Non-Trusted.
I'm talking thousands in one morning. And I am suspecting that this is the cause of the performance degradation. I thought when you
<br />
conn.Open();<br />
ADO.NET draws from ADO.NET connection pool using existing connections in the pool and you don't "login" again? Is this assumption right? I ran a very very simple test:
<br />
using System;<br />
using System.Data;<br />
using System.Data.SqlClient;<br />
<br />
static void Main(string[] args)<br />
{<br />
String s_conn = "Data Source=127.0.0.1;Initial Catalog=pubs;User Id=sa;Password=secret;Max Pool Size=80;Min Pool Size=30;";<br />
IDbConnection oconn;<br />
Int32 i;<br />
<br />
oconn = new SqlConnection (s_conn);<br />
for(i=0; i<100; i++) <br />
{<br />
oconn.Open();<br />
<br />
oconn.Close();<br />
}<br />
<br />
return;<br />
}<br />
This did NOT generate a bunch of "Login succeeded" in my SQL Server's Server Log. But then, the surprise was, there was NOT even ONE "Login succeeded" registered.
Btw, I'm using NHibernate... And the way NHibernate open an ISession (corresponds to a IDbConnection) is as follows:
<br />
Dim _nhibernate_conn_factory ISessionFactory = BuildFactory() 'This is time consuming so we only create it once...<br />
<br />
Public Function GetWarehouseNHibernateSession() As ISession Implements IWarehouseConnectionManager.GetWarehouseNHibernateSession<br />
Dim conn As ISession<br />
Dim maxRetry, retryFreq As Int32<br />
<br />
Try<br />
<br />
conn = nhibernate_conn_factory.OpenSession()<br />
<br />
Catch ex As System.Data.SqlClient.SqlException<br />
'handle the exception<br />
Catch ex As Exception<br />
'handle the exception<br />
End Try<br />
<br />
Return conn<br />
End Function<br />
My finding is, for each "OpenSession" there's a corresponding entry in SQL Server's log:
"SQL Server log: Login succeeded for user 'APPL_ACCOUNT'. Connection: Non-Trusted."
Is this normal? On one of my page there's 35 OpenSession - seems like this is what's slowing down the application. Advice? Thanks Thanks!
If you want to look deeper into NHibernate's code, look here
ADO.NET connection pooling REF: http://www.15seconds.com/issue/040830.htm
NHibernate REF: http://nhibernate.sourceforge.net/NHibernateEg/NHibernateEg.Tutorial1A.html
|
|
|
|
|
I just finished reading about NHibernate a couple of days ago (I've only played with it for a while, haven't used it myself).
From my understanding, you need to use and keep just one session object for each user (and each page) that is logged in. All the samples I saw only use one session, and for updating the sample either loads from the database before updating, or stores the session in the ASP.Net session variable for later use.
Apologies if it's a bit unclear. I'm a bit tired after a weekend full of wedding (not mine) and moving out.
Edbert
Sydney, Australia
"A day without sunshine is like, you know, night."
|
|
|
|
|
Hey thanks. Just one question first. SessionImpl implements ISerializable. Do you think that it will be compatible with NLB (Network Load Balancing)+StateServer if I cache NHibernate.ISession/SessImpl in HttpSession?
|
|
|
|
|
I reckon it can if you use MSSQL Server for the HttpSession, but if you use in-proc session with multiple servers I'm not sure.
You don't actually need to cache the NHibernate.ISession itself.
I saw a different implementation which requery the database for the previous state before updating with data from user, and then commiting to the database (it was a sample on how to use NHibernate for ASP.Net, I don't like the sample for having to requery the database only for that).
There is a good BugTracker[^] project using ASP.Net 2.0 and NHibernate that you might want to look at for more real-life sample of using NHibernate.
Edbert
Sydney, Australia
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
