Writing to SQL server w/ VB.Net
-
First the setup My Code:
Imports System.Data.SqlClient Imports System.Text Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim myConnection As New SqlConnection() Dim myDataSet As New DataSet() Dim myDataAdapter As SqlDataAdapter Dim myUpdateCommand As SqlCommand Dim myNewStr As String 'create connection string myConnection.ConnectionString ="Server=.;Database=TestDB;UID=steve;pwd=password;Trusted_Connection=yes;" 'open connection to DB myConnection.Open() 'setup data adapter myDataAdapter = New SqlDataAdapter("select TEXT from SCRIPTS WHERE ID=1", myConnection) 'Initialize the SqlCommand object that will be used as the DataAdapter's UpdateCommand. 'Note that the WHERE clause uses only the CustId field to locate the record that is to be updated. myUpdateCommand = New SqlCommand("UPDATE SCRIPTS SET TEXT=@pNewText FROM SCRIPTS WHERE ID =@pMyID", myDataAdapter.SelectCommand.Connection) 'Create and append the parameters for the Update command. myUpdateCommand.Parameters.Add(New SqlParameter("@pNewText", SqlDbType.VarChar)) myUpdateCommand.Parameters("@pNewText").SourceVersion = DataRowVersion.Current myUpdateCommand.Parameters("@pNewText").SourceColumn = "TEXT" myUpdateCommand.Parameters.Add(New SqlParameter("@pMyID", SqlDbType.Int)) myUpdateCommand.Parameters("@pMyID").SourceVersion = DataRowVersion.Original myUpdateCommand.Parameters("@pMyID").SourceColumn = "ID" 'Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter. myDataAdapter.UpdateCommand = myUpdateCommand 'call the fill method to populate our dataset myDataAdapter.Fill(myDataSet, "SCRIPTS") 'present the current entry in database MsgBox("Script before updating" + vbCrLf + Encoding.Unicode.GetString(myDataSet.Tables("SCRIPTS").Rows(0)("TEXT"))) 'modify current with replace to update field myNewStr = Replace(Encoding.Unicode.GetString(myDataSet.Tables("SCRIPTS").Rows(0)("TEXT")), "hello", "steve") 'now convert our replaced field to a byte array Dim dBytes As Byte() dBytes = StrToByteArray(myNewStr) 'set the dataset table row to our converted byte array myDataSet.Tables("SCRIPTS").Rows(0)("TEXT") = dBytes 'c -
First the setup My Code:
Imports System.Data.SqlClient Imports System.Text Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim myConnection As New SqlConnection() Dim myDataSet As New DataSet() Dim myDataAdapter As SqlDataAdapter Dim myUpdateCommand As SqlCommand Dim myNewStr As String 'create connection string myConnection.ConnectionString ="Server=.;Database=TestDB;UID=steve;pwd=password;Trusted_Connection=yes;" 'open connection to DB myConnection.Open() 'setup data adapter myDataAdapter = New SqlDataAdapter("select TEXT from SCRIPTS WHERE ID=1", myConnection) 'Initialize the SqlCommand object that will be used as the DataAdapter's UpdateCommand. 'Note that the WHERE clause uses only the CustId field to locate the record that is to be updated. myUpdateCommand = New SqlCommand("UPDATE SCRIPTS SET TEXT=@pNewText FROM SCRIPTS WHERE ID =@pMyID", myDataAdapter.SelectCommand.Connection) 'Create and append the parameters for the Update command. myUpdateCommand.Parameters.Add(New SqlParameter("@pNewText", SqlDbType.VarChar)) myUpdateCommand.Parameters("@pNewText").SourceVersion = DataRowVersion.Current myUpdateCommand.Parameters("@pNewText").SourceColumn = "TEXT" myUpdateCommand.Parameters.Add(New SqlParameter("@pMyID", SqlDbType.Int)) myUpdateCommand.Parameters("@pMyID").SourceVersion = DataRowVersion.Original myUpdateCommand.Parameters("@pMyID").SourceColumn = "ID" 'Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter. myDataAdapter.UpdateCommand = myUpdateCommand 'call the fill method to populate our dataset myDataAdapter.Fill(myDataSet, "SCRIPTS") 'present the current entry in database MsgBox("Script before updating" + vbCrLf + Encoding.Unicode.GetString(myDataSet.Tables("SCRIPTS").Rows(0)("TEXT"))) 'modify current with replace to update field myNewStr = Replace(Encoding.Unicode.GetString(myDataSet.Tables("SCRIPTS").Rows(0)("TEXT")), "hello", "steve") 'now convert our replaced field to a byte array Dim dBytes As Byte() dBytes = StrToByteArray(myNewStr) 'set the dataset table row to our converted byte array myDataSet.Tables("SCRIPTS").Rows(0)("TEXT") = dBytes 'csvanwass wrote:
'Create and append the parameters for the Update command. myUpdateCommand.Parameters.Add(New SqlParameter("@pNewText", SqlDbType.VarChar))
You created this parameter as a string, not as a binary type. This is where the error is comming from.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007