Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The script below was ok for the task I want to use it for. It was executed and and gave d expected result from the sql server database. But I want to convert it to a method to be used in vb.net.
Just like:
VB
private sub employee(ByVal yyyy As string, ByVal  xxxx As string)
End sub
The script is shown below.

SQL
Insert into Employee Values('yyyy',
(select distinct Surname from Employee where EmployeeNo=xxxx))
GO
update employeeBenefits set EmployeeNo='yyyy' where EmployeeNo='xxxx'
Go
update EmployeeCareert set EmployeeNo='yyyy' where EmployeeNo='xxxx'
Go
delete from Employee where EmployeeNo='xxxx'
GO

Thanks.
Posted
Updated 18-Sep-14 3:47am
v2
Comments
[no name] 17-Sep-14 11:37am    
Okay... and? I am not seeing a problem or a question here.
Simeon Abiodun 17-Sep-14 16:59pm    
The question is right their but for clarification, I just want to know how I`m going to pass the script into a query withing a declared method.

Public Sub editEmployeeNumber(ByVal yyyy As String, ByVal xxxx As String)
Dim conn As SqlConnection
Dim cmd As SqlCommand
conn.ConnectionString = getVHRSConstring()
conn.Open()
Dim query As String = "MY SQL SCRIPTS"
cmd = New SqlCommand(query, conn)
cmd.ExecuteNonQuery()
End Sub
vb.net does not recognize all these go statement of a thing it throw a lot of errors.
So How do I handle the script from vb.net environment.
Hope you understand me well now.
ChauhanAjay 17-Sep-14 21:47pm    
You don't need to use the "GO" statements just remove the "GO" statement. You may concatinate your query's and then execute them using the cmd.ExecuteNonQuery(). Kindly try out and let me know the result.

1 solution

Public Sub EditEmployeeNumber(ByVal yyyy As String, ByVal xxxx As String)
    Dim strCmd As String = "Insert into Employee " & _
                            "Select distinct @Py,Surname from Employee where EmployeeNo=@Px;" & _
                            "update employeeBenefits set EmployeeNo= @Py where EmployeeNo= @Px;" & _
                            "update EmployeeCareert set EmployeeNo= @Py where EmployeeNo= @Px;" & _
                            "delete from Employee where EmployeeNo= @Px;"

    Using conn As New SqlConnection(getVHRSConstring())
        Using cmd As New SqlCommand(strCmd, conn)
            conn.Open()
            cmd.Parameters.AddWithValue("@Py", yyyy)
            cmd.Parameters.AddWithValue("@Px", xxxx)
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub
 
Share this answer
 
v2
Comments
Simeon Abiodun 18-Sep-14 18:31pm    
it throw d exception error as shown below.

Server Error in '/lEAD' Application.
--------------------------------------------------------------------------------

Incorrect syntax near the keyword 'distinct'.
Incorrect syntax near '@px'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'distinct'.
Incorrect syntax near '@px'.

Source Error:


Line 202: cmd.Parameters.AddWithValue("@py", yyyy)
Line 203: cmd.Parameters.AddWithValue("@px", xxxx)
Line 204: cmd.ExecuteNonQuery()
Line 205: End Using
Line 206: End Using


Source File: C:\Users\Dev02\Documents\Visual Studio 2008\WebSites\lEAD\App_Code\databaseConnect.vb Line: 204
Simeon Abiodun 18-Sep-14 18:33pm    
I need an heelping hand to this regard.
Thanks.
Abdul Samad KP 19-Sep-14 14:39pm    
Can you post your code?
Simeon Abiodun 19-Sep-14 16:08pm    
I have an employee which was assign a wrong employee number, I want to change the employee number to the new one. yyyy is the new while xxxx depicts the old one. Due to how the tables are related, I cannot update the old employee number being the primary key upon which all other tables within the application depends on. So I thought of creating a new employee with correct employee number and select all the data from the old one into the new one. Then later update every other tables with the current employee number, and finally delete the old employee number.
With the sql script I generated it work exactly as I want.
To avoid the stress of going to the back end database server to run the scripts, I decided to write a simple Asp.net web application that will prompt the user to select specified database from drop down box and supply both old and new employee number and with a click of button, the task be done.
The code is shown below.
Simeon Abiodun 19-Sep-14 16:25pm    
Public Sub updateEmpNo(ByVal xxxx As String, ByVal yyyy As String, ByVal cmbCompDesc As String)

Dim strcmd As String = "Insert into EmployeeMaster" & _
"@py" & _
"select Emp_Surname from EmployeeMaster where Emp_EmployeeNumber=@px" & _
"select Emp_FirstName from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_OtherNames from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_Title from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_Gender from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_OfficePhoneExtension from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_YearsInService from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_DefaultHours from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_Status from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_DepartmentCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_DivisionCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_SectionCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_BranchCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_RegionCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_LocationCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_ReportTo from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_JobCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_ClassCode from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_PositionCode from EmployeeMaster where Emp_EmployeeNumber=@px" & _
"select Emp_Group1Code from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_Group2Code from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_Group3Code from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_EmploymentDate from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_EffectiveDate from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_EmploymentType from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_ReasonForChange from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_LastChangedBy from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_DateofChange from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_AlternateNumber1 from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"select Emp_PayPending from EmployeeMaster where Emp_EmployeeNumber= @px" & _
"update employeeBenefits set EmployeeNo=@py where EmployeeNo=@px" & _
"update EmployeeCareerHist set ECareer_EmployeeNumber=@py where ECareer_EmployeeNumber=@px" & _
"update EmployeeCareerHist set ECareer_EmployeeNumber=@py where ECareer_EmployeeNumber= @px" & _
"update EmployeeConfirmation set Econfirmation_EmployeeNumber=@py where Econfirmation_EmployeeNumber= @px" & _
"update EmployeeContact set EContact_EmployeeNumber=@py where EContact_EmployeeNumber= @px" & _
"update EmployeeAbsence set EAbsence_EmployeeNumber=@py where EAbsence_EmployeeNumber= @px" & _
"update EmployeeAccidentHist set EAccidHist_EmployeeNumber=@py where EAccidHist_EmployeeNumber= @px" & _
"update EmployeeAppraisal set EAppraisal_EmployeeNumber=@py where EAppraisal_Empl

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900