index > .NET Framework Data Access and Storage > DataSet, SqlCommand...

DataSet, SqlCommand...

Hi,

I have function that gets a datatable

Public Function PageGetByID(ByVal id As String) As DataTable

Try

Dim par() As SqlParameter = New SqlParameter(1) {}

par(0) = New SqlParameter("?id", id)

Dim ds As DataSet = dl.getDataset("select * from News where id = ?id", par)

If ds.Tables(0).Rows.Count > 0 Then

Return ds.Tables(0)

End If

Return Nothing

Catch

Return Nothing

End Try

End Function

Later I use it to get string of html from my db and show it through Literal

Dim dt As DataTable = PageGetByID("1")

Literal1.Text = dt.Rows(0)("Page").ToString()

But I have an exeption in getDataset emthod of DataLayer object. Here is this method:

Public Function getDataset(ByVal sql As String, ByVal ParamArr() As SqlParameter) As DataSet

Dim conn As SqlConnection

Dim da As SqlDataAdapter = Nothing

Dim cmd As SqlCommand = Nothing

conn = New SqlConnection(ConStr)

Try

conn.Open()

Dim data As DataSet = New DataSet()

cmd = New SqlCommand(sql, conn)

Dim i As Integer

For i = 0 To (ParamArr.Length - 1) Step i + 1

cmd.Parameters.Add(ParamArr(i))

da = New SqlDataAdapter(cmd)

da.Fill(data) ', "data");

Return data

Catch mex As SqlException

Throw mex

Catch ex As Exception

Throw ex

Finally

If conn.State = ConnectionState.Open Then

conn.Close()

conn.Dispose()

cmd.Dispose()

da.Dispose()

End If

End Try

End Function

In yellow block try catches an null-exeption. What is a problem?

juayen

Hi,

Try using SqlParameterColleciton instead of an array in storing SqlParamters.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.aspx

cheers,

Paul June A. Domag

Visit my Online Blog - http://webmag.mvps.org

Paul Domag

To debug this, add a breakpoint on your for loop. At this point, check the values of your array, and its length. Not sure about a foreach loop in vb.net, but in c#, try

foreach(SqlParameter sqlParam in Arrayname)

cmd.Parameters.add(sqlparam);

You may find that you never go into this loop, because the length of your array is 0.

ryan

ryan031581

But what is a way out?

juayen

Do you mean what is equal to EndIf? In C# the first line of code under a loop, or if statement is the only one that executes if conditions are met. If there are multiple lines the sytax is

foreach(whatever)

{

}

ryan

ryan031581

reply 5