Visual Basic

22-Jan-10 5:51

What I try to do is reading any existed row in the table 'Info' (normally only 1 row existed) then update that row immediately, but I have an error when execute at cmd2.ExecuteNonQuery()

- However, if I move the If room_N > 0 Then out of the While loop & place it under lrd.Close() then it works OK! Can some one know the reason?

Dim ra As Integer
Dim cmd As New SqlCommand

Dim cnt_del As Integer
Dim room_N As Integer
Dim str As String

con.ConnectionString = strOpen
con.Open()

cmd.Connection = con
cmd.CommandText = "SELECT * FROM Info"
Dim lrd As SqlDataReader = cmd.ExecuteReader()

If lrd.HasRows Then

    Try
        'Dim lwr As SqlDataReader

        While lrd.Read()
            ListBox1.Items.Clear()

            str = lrd.GetValue(0).ToString() + ","
            str = str + lrd.GetValue(1).ToString() + ","
            str = str + lrd.GetValue(2).ToString() + ","
            str = str + lrd.GetValue(3).ToString() + ","
            str = str + lrd.GetValue(4).ToString() + ","
            str = str + lrd.GetValue(5).ToString()+ ","
            str = str + lrd.GetValue(6).ToString()

            ListBox1.Items.Insert(cnt_del, str)
            cnt_del = cnt_del + 1

            ListBox_Available.Items.Clear()
            room_N = SearchAvailableRooms(lrd.GetValue(2), lrd.GetValue(3), lrd.GetValue(4), lrd.GetValue(0), lrd.GetValue(1))

            If room_N > 0 Then
                cmd.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
                cmd.ExecuteNonQuery()
            End If

        End While

        lrd.Close()


    Catch ex As Exception
        cnt_error = cnt_error + 1
        TextBox_Error.Text = cnt_error.ToString()
    End Try
End If

con.Close() 'Whether there is error or not. Close the connection.

DJ Matthews22-Jan-10 6:01

DJ Matthews

22-Jan-10 6:01

What is the error you are getting? Just telling us you have an error won't help.

Curious 200922-Jan-10 6:33

22-Jan-10 6:33

I forgot to display the error message, here it is:

Error while connecting SQL Server. There is already an open DataRedaer associate with this command which must be close first!

However, since I have to update that row before scan the next row (while loop), I can't close it ... may I need another cmd2.ExecuteNonQuery()? But then I need to connect database again?

Is there better command to avoid the abobe problems?

loyal ginger22-Jan-10 6:31

loyal ginger

22-Jan-10 6:31

When you use a reader, the connection is not closed while the reading is going on. Therefore you cannot use a different command associated with that connection to do the update.

Try to create a different connection and command to do the update.

Curious 200922-Jan-10 6:39

22-Jan-10 6:39

You are right, however my database is already open & connect ... now I have open & connect anothe one with the same database?

con.ConnectionString = strOpenSQLDatabase
con.Open()

con2.ConnectionString = strOpenSQLDatabase
con2.Open()

Is there other way around?

loyal ginger22-Jan-10 7:10

loyal ginger

22-Jan-10 7:10

Since nobody else offers any new ideas, I will post the modified version here, just for your information:

        Dim ra As Integer
        Dim cmd As New SqlCommand

        Dim cnt_del As Integer
        Dim room_N As Integer
        Dim str As String

        con.ConnectionString = strOpen        
        con.Open()

        cmd.Connection = con
        cmd.CommandText = "SELECT * FROM Info"

	'con2 should be declared somewhere
        con2.ConnectionString = strOpen        
        con2.Open()

	'cmd2 should be declared somewhere
        cmd2.Connection = con2
        Dim lrd As SqlDataReader = cmd.ExecuteReader()

        If lrd.HasRows Then

            Try
                'Dim lwr As SqlDataReader

                While lrd.Read()
                    ListBox1.Items.Clear()

                    str = lrd.GetValue(0).ToString() + ","
                    str = str + lrd.GetValue(1).ToString() + ","
                    str = str + lrd.GetValue(2).ToString() + ","
                    str = str + lrd.GetValue(3).ToString() + ","
                    str = str + lrd.GetValue(4).ToString() + ","
                    str = str + lrd.GetValue(5).ToString()+ ","
                    str = str + lrd.GetValue(6).ToString()

                    ListBox1.Items.Insert(cnt_del, str)
                    cnt_del = cnt_del + 1

                    ListBox_Available.Items.Clear()
                    room_N = SearchAvailableRooms(lrd.GetValue(2), lrd.GetValue(3), lrd.GetValue(4), lrd.GetValue(0), lrd.GetValue(1))

                    If room_N > 0 Then
                        cmd.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
                        cmd.ExecuteNonQuery()
                        cmd2.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
                        cmd2.ExecuteNonQuery()
                    End If

                End While

                lrd.Close()


            Catch ex As Exception
                cnt_error = cnt_error + 1
                TextBox_Error.Text = cnt_error.ToString()
            End Try
        End If

        con.Close() 'Whether there is error or not. Close the connection.
	con2.Close()

I really don't know if there is an alternative to this. Sorry!

Curious 200922-Jan-10 7:35

22-Jan-10 7:35

I will use your update codes

There is an alternative, but I don't know which one is better regarding of process speed (considering there are many rows existed)

1. I move the update If room_N > 0 Then outside the while loop, in the loop I will put LOOP's str values into a string array, when Loop done ---> lrd.Close()

2. Then I use a While Loop to Update all rows from the array

*) This will avoid Open/Connect twice & Close twice ... but might it be a clumsy programming?

Thanks for help Smile | :)

loyal ginger22-Jan-10 7:44

loyal ginger

22-Jan-10 7:44

If you use transactions on the second connection, the performance will be improved. I don't know how much improvement you will get. On a different database engine, I once improved the performance of a bunch of updates by over 100 times just by putting them into transactions.

Your solution of saving the stuff into an array and updating after the loop is also good.

Good luck!

Curious 200922-Jan-10 8:49

Database record to textboxes

22-Jan-10 8:49

Then I will try both methods, the latter is just for curiously

Thanks & Wink | ;)

offroaderdan22-Jan-10 5:06

offroaderdan

22-Jan-10 5:06

Hi experts,

I have read that using a datasource with ADODC1 you can use text boxes to display the database.

I read that all you have to do is on the text box you just change the properties, datafiled and data source

so it would like
Data field = ID
and
DataSource = Adodc1

however the textbox doesn't display anything? THe database works fine, can anybody help?

Re: Database record to textboxes

Eddy Vluggen23-Jan-10 5:48

How can I import table from one database to another thru a from in VB.net

23-Jan-10 5:48

VB.NET

' Bind a control at run time.
txtFirstName.DataField = "FirstName"
Set txtFirstName.DataSource = Adodc1
...
' Unbind it.
Set txtFirstName.DataSource = Nothing

Copied from an article[^] on databinding Smile | :)

I are Troll Suspicious | :suss:

waner michaud22-Jan-10 3:30

Re: How can I import table from one database to another thru a from in VB.net

22-Jan-10 3:30

Hi,
Can Anyone guide me with scenario below:

I have two applications A and B with its respective database. One table is the same by both applications. For example Officer Profile table. In application A, Officer Profile table get updated very often.
So I have a Connection button in application B to get the updated records from Application A ..
Once connected, I want to show all available tables. and I want to import a selected table.
I want to have a restriction whereas if table is not resided on both databases it cannot be imported..

Steps are:
Connection->select tables(ex.Officer Profile table)->Import table(ex.Officer Profile table)

Now Officer Profile table for database in application B get updated.

Hope I'm clear with my explaination..

Thanks in advance

Eddy Vluggen23-Jan-10 5:04

Re: How can I import table from one database to another thru a from in VB.net

23-Jan-10 5:04

You want to synchronize tables that reside in different databases? What database are you using?

SQL Server[^] has good support for replication Smile | :)

I are Troll Suspicious | :suss:

waner michaud23-Jan-10 7:23

Re: How can I import table from one database to another thru a from in VB.net

23-Jan-10 7:23

Hi,
Thanks for replying..
Visual Studio 2005 and Sql Express

Eddy Vluggen24-Jan-10 1:36

Re: How can I import table from one database to another thru a from in VB.net

24-Jan-10 1:36

waner michaud wrote:
Visual Studio 2005 and Sql Express

SQL Express cannot serve as a Publisher or Distributer, only as a Subscriber[^]. There's also the Microsoft Sync Framework to consider, but I couldn't find much examples on Sql Express[^].

The alternative would be to synchronize the data manually. There's a CodeProject article here[^] that explains how this could be done.

I are Troll Suspicious | :suss:

waner michaud25-Jan-10 4:12

Re: How can I import table from one database to another thru a from in VB.net

25-Jan-10 4:12

Hi,
thanks again for the information you provided me..

Ok.for my purpose I only need to work with one table..
one table from the source and one table to destination..
destination table has no foreign key constraint..

OfficerTable on the destination database is solely there for searching..
I can do basecally anything on it such(del, alter, modify and so forth) since no constraint..

I think using a stored procedure will be ideal situation in my case..

The article is bit convoluted, with my very litle experience in sp..
can you lead me a bit so I don't get lost in a carbweb.. I do not like to waste valuable in something I am not good at..

Briefly, my cenario is as such, Open a connection, select OfficerTable from the source, send it to destination database which was the original point of initiating the connection to modify OfficerTable in destination.

Once again, I thank you..

Eddy Vluggen25-Jan-10 7:58

Re: How can I import table from one database to another thru a from in VB.net

25-Jan-10 7:58

waner michaud wrote:
The article is bit convoluted, with my very litle experience in sp..

No worries, experience comes from doing Smile | :)

waner michaud wrote:
I do not like to waste valuable in something I am not good at..

If the client wants it, then somebody has to build it. Building yields experience and valuable knowledge.

waner michaud wrote:
Briefly, my cenario is as such, Open a connection, select OfficerTable from the source, send it to destination database which was the original point of initiating the connection to modify OfficerTable in destination.

Let's make three scenario's out of that;
0) Records to be deleted from Destination database table
1) Records to be inserted into Destination database table
2) Records to be updated in the Destination database table

The link to the CodeProject[^]-article contains example sourcecode on performing those three steps using stored procedures.

I are Troll Suspicious | :suss:

waner michaud25-Jan-10 8:10

Re: How can I import table from one database to another thru a from in VB.net

25-Jan-10 8:10

Hi,
Must I create a sp for delete, insert, and update that reside in the database for the destination table and as well in the source database?

Thanks

Eddy Vluggen25-Jan-10 8:34

Re: How can I import table from one database to another thru a from in VB.net

25-Jan-10 8:34

waner michaud wrote:
Must I create a sp for delete, insert, and update that reside in the database for the destination table and as well in the source database?

According to the article, you'd only have to create stored procedures for the destination database Smile | :)

I are Troll Suspicious | :suss:

waner michaud26-Jan-10 6:11

Re: How can I import table from one database to another thru a from in VB.net

26-Jan-10 6:11

Hi,

Ok. since I need the destination table for search only. Can I just simply alter the destiantion table?

thanks..

Eddy Vluggen26-Jan-10 6:24

Re: How can I import table from one database to another thru a from in VB.net

26-Jan-10 6:24

waner michaud wrote:
Ok. since I need the destination table for search only. Can I just simply alter the destiantion table?

Would it be OK to overwrite the destination-table with the source-data? If that's the case, then DROP the destination table and do a SELECT INTO. That would create a new table, based on the data that the query returns.

If you want readonly remote access to a table in a different database from the same server, then a view could be another alternative;

SQL

CREATE VIEW [destinationDb].[dbo].[RemoteOrders]
AS 
    SELECT *
      FROM [sourceDb].[dbo].[Orders]

I are Troll Suspicious | :suss:

waner michaud26-Jan-10 8:21

Re: How can I import table from one database to another thru a from in VB.net

26-Jan-10 8:21

hi,

I am getting the concept of stored procedure a bit..I am going to create a sp to do DROP AND INSERT INTO.

which the following Should I do?

I am using dataset.
I will have a Connect_to_HNP_button. Once I establish a connection, I want to show all the tables. Select OfficerProfile_table, and then call the sp to import table.

Or on the Connect_to_HNP_button_click event call the stored procedure. Inside the sp, establish the connection, and then do DROP DESTINATION OfficerProfile_table, and INSERT INTO DESTINATION OfficerProfile_table FROM THE SOURCE..

Which way to go and how?

Thanks

Eddy Vluggen26-Jan-10 8:35

Re: How can I import table from one database to another thru a from in VB.net

26-Jan-10 8:35

waner michaud wrote:
Which way to go and how?

The second option: create a form, drop a button on it, and make it call a stored procedure. Create a table and see if you can drop it from code Smile | :)

I are Troll Suspicious | :suss:

waner michaud26-Jan-10 8:44