Form to either Update or Insert data in SQL table

snteran · September 19th, 2012, 10:51 PM

I have a report that generates a list of servers, local User accts and a Custom Label. I have a form that will allow a user to manually put in the information but there is also a service that could add the Server Name and Local user acct.

So I need my form to first see if the server name and local user acct exist, if it does, then update the column Custom Label. Else I need too insert the Server Name, Local user acct and Custom Label.

Code:

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- #include file = "conn.asp" -->
<!--#include virtual="adovbs.inc"-->
<%  DIM strServer,cpSearch,strUserlbl,strUserID,User_ID
strServer = Request.Form("servername")
strUserID = Request.Form("userid")
strUserlbl = Request.Form("useracctlbl")
%>
<% 
  Dim objRS,objConn,testSQL,objRS2, mySQL
  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.ConnectionString = sConnection
  objConn.Open
  testSQL = "select count(*) AS Userlbl from LocalUserLabel where server_name =  '" & strServer & "'" and User_ID =  "'" & strUserID & "'"
if Userlbl = 0 then
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "LocalUserLabel", objConn, adOpenStatic, adLockOptimistic, adCmdTable
  objRS.AddNew Array("Server_Name", "User_ID", "CustomLabel"), Array(uCase(Request.Form ("servername")), Request.Form("userid"), uCase(Request.Form("useracctlbl")))
  objRS.Update
 end if
if Userlbl = 1 then
mySQL = "SELECT * FROM server WHERE server_name = '" & strServer & "'"
       Set objRS2 = Server.CreateObject("ADODB.Recordset")
       objRS2.Open mySQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
objRS2("CustomLabel") = Request.Form("useracctlbl")
  objRS2.Update
  end if
  objRS.Close
  Set objRS = Nothing
  objRS2.Close
  Set objRS2 = Nothing
  objConn.Close
  Set objConn = Nothing
  Response.Redirect "customerLabelrpt.asp"
%>

I get an error:

Type mismatch: '[string: "select count(*) AS U"]'

Can I please get some assistance on creating my page. Let me know if any other information is needed.

Thanks,

mat41 · September 20th, 2012, 07:01 PM

You should indicate the line number point out which line this is in your code. help others help you!!

You are lucky I can tell from the msge what line it is. you have got all double quote happy with your SQL. This is what you need to have:

Code:

testSQL = "select count(*) AS Userlbl from LocalUserLabel where server_name =  '" & strServer & "' and User_ID = '" & strUserID & "';"

Note - The trailing semi colon is not required and is not part of the solution but a good idea for a few reasons. you should alays place a semi colon at the end of every SQL statement

snteran · September 20th, 2012, 08:23 PM

Hello Matt,

Thank you for your assistance. I was able to proceed but unfortunately I got a different error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/servermgmtfsa/customerLabelrpt_update2.asp, line 31

Line 31 references line objRS2.Close.

Not sure why this would be a problem, when I took out the two references about objRS2, Line 31 & 32, the process would complete but nothing was updated or inserted.

Any additional information/help would be greatly appreciated.

Thanks,