June 30, 2011 at 10:50 am
This may be a problem with ADO.NET and if responders believe this is not the correct forum, please let me know.
The problem is that when I try to fill a dataset with a table that contains text that is digits, ADO (thorugh a VB.NET Try/Catch statement) returns the following error:
Error converting data type varchar to int.
But, here is the SQL that generates this table:
DECLARE @Solution TABLE
(
[System] NUMERIC(4,0) NOT NULL,
[SystemName] VARCHAR(50) NULL,
[Sponsor] NUMERIC(8,0) NULL,
[SponsorName] VARCHAR(1000) NULL,
[Site] NUMERIC(4,0) NULL,
[SiteDescription] VARCHAR(1000) NULL,
[Group] NUMERIC(4,0) NULL,
[GroupDescription] VARCHAR(1000) NULL
)
DECLARE @System TABLE
(
SystemNumber VARCHAR(10) NOT NULL,
SystemName VARCHAR(100) NULL
)
INSERT INTO @System
SELECT DISTINCT CONVERT(VARCHAR(10),s1.[System]), s1.[SystemName] FROM @Solution s1
If any one has any thoughts on the problem here I would appeciate your comments. thank you for your time!
June 30, 2011 at 10:59 am
i'll bet at least one row is an empty string;
an empty string can be converted to Integer, but not Numeric:
SELECT CONVERT(int,'')--works! value = 0
SELECT CONVERT(NUMERIC(8,0),'') --fails! Error converting data type varchar to numeric.
Lowell
June 30, 2011 at 11:05 am
Thank you for your reply.
I would have thought the same thing except that here is the table I get when running the stored procedure from within SQL Server:
SystemNumberSystemName
73 HOSPICE PHARMACY SOLUTIONS
Only one record in the table and two fields. Neither is null. Running the stored procedure from within SQL Server does not return any errors. But when I execute the SP from ADO.NET, I get the error. hence my earlier statement that this may be a problem with ADO.NET.
Wierd, huh?
June 30, 2011 at 11:10 am
are you using a SQLCcmdObject with parameters to call the proc, or as a text command with ExecuteNonQuery??
show me the code you are using to call the proc, i'd guess that's where the issue lies.
Lowell
June 30, 2011 at 11:16 am
Well, the code is generalized within our .NET application for all data retrieval and it works fine in all other situations. But here are the pertinatent routines:
Here's the routine form which the call originates:
Public Function GetSubservientRecords_NewUser(ByVal Token As String, ByVal UserId As String, ByVal AdminLevel As Integer) As DataSet Implements IServiceMTP.GetSubservientRecords_NewUser
Dim CF As New CommonFunctions()
Dim Params As List(Of SqlClient.SqlParameter) = New List(Of SqlClient.SqlParameter)
Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@UserId", SqlDbType.VarChar)
p.Value = UserId
Params.Add(p)
p = New SqlClient.SqlParameter("@AdminLevel", SqlDbType.VarChar)
p.Value = UserId
Params.Add(p)
Return CF.ExecuteSP(Token, "sp_GetSubservientRecords_NewUser", Params, False)
End Function
Followed by these that do the work:
Public Function ExecuteSP(ByVal Token As String, ByVal SPName As String, Optional ByRef Parameters As List(Of SqlClient.SqlParameter) = Nothing, Optional ByVal LogExceptionIfFailure As Boolean = True) As DataSet
If Parameters Is Nothing Then
Parameters = New List(Of SqlClient.SqlParameter)
End If
Dim Cmd As New SqlClient.SqlCommand(SPName)
Dim DS As New DataSet
Try
Cmd.CommandType = CommandType.StoredProcedure
For I As Integer = 0 To Parameters.Count - 1
Cmd.Parameters.Add(Parameters(I))
Next
GetSQLConnection(Cmd)
DS = RetrieveDataSet(Cmd)
Catch ex As Exception
If LogExceptionIfFailure Then
Dim PGUID = DirectCast((From P In Parameters
Select P
Where P.ParameterName = "@PatientGUID").FirstOrDefault.Value, Guid)
LogException(Token, ex, PGUID)
End If
Return DS
Finally
If Not ConnectionsManaged Then
Cmd.Connection.Close()
End If
End Try
Return DS
End Function
Public Function RetrieveDataSet(ByVal Cmd As SqlClient.SqlCommand) As DataSet
Dim ds As New DataSet
ds.EnforceConstraints = False
Dim myAdapter = New SqlClient.SqlDataAdapter(Cmd)
Try
myAdapter.Fill(ds) 'This is where the error occurs.
Catch e As Exception
Throw e
Finally
myAdapter = Nothing
End Try
Return ds
End Function
Public Function GetSQLConnection(Optional ByVal Cmd As SqlClient.SqlCommand = Nothing, Optional ByVal ManagedByQueries As Boolean = False) As SqlClient.SqlConnection
If MyConn Is Nothing Then
MyConn = New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MTPConnectionString").ToString())
MyConn.Open()
ElseIf MyConn.State = ConnectionState.Broken Or MyConn.State = ConnectionState.Closed Then
MyConn.Open()
End If
If ManagedByQueries Then
ConnectionsManaged = True
End If
If Cmd IsNot Nothing Then
Cmd.Connection = MyConn
End If
Return MyConn
End Function
Pretty strightforward stuff. Thank you for your time!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply