Dim myconnection As SqlConnection
Dim mycommand As SqlCommand
Dim dr As SqlDataReader
Dim dr1 As SqlDataReader
--INSERT----------------------------------------
myconnection = New SqlConnection("server=VS8HRSQL\HRNNC2008R2;database=crousescripts;uid=user;pwd=password") ';Trusted_Connection=Yes")
'you need to provide password for sql server
myconnection.Open()
'mycommand = New SqlCommand("insert into tbl_cus([name],[class],[phone],[address]) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')", myconnection)
mycommand = New SqlCommand("INSERT INTO [robert_report_staging]([tagid],[description],[monitorid])VALUES(@tagid,@description,@monitorid)", myconnection)
mycommand.Parameters.AddWithValue("@tagid", funGrabBetween(sEntry(1), ">", "</td>"))
mycommand.Parameters.AddWithValue("@description", funGrabBetween(sEntry(2), ">", "</td>"))
mycommand.Parameters.AddWithValue("@monitorid", funGrabBetween(sEntry(3), ">", "</td>"))
Dim ra As Integer = mycommand.ExecuteNonQuery()
Debug.Print("New Row Inserted" & ra)
myconnection.Close()
@@IDENTITY
returns the last identity value generated for any table in the current
session, across all scopes. You need to be careful here, since it's
across scopes. You could get a value from a trigger, instead of your
current statement.
SCOPE_IDENTITY() returns the last identity value
generated for any table in the current session and the current scope.
Generally what you want to use.
--DELETE----------------------------------------
myconnection = New SqlConnection("server=VS8HRSQL\HRNNC2008R2;database=crousescripts;;uid=user;pwd=password") ';Trusted_Connection=Yes")
'you need to provide password for sql server
myconnection.Open()
'mycommand = New SqlCommand("insert into tbl_cus([name],[class],[phone],[address]) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')", myconnection)
'mycommand = New SqlCommand("INSERT INTO [robert_report_staging]([tagid],[description],[monitorid])VALUES(@tagid,@description,@monitorid)",myconnection)
'mycommand = New SqlCommand("TRUNCATE TABLE [robert_report_staging]", myconnection)
mycommand = New SqlCommand("DELETE FROM [robert_report_staging]", myconnection)
Dim ra As Integer = mycommand.ExecuteNonQuery()
Debug.Print("Rows affected " & ra)
myconnection.Close()
--SELECT----------------------------------------
Dim Conn As New SqlConnection(sConnStringSD)
Conn.Open()
Dim x As Integer = 0
Dim aSQL As String = "SELECT [id] ,[sAppName], [sServerName] ,[sServiceName] ,[iOrderID], [sADGroup] FROM [CrouseScripts].[dbo].[EPSi_ServicesRestart] ORDER by [sServerName],[iOrderID],[sServiceName]"
Dim cmdCommand As SqlCommand = Conn.CreateCommand()
cmdCommand.CommandText = sSQL
'http://www.dotnetheaven.com/article/listview-control-with-multiple-column-in-vb.net
'Application.DoEvents()
Using rsRS As SqlDataReader = cmdCommand.ExecuteReader()
If rsRS.HasRows Then
While rsRS.Read()
'ReDim Preserve strcServerInfo(x)
If bGRPIT Or bShowAll Or IsInGroup(rsRS("sADGroup")) Then
Dim lvItem As ListViewItem = ListView1.Items.Add("-")
lvItem.SubItems.Add(rsRS("sAppName"))
lvItem.SubItems.Add(rsRS("sServerName"))
lvItem.SubItems.Add(rsRS("sServiceName"))
lvItem.SubItems.Add(rsRS("iOrderID"))
End If
x += 1
End While
End If
End Using
If Conn.State > 0 Then Conn.Close()
Dim p As New SqlParameter("@sFileContents", SqlDbType.Image)
p.Value = sFileContents 'UnicodeStringToBytes(sFileContents)
cm.Parameters.Add(p)
;MultipleActiveResultSets=true
aFileExt = split(rsRS("sFileName"),".")
' response.write aFileExt(ubound(aFileExt))
select case lcase(aFileExt(ubound(aFileExt)))
case "pdf"
sDocType="application/pdf"
case "doc"
sDocType="application/msword"
case "xls"
sDocType="application/vnd.ms-excel"
case "xml"
sDocType="application/xml" '"text/xml"
case else
sDocType="application/octet-stream"
end select
Response.Buffer = true
' ' Response.ContentType = "application/pdf"
Response.ContentType = sDocType
Response.AddHeader "Content-Disposition", "inline;filename=" & rsRS("sFileName")
' Response.AddHeader "Content-Disposition", "attachment;filename=" & rsRS("sFileName")
'Response.OutputStream.Write rsRS("sFileContents") ,0,rsRS("iSize")
Response.BinaryWrite rsRS("sFileContents")
Response.Flush
end if
Set rsRS = nothing
set objConn = nothing
Set objConn = Server.CreateObject("ADODB.Connection")
Set rsRS = Server.CreateObject("ADODB.RecordSet")
objConn.Open sConn
sSQL = "SELECT * from AoC_Archive WHERE id='" & Request.QueryString("msg") & "';"
rsRS.open sSQL, objConn
if not rsRS.eof then
' aFileExt = split(rsRS("sFileName"),".")
' ' response.write aFileExt(ubound(aFileExt))
' select case lcase(aFileExt(ubound(aFileExt)))
' case "pdf"
' sDocType="application/pdf"
' case "doc"
' sDocType="application/msword"
' case "xls"
' sDocType="application/vnd.ms-excel"
' case "xml"
' sDocType="application/xml" '"text/xml"
' case else
sDocType="text/html"
' end select
Response.Buffer = true
' ' Response.ContentType = "application/pdf"
Response.ContentType = sDocType
Response.AddHeader "Content-Disposition", "inline;filename=" & "file.html"
' Response.AddHeader "Content-Disposition", "attachment;filename=" & rsRS("sFileName")
'Response.OutputStream.Write rsRS("sFileContents") ,0,rsRS("iSize")
Response.BinaryWrite rsRS("sBodyHTML")
Response.Flush
data = "username=" & vc_employeeID
' response.write data
' response.end
'data = data & "&var2=someothervalue"
'data = data & "&var3=someothervalue"
Set httpRequest = Server.CreateObject("MSXML2.ServerXMLHTTP")
httpRequest.Open "POST", "https://enroll.crouse.org/DUO2FA", False
httpRequest.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.Send data
postResponse = httpRequest.ResponseText
Response.Write postResponse ' or do something else with it
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider=SQLOLEDB;Data Source=" & "sqlcluster" & ";Trusted_Connection=Yes;Initial Catalog=" & "PatientFeedback_Historical" & ";"';UserID=username;Password=password;
REM "Provider=SQLOLEDB;Data Source=" & "sqlcluster" & ";Trusted_Connection=Yes;Initial Catalog=" & "import" & ";"';UserID=username;Password=password;
set ofso = CreateObject("Scripting.FileSystemObject")
set oFolder = ofso.getfolder("S:\IT Projects\RL Solutions\FMPro_Live_Extract\Attachments\FMProFollowAttach")
for each oFile in oFolder.files
wscript.echo oFile
funInsertBLOBintoRM( oFile)
wscript.sleep 1000
next
function funInsertBLOBintoRM(sFile)
dim x
set rsRS = CreateObject("ADODB.Recordset")
Set ObjStr = CreateObject("ADODB.Stream")
ObjStr.Type = 1 'AdBinary
ObjStr.Open
wscript.echo "" & sFile & ""
ObjStr.LoadFromFile "" & sFile & ""
'Evita sql inject
Set oPreparedStatementADO = CreateObject("ADODB.Command")
oPreparedStatementADO.ActiveConnection = objConnection
REM cSql = "UPDATE import SET photos_blob=? WHERE [UDF_Employee_ID]='" & replace(sFile,".jpg","") & "'"
cSql = "INSERT INTO FMProAttachment(attachId,AttachFileName,AttachmentFile)VALUES(?,?,?)"
wscript.echo cSql
oPreparedStatementADO.CommandText = cSQL
x = ObjStr.Read
REM oPreparedStatementADO.Parameters.Item(0) = x
oPreparedStatementADO.Parameters.Item(0) = funGrabBetween(sFile.Name,"","_")
oPreparedStatementADO.Parameters.Item(1) = replace(sFile.Name,funGrabBetween(sFile.Name,"","_") & "_","")
oPreparedStatementADO.Parameters.Item(2) = x
'cmd.Parameters.AddWithValue("@custID", "DS212")
'ObjStr.SaveToFile("C:\crousescripts\richtest\" & sfile.name)
set rs = oPreparedStatementADO.Execute
set ObjStr = nothing
set x = nothing
end function
function funGrabBetween( sText, sParam1, sParam2)
'response.write "sText = " & sText
'wscript.echo ">> " & position
lngLocOfAvail = instr(1,sText , sParam1,1)
'wscript.echo vbtab & lngLocOfAvail
if lngLocOfAvail > 0 then
'wscript.echo vbtab & "start: " & lngLocOfAvail+len(sParam1)
'wscript.echo vbtab & "end : " & instr(lngLocOfAvail+len(sParam1),sText,sParam2,1)-lngLocOfAvail-+len(sParam1)
funGrabBetween= mid(sText ,lngLocOfAvail+len(sParam1),instr(lngLocOfAvail+len(sParam1),sText,sParam2,1)-lngLocOfAvail-+len(sParam1))
'position = lngLocOfAvail +1
else
funGrabBetween= -1
'position = 1
end if
end Function
Using cn = GetConnection()
cn.Open()
' Create the command with the sproc name and add the parameter required'
Dim cmd As SqlCommand = new SqlCommand("GetNameAddress", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@custID", "DS212")
' Ask the command to create an SqlDataReader on the result of the sproc'
Using r = cmd.ExecuteReader()
' If the SqlDataReader.Read returns true then there is a customer with that ID'
if r.Read() then
' Get the first and second field frm the reader'
lblName.Text = r.GetString(0)
lblAddress.Text = r.GetString(1)
end if
End Using
End using