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

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