da insert command problems

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

I'm using a SQLDataAdapter with SELECT, INSERT and UPDATE
stored procedures/commands. Just before I call da.Update
(ds), all the data rows in the DataSet contain what they
should and the RowState of each row is marked "Added".
But when da.Update is executed in the code, no rows are
inserted. When I execute the INSERT stored procedure with
some input arguments in query analyzer, a row is
inserted. I made sure that the DataAdapter's
InsertCommand parameters collection are properly set (and
I also tried using no SourceColumn too). Does anyone have
any ideas? This is driving me crazy! I'm Using VS.net
2003
 
It's a good idea to turn on the profiler to see what's getting sent to the
server. This will tell you if the Update is actually finding the changes you
think are there. Let's see some code--and make sure you aren't executing
AcceptChanges before the Update.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks...Here goes:

NUnit Test There's no way it sould bass but it does (no
row is inserted)

<Test()> Sub InsertUserAccessData()
'Get user data row
dsUserAccess = objUser.GetUserAccessData
("(e-mail address removed)")
If dsUserAccess.Users.Rows.Count < 1 Then
Assertion.Fail("No UserAccess.User Data
Retrieved")
End If
Dim drUser As
ElectionResults.Data.UserAccessData.UsersRow =
dsUserAccess.Users.Rows(0)
Dim drUserAccess As
ElectionResults.Data.UserAccessData.AccessRow =
dsUserAccess.Access.NewRow

'insert new access data for that row
drUserAccess.WSUserID = drUser.WSUserID
drUserAccess.UserKey =
objPassword.GetRandomPassword(25)
drUserAccess.ElectionDate = "9/25/2003"
drUserAccess.UserValidated = False
dsUserAccess.Access.Rows.Add(drUserAccess)
objUser.UserAccessDataUpdate(dsUserAccess)

dsUserAccess = Nothing

'get rows from both tables
dsUserAccess = objUser.GetUserAccessData
("(e-mail address removed)")
Dim drU As
ElectionResults.Data.UserAccessData.UsersRow =
dsUserAccess.Users.Rows(0)

Console.WriteLine(drU.FirstName)
Console.WriteLine(drU.LastName)
Console.WriteLine(drU.EMail)
Console.WriteLine(drU.Phone)
Console.WriteLine(drU.Organization)

Dim drUA As
ElectionResults.Data.UserAccessData.AccessRow =
dsUserAccess.Access.Rows(0)

Console.WriteLine(drUA.WSUserID)
Console.WriteLine(drUA.WSAccessID)
Console.WriteLine(drUA.UserKey)
Console.WriteLine(drUA.ElectionDate)
Console.WriteLine(drUA.UserValidated)

If dsUserAccess.Users.Rows.Count < 1 Then
Assertion.Fail("No UserAccess.User Data
Retrieved After Access Data Update")
End If
If dsUserAccess.Access.Rows.Count < 1 Then
Assertion.Fail("No UserAccess.Access Data
Retrieved")
End If
End Sub

BusinessRules objUser is DataAccess Class

Public Sub UserAccessDataUpdate(ByVal ds As
ElectionResults.Data.UserAccessData)
objUser.UserAccessDataUpdate(ds)
End Sub

DataAccess Sorry it's so long but at this point the
problem could be anywhere

Public Class User
Inherits System.ComponentModel.Component

#Region " Component Designer generated code "

Public Sub New(Container As
System.ComponentModel.IContainer)
MyClass.New()

'Required for Windows.Forms Class Composition
Designer support
Container.Add(me)
End Sub

Public Sub New()
MyBase.New()

'This call is required by the Component Designer.
InitializeComponent()

'Add any initialization after the
InitializeComponent() call

End Sub

'Component overrides dispose to clean up the component
list.
Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Component Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the
Component Designer
'It can be modified using the Component Designer.
'Do not modify it using the code editor.
Friend WithEvents daUser As
System.Data.SqlClient.SqlDataAdapter
Friend WithEvents cnElectionResults As
System.Data.SqlClient.SqlConnection
Friend WithEvents dsUser As
ElectionResults.Data.UserData
Friend WithEvents SelectUserData As
System.Data.SqlClient.SqlCommand
Friend WithEvents InsertUserData As
System.Data.SqlClient.SqlCommand
Friend WithEvents daUserAccess As
System.Data.SqlClient.SqlDataAdapter
Friend WithEvents SelectUserAccess As
System.Data.SqlClient.SqlCommand
Friend WithEvents InsertUserAccess As
System.Data.SqlClient.SqlCommand
Friend WithEvents UpdateUserAccess As
System.Data.SqlClient.SqlCommand
Friend WithEvents dsUserAccess As
ElectionResults.Data.UserAccessData
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Dim configurationAppSettings As
System.Configuration.AppSettingsReader = New
System.Configuration.AppSettingsReader
Me.daUser = New
System.Data.SqlClient.SqlDataAdapter
Me.InsertUserData = New
System.Data.SqlClient.SqlCommand
Me.cnElectionResults = New
System.Data.SqlClient.SqlConnection
Me.SelectUserData = New
System.Data.SqlClient.SqlCommand
Me.dsUser = New ElectionResults.Data.UserData
Me.daUserAccess = New
System.Data.SqlClient.SqlDataAdapter
Me.InsertUserAccess = New
System.Data.SqlClient.SqlCommand
Me.SelectUserAccess = New
System.Data.SqlClient.SqlCommand
Me.UpdateUserAccess = New
System.Data.SqlClient.SqlCommand
Me.dsUserAccess = New
ElectionResults.Data.UserAccessData
CType(Me.dsUser,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.dsUserAccess,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'daUser
'
Me.daUser.InsertCommand = Me.InsertUserData
Me.daUser.SelectCommand = Me.SelectUserData
Me.daUser.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "User", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping
("WSUserID", "WSUserID"), New
System.Data.Common.DataColumnMapping
("LastName", "LastName"), New
System.Data.Common.DataColumnMapping
("FirstName", "FirstName"), New
System.Data.Common.DataColumnMapping
("Organization", "Organization"), New
System.Data.Common.DataColumnMapping("EMail", "EMail"),
New System.Data.Common.DataColumnMapping
("Phone", "Phone"), New
System.Data.Common.DataColumnMapping
("Password", "Password")})})
'
'InsertUserData
'
Me.InsertUserData.CommandText = "dbo.
[lp_InsertWSUser]"
Me.InsertUserData.CommandType =
System.Data.CommandType.StoredProcedure
Me.InsertUserData.Connection = Me.cnElectionResults
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.VarChar, 127, "LastName"))
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Firstname",
System.Data.SqlDbType.VarChar, 127, "FirstName"))
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Organization",
System.Data.SqlDbType.VarChar, 127, "Organization"))
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Email",
System.Data.SqlDbType.VarChar, 127, "EMail"))
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Phone",
System.Data.SqlDbType.VarChar, 15, "Phone"))
Me.InsertUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Password",
System.Data.SqlDbType.VarChar, 10, "Password"))
'
'cnElectionResults
'
Me.cnElectionResults.ConnectionString = CType
(configurationAppSettings.GetValue
("cnElectionResults.ConnectionString", GetType
(System.String)), String)
'
'SelectUserData
'
Me.SelectUserData.CommandText = "dbo.
[lp_GetWSUserData]"
Me.SelectUserData.CommandType =
System.Data.CommandType.StoredProcedure
Me.SelectUserData.Connection = Me.cnElectionResults
Me.SelectUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SelectUserData.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Email",
System.Data.SqlDbType.VarChar, 127))
'
'dsUser
'
Me.dsUser.DataSetName = "UserData"
Me.dsUser.Locale = New
System.Globalization.CultureInfo("en-US")
'
'daUserAccess
'
Me.daUserAccess.InsertCommand = Me.InsertUserAccess
Me.daUserAccess.SelectCommand = Me.SelectUserAccess
Me.daUserAccess.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Users", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping
("WSUserID", "WSUserID"), New
System.Data.Common.DataColumnMapping
("LastName", "LastName"), New
System.Data.Common.DataColumnMapping
("FirstName", "FirstName"), New
System.Data.Common.DataColumnMapping
("Organization", "Organization"), New
System.Data.Common.DataColumnMapping("EMail", "EMail"),
New System.Data.Common.DataColumnMapping
("Phone", "Phone"), New
System.Data.Common.DataColumnMapping
("Password", "Password")}), New
System.Data.Common.DataTableMapping("Table1", "Access",
New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping
("WSAccessID", "WSAccessID"), New
System.Data.Common.DataColumnMapping
("WSUserID", "WSUserID"), New
System.Data.Common.DataColumnMapping
("UserKey", "UserKey"), New
System.Data.Common.DataColumnMapping
("ElectionDate", "ElectionDate"), New
System.Data.Common.DataColumnMapping
("LastAccessTime", "LastAccessTime"), New
System.Data.Common.DataColumnMapping
("UserValidated", "UserValidated")})})
Me.daUserAccess.UpdateCommand = Me.UpdateUserAccess
'
'InsertUserAccess
'
Me.InsertUserAccess.CommandText = "dbo.
[lp_InsertWebserviceUserAccessData]"
Me.InsertUserAccess.CommandType =
System.Data.CommandType.StoredProcedure
Me.InsertUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.InsertUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@WSUserID",
System.Data.SqlDbType.Int, 4, "WSUserID"))
Me.InsertUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@UserKey",
System.Data.SqlDbType.VarChar, 25, "UserKey"))
Me.InsertUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ElectionDate",
System.Data.SqlDbType.DateTime, 8, "ElectionDate"))
Me.InsertUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastAccessTime",
System.Data.SqlDbType.DateTime, 8, "LastAccessTime"))
Me.InsertUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@UserValidated",
System.Data.SqlDbType.Bit, 1, "UserValidated"))
'
'SelectUserAccess
'
Me.SelectUserAccess.CommandText = "dbo.
[lp_GetWebServiceUserData]"
Me.SelectUserAccess.CommandType =
System.Data.CommandType.StoredProcedure
Me.SelectUserAccess.Connection =
Me.cnElectionResults
Me.SelectUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SelectUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Email",
System.Data.SqlDbType.VarChar, 127, "EMail"))
'
'UpdateUserAccess
'
Me.UpdateUserAccess.CommandText = "dbo.
[lp_UpdateWebServiceUserData]"
Me.UpdateUserAccess.CommandType =
System.Data.CommandType.StoredProcedure
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType
(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@WSUserID",
System.Data.SqlDbType.Int, 4, "WSUserID"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Lastname",
System.Data.SqlDbType.VarChar, 127, "LastName"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Firstname",
System.Data.SqlDbType.VarChar, 127, "FirstName"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Organization",
System.Data.SqlDbType.VarChar, 127, "Organization"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Email",
System.Data.SqlDbType.VarChar, 127, "EMail"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Phone",
System.Data.SqlDbType.VarChar, 127, "Phone"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Password",
System.Data.SqlDbType.VarChar, 10, "Password"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@WSAccessID",
System.Data.SqlDbType.Int, 4, "WSAccessID"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@UserKey",
System.Data.SqlDbType.VarChar, 25, "UserKey"))
Me.UpdateUserAccess.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ElectionDate",
System.Data.SqlDbType.DateTime, 8, "ElectionDate"))
'
'dsUserAccess
'
Me.dsUserAccess.DataSetName = "UserAccessData"
Me.dsUserAccess.Locale = New
System.Globalization.CultureInfo("en-US")
CType(Me.dsUser,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.dsUserAccess,
System.ComponentModel.ISupportInitialize).EndInit()

End Sub

#End Region


Public Function GetUserData(ByVal EMail As String) As
ElectionResults.Data.UserData
Me.SelectUserData.Parameters(1).Value = EMail
daUser.Fill(dsUser)
Return dsUser
End Function


Public Sub InsertUser(ByVal ds As
ElectionResults.Data.UserData)
daUser.Update(ds)
End Sub

Public Function GetUserAccessData(ByVal EMail As
String) As ElectionResults.Data.UserAccessData
Me.SelectUserAccess.Parameters(1).Value = EMail
daUserAccess.Fill(dsUserAccess)
Return dsUserAccess
End Function

Public Sub UserAccessDataUpdate(ByVal ds As
ElectionResults.Data.UserAccessData)
daUserAccess.Update(ds)
End Sub

End Class
 
Back
Top