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

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

                  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