Get Data

[shapper]

Hello,

I have an SQL database table with 20 fields: Field_01, Field_02, etc.
I am creating a string with StringBuilder.
I want to include in my string the fields of one record of the
database.
The SQL procedure "GetOneRecord" gets one record when its ID is givem.

So my problem is how to access each record field and use it in my
string.
For example:

MyStringBuilder.Append("<title>")
MyStringBuilder.Append(Field_01) <<<< Field_01 of selected
record.
MyStringBuilder.Append("</title>")

Here is my code:

' Define connection
Dim connection As New
SqlClient.SqlConnection(connectionString.ToString)

' Define command
Dim command As New SqlClient.SqlCommand
With command
.CommandText = "GetOneRecord"
.Connection = connection
.CommandType = CommandType.StoredProcedure
End With

' Add command parameters
With command.Parameters
.Add(New SqlClient.SqlParameter("@Id", Id))
End With

connection.Open()
values in my String Builder.

connection.Close()

How can I do this?

Thanks,
Miguel

 

[Mark Rae]

I have an SQL database table with 20 fields: Field_01, Field_02, etc.

You can't be serious...! You call your fields Field_01, Field_02 etc...???

Surely not!!!

 

[shapper]

Of course not ... that was just an example.

Thatwas not even the point of my question!

Cheers,
Miguel

 

[Guest]

Howdy shapper,

You're asking about basics

1. use data reader

dim reader as SqlDataReader
Dim field01Value As String
Dim field02Value As Integer

Try
connection.Open()
reader = command.ExecuteReader()

If reader.Read() Then

field01Value = CType(reader("field01"), String)
field02Value = CType(reader("field02"), Integer)
' etc.

End If

Catch ex As Exception
Throw
Finally
If Not reader Is Nothing Then
reader.Close()
End If
connection.Close()
End Try
reader.Close()

2. Use datatable

connection.Open()
adapter.Fill(table)
Catch ex As Exception
Throw
Finally
connection.Close()
End Try

If table.Rows.Count > 0 Then
Dim row As DataRow = table.Rows(0)
myStringBuilder.Append(row("Field01"))
' etc
End If

3. i see you're trying to build xml using values from MSSQL database - you
may use FOR XML statement i.e.:

select Field01 as Title, Field02 as FirstName from TableName WHERE RecordId
= 2
for xml auto, ELEMENTS

hope this helps

 

[sloan]

Shapper,

MyStringBuilder.Append("<title>")
MyStringBuilder.Append(Field_01) <<<< Field_01 of selected
record.
MyStringBuilder.Append("</title>")



Not to be rude, but string manipulation is the absolute WORST way to build
up xml or html.
This is very "asp-ish" and very hacky.


One way, among several different legimate methods for creating xml is
http://msdn2.microsoft.com/en-us/library/system.xml.xmlwriter_methods.aspx
XmlWriter.

Or, as someone says, you can get Xml out of the database (Sql Server and
Oracle) by using their languages.

...

If you're doing HTML, then you'll use
DataGrid, Repeater, DataList in 1.1
GridView, Repeater in 2.0.


response.write "<table><tr><td>" is NOT the way to go.