MSSQL Database INSERT, DELETE, and SELECT

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()

--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 [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()

MSSQL Insert type Image to field

Dim p As New SqlParameter("@sFileContents", SqlDbType.Image)

p.Value = sFileContents 'UnicodeStringToBytes(sFileContents)

cm.Parameters.Add(p)

ASP Get Image File from DB and Display in Browser

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

ASP Get Image HTML from DB and display in Browser

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