An "Import" Feature - alas - so close!

Andrew.Berry · October 24th, 2007, 02:41 AM

I'm still having problems with this.

Here is the vb code for the button click:

Code:

        Dim strConnection As String
        strConnection = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("FSPConnectionString").ConnectionString
        strConnection = "Data source=(192.168.1.101);database=tharFSP;User ID=sa;Password=tharSQL"
        Dim MyConnection As SqlConnection

        MyConnection = New SqlConnection(strConnection)

        Dim cmdTest As New SqlDataAdapter("dbo.thar_HIP_importcusts", strConnection)

        cmdTest.SelectCommand.CommandType = Data.CommandType.StoredProcedure

        cmdTest.SelectCommand.Parameters.Add(New SqlParameter("@custcode_old", Data.SqlDbType.VarChar, 10))
        cmdTest.SelectCommand.Parameters("@custcode_old").Value = DDL_Import.SelectedValue

        cmdTest.SelectCommand.Parameters.Add(New SqlParameter("@custcode_new", Data.SqlDbType.VarChar, 10))
        cmdTest.SelectCommand.Parameters("@custcode_new").Value = DDL_CurrentCustomer.SelectedValue

        MyConnection.Open()


        If MyConnection.State = ConnectionState.Open Then
            cmdTest.SelectCommand.ExecuteReader()
            MyConnection.Close()
        Else
            MyConnection.Open()
            cmdTest.SelectCommand.ExecuteReader()
            MyConnection.Close()
        End If
    End Sub

I appreciate everyone's efforts.

Andrew.Berry · October 24th, 2007, 03:25 AM

Oh and I tried it with reader, but have had no luck.

Andrew.Berry · October 24th, 2007, 05:17 AM

I have attached an imadge with the InvalidOperationException in detail:

http://server6.theimagehosting.com/i...=error.5de.jpg

Andrew.Berry · October 24th, 2007, 05:54 AM

YESSSSS!!!!!!!!

I got it working, after much pain!

Code:

        Dim sConnStr As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("FSPConnectionString").ConnectionString
        Dim cnBKTest As New SqlConnection(sConnStr)
        Dim cmdTest As New SqlCommand("dbo.thar_HIP_importcusts", cnBKTest)

        cmdTest.CommandType = Data.CommandType.StoredProcedure

        cmdTest.Parameters.Add(New SqlParameter("@custcode_old", Data.SqlDbType.VarChar, 10))
        cmdTest.Parameters("@custcode_old").Value = DDL_Import.SelectedValue

        cmdTest.Parameters.Add(New SqlParameter("@custcode_new", Data.SqlDbType.VarChar, 10))
        cmdTest.Parameters("@custcode_new").Value = DDL_CurrentCustomer.SelectedValue

        cnBKTest.Open()
        cmdTest.ExecuteNonQuery()
        cnBKTest.Close()

Sorry the variable names changed, found it on a tutorial, so just copied it. still not too sure why it didnt work before, but I don't care! :D

Thanks everybody for your efforts, really appreciate them.

gbianchi · October 24th, 2007, 07:39 AM

hi there..

to clarify why your first code didn't work, it was because you were not passing a connection to the command, you were passing a conection string!

look at this:

Code:

Dim strConnection As String
        strConnection = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("FSPConnectionString").ConnectionString
        strConnection = "Data source=(192.168.1.101);database=tharFSP;User ID=sa;Password=tharSQL"
        Dim MyConnection As SqlConnection

        MyConnection = New SqlConnection(strConnection)

        Dim cmdTest As New SqlDataAdapter("dbo.thar_HIP_importcusts", strConnection)

in your second try, you pass the rigth connection object to the command ;)

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Andrew.Berry · October 24th, 2007, 07:43 AM

Goddammit, so I was!

Glad someone spotted it, I thought I was going crazy!

Andrew.Berry · October 24th, 2007, 10:41 AM

Right. The page works fine, when I run it through VWD.

The item is designed to be ran from within an application. When it is ran through said application, It starts off fine, but after a bit, when you select an item, change a dropdown, or anything that refreshes the page, it reports the following error:

The gateway has lost the connection with the Web site you are trying to access.

I cant replicate this in debug mode, so don't know where to start in resolving this. :(