Verify every record that being inserted in sql server

[shuhana84]

HI,anyone know how to check every single record that we have been inserted in sql server?either exist or not..please help.

[rubberman]

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]

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]

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]

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]

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]

Get all the excel values into dataset and do loop and compare..

[shuhana84]

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]

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]

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]

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.