Hello, I have a main report and a subreport, since I want to have total from subreport displayed in the main report, I create a shared currency variable. The problem is if the datasource return empty data. I used arraylist for the datasource, because the arraylist length is zero, the datasource is also become empty. When I tried to print, the report was showing error: a number of field or currency amount field is required here. My formula is like this: Shared currencyVar saledetail_sumtotal := Sum({PoS_DataSource_clsRptSaleDetail.saledetail_total}) I tried to use If IsNull but the result is just the same Can somebody help me? :doh:

- No Signature Available -

Hello, I like to make a simple application which can execute mail-merge that I've made before using ms word. The application load that document, then I can fill the merge-field from the datagridview in vb.net. Can someone help me, I need some references or maybe final source-code. I've searched through google, but I couldn't find it. Projects that were found there using database as the datasource (not datagridview). Thanks, Indra Permana Rusli

- No Signature Available -

SQL Server 2000 Enterprise Edition

- No Signature Available -

Mika Wendelius wrote:

especially multi database joins are often problematic

I've read something about this before, it is said that the performance will be better if we create a temporary table from the table in other database first. Then we join the temporary table with the 2nd table. Is that right?

Mika Wendelius wrote:

Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)

If it so, then it is right for me to use linked-server, and I don't have any other choice of this, the only way is to optimize the query. Isn't it? Correct me if I'm wrong. ;)

- No Signature Available -

Yep, my code is exactly as the same as yours. I still don't know about pool? Before, Mika also said something about DB pooling, or pooling connection. What is that? I've read the reference but still don't understand :laugh: I only know that DB pooling is a feature in ADODB to minimize cost of opening connection, we have to set it first or what? :confused:

- No Signature Available -

I still don't know about the connection pooling Mika. Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time? Can you give me a little description about that, and what should I do to check it, or maybe a link of reference. Thanks before.

- No Signature Available -

Dear all, I have a linked-server on my database. My senior in my company make it like that, because in the future, we will have some servers to handle some applications in the company. But after we make it so and running it, the transfer is so damn slow. Is it because the linked-server? Are there any better ways to access database from multi-server in one application. Using mirror or something? If you have any reference for this, please help me. Thanks.

- No Signature Available -

Dear all, I have some problems with SQL Server 2000 that I want to ask. I am using the database with OLEDB connection that is called from VB.NET. The application that I make is an ERP software. 1. Sometimes, and it is often happened in the application, is an error saying "Connection is busy". 2. Then sometimes the application also shows a dialog box with a connection error saying "Cannot create a new transaction becase capacity was exceeded". I don't know how to solve them, I've tried to look for the answers from Google, but the only answer that I found is that I should update the service pack into the latest version. But I've checked my database, and its version is SP4. From the application itself, some people said that maybe I forgot to close the connection, but I've made only one function to fill a datatable, and I've put the Connection.Close() in the end after doing every transaction. Are there any better answer for these?

- No Signature Available -

It turns to be missing reference from my library. I have add the reference in the MDI parent project and it works. ;P

- No Signature Available -

Something like this maybe?

Me.TextBox1.SelectAll()
Me.TextBox1.Copy()

- No Signature Available -

Yep, me refers to a form. It has no problem, the problem is in here:

usc = LoadAssembly("C:\WindowsControlLibrary1.dll", "WindowsControlLibrary1.UserControl1")

- No Signature Available -

Wuaaaa thanks, I've modified my code.

Dim usc As UserControl

Try
Me.WindowState = FormWindowState.Maximized
usc = LoadAssembly("C:\WindowsControlLibrary1.dll", "WindowsControlLibrary1.UserControl1")
usc.Dock = DockStyle.Fill
Me.Controls.Add(usc)
Catch ex As Exception
MsgBox(ex.Message)
End Try

It works :laugh: Then I add uiBase.vb (UserControl) inside WindowsControlLibrary1, and made the UserControl1 inherits the uiBase. But when I was trying to call with "WindowsControlLibrary1.UserControl1" it failed to load. I've tried to call with parameter "WindowsControlLibrary1.uiBase.UserControl1" too, but it still returned me an error. Ooooh, I need help here :doh: Thanks Best Regard, Indra Permana Rusli

- No Signature Available -

Like this?

Me.ListBox1.SelectedItem = "item1"

- No Signature Available -

I made a function like this:

Public Shared Function LoadAssembly(ByVal AssemblyPath As String, ByVal ClassName As String) As Object
Dim objAssembly As Reflection.Assembly

    objAssembly = Reflection.Assembly.LoadFrom(AssemblyPath)

    Try
        LoadAssembly = objAssembly.CreateInstance(ClassName, True)
    Catch ex As Exception
        MsgBox(ex.Message)
        Return Nothing
    End Try

    If LoadAssembly Is Nothing Then
        MsgBox("Assembly : " & AssemblyPath & vbNewLine & "Class : " & ClassName)
        Return Nothing
    End If

End Function

And I call the function in the form load of the new project:

Dim frm As Form

Try
frm = LoadAssembly("C:\WindowsControlLibrary1.dll", "WindowsControlLibrary1.Form1")
Me.IsMdiContainer = True
frm.MdiParent = Me
frm.Dock = DockStyle.Fill
frm.Show()
Catch ex As Exception
MsgBox(ex.Message)
End Try

In order to make it works, I create 1 form inside the WindowsControlLibrary1.dll that I name it Form1. But when I want to add the UserControl1 inside the form, I don't know what to do next.

Dim usc As Form

Try
usc = DirectCast(LoadAssembly("C:\WindowsControlLibrary1.dll", "WindowsControlLibrary1.UserControl1"), Form)
Me.IsMdiContainer = True
usc.MdiParent = Me
usc.Dock = DockStyle.Fill
usc.Show()
Catch ex As Exception
MsgBox(ex.Message)
End Try

This script returned an error:

Unable to cast object of type 'WindowsControlLibrary1.UserControl1' to type 'System.Windows.Forms.Form'.

I need to add the UserControl1 on my project form. Is there any UserControl Container or something that I should add on my form? :doh:

But I don't want to link it when I code the new project, I want to link the project and the dll (add as reference) on runtime. Yesterday, I got a reference, telling to use Assembly.Load() Method. I've tried to use it but it only succeed when I made my first project as a WindowsApplication and create Forms inside (not UserControl). Then when I deploy the project, I get an exe file and I can use it well with my project. But when I try use the WindowsControlLibrary with a dll output, I cannot do that, the compiler showed me an error. I don't have my code right now, maybe I'll post it here later. If possible, can someone give me a reference to use the dll and get the controls inside, or maybe give me an example related to this topic. Please ;) Thanks Best Regard, Indra Permana Rusli

- No Signature Available -

If the relation is one-to-one, I think you can do something like this:

Me.cboDepartment.DataBindings.Clear()
Me.cboDepartment.DataBindings.Add(New Binding("SelectedValue", Me.cboManager, "SelectedValue"))

I assumed that you made your combobox setting like this:

cboDepartment.ValueMember = "manager"
cboDepartment.DisplayMember = "department"

cboManager.ValueMember = "manager"
cboManager.DisplayMember = "manager"

So the program will read it like this:

cboDepartment.SelectedValue = cboManager.SelectedValue
cboManager.SelectedValue = cboDepartment.SelectedValue

Thanks, I hope this can answer your question. Best Regard, Indra Permana Rusli

- No Signature Available -

Hello all, there is a problem that I want to ask: I have made 2 UserControls using Visual Basic 2005. Let's say the names are UserControl1 and UserControl2 . Both were made under the same project that I name it: WindowsControlLibrary1. On both of them, I put different components, in UserControl1 I use a DataGridView component, and in the other one, I put a TextBox and a Button or something. After scripting on both of them, I built the project and I got 1 dll file: WindowsControlLibrary1.dll. Now, I want to make new project, I create 1 MDI form. And I want to make that MDI form to be the parent of 2 MDI child form: UserControl1 and UserControl2 that I can get from WindowsControlLibrary1.dll. How can I do that, what should I do to get the controls inside the dll file. I want to use it as part of the new project. So that if i call function like: UserControl1.Show(), the UserControl will be appeared under the main form of the new project, and have all function as child form. Can someone help me? If I could do that, next I want to add the path of the dll inside SQL Server database. When I run the main form, it will read the path, and dinamically run them as the MDI child on runtime. Thanks Best Regard, Indra Permana Rusli

- No Signature Available -

I just got some SQL Query to get updated cell like below:

CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR ('Dont change the key column!!', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1

INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2

INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3

And, I want to change it into dynamic SQL since it is too much to type the column one by one:

DECLARE @COLUMN_NUMBER AS integer
DECLARE @COLUMN_TOTAL AS integer
DECLARE @COLUMN_NAME AS nvarchar(100)
DECLARE @SQL AS nvarchar(1024)

SET @COLUMN_NUMBER = 1
SET @COLUMN_TOTAL = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request')

WHILE @COLUMN_NUMBER <= @COLUMN_TOTAL
BEGIN
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request' AND ORDINAL_POSITION = @COLUMN_NUMBER)
SET @SQL =
'INSERT transaksi_requestlog(log_date, log_user, log_id, log_column)
SELECT ''' + CAST(GETDATE() AS nvarchar(20)) + ''', Inserted.request_modifiedby, Inserted.request_id, ''' + @COLUMN_NAME + '''
FROM Inserted
INNER JOIN Deleted ON Deleted.request_id' + ' = Inserted.request_id' + '
WHERE Inserted.' + @COLUMN_NAME + ' <> Deleted.' + @COLUMN_NAME
EXEC(@SQL)

SET @COLUMN_NUMBER = @COLUMN_NUMBER + 1
END

And it got an error message saying Invalid object name 'Inserted' Can anyone help me? Thanks

- No Signature Available -

Hohohoho, thanks, finally I can import it well using DTS. I am using Microsoft DTS Custom Task Object Library reference in my source code and create DTS Package with a global variable named gvDataSource in the SQL Server. Then here is my new code.

Private Function frmUploader\_upload() As Boolean
    Dim pkg As DTS.Package2

    pkg = New DTS.Package
    pkg.LoadFromSQLServer(mDBSERVER, mDBUSER, mDBPASSWORD, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag\_Default, , , , "APLIKASI3:E\_START.Sl\_Logproof\_200311")

    For Each gv As DTS.GlobalVariable In pkg.GlobalVariables
        pkg.GlobalVariables.Remove(gv.Name)
    Next

    pkg.GlobalVariables.AddGlobalVariable("gvDataSource", Me.tbFile.Text)

    Me.Cursor = Cursors.WaitCursor
    Try
        pkg.Execute()
    Catch ex As Exception
        MsgBox(ex.Message)
        Return False
    Finally
        pkg.UnInitialize()
        pkg = Nothing
    End Try

    Me.Cursor = Cursors.Default

    Return True
End Function

Best Regard, Indra Permana Rusli

I tried to follow your code and modified it with a data reader.

Public Function ExecuteQuery(ByVal query As String) As Boolean
    Dim con As System.Data.OleDb.OleDbConnection
    Dim cmd As System.Data.OleDb.OleDbCommand

    con = New System.Data.OleDb.OleDbConnection(DSN)
    cmd = New System.Data.OleDb.OleDbCommand(query, con)
    cmd.CommandType = CommandType.Text

    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        Throw ex
    Finally
        con.Close()
    End Try

    Return True
End Function

Private Function frmUploader\_upload() As Boolean
    Dim strConnection As String
    Dim strCommand As String
    Dim query As String
    Dim excelConnection As System.Data.OleDb.OleDbConnection
    Dim excelCommand As System.Data.OleDb.OleDbCommand
    Dim dr As System.Data.OleDb.OleDbDataReader
    Dim dt As New DataTable
    Dim i As Integer = 0

    strConnection = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=Excel 8.0;Persist Security Info=False", Me.tbFile.Text)
    excelConnection = New System.Data.OleDb.OleDbConnection(strConnection)
    strCommand = String.Format("SELECT \* FROM \[{0}$\]", Me.cbSheet.Text)
    excelCommand = New System.Data.OleDb.OleDbCommand(strCommand, excelConnection)

    Try
        excelConnection.Open()
        dr = excelCommand.ExecuteReader
        While dr.Read
            query = String.Format("INSERT INTO Sl\_Logproof\_200311 VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18},{19},{20},{21},'{22}',{23},{24},'{25}','{26}','{27}',{28})", \_
            ReplaceQuote(dr(0).ToString), ReplaceQuote(dr(1).ToString), ReplaceQuote(dr(2).ToString), ReplaceQuote(dr(3).ToString), ReplaceQuote(dr(4).ToString), ReplaceQuote(dr(5).ToString), ReplaceQuote(dr(6).ToString), ReplaceQuote(dr(7).ToString), ReplaceQuote(dr(8).ToString), ReplaceQuote(dr(9).ToString), ReplaceQuote(dr(10).ToString), \_
            ReplaceQuote(dr(11).ToString), ReplaceQuote(dr(12).ToString), ReplaceQuote(dr(13).ToString), ReplaceQuote(dr(14).ToString), ReplaceQuote(dr(15).ToString), ReplaceQuote(dr(16).ToString), ReplaceQuote(dr(17).ToString), IsEmptyText(dr(18).ToString, "0"), IsEmptyText(dr(19).ToString, "0"), IsEmptyText(dr(20).ToString, "0"), \_
            IsEmptyText(dr(21)