Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to update SQL Server database through code Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 8:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:51 AM
Points: 14, Visits: 28
I am writing a vb.net code to update SQL SERVER database. When I run the query through the SSMS, I can update the database, when I run the same query through vb.net code, the database does not get updated.
Post #1370133
Posted Monday, October 8, 2012 9:06 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 722, Visits: 1,022
What happens? Are there any errors? We need more information to further troubleshoot this issue. What's your code? Have you run a SQL Trace (or XE) to see exactly what is hitting the database?



Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com
Post #1370141
Posted Monday, October 8, 2012 10:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:51 AM
Points: 14, Visits: 28
Nothing happens. Below is my vb.net code

Protected Sub btnSaves_Click(sender As Object, e As System.EventArgs) Handles btnSaves.Click
Dim SubmitQueryStringSuggID As String = Request.QueryString("SuggID").ToString()
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmdUpdateEmp As SqlCommand
'Try
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("DatabaseConnection").ConnectionString)
conn.Open()
cmdUpdateEmp = New SqlCommand()
cmdUpdateEmp.Connection = conn
'cmdUpdateEmp.Transaction = trans
cmdUpdateEmp = New SqlCommand("UPDATE tblEmpTeam SET FName=@FName, LName=@LName,DeptID=@DeptID,JobTitle=@JobTitle, empInitials=@empInitials, MailCode=@MailCode,EmpPhone=@EmpPhone, EmpGroup=@EmpGroup, EmpShift=@EmpShift, FirstSuggestion=@FirstSuggestion,EmpEmail=@EmpEmail where SuggID= " + SubmitQueryStringSuggID, conn)
'cmdUpdateEmp = New SqlCommand("UPDATE tblEmpTeam SET FName=@FName, LName=@LName,EmpID=@EmpID,DeptID=@DeptID,JobTitle=@JobTitle, empInitials=@empInitials, MailCode=@MailCode,EmpPhone=@EmpPhone, EmpGroup=@EmpGroup, EmpShift=@EmpShift, FirstSuggestion=@FirstSuggestion,EmpEmail=@EmpEmail", conn)
cmdUpdateEmp.Parameters.Add("@FName", Data.SqlDbType.VarChar, 50).Value = emp_fname1.Text
cmdUpdateEmp.Parameters.Add("@LName", Data.SqlDbType.VarChar, 50).Value = emp_lname1.Text
'cmdUpdateEmp.Parameters.Add("@EmpID", Data.SqlDbType.Char, 9).Value = emp_gmin1.Text
cmdUpdateEmp.Parameters.Add("@DeptID", Data.SqlDbType.Int).Value = emp_dept.Text
cmdUpdateEmp.Parameters.Add("@JobTitle", Data.SqlDbType.NVarChar, 50).Value = emp_title.Text
cmdUpdateEmp.Parameters.Add("@empInitials", Data.SqlDbType.NVarChar, 50).Value = emp_Initials.Text
cmdUpdateEmp.Parameters.Add("@MailCode", Data.SqlDbType.NVarChar, 50).Value = emp_mail.Text
cmdUpdateEmp.Parameters.Add("@EmpPhone", Data.SqlDbType.NVarChar, 50).Value = emp_phone.Text
cmdUpdateEmp.Parameters.Add("@EmpGroup", Data.SqlDbType.NVarChar, 50).Value = emp_group.Text
cmdUpdateEmp.Parameters.Add("@EmpShift", Data.SqlDbType.NVarChar, 50).Value = emp_shift.Text
cmdUpdateEmp.Parameters.Add("@FirstSuggestion", Data.SqlDbType.Decimal).Value = emp_firstsugg.Checked
cmdUpdateEmp.Parameters.Add("@EmpEmail", Data.SqlDbType.VarChar, 100).Value = emp_Email.Text
cmdUpdateEmp.Parameters.Add("@SuggID", Data.SqlDbType.Int).Value = SubmitQueryStringSuggID
'conn.Open()
cmdUpdateEmp.ExecuteNonQuery()

cmdUpdateEmp.Parameters.Clear()
cmdUpdateEmp = New SqlCommand("UPDATE tblSuggestions SET SuggNo=@SuggNo,DeptID=@DeptID,LocId=@LocId,OrigDeptID=@OrigDeptID,StatusID=@StatusID,TypeCode=@TypeCode,SupvrFName=@SupvrFName,SupvrLName=@SupvrLName,SupvrEmail=@SupvrEmail," & _
"Supvrlsignature=@Supvrlsignature,SupvrDate=@SupvrDate,SupvrPlantBldg=@SupvrPlantBldg,SupvrDept=@SupvrDept,SupvrShift=@SupvrShift,SupvrPhone=@SupvrPhone,SupvrMailcode=@SupvrMailcode,SupvrEmpID=@SupvrEmpID WHERE SuggID=" + SubmitQueryStringSuggID, conn)
cmdUpdateEmp.Parameters.Add("@SuggNo", Data.SqlDbType.NVarChar, 50)
cmdUpdateEmp.Parameters.Add("@DeptID", Data.SqlDbType.Int)
cmdUpdateEmp.Parameters.Add("@LocId", Data.SqlDbType.Int)
cmdUpdateEmp.Parameters.Add("@OrigDeptID", Data.SqlDbType.Int)
cmdUpdateEmp.Parameters.Add("@StatusID", Data.SqlDbType.NVarChar, 50)
cmdUpdateEmp.Parameters.Add("@TypeCode", Data.SqlDbType.NVarChar, 50)
cmdUpdateEmp.Parameters.Add("@SupvrFName", Data.SqlDbType.NVarChar, 12)
cmdUpdateEmp.Parameters.Add("@SupvrLName", Data.SqlDbType.NVarChar, 21)
cmdUpdateEmp.Parameters.Add("@SupvrEmail", Data.SqlDbType.NVarChar, 100)
cmdUpdateEmp.Parameters.Add("@Supvrlsignature", Data.SqlDbType.NVarChar, 50)
cmdUpdateEmp.Parameters.Add("@SupvrDate", Data.SqlDbType.DateTime)
cmdUpdateEmp.Parameters.Add("@SupvrPlantBldg", Data.SqlDbType.NVarChar, 50)
cmdUpdateEmp.Parameters.Add("@SupvrDept", Data.SqlDbType.NVarChar, 6)
cmdUpdateEmp.Parameters.Add("@SupvrShift", Data.SqlDbType.NVarChar, 1)
cmdUpdateEmp.Parameters.Add("@SupvrPhone", Data.SqlDbType.NVarChar, 50)
cmdUpdateEmp.Parameters.Add("@SupvrMailcode", Data.SqlDbType.NVarChar, 9)
cmdUpdateEmp.Parameters.Add("@SupvrEmpID", Data.SqlDbType.Char, 9)
cmdUpdateEmp.Parameters.Add("@SuggID", Data.SqlDbType.Int).Value = SubmitQueryStringSuggID
cmdUpdateEmp.Parameters("@SuggNo").Value = "H"
cmdUpdateEmp.Parameters("@DeptID").Value = Convert.ToInt32(CType(btSubmits.FindControl("emp_dept"), TextBox).Text)
cmdUpdateEmp.Parameters("@LocID").Value = 31031
cmdUpdateEmp.Parameters("@OrigDeptID").Value = 1
cmdUpdateEmp.Parameters("@StatusID").Value = "D"
cmdUpdateEmp.Parameters("@TypeCode").Value = 2
cmdUpdateEmp.Parameters("@SupvrFName").Value = CType(btSubmits.FindControl("supvr_fname"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrLName").Value = CType(btSubmits.FindControl("supvr_lname"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrEmail").Value = CType(btSubmits.FindControl("super_email"), TextBox).Text
cmdUpdateEmp.Parameters("@Supvrlsignature").Value = CType(btSubmits.FindControl("supvr_lsignature"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrDate").Value = CType(btSubmits.FindControl("supvr_date"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrPlantBldg").Value = CType(btSubmits.FindControl("supvr_pltbldg"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrDept").Value = CType(btSubmits.FindControl("supvr_dept"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrShift").Value = CType(btSubmits.FindControl("supvr_shift"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrPhone").Value = CType(btSubmits.FindControl("supvr_phone"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrMailcode").Value = CType(btSubmits.FindControl("supvr_mail"), TextBox).Text
cmdUpdateEmp.Parameters("@SupvrEmpID").Value = CType(btSubmits.FindControl("supvr_gmin"), TextBox).Text
cmdUpdateEmp.ExecuteNonQuery()
'MySuggestions.aspx()
Response.Redirect("MySuggestions.aspx?txtUserGMIN=" + txtUserGMIN.Text)

'End Sub

End Sub
Post #1370151
Posted Tuesday, October 9, 2012 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 13,455, Visits: 12,318
As previously suggested you should run a trace to see what actually hits your server.

Also, you did such a great job protecting yourself from sql injection EXCEPT for the querystring.

SubmitQueryStringSuggID

Turn that into a parameter in your query too.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1370404
Posted Tuesday, October 9, 2012 10:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:51 AM
Points: 14, Visits: 28
Thanks got it resolved. I left out OUTPUT INSERTED clause I was using
Post #1370668
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse