HI,anyone know how to check every single record that we have been inserted in sql server?either exist or not..please help.
shuhana84 0 Light Poster
rubberman 1,355 Nearly a Posting Virtuoso Featured Poster
It depends upon what you mean by "verify". You can create post-insert/pre-commit triggers on each field that needs verification so that your own code (rules) will "verify" that each field is valid before it will commit the record to disc. You can also apply these rules for updates as well. This is pretty standard relational database cruft. Mostly, it is a matter of writing the rules, and creating the triggers to apply them. That's mostly grunt work, but necessary for any significant database application.
shuhana84 0 Light Poster
It depends upon what you mean by "verify". You can create post-insert/pre-commit triggers on each field that needs verification so that your own code (rules) will "verify" that each field is valid before it will commit the record to disc. You can also apply these rules for updates as well. This is pretty standard relational database cruft. Mostly, it is a matter of writing the rules, and creating the triggers to apply them. That's mostly grunt work, but necessary for any significant database application.
actually,i have some data in excel,and i have insert all the data into sql server..so i want to validate that the data in excel successfully inserted in sql server..looks like i need to compare the data between excel and sql server..the problem now,it compare with the first record it found,not with first to end record..like in vb they have recordset.find.but how about vb.net?does it have any function that similar to it?
Pgmer 50 Master Poster Featured Poster
you mean u want to verify the row already exist or not in SQL? IF not exist u want insert if exist dont insert?
shuhana84 0 Light Poster
you mean u want to verify the row already exist or not in SQL? IF not exist u want insert if exist dont insert?
no,if exist then ok,if not exist then display the data which is not exist then force the script to stop(actually i use vb.net code in silktest, and i try to validate data after doing data driven).
shuhana84 0 Light Poster
no,if exist then ok,if not exist then display the data which is not exist then force the script to stop(actually i use vb.net code in silktest, and i try to validate data after doing data driven).
i try to validate with this code:
if postcode=lrd("zipcode").tostring then
else
msgbox(postcode &"=not exists")
exit sub
end if
it will compare with the the first row only,i don't know how to move to next record
Pgmer 50 Master Poster Featured Poster
Get all the excel values into dataset and do loop and compare..
shuhana84 0 Light Poster
Get all the excel values into dataset and do loop and compare..
yes i already use do loop,and postcode(data excel),zipcode(data from sql server)
shuhana84 0 Light Poster
Get all the excel values into dataset and do loop and compare..
my code:
Imports System.Data
Imports SilkTest.Ntf.XBrowser
Imports System.Data.SqlClient
Public Module Main
Dim _desktop As Desktop = Agent.Desktop
Dim postcode As String
Dim city As String
Dim state As String
'Dim a,b,c As String
Public Sub Main
Dim data As ActiveData=Workbench.LoadActivedata("OtherProfiles_PostCode")
Dim row As ActiveDataRow
With _desktop.Window("window")
For Each row In data
postcode =row.GetString("PostCode")
city =row.GetString("City")
state =row.GetString("State")
.MenuItem("otherProfiles").Select()
.ListBox("listBox").Select("Post Code")
.Control("clear").Click(MouseButton.Left, New Point(26, 17), ModifierKeys.None)
.TextField("textField").SetPosition(New TextPosition(0, 0))
.TextField("textField").SetText(postcode)
.TextField("textField4").SetPosition(New TextPosition(0, 0))
.TextField("textField4").SetText(city)
.TextField("textField5").SetPosition(New TextPosition(0, 0))
.TextField("textField5").SetText(state)
.Control("save").Click(MouseButton.Left, New Point(19, 10), ModifierKeys.None)
Next
End With
Dim lrd As SqlDataReader
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New System.Data.DataSet
Dim i As Integer
Dim firstSql As String
connetionString="Data Source=x;Initial Catalog=x;User ID=x;Password=x"
firstSql = "Select ZipCode,CityName,StateName from AdmZipCode"
connection = New SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(firstSql, connection)
adapter.SelectCommand = command
adapter.Fill(ds, "AdmZipCode")
lrd=command.ExecuteReader
If lrd.HasRows
Do While lrd.read
For i = 0 To ds.Tables(0).Rows.Count - 1
If postcode=(lrd("ZipCode").ToString)Then
MsgBox(ds.Tables(0).Rows(i).Item(0) & " -- " & ds.Tables(0).Rows(i).Item(1)&" -- " & ds.Tables(0).Rows(i).Item(2))
Else
msgbox("no row")
Exit Sub
End If
Next
Loop
Else
msgbox("no record")
End If
adapter.Dispose()
command.Dispose()
connection.Close()
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
End Sub
End Module shuhana84 0 Light Poster
my code:
Imports System.Data Imports SilkTest.Ntf.XBrowser Imports System.Data.SqlClient Public Module Main Dim _desktop As Desktop = Agent.Desktop Dim postcode As String Dim city As String Dim state As String 'Dim a,b,c As String Public Sub Main Dim data As ActiveData=Workbench.LoadActivedata("OtherProfiles_PostCode") Dim row As ActiveDataRow With _desktop.Window("window") For Each row In data postcode =row.GetString("PostCode") city =row.GetString("City") state =row.GetString("State") .MenuItem("otherProfiles").Select() .ListBox("listBox").Select("Post Code") .Control("clear").Click(MouseButton.Left, New Point(26, 17), ModifierKeys.None) .TextField("textField").SetPosition(New TextPosition(0, 0)) .TextField("textField").SetText(postcode) .TextField("textField4").SetPosition(New TextPosition(0, 0)) .TextField("textField4").SetText(city) .TextField("textField5").SetPosition(New TextPosition(0, 0)) .TextField("textField5").SetText(state) .Control("save").Click(MouseButton.Left, New Point(19, 10), ModifierKeys.None) Next End With Dim lrd As SqlDataReader Dim connetionString As String Dim connection As SqlConnection Dim command As SqlCommand Dim adapter As New SqlDataAdapter Dim ds As New System.Data.DataSet Dim i As Integer Dim firstSql As String connetionString="Data Source=x;Initial Catalog=x;User ID=x;Password=x" firstSql = "Select ZipCode,CityName,StateName from AdmZipCode" connection = New SqlConnection(connetionString) Try connection.Open() command = New SqlCommand(firstSql, connection) adapter.SelectCommand = command adapter.Fill(ds, "AdmZipCode") lrd=command.ExecuteReader If lrd.HasRows Do While lrd.read For i = 0 To ds.Tables(0).Rows.Count - 1 If postcode=(lrd("ZipCode").ToString)Then MsgBox(ds.Tables(0).Rows(i).Item(0) & " -- " & ds.Tables(0).Rows(i).Item(1)&" -- " & ds.Tables(0).Rows(i).Item(2)) Else msgbox("no row") Exit Sub End If Next Loop Else msgbox("no record") End If adapter.Dispose() command.Dispose() connection.Close() Catch ex As Exception MsgBox("Can not open connection ! ") End Try End Sub End Module
the problem in the code are,it always return msg "no row" although there is the same record in excel and sql server,and look like it didn't loop in sql server to find the same record with excel,it just compare with the first record it found.HOw to fix this..?
Pgmer 50 Master Poster Featured Poster
Ur comparring the literal strings here so try using TRIM function on both side..
And why ur doing the loops seperately?.
you need to compare all the postcode with zipcode right?
you can write one sp which takes Postcode as paramaeter and do the search in DB instead doing at front end if value found return true or else return false.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.