John Gates, CISSP, CDPSE
asked on
Strange database write problem
When I am reading data from one table and writing it to another somehow data is getting lost:
Example
From the maintable:
(12 leading spaces)606002
When reading it into the datareader looks great:
Then when I write it to my table it appears as:
(6 leading spaces)6002
This is a SQL2005 Database and both tables have identical varchar(12) settings. Why would this happen?
Relevant code:
Start---->
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
SqlDataAdapter1.Fill(DataS et11)
SqlDataAdapter2.Fill(DataS et21)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Dim objDataReader As OleDb.OleDbDataReader
'objDataReader = DataSet11.executereader
'Dim MyDataReader As DataSet1
Dim dt As DataTable = DataSet11.Tables(0)
Dim dt1 As DataTable = DataSet21.Tables(0)
For Each Dr As DataRow In dt.Rows
'first see if the current student already exists in the StuInfo table, if it does move on to the next
For Each Dr1 As DataRow In dt1.Rows
' MsgBox(Dr(5) & " " & Dr1(0))
If Trim(Dr(5)) = Trim(Dr1(0)) Then ' Student ID exists skip to the next ID!
GoTo 7
Else
'MsgBox("Moving On!")
'Continue to next ID
End If
Next
Dim strLast As String
Dim strFirst As String
strLast = Dr(2).ToString()
strFirst = Dr(3).ToString().Substring (0, 3)
'Create username
'MsgBox(strFirst & strLast)
Dim MyName As String
MyName = (strFirst & strLast)
'Generate random password
Dim dRandNumFull As Object
Dim dRandNumDisplay As Double
Dim k As Object
Dim PNum As String
k = Date.Now.Month * Date.Now.Minute * 1001
dRandNumFull = Format(Int(k * Rnd()), "000000")
dRandNumDisplay = dRandNumFull
'MsgBox(dRandNumFull)
Dim drNew As System.Data.DataRow
PNum = Dr(5) 'Trim(Dr(5))
'MsgBox(DateTime.Parse(Dr( 6).ToStrin g))
'MsgBox(PNum)
drNew = Me.DataSet21.StuInfo.NewRo w
drNew.Item("PermNum") = PNum
drNew.Item("Lastname") = strLast
drNew.Item("Firstname") = Dr(3)
If IsDBNull(Dr(6)) Then
Else
drNew.Item("DOB") = DateTime.Parse(Dr(6))
End If
drNew.Item("Webname") = MyName
drNew.Item("Webpin") = dRandNumDisplay
'drNew.Item("Phone 1") = ("New Phone 1")
'drNew.Item("Phone 2") = ("New Phone 2")
'drNew.Item("Phone 3") = ("New Phone 3")
'drNew.Item("Fax") = ("New Fax")
'drNew.Item("Email") = "New Email"
Me.DataSet21.StuInfo.Rows. Add(drNew)
SqlDataAdapter2.Update(Dat aSet21)
'Students should now be created!!
7:
Next
End Sub
<------End
Any help would be appreciated!
-D-
Example
From the maintable:
(12 leading spaces)606002
When reading it into the datareader looks great:
Then when I write it to my table it appears as:
(6 leading spaces)6002
This is a SQL2005 Database and both tables have identical varchar(12) settings. Why would this happen?
Relevant code:
Start---->
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
SqlDataAdapter1.Fill(DataS
SqlDataAdapter2.Fill(DataS
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Dim objDataReader As OleDb.OleDbDataReader
'objDataReader = DataSet11.executereader
'Dim MyDataReader As DataSet1
Dim dt As DataTable = DataSet11.Tables(0)
Dim dt1 As DataTable = DataSet21.Tables(0)
For Each Dr As DataRow In dt.Rows
'first see if the current student already exists in the StuInfo table, if it does move on to the next
For Each Dr1 As DataRow In dt1.Rows
' MsgBox(Dr(5) & " " & Dr1(0))
If Trim(Dr(5)) = Trim(Dr1(0)) Then ' Student ID exists skip to the next ID!
GoTo 7
Else
'MsgBox("Moving On!")
'Continue to next ID
End If
Next
Dim strLast As String
Dim strFirst As String
strLast = Dr(2).ToString()
strFirst = Dr(3).ToString().Substring
'Create username
'MsgBox(strFirst & strLast)
Dim MyName As String
MyName = (strFirst & strLast)
'Generate random password
Dim dRandNumFull As Object
Dim dRandNumDisplay As Double
Dim k As Object
Dim PNum As String
k = Date.Now.Month * Date.Now.Minute * 1001
dRandNumFull = Format(Int(k * Rnd()), "000000")
dRandNumDisplay = dRandNumFull
'MsgBox(dRandNumFull)
Dim drNew As System.Data.DataRow
PNum = Dr(5) 'Trim(Dr(5))
'MsgBox(DateTime.Parse(Dr(
'MsgBox(PNum)
drNew = Me.DataSet21.StuInfo.NewRo
drNew.Item("PermNum") = PNum
drNew.Item("Lastname") = strLast
drNew.Item("Firstname") = Dr(3)
If IsDBNull(Dr(6)) Then
Else
drNew.Item("DOB") = DateTime.Parse(Dr(6))
End If
drNew.Item("Webname") = MyName
drNew.Item("Webpin") = dRandNumDisplay
'drNew.Item("Phone 1") = ("New Phone 1")
'drNew.Item("Phone 2") = ("New Phone 2")
'drNew.Item("Phone 3") = ("New Phone 3")
'drNew.Item("Fax") = ("New Fax")
'drNew.Item("Email") = "New Email"
Me.DataSet21.StuInfo.Rows.
SqlDataAdapter2.Update(Dat
'Students should now be created!!
7:
Next
End Sub
<------End
Any help would be appreciated!
-D-
Which field is this data for ?
ASKER
Ahh that would have helped:
drNew.Item("PermNum") = PNum
And I know when you make database changes you have to regenerate the datasets. The other thing is to show numeric leading 000's does the field have to be nvarchar?
drNew.Item("PermNum") = PNum
And I know when you make database changes you have to regenerate the datasets. The other thing is to show numeric leading 000's does the field have to be nvarchar?
Your assigning : drNew.Item("PermNum") = PNum
What is the type and size of the column PermNum, in the db and DataSet21.StuInfo ?
I think DataSet21.StuInfo is different ..
What is the type and size of the column PermNum, in the db and DataSet21.StuInfo ?
I think DataSet21.StuInfo is different ..
ASKER
They are exactly the same. What about the nvarchar that I asked about?
Theres no different in the varchar and nvarchar as your using it.
ASKER
Well where I was going is it expecting a string value over numeric?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.