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


Example of SQL Image


p = New SqlParameter("@sBody", SqlDbType.Image)
p.Value = sBody 'UnicodeStringToBytes(sFileContents)
cm.Parameters.Add(p)

Paramertize LIKE operator


 sqlCommand.Parameters.AddWithValue("@sString", "%" + sString + "%")

Identity - get return value of the auto increment value

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

MSSQL Insert type Image to field

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

            p.Value = sFileContents 'UnicodeStringToBytes(sFileContents)

            cm.Parameters.Add(p)

Fill a SQL Adapter

  'Get all the presently active resources for the Launch menu, as well as Soarian and the Chart Summary Toolbar
        Dim strRes = "SELECT ID, AppDesignator, CommonName, URL, useAD, WindowTitle, IsEXE " _
                     & "FROM [dbo].[CXv2_Resources] " _
                     & "WHERE(Active = 1) " _
                     & "    AND (Expired = 0) " _
                     & "    AND (DateOn < GETDATE()) " _
                     & "    AND ((DateOff < DateOn) OR (DateOff > GETDATE())) " _
                     & "ORDER BY CommonName"

        Dim sqlConn As New SqlConnection
        Dim sqlCmd As New SqlCommand
        Dim strConn As String = ConfigurationManager.ConnectionStrings("chLauncher_vb.My.MySettings.connstrLauncher").ConnectionString
        Dim dtRes As New DataTable

        Try
            sqlConn = New SqlConnection(strConn)
            sqlCmd.Connection = sqlConn
            sqlCmd.CommandType = CommandType.Text
            sqlCmd.CommandText = strRes
            sqlConn.Open()

            Dim sqlAdapter As New SqlDataAdapter(sqlCmd)
            sqlAdapter.Fill(dtRes)
            sqlConn.Close()

MultipleActiveResultSets=true

;MultipleActiveResultSets=true

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

VB.Net Add Image to DB table


Function AddFileToRecord(iTag As Integer, sFileName As String) As Integer
        Dim Conn As New SqlConnection(aConnectionStrings(iMode))
        Dim iRet As Integer = -1
        Conn.Open()
        Dim x As Integer = 0

        Dim cmd As SqlCommand = Conn.CreateCommand()

        'cmdCommand.CommandText = sSQL

        Dim FileSize As Int32
        Dim rawData() As Byte
        Dim fs As FileStream
        'Dim sID As String = funGetIDfromData(lstEntries.SelectedItems(0).Text)
        fs = New FileStream(sFileName, FileMode.Open, FileAccess.Read)

        FileSize = fs.Length
        rawData = New Byte(FileSize) {}
        fs.Read(rawData, 0, FileSize)
        fs.Close()
        Dim Sql As String = "INSERT INTO [ChadRocksTooThree].[dbo].[EntryRecords_Attachments](EntriesID,FileName,FileSize,FileBlob) VALUES(@EntriesID, @FileName, @FileSize, @FileBlob); SELECT SCOPE_IDENTITY() AS TheNumber;"
        'cmd.Connection = conn

        cmd.CommandText = Sql

        cmd.Parameters.AddWithValue("@EntriesID", iTag)

        cmd.Parameters.AddWithValue("@FileName", Path.GetFileName(sFileName))

        cmd.Parameters.AddWithValue("@FileSize", FileSize)

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

        p.Value = rawData 'UnicodeStringToBytes(sFileContents)

        cmd.Parameters.Add(p)

        ' cmd.Parameters.AddWithValue("@File", rawData)

        Dim rs As SqlDataReader = cmd.ExecuteReader()        If rs.HasRows Then
            rs.Read()
            iRet = rs("TheNumber")
        Else
            iRet = -1
        End If

        If Conn.State > 0 Then Conn.Close()
        Return iRet

    End Function

VB.Net Get Image from Table

  Function funGetFile(sID As String)
        Dim myData As SqlDataReader
        Dim SQL As String
        Dim rawData() As Byte
        Dim FileSize As UInt32
        Dim fs As FileStream

        Dim SQLConnection As New SqlConnection(aConnectionStrings(iMode))
        Dim cmd As New SqlCommand
        cmd.Connection = SQLConnection


        SQLConnection.Open()
        Debug.Print(SQLConnection.State.ToString)
        'Try
        Dim sqlQuery As String = "SELECT [id],[EntriesID],[FileName],[FileSize],FileBlob FROM [ChadRocksTooThree].[dbo].[EntryRecords_Attachments] WHERE id=" & sID

        cmd.CommandText = sqlQuery
        cmd.CommandType = CommandType.Text
        myData = cmd.ExecuteReader

        If Not myData.HasRows Then Return "" ' Throw New Exception("There are no BLOBs to save")

        myData.Read()

        FileSize = myData("FileSize")        'FileSize = myData.GetInt32(myData.GetOrdinal("FileSize"))
        rawData = New Byte(FileSize) {}

        myData.GetBytes(myData.GetOrdinal("FileBlob"), 0, rawData, 0, FileSize)
        Dim sFileName As String = Path.GetTempPath & "\" & Path.GetFileName(myData("FileName"))

        fs = New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
        fs.Write(rawData, 0, FileSize)
        fs.Close()

        'MessageBox.Show("File successfully written to disk!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

        myData.Close()
        SQLConnection.Close()
        Process.Start(sFileName)
    End Function


VB.Net Get Image from Table (MySql)


   Function funGetFile(sID As String)

        Dim myData As MySqlDataReader
        Dim SQL As String
        Dim rawData() As Byte
        Dim FileSize As UInt32
        Dim fs As FileStream

        Dim SQLConnection As New MySqlConnection(connectionString)
        Dim cmd As New MySqlCommand
        cmd.Connection = SQLConnection


        SQLConnection.Open()
        Debug.Print(SQLConnection.State.ToString)
        'Try
        Dim sqlQuery As String = "SELECT id, FileName, FileSize, File FROM Accounts_Files WHERE localid=" & sID

        cmd.CommandText = sqlQuery
        cmd.CommandType = CommandType.Text
        myData = cmd.ExecuteReader

        If Not myData.HasRows Then Return "" ' Throw New Exception("There are no BLOBs to save")

        myData.Read()

        FileSize = myData.GetUInt32(myData.GetOrdinal("FileSize"))
        rawData = New Byte(FileSize) {}

        myData.GetBytes(myData.GetOrdinal("File"), 0, rawData, 0, FileSize)
        Dim sFileName As String = Path.GetTempPath & "\" & Path.GetFileName(myData("FileName"))

        fs = New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
        fs.Write(rawData, 0, FileSize)
        fs.Close()

        'MessageBox.Show("File successfully written to disk!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

        myData.Close()
        SQLConnection.Close()
        Process.Start(sFileName)
    End Function


VB.Net Add Image to DB Table (MySql)


Private Sub AddFileToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AddFileToolStripMenuItem.Click
        Dim result As DialogResult = OpenFileDialog2.ShowDialog

        If result = Windows.Forms.DialogResult.OK Then
            Dim SQLConnection As New MySqlConnection(connectionString)
            Dim cmd As New MySqlCommand
            cmd.Connection = SQLConnection

            Dim sRet As String = ""


            Dim FileSize As UInt32
            Dim rawData() As Byte
            Dim fs As FileStream
            Dim sFileName As String = OpenFileDialog2.FileName
            Dim sID As String = funGetIDfromData(lstEntries.SelectedItems(0).Text)

            fs = New FileStream(sFileName, FileMode.Open, FileAccess.Read)
            FileSize = fs.Length

            rawData = New Byte(FileSize) {}
            fs.Read(rawData, 0, FileSize)
            fs.Close()

            SQLConnection.Open()
            Debug.Print(SQLConnection.State.ToString)
            Dim Sql As String = "INSERT INTO Accounts_Files(id,FileName,FileSize,File) VALUES(@id, @FileName, @FileSize, @File)"

            'cmd.Connection = conn
            cmd.CommandText = Sql
            cmd.Parameters.AddWithValue("@id", sID)
            cmd.Parameters.AddWithValue("@FileName", Path.GetFileName(sFileName))
            cmd.Parameters.AddWithValue("@FileSize", FileSize)
            Dim p As New MySqlParameter("@File", MySqlDbType.LongBlob)
            p.Value = rawData 'UnicodeStringToBytes(sFileContents)
            cmd.Parameters.Add(p)

            ' cmd.Parameters.AddWithValue("@File", rawData)

            cmd.ExecuteNonQuery()

            ' Catch ex As MySqlException
            ' add your exception here '
            '    MsgBox(ex.Message.ToString)
            ' End Try


            'If Not sqlReader.IsClosed Then sqlReader.Close()
            SQLConnection.Close()
            Debug.Print(SQLConnection.State.ToString)
        End If

    End Sub


Classic ASP repost Form data

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

Import binary file into a sql varbinary(max) column (VBScript)

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

Execute Stored Procedure in VBNet

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