browe68
asked on
vb.net error after moving sql database to cloud (rackspace)
Incorrect Syntax Near "44446"
Specifically, getting an error in the code on the dataapapter.update line:
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadap ter)
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.
Specifically, getting an error in the code on the dataapapter.update line:
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadap
dataadapter.Update(dataset
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.
can you provide the text of the command?
ASKER
The error is thrown on the line "dataadapter.Update(datase t, "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.StoredPro cedure
command.CommandText = "GetLoanData"
command.Parameters.AddWith Value("@ID ", CURRID)
dataadapter.SelectCommand = command
dataadapter.Fill(dataset, "Info")
With dataset.Tables("Info")
.Rows(0)("lStatusDate") = Date.Now
If fmClientInfo.txtEstClosing Date.Text. ToString.L ength = 0 Then
.Rows(0)("lestClosingDate" ) = System.DBNull.Value
Else
.Rows(0)("lestClosingDate" ) = fmClientInfo.txtEstClosing Date.Text. ToString
End If
End With
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadap ter)
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
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.StoredPro
command.CommandText = "GetLoanData"
command.Parameters.AddWith
dataadapter.SelectCommand = command
dataadapter.Fill(dataset, "Info")
With dataset.Tables("Info")
.Rows(0)("lStatusDate") = Date.Now
If fmClientInfo.txtEstClosing
.Rows(0)("lestClosingDate"
Else
.Rows(0)("lestClosingDate"
End If
End With
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadap
dataadapter.Update(dataset
Catch ex As Exception
MAINUPDATEERROR = True
MessageBox.Show(ex.Message
Finally
dataadapter.Dispose()
command.Dispose()
End Try
so you are calling a SP. can it be permission related?
ASKER
The dataadapter is successfully created, but when calling the update command it fails with this error.
ASKER
I have dataadapters all thru out my application.... With each .update command, I get the Error: Incorrect Syntax near '44446'
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!
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!
ASKER
The commandbuilder creates the sql needed... for the update.
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadap ter)
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
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dataadap
dataadapter.Update(dataset
It works just fine on the local install of my sql server. Just not when connected to the 'cloud sql server
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Also, please post the contents of your GetLoanData Stored Procedure.
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.