Hmm...the MSDN article that I read only mentioned ".NET Framework Data Provider for SQL Server provides connection pooling automatically for your ADO.NET client application", so I assumed that it's only for SQL Server. Thanks for the info :)

Well, some people also suggested that there's a bug when calling memo fields. They recommend either getting a column for every 256 chars (using MID) :wtf: Others recommend calling the GetChunk() function. It takes a long as argument, so you should be able to get everything using it. Here's a sample of the function called in ASP:

set rst = conn.execute("select job_title, job_descr from jobs")
strTitle = rst.Fields("job_title").value & ""
vChunk = rst.Fields("job_descr").GetChunk(4000)
strDescr = vChunk
Do Until IsNull(vChunk) = true
vChunk = rst.Fields("job_descr").GetChunk(4000)
vDescr = vDescr & vChunk
Loop

Hope it helps! :-D

So I suppose that also depends on the database server you're connecting to then? I'm assuming Access won't have such a luxury... I'll try to implement Open before doing a whole bunch of sql operations then. Thanks Colin! :)

I'd say ADO.NET is much more different to ADO than ADO to DAO. You're better off starting with ADO.NET right away if you don't need ADO. Have a look at some MSDN articles on starting with ADO.NET, it's simple enough that you'll understand it in no time.

Did you use aggregate functions (SUM, AVG, MAX, COUNT, etc) in your sql statement? Also a memo field will be truncated to 255 characters in a query that uses sorting (ORDER BY).

I have something I want to clarify. I did a .NET project some time ago and I'm not sure I remember this correctly, but I don't think I called conn.Open() whenever I want to fill a dataset. I think I read somewhere that calling dataAdapter.Fill() will open the connection and close it right away after it fills the dataset. If for example I have 100 datasets to fill, would it make a difference if I call conn.Open() before I tried to fill the datasets and then call conn.Close(), or should I leave it to the dataAdapter.Fill() to open and close the connection? Which one is more efficient? Does it depend on the database server (connection pooling, etc)? Thanks, Edbert P.

That is most likely a corrupt Normal.dot or Word trying to recover a previously damaged document. In addition to deleting the Normal.dot, try calling your Word with the /a switch. It kinda resets the settings back. e.g. "C:\Program Files\Microsoft Office\Office10\winword.exe" /a Hope it helps, Edbert

Shouldn't it be "COL2 IS NULL" ?

Erm...you're about 10 years behind. Today's popular music is Britney Spears, Christina Aguilera, Jessica Simpson, Delta Goodrem, Human Nature, Black Eyed Peas, Pete Murray, Norah Jones, etc. There are also remnants of the past that have somehow survived like Justin Timberlake and Darren Hayes. And how can we not include the by-products of "<Insert country here> Idols" everywhere. I'd say the list I gave is pretty mixed. Some are good (Pete Murray, Norah Jones), some are..well...only good when I was a teenager. But I would blame the popularity of certain music to the poor taste of teenagers. After all, they're the ones that voted on Channel V and MTV a lot, watched the Idols and bought the CDs ;P

Correct me if I'm wrong, but I think I know what you want: You want to set a specific text into the combobox (from one of the items in the list) through code and you don't want users to edit the combobox text. There are several ways you can do this: 1. Use this function to get and set the index of the item (instead of the text) you want to select in the combobox while keeping your combobox style to 2:

Public Function GetComboBoxIndex(hWnd As Long, SearchKey As String, Optional FindExactMatch As Boolean = True) As Integer

'Parameters:
' hWnd - the handle to the ComboBox control.  Usage:  Combo1.hWnd
' SearchKey - item that you would like to search for.  Can be any string - case doesn't matter when searching
' Optional FindExactMatch - Default is True.  Pass False to find a partial match
'Return:
' Returns the index of the found match.  If the match is not found, -1 is returned
'Usage:
' Combo1.ListIndex = GetComboBoxIndex(Combo1.hWnd, "Test Item")
' Combo1.ListIndex = GetComboBoxIndex(Combo1.hWnd, "Test Item", False)

If FindExactMatch Then
    GetComboBoxIndex = CInt(SendMessage(hWnd, CB\_FINDSTRINGEXACT, -1, ByVal SearchKey))
Else
    GetComboBoxIndex = CInt(SendMessage(hWnd, CB\_FINDSTRING, -1, ByVal SearchKey))
End If

End Function

2. Similar to #1, you can iterate through the combobox item and compare the text to get the index, but this is inefficient. 3. You can implement the AutoFill function on the KeyPress event and IsComboboxItemValid on the Validate event for the combobox. This enables user to type into the combobox (using style 0) but also makes sure that whatever they type will be in the items list.

Public Sub AutoFillCombobox(ByRef comboBox As comboBox, ByRef KeyAscii As Integer)

Dim intIndex As Integer
Dim intRemainder As Integer
Dim strFullText As String
Dim strPartialText As String

If comboBox.Locked Then
    KeyAscii = 0
    Exit Sub
End If

If KeyAscii >= 33 And KeyAscii <= 126 Then
    comboBox.SelText = Chr(KeyAscii)
    strPartialText = comboBox.Text
    
    intIndex = GetComboBoxIndex(comboBox.hWnd, strPartialText, False)
    If intIndex > -1 Then
        strFullText = comboBox.List(intIndex)
        intRemainder = Len(strFullText) - Len(strPartialText)
        If intRemainder > 0 Then
            comboBox.SelStart = Len(comboBox.Text)
            comboBox.SelText = Right(strFullT

True, but average Joe doesn't understand the difference and can't be bothered to get a better one (especially if they have to pay extra) if what they have works. This is what kills other companies. I'm sure someone will come up with Firefox even if Microsoft didn't add IE to Windows. And a world without IE is a better world to me. Note: this message is written while using IE 6.0 ;P

1 or 5. I vote only when I really agree or disagree with something.

Just create a function that replaces all your ' (single apostrophe) with '' (TWO single apostrophe), i.e. [Joe O'Connell] becomes [Joe O''Connell] However, I do recommend using Data Adapters and SQLCommands to pass your parameters so you don't need to worry about this anymore. It does make my life easier aside from being safer from SQL injection.

Try using UNION to solve the INNER JOIN condition. Here's how it should be like: SELECT ... FROM ... INNER JOIN rtiC ON b.cenid = c.cenidr AND b.polyid = c.polyidr ... UNION SELECT ... FROM ... INNER JOIN rtiC ON b.cenid = c.cenidl AND b.polyid = c.polyidl ...

Go here http://www.connectionstrings.com/[^] and check whether you specified the correct connection string or not.

I'm not particularly sure that this is what you want. You want to update or insert into the test_user table depending on the records in test_answer, right? If that is the case, this might be the answer:

INSERT INTO test_user (user_id, time_answered)
(
SELECT user_id, SUM(answered)
FROM test_answer
WHERE answered = 1 AND user_id NOT IN (SELECT user_id FROM test_user)
GROUP BY user_id
);

UPDATE test_user
SET time_answered = SUM(answered)
FROM test_answer
WHERE test_answer.user_id = test_user.user_id;

You can try passing a string with the user id's and use it in the WHERE clause of your statement, e.g.

SELECT *
FROM tblUsers
WHERE UserID IN (SELECT [Value] FROM dbo.Split(@ListOfUserIDs,','))

The code above uses the user defined function Split, which splits a string by the delimiter and converts it into a table. Below is the user defined function that you can modify as needed.

CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)

)
AS
BEGIN

WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN 

INSERT INTO @RtnValue (value)
SELECT 
    Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1))) 

   Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END

INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))

RETURN

END

There is another way using the EXEC statement in your stored procedure to execute the SQL, and you don't need the split function for this. Some users argued about the efficiency and safety of using EXEC statement, but it's up to you to decide.

DECLARE SqlStr varchar(1000)

SET SqlStr = 'SELECT * FROM tblUser WHERE UserID IN (' + @ListOfUserID + ')'
EXEC (SqlStr)

Both code will work even if you only pass one ID. I hope it helps ;).

If you mean connection string, you can find it here http://www.connectionstrings.com/[^]

[Attention: Possible Spoiler] Did you mean when he sang Ricky Martin's Living La Vida Loca? Plus after the credit you can see something interesting. Kids are gonna ask, "Mommy, where did donkey and dragon's babies come from?"

Like Mike said, using parameters is best way to do it. But if you want to build your own SQL statement, try to format the date into yyyy-mm-dd or yyyy/mm/dd format in the SQL statement, e.g. for Access it is #1999/05/24# By using this you'll: 1. Remove ambiguity between date and month. 2. Remove error when passing date into SQL statement. Cheers, Edbert P.