Well, In my application I have a datagridview ctrl with 4 columns. 2 datagridviewtext columns and 2 datagridviewImage columns. with the code below along with the store procedure I was able to store all this columns into sql table. the reason behind storing in sql because the user will make changes to this columns I need to be able to save is just in case we need to reboot the PC. the images are embedded resource icons.
In sql table, I see all the names , messages and the images are in <binary data=""> format.
But with code above I can't retrieve this table to show it back on datagridview
For X As Integer = 0 To DataGridView1.RowCount - 1
Dim strFn As String = DataGridView1.Rows(X).Cells"CompanyStatusImage".Value.ToString
Dim fiImage As New FileInfo(strFn)
Me.m_lImageFileLength = fiImage.Length
m_barrImg = New Byte(Convert.ToInt32(Me.m_lImageFileLength) - 1) {}
Private Sub frmMainForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Dim _DelimitedString As String = String.Empty
For X As Integer = 0 To DataGridView1.RowCount - 1
_DelimitedString &= DataGridView1.Rows(X).Cells("Coworker_name").Value.ToString() & "," _
& DataGridView1.Rows(X).Cells("StatusImage").Value.ToString() & "," _
& DataGridView1.Rows(X).Cells("StatusImage1").Value.ToString() & "," _
& DataGridView1.Rows(X).Cells("Message").Value.ToString() & "," _
Next
_Connection.Open()
_Command = New System.Data.SqlClient.SqlCommand()
_Command.CommandType = CommandType.StoredProcedure
_Command.CommandText = "sp_InputWork"
_Command.Connection = _Connection
_Command.Parameters.AddWithValue("@_DelimitedString", System.Data.SqlDbType.NVarChar).Value = _DelimitedString
_Command.Dispose()
_Command.ExecuteNonQuery()
End Sub
Store Procedure
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SQLQuery3]
@_DelimitedString nvarchar(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @_DataRow nvarchar(MAX)
DECLARE @_coworker_name nvarchar(MAX)
DECLARE @_TemporaryStorage nvarchar(MAX)
DECLARE @_m_barrImg varchar(MAX)
DECLARE @_m_barrImg1 varchar(MAX)
DECLARE @_message nvarchar(MAX)
DECLARE @_CurrentField int
WHILE CHARINDEX(';', @_DelimitedString) > 0
BEGIN
SET @_DataRow = CAST(SUBSTRING(@_DelimitedString, 0, CHARINDEX(';', @_DelimitedString)) AS nvarchar(MAX))
SET @_CurrentField = 1
WHILE CHARINDEX(',', @_DataRow) > 0
BEGIN
SET @_TemporaryStorage = CAST(SUBSTRING(@_DataRow, 0, CHARINDEX(',', @_DataRow)) AS nvarchar(MAX))
IF @_CurrentField = 2
SET @_m_barrImg = @_TemporaryStorage
IF @_CurrentField = 3
SET @_m_barrImg1 = @_TemporaryStorage
IF @_CurrentField = 1
SET @_coworker_name = @_TemporaryStorage
IF @_CurrentField = 4
SET @_company_name = @_TemporaryStorage
SET @_DataRow = SUBSTRING(@_DataRow, CHARINDEX(',', @_DataRow) + 1, LEN(@_DataRow))
SET @_CurrentField = @_CurrentField + 1
END
INSERT INTO tblImgData (coworker_name,CompanyStatusImage,CompanyStatusImage1,message) VALUES (@_coworker_name,@_m_barrImg,@_m_barrImg1,@_message)
SET @_DelimitedString = SUBSTRING(@_DelimitedString, CHARINDEX(';', @_DelimitedString) + 1, LEN(@_DelimitedString))
END
END
sql table has 4 columns
nvarchar(max), image,image, nvarchar(max)
Submit an article or tip