Connect to SQL Server from LN
Connect to SQL Server from LN
(OP)
Hi,
I hope some one can point me in the right direction here.
I'd like to update a table on a SQL Server with data in a LN document, but I've no idea how to connect to it.
I've read something about LN being able to connect with OLE DB, but how I'm to put that in code, I could not find.
Cheers,
Roel
I hope some one can point me in the right direction here.
I'd like to update a table on a SQL Server with data in a LN document, but I've no idea how to connect to it.
I've read something about LN being able to connect with OLE DB, but how I'm to put that in code, I could not find.
Cheers,
Roel
RE: Connect to SQL Server from LN
look in the help files of designer under "LCConnection Class Methods"
____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done
RE: Connect to SQL Server from LN
CODE
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
rdate = Format(rdate, "YYYYMMDD")
rtime = Format(rtime, "HHMM")
odate = Format(odate, "YYYYMMDD")
otime = Format(otime, "HHMM")
con.ConnectTo("iseries")
qry.SQL = "SELECT JUDCOD FROM CMLIB.CMPJUDNM WHERE JUDNAM = '" + rjudge + "'"
result.Execute
rjudge = result.GetValue("JUDCOD")
' Update jury slot record to AS/400 jury calendaring
If rjudge <> 5 And rjudge <> 15 Then
qry.SQL = "SELECT CASNUM, CASPRE FROM CMLIB.CMPHERMF WHERE HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ' AND (STSCOD = 0 OR STSCOD = 1)"
'qry.SQL = "SELECT CASNUM, CASPRE FROM Y2K.CMPHERMF WHERE HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ' AND (STSCOD = 0 OR STSCOD = 1)"
result.Execute
If result.NumRows = 0 Then
qry.SQL = "UPDATE CMLIB.CMPSCHCT SET HERNGDAT = " + rdate + ", HERTIM = " + rtime + " WHERE JUDCOD = " + rjudge + _
" AND HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ'"
'qry.SQL = "UPDATE Y2K.CMPSCHCT SET HERNGDAT = " + rdate + ", HERTIM = " + rtime + " WHERE JUDCOD = " + rjudge + _
'" AND HERNGDAT = " + odate + " AND HERTIM = " + otime + " AND HERTYP = 'SJ'"
result.Execute
result.Close(DB_CLOSE)
con.Disconnect
Update = True
End If
Else
qry.SQL = "UPDATE CVLIB.CVPSCHCTA SET DKTDATE = " + rdate + ", DKTTIM = " + rtime + " WHERE JUDCOD = " + rjudge + _
" AND DKTDATE = " + odate + " AND DKTTIM = " + otime + " AND DKTTYP = 'SJ'"
End If
End Function
Leslie
Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual
Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins
RE: Connect to SQL Server from LN
thanks for the replies. I've tried both tactics, unfortunately without success.
Regarding the ODBC, I'm not sure where I need to reference what exactly.
For the ConnectTo statement, should I put in the name of the server or database (maybe both)?
CODE
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim MyResult As String
Set qry.Connection = con
Set result.Query = qry
con.ConnectTo(SQLServerName)
qry.SQL = "SELECT CURRENCY FROM SQLServerDatabase.SourceTable"
result.Execute
myresult = result.GetValue("CURRENCY")
Messagebox myresult
result.Close(DB_CLOSE)
con.Disconnect
End Sub
When I run the above, MyResult returns FALSE.
As to the LCConnection, I've tried the following:
CODE
Dim con As New LCConnection ("oledb")
With con
.Provider = "sqloledb"
.Server = "EUNLROTDB007"
.Database = "MJU_Validation"
.Metadata = "tbl_CURRENCY"
End With
On Error Resume Next
con.Connect
If Err = False Then
Messagebox "Successfully connected to SQLServer."
Else
Messagebox "Connection failed with error " & Err & ": " & Error
End If
End Sub
This seems at least to be trying to access the db, but it tells me that there is 'Invalid authorization specification'. I've added the lines:
CODE
.Password = "MyWindowsPassword"
but then it says that 'Login Failed for user MyUserID'
I've have ownership of the database I'm trying to connect to. I'm not sure what UserID and/or Password it is expecting. (i.e. I can just access the db through SQL Server Enterprise Manager without ever having to provide a password as it should use my windows login).
I've the feeling that I'm close, but close is never quite good enough. :)
Does anyone know what I'm missing?
Cheers,
Roel
RE: Connect to SQL Server from LN
CODE
Dim ses As New LCSession
ses.ClearStatus
Dim con As New LCConnection ("oledb")
With con
.Provider = "SQLOLEDB"
.Server = "SQLServerName"
.Database = "DatabaseName"
.Metadata = "tbl_CURRENCY"
.Auth_Integrated = "SSPI"
End With
On Error Resume Next
con.Connect
If Err = False Then
Messagebox "Successfully connected to SQLServer."
Else
Messagebox "Connection failed with error " & Err & ": " & Error
End If
Dim MyFldList As New LCFieldList
Dim MyFld As LCField
Dim SQL As String
Dim MyResult As String
SQL = "SELECT CURRENCY FROM tbl_CURRENCY"
con.Execute sql,myfldlist
Set myfld = myfldlist.GetField(1)
While (con.Fetch(myfldlist) > 0)
myresult = myresult & myfld.Text(0) & Chr(13)
Wend
Messagebox myresult
con.Disconnect
Set myfldlist = Nothing
Set myfld = Nothing
Set con = Nothing
Set ses = Nothing
End Sub
Thanks for pointing me to those LCConnection Classes, ONPNT, star for you!
Cheers,
Roel
RE: Connect to SQL Server from LN
And thank you for the * as well
____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done