Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Posts
    20

    Unhappy Unanswered: VBScript to call data from MS SQL SERVER database

    anyone know how to use VBScript to call the data from the MS SQL SERVER database?

    And how to assign the database record value to Document.write, plus the text together.

    Example:

    Dim a
    a = DoctorsProfile.DoctirName.value

    Is it correct?

    --------------------------------------------

    Next one would be:

    Document.write DoctorsProfile.DoctorName + "is specialize in eye diseases"

    Is it correct?

    --------------------------------------------

    How to write them, can anyone please help me?
    In the ASP page I also created three recordset, meaning that there are three different tables.

    I'm very appreciate your help. And would be very thankful to you.
    Please reply soon, as soon as possible.

    Best regards,
    Yutaka

  2. #2
    Join Date
    Oct 2002
    Posts
    48

    This will help you.

    CONTENT OF PIRCONNECTION.ASP

    <%
    ' FileName="Connection_odbc_conn_dsn.htm"
    ' Type="ADO"
    ' HTTP="false"
    ' Catalog=""
    ' Schema=""
    'MM_capacity_STRING = "dsn=Test_UGN_DB_DSN;uid=sa;pwd=tiger;"
    MM_capacity_STRING = "dsn=UGN_DB_DSN;uid=sa;pwd=tiger;"
    %>


    You need to create DSN: UGN_DB_DSN in control Panel, ODBC.

    then use following code to play with Database.


    ----



    <%
    MM_editConnection = MM_capacity_STRING
    set conn = Server.CreateObject("ADODB.Command")
    conn.ActiveConnection = MM_editConnection

    set conn_temp = Server.CreateObject("ADODB.Connection")
    conn_temp.open = MM_editConnection
    set rs = Server.Createobject("ADODB.RecordSet")


    'Searching Role of User
    set rs = Server.Createobject("ADODB.RecordSet")
    RoleStr = "Select RoleId from User_Role_M where userid = '" & UserId & "' and roleid like 'PIR%'"
    'Response.write (RoleStr)
    rs.open RoleStr , conn_temp
    If not rs.EOF then
    UserRole = rs("RoleID")
    Else
    UserRole = "Unknown"
    End if
    rs.Close




    %>


    <%

    'Insert OR Update Record
    IF Actiontobetaken = "InsertRec" then
    str = "select max(PIRNo) from PIR_M"
    rs.open str, conn_temp
    If rs.eof then
    PIRNo = 1
    Else
    PIRNo = rs(0) + 1
    End if
    rs.close

    'Inserting Record
    'Original SqlText = " Insert into PIR_M (PIRNo, PartType, InitiatedBy, InitiatedDate, OEMName, CustPartNumber, CustNo, CustAbbrev, DestNo, DestAbbrev, BPCSPartNumber, BPCSDescription, PartName, ManuUGNFacility, PartWeight, StdPackQty, ContainerSize, TareWeight, SalesPriceinsystem) values (" & PirNo & " , '" & PartType & "', '" & InitiatedBy & "', GetDate(), '" & OEMName & "', '" & CustPartNumber & "', " & CustNo & ", '" & CustAbbrev & "'," & DestNo & ", '" & DestAbbrev & "','" & BPCSPartNumber & "','" & BPCSDescription & "', '" & PartName & "', " & ManuUGNFacility & ", '" & PartWeight & "', " & StdPackQty & ", '" & ContainerSize & "', " & TareWeight & ", " & SalesPriceinsystem & ")"
    SqlText = " Insert into PIR_M (PIRNo, PartType, InitiatedBy, InitiatedDate, OEMName, CustPartNumber, CustNo, CustAbbrev, DestNo, DestAbbrev, BPCSPartNumber, BPCSDescription, PartName, ManuUGNFacility, PartWeight, StdPackQty, ContainerSize, TareWeight, SalesPriceinsystem, QECheckbox, QEInitials, QEDate, PlantCntrlrCheckbox, PlantCntrlrInitials, PlantCntrlrDate, BillingCheckbox, BillingInitials, BillingDate, QE2Checkbox, QE2Initials, QE2Date, PackagingCheckbox, PackagingInitials, PackagingDate, EDICheckbox, EDIInitials, EDIDate ) values (" & PirNo & " , '" & PartType & "', '" & InitiatedBy & "', GetDate(), '" & OEMName & "', '" & CustPartNumber & "', " & CustNo & ", '" & CustAbbrev & "'," & DestNo & ", '" & DestAbbrev & "','" & BPCSPartNumber & "', '" & BPCSDescription & "', '" & PartName & "', " & ManuUGNFacility & ", '" & PartWeight & "', " & StdPackQty & ", '" & ContainerSize & "', " & TareWeight & ", " & SalesPriceinsystem & ", " & QECheckbox & ", " & QEInitials & ", '" & QEDate & "', " & PlantCntrlrCheckbox & ", " & PlantCntrlrInitials & ", '" & PlantCntrlrDate & "', " & BillingCheckbox & ", " & BillingInitials & ", '" & BillingDate & "', " & QE2Checkbox & ", " & QE2Initials & ", '" & QE2Date & "', " & PackagingCheckbox & ", " & PackagingInitials & ", '" & PackagingDate & "', " & EDICheckbox & ", " & EDIInitials & ", '" & EDIDate & "' )"
    'response.write(" Insert ::: " & SqlText)
    'response.end
    conn.CommandText = SqlText
    conn.execute

    ELSE 'UpdateRec
    PIRNo = Request.form("PIRNo")
    SqlText = " Update PIR_M Set PartType = '" & PartType & "', InitiatedBy = '" & InitiatedBy & "', InitiatedDate = '" & InitiatedDate & "', OEMName = '" & OEMName & "', CustPartNumber = '" & CustPartNumber & "', CustNo = " & CustNo & ", CustAbbrev = '" & CustAbbrev & "', DestNo = " & DestNo & ", DestAbbrev = '" & DestAbbrev & "', BPCSPartNumber = '" & BPCSPartNumber & "', BPCSDescription = '" & BPCSDescription & "', PartName = '" & PartName & "', ManuUGNFacility = " & ManuUGNFacility & ", PartWeight = '" & PartWeight & "', StdPackQty = " & StdPackQty & ", ContainerSize = '" & ContainerSize & "', TareWeight = " & TareWeight & ", SalesPriceinsystem = " & SalesPriceinsystem & ", QECheckbox = " & QECheckbox & ", QEInitials = " & QEInitials & ", QEDate = '" & QEDate & "', PlantCntrlrCheckbox = " & PlantCntrlrCheckbox & ", PlantCntrlrInitials = " & PlantCntrlrInitials & ", PlantCntrlrDate = '" & PlantCntrlrDate & "', BillingCheckbox = " & BillingCheckbox & ", BillingInitials = " & BillingInitials & ", BillingDate = '" & BillingDate & "', QE2Checkbox = " & QE2Checkbox & ", QE2Initials = " & QE2Initials & ", QE2Date = '" & QE2Date & "', PackagingCheckbox = " & PackagingCheckbox & ", PackagingInitials = " & PackagingInitials & ", PackagingDate = '" & PackagingDate & "', EDICheckbox = " & EDICheckbox & ", EDIInitials = " & EDIInitials & ", EDIDate = '" & EDIDate & "' Where PIRNo = " & PIRNo
    'response.write(" Update ::: " & SqlText)
    'response.end

    conn.CommandText = SqlText
    conn.execute

    End if



    %>

    <%

    'Send email to QE Enginner Email

    'Searching From Address
    set UGNQEEmployeeEmail = Server.Createobject("ADODB.RecordSet")
    UGNQEEmployeeStr = "Select empemail from employee_M where EmpId = " & EDIInitials
    UGNQEEmployeeEmail.open UGNQEEmployeeStr , conn_temp
    EmailFromAddress = UGNQEEmployeeEmail("empemail")
    UGNQEEmployeeEmail.close

    'Searching To Address
    set UGNQEEmployeeEmail = Server.Createobject("ADODB.RecordSet")
    UGNQEEmployeeStr = "Select empemail from employee_M where EmpId = " & QEInitials
    UGNQEEmployeeEmail.open UGNQEEmployeeStr , conn_temp
    EmailToAddress = UGNQEEmployeeEmail("empemail")
    UGNQEEmployeeEmail.close

    Subject = "PIR Notification for PIR # " & PirNo
    Body = "EDI Coordinator has created PIR. PIR Notification for PIR #: " & PirNo & ". For BPCS Part Number: " & BPCSPartNumber & ", BPCS Description: " & BPCSDescription & ", Customer Part Number: " & CustPartnumber

    'response.write(" From address = " & EmailFromAddress)
    'response.write(" To address = " & EmailToAddress)

    Set MailObj = Server.CreateObject ("CDONTS.NewMail")
    MailObj.BodyFormat = 1
    MailObj.MailFormat = 0

    'MailObj.Send EmailFromAddress, EmailToAddress, Subject, Body
    Response.write ("Email sent to QE Person successfully. From: " & EmailFromAddress & ", To: " & EmailToAddress )


    %>
    Last edited by vsshah; 02-13-03 at 12:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •