Solved

vb.net error after moving sql database to cloud (rackspace)

Posted on 2013-01-19
12
666 Views
Last Modified: 2013-01-23
Incorrect Syntax Near "44446"

Specifically, getting an error in the code on the dataapapter.update line:

Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadapter)

dataadapter.Update(dataset, "Info")

do dataadapters work on hosted sql servers?

I have a sql server/vb.net application where we house the database onsite.   The application works perfectly in house.  We are testing hosting the database in the cloud (rackspace).  We have successfully created the database and connected to it.

When our client (vb.net desktop application) connects to the cloud version of the database, it throws some new errors (that are not present when we connect to the local version).

Trying to understand/figure out what the difference is and if the code needs to be modified due to the cloud hosting.
0
Comment
Question by:browe68
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38796993
can you provide the text of the command?
0
 

Author Comment

by:browe68
ID: 38797019
The error is thrown on the line "dataadapter.Update(dataset, "Info")"

It works just fine when connected to a local database.  But, when connecting to SQL Server in Web (SQL 2012 Web Edition), I get an error.  

So, the only difference is the location of the database (could be that the Web Edition doesn't work too).


        Try
            command.Connection = CN_Str
            command.CommandType = Data.CommandType.StoredProcedure
            command.CommandText = "GetLoanData"
            command.Parameters.AddWithValue("@ID", CURRID)


            dataadapter.SelectCommand = command
            dataadapter.Fill(dataset, "Info")

            With dataset.Tables("Info")



                .Rows(0)("lStatusDate") = Date.Now

 

                If fmClientInfo.txtEstClosingDate.Text.ToString.Length = 0 Then
                    .Rows(0)("lestClosingDate") = System.DBNull.Value
                Else
                    .Rows(0)("lestClosingDate") = fmClientInfo.txtEstClosingDate.Text.ToString
                End If
            End With

            Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadapter)
            dataadapter.Update(dataset, "Info")
       
       Catch ex As Exception
            MAINUPDATEERROR = True
            MessageBox.Show(ex.Message, "Error- Status1", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Finally
            dataadapter.Dispose()
            command.Dispose()
           
        End Try
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38797048
so you are calling a SP. can it be permission related?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:browe68
ID: 38797062
The dataadapter is successfully created, but when calling the update command it fails with this error.
0
 

Author Comment

by:browe68
ID: 38797079
I have dataadapters all thru out my application....  With each .update command, I get the Error: Incorrect Syntax near '44446'
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38797086
wait a minute!

your dataadapter is calling GetLoanData which is a stored proc designed to query data. It just cannot update data!

If you have an adapter created with something like "select * from table", an update statement is automatically created and you can call the update method. But that's not true for a SP. You need to provide another command!
0
 

Author Comment

by:browe68
ID: 38797097
The commandbuilder creates the sql needed... for the update.

  Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadapter)
            dataadapter.Update(dataset, "Info")


It works just fine on the local install of my sql server.  Just not when connected to the 'cloud sql server
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 38797149
I am not a fan of dataapdapters but I was sure you had to provide your own insert/update/delete command when using Stored Procs (not when using simple SELECT statement).

maybe it is a restriction of the cloud version!

can you please try to provide an update command as shown in http://www.c-sharpcorner.com/UploadFile/dclark/UseSPwithDP11282005035417AM/UseSPwithDP.aspx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38797170
So you migrated the database from a local install of SQL Server (Which Version/Edition?) to a server with SQL Server 2012 Web Edition?  If the answer is yes, have you verified that the schema is identical between the two and there are no TRIGGERs on the tables in the Stored Procedure ?

Also, please post the contents of your GetLoanData Stored Procedure.
0
 

Author Comment

by:browe68
ID: 38797206
acperkins, that is correct.   To Migrate, I detached database, copied files to cloud server, reattached.   It is working perfectly other than this...

The only difference is in security, the local is a Windows Authentication.  The cloud database is SQL Authenticaiton.

This error is occurring on every commandbuilder update statement, so it is not specific to this one statement.

The local version is SQL 2008 SP1
The cloud version is SQL 2012 Web Edition.  (maybe commandbuilder not supported)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 38799543
This may be a bit of a stretch, but as you did not explicitly respond to my question:
If the answer is yes, have you verified that the schema is identical between the two and there are no TRIGGERs on the tables in the Stored Procedure ?

So I am going to go out on liml and suggest that  the SQL Server Upsizing Wizard left some TRIGGERs on the tables that is causing this error.
0
 

Author Comment

by:browe68
ID: 38811939
The answer is that in Sql Web 2012, they must treat the data adapter differently.   When I spin a new server to Sql Web 2008, it works with no tweaks to the code.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question