INSERT using vb.Net - how to continue

[Fred48]

Hi,

I have a INSERT to an access database that has about twenty fields. Is there anyway to continue the INSERT statement instead of making one continous statement as below:

cmd = New OleDbCommand(" INSERT INTO tblRevResults ([Update Resp Code], [Check Field], [Save Ind], [TF Resp Code], TGSN , TGID, [Carrier Id],[Orig Eqpt Type],[Term Eqpt Type],[Other TGID],[ColorCode],[TGSNSortOrder],[Modified], [OrigSaveInd] ) Values('" & m_FORDownLoadRequest.UpdateRespCode & "', '" & "0" & "','" & m_FORDownLoadRequest.SaveRevInd & "','" & m_FORDownLoadRequest.TFRespCode & "', '" & m_FORDownLoadRequest.TGSN & "', '" & GetTGIDFromRecord() & "', '" & m_FORDownLoadRequest.CarrierID & "', '" & m_FORDownLoadRequest.OrigEqpType & "','" & m_FORDownLoadRequest.TermEqpType & "', '" & m_FORDownLoadRequest.OtherTGID & "', '" & strColor & "','" & intrecordcount & "','" & "N" & '" &m_FORDownLoadRequest.SaveRevInd & "',"')", cnn)

Thanks

 

[Andrzejek]

How about:

Dim [blue]strIns[/blue] As String = "INSERT INTO tblRevResults ([Update Resp Code], " _
& ""[Check Field], [Save Ind], [TF Resp Code],  TGSN ,  TGID, [Carrier Id], " _
& "[Orig Eqpt Type],[Term Eqpt Type],[Other TGID],[ColorCode],[TGSNSortOrder], " _
& "[Modified], [OrigSaveInd]  ) Values " _
& "('" & m_FORDownLoadRequest.UpdateRespCode & "',  '" & "0" & "', " _
& "'" & m_FORDownLoadRequest.SaveRevInd & "','" & m_FORDownLoadRequest.TFRespCode & "', " _
& "'" & m_FORDownLoadRequest.TGSN & "', '" & GetTGIDFromRecord() & "', " _
& "'" & m_FORDownLoadRequest.CarrierID & "', '" & m_FORDownLoadRequest.OrigEqpType & "', " _
& "'" & m_FORDownLoadRequest.TermEqpType & "', '" & m_FORDownLoadRequest.OtherTGID & "', " _
& "'" & strColor & "','" & intrecordcount & "', " _
& "'" & "N" &  '" &m_FORDownLoadRequest.SaveRevInd & "'[red],"'[/red])"

cmd = New OleDbCommand([blue]strIns[/blue], cnn)

Have fun.

---- Andy

 

[Andrzejek]

After submiting, it does not look right.

The idea was:

Dim strIns As String = "INSERT INTO tblRevResults " _
    & "(Field1, Field2, Field3, ...) " _
    & " VALUES(123, 'abc', 888, ...)

cmd = New OleDbCommand(strIns, cnn)

Have fun.

---- Andy

 

[RiverGuy]

Here's another example:

Dim str As String = ""
str = "SomeText" & ControlChars.CrLf
str &= "Some More Text"  & ControlChars.CrLf
str &= "Even More Text"

That will yield the following

SomeText
Some MoreText
Even More Text

 

[Fred48]

Had to change some " and ' around but it works. Below is the code:


strIns = "INSERT INTO tblRevResults ([Update Resp Code], " _
& "[Check Field], [Save Ind], [TF Resp Code], TGSN , TGID, [Carrier Id], " _
& "[Orig Eqpt Type],[Term Eqpt Type],[Other TGID],[ColorCode],[TGSNSortOrder], " _
& "[Modified], [OrigSaveInd] ) Values " _
& "('" & m_FORDownLoadRequest.UpdateRespCode & "', '" & "0" & "', " _
& "'" & m_FORDownLoadRequest.SaveRevInd & "','" & m_FORDownLoadRequest.TFRespCode & "', " _
& "'" & m_FORDownLoadRequest.TGSN & "', '" & GetTGIDFromRecord() & "', " _
& "'" & m_FORDownLoadRequest.CarrierID & "', '" & m_FORDownLoadRequest.OrigEqpType & "', " _
& "'" & m_FORDownLoadRequest.TermEqpType & "', '" & m_FORDownLoadRequest.OtherTGID & "', " _
& "'" & strColor & "','" & intrecordcount & "', " _
& "'" & "N" & "','" & m_FORDownLoadRequest.SaveRevInd & "')"

Thanks for everyones help!!

 

[Andrzejek]

I've always liked [tt]vbNewLine[/tt]:

Dim str As String = ""
str = "SomeText" & [blue]vbNewLine[/blue]
str += "Some More Text" & [blue]vbNewLine[/blue]
str += "Even More Text"

Easier to remember (for me). Same outcome.

Have fun.

---- Andy

 

[RiverGuy]

Here is another tip for you, don't use string concatenation to create sql statements to send to the database. Use parameters. It's both easier to code (you won't have to worry about your quotes), and safer against SQL Injection.

Here's an example:

http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

 

[RiverGuy]

I've always liked vbNewLine:

I try to stay away from most of the functions and constants in the Microsoft.VisualBasic namespace. Not that it really matters, it's just that I like to stay consistent when using the .Net libraries if I were to ever switch languages.

 

[Andrzejek]

Fred48, if you stay with your approach, I would:
[tt]
[blue]With m_FORDownLoadRequest[/blue]
strIns = "INSERT INTO tblRevResults ([Update Resp Code], " _
& "[Check Field], [Save Ind], [TF Resp Code], TGSN , TGID, [Carrier Id], " _
& "[Orig Eqpt Type],[Term Eqpt Type],[Other TGID],[ColorCode],[TGSNSortOrder], " _
& "[Modified], [OrigSaveInd]) Values " _
& "('" & .UpdateRespCode & "', '" & "0" & "', " _
& "'" & .SaveRevInd & "','" & .TFRespCode & "', " _
& "'" & .TGSN & "', '" & GetTGIDFromRecord() & "', " _
& "'" & .CarrierID & "', '" & .OrigEqpType & "', " _
& "'" & .TermEqpType & "', '" & .OtherTGID & "', " _
& "'" & strColor & "','" & intrecordcount & "', " _
& "'" & "N" & "','" & .SaveRevInd & "')"
[blue]End With[/blue]
[/tt]

Have fun.

---- Andy

 

[Fred48]

Everyone,

Below is the approach I went with because of ease of documentation.

strIns = " INSERT INTO tblRevResults ([Update Resp Code], [Check Field], [Save Ind], [TF Resp Code], TGSN , TGID, [Carrier Id],[Orig Eqpt Type],[Term Eqpt Type],[Other TGID], ColorCode , TGSNSortOrder , Modified , OrigSaveInd ) Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

Using cmd As New OleDbCommand(strIns, cnn)

cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("Update Resp Code", m_FORDownLoadRequest.UpdateRespCode)
cmd.Parameters.AddWithValue("Check Field", "0")
cmd.Parameters.AddWithValue("Save Ind", m_FORDownLoadRequest.SaveRevInd)
cmd.Parameters.AddWithValue("TF Resp Code", m_FORDownLoadRequest.TFRespCode)
cmd.Parameters.AddWithValue("TGSN", m_FORDownLoadRequest.TGSN)
cmd.Parameters.AddWithValue("TGID", GetTGIDFromRecord())
cmd.Parameters.AddWithValue("Carrier Id", m_FORDownLoadRequest.CarrierID)
cmd.Parameters.AddWithValue("Orig Eqpt Type", m_FORDownLoadRequest.OrigEqpType)
cmd.Parameters.AddWithValue("Term Eqpt Type", m_FORDownLoadRequest.TermEqpType)
cmd.Parameters.AddWithValue("Other TGID", m_FORDownLoadRequest.OtherTGID)
cmd.Parameters.AddWithValue("ColorCode", strColor)
cmd.Parameters.AddWithValue("TGSNSortOrder", intrecordcount)
cmd.Parameters.AddWithValue("Modified", "N")
cmd.Parameters.AddWithValue("OrigSaveInd", m_FORDownLoadRequest.SaveRevInd)
cmd.ExecuteNonQuery()

End Using

Again thanks for the help.