Home....
Developer Network >> .NET Programming >> ADO.NET

Desperate for help, not filling dataset

Im really stuck and confused. The first part of my code takes a csv file and puts it into a dataset and then populates a list box from the dataset. The second part tries to do a select against the database for each item in the listbox but fails to populate the dataset with no exceptions. obviously the third part fails to write the html because their is nothing in the dataset. Any help would be gretatly appreciated, I apologize for adding all the code but I think it helps you get the idea of what I'am doing.

Thanks in advance,
Joel

Code: <part1>
Private Sub browseBut_click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles browseBut.Click

Dim filepath As String
Dim filename As String
Dim connstring As String

OpenFileDialog1.Filter() = "csv files (*.csv)|*.csv"
If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
filePath = OpenFileDialog1.FileName
filename = Path.GetFileName(filepath)

If OpenFileDialog1.FileNames.Length > 0 Then
TextBox1.Text = filepath
connstring = "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=" + Path.GetDirectoryName(filepath) + "; Extensions=csv,txt "
Dim conn As New Odbc.OdbcConnection(connstring)

Dim da As New Odbc.OdbcDataAdapter("SELECT * from [" + filename + "]", conn)
Dim ds As New DataSet

Try
da.Fill(ds)
MsgBox(ds.Tables(0).Rows.Count & " KBs found")
For Each r As DataRow In ds.Tables(0).Rows
ListBox1.Items.Add(r(0))

Next

Catch ex As Odbc.OdbcException
MessageBox.Show(ex.ToString, MessageBoxButtons.OK)

Catch any As Exception
MessageBox.Show(any.ToString, MessageBoxButtons.OK)
</part1>
<part2>
End Try
Try
Dim test As String
Do Until test = 0

test = ListBox1.Items.Count

Dim sqlda As New SqlClient.SqlDataAdapter
sqlda = New SqlClient.SqlDataAdapter("select * from ABCARD where card_id = [" + test + "]", SqlConnection2)
test -= 1
sqlda.Fill(DataSet11)
Loop

Catch ex As Exception
MessageBox.Show(ex.ToString)
MessageBox.Show(DataSet11.Tables(0).Rows.Count)
End Try

For Each row As DataRow In DataSet11.Tables(0).Rows
ListBox2.Items.Add(row(0))
Next

End If
End If
End Sub
</part2>
<part3>
Dim savepath As String
Private Sub browseSaveBut_click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles browseSaveBut.Click
If FolderBrowserDialog1.ShowDialog() Then
savepath = FolderBrowserDialog1.SelectedPath()
saveText.Text = savepath
End If
End Sub

Public Sub writeKB()
For Each r As DataRow In DataSet11.Tables(0).Rows
savepath = FolderBrowserDialog1.SelectedPath & "\" & r("card_id").ToString() & ".html"
Dim fs As New FileStream(savepath, FileMode.Create, FileAccess.Write)
Dim s As New StreamWriter(fs)

s.WriteLine("<body style=""margin: 10px"">")
s.WriteLine("<h4> KB Number: ")
s.WriteLine("<!--BEGIN CARD_ID-->")
s.WriteLine(r("card_id").ToString())
s.WriteLine("<!--END CARD_ID-->")
s.WriteLine("</h4>")

s.WriteLine("<h4>")
s.WriteLine("<!--BEGIN SUMMARY-->")
s.WriteLine(r("summary").ToString())
s.WriteLine("<!--END SUMMARY-->")
s.WriteLine("</h4>")

s.WriteLine("<table cellpadding=""5"" cellspacing=""0""><tr><td style=""border: 1px solid black"">")
s.WriteLine("<!--BEGIN MEMO-->")
s.WriteLine(r("memo").ToString())
s.WriteLine("<!--END MEMO-->")
s.WriteLine("</td></tr></table>")
s.WriteLine("</body>")
s.Close()
Next
MsgBox(DataSet11.Tables(0).Rows.Count.ToString() & " files created!")
End Sub
</part3>
[4981 byte] By [jmckown] at [2007-11-19 13:12:17]
«« bypassing api
»» Parse Line
# 1 Re: Desperate for help, not filling dataset
So their is an error that states

system.data.sqlclient.sqlexception: invalid column name'system.windows.forms.textbox, textbox, text: 1'.

their is a row with value 1 for the column card_id

Joel
jmckown at 2007-11-10 3:30:42 >
# 2 Re: Desperate for help, not filling dataset
I got questions???

test = ListBox1.Items.Count

test is declared as string does ListBox1.Items.Count return a string or integer....... does vb care if you assign an integer to a string.??

another question?????

sqlda = New SqlClient.SqlDataAdapter("select * from ABCARD where card_id = [" + test + "]", SqlConnection2)

What is the data type of card_id? is it an integer? if so I think you dont have to place it in between " "..... in your sql statement
riscoh at 2007-11-10 3:31:43 >
# 3 Re: Desperate for help, not filling dataset
So I corrected it to dim test as integer and the data type for card_id is also int. Now I get an invalid cast exception. When I get rid of the quotes around test i get an error invalid column name +test+. The exception states:

system.invalidcastexception: Cast from string "select * from ABCARD where card_" to type 'double' is not valid.-->system.formatexception::Input string was not in a correct format.

Everything works fine if I create a sql adapter in vb and use a query like " select * from ABCARD where card_id = 3", fills the dataset no problem. But I need to loop through and grab various rows. Any Ideas?

Here's the code in question again:

Dim test As Integer
For Each r As DataRow In ds.Tables(0).Rows
ListBox2.Items.Add(r)
Next
'test = ds.Tables(0).Rows.Count
test = ListBox2.Items.Count
Try
'Do Until test = 0
Dim sqlda As New SqlClient.SqlDataAdapter
sqlda = New SqlClient.SqlDataAdapter("select * from ABCARD where card_id = [" + test + "]", SqlConnection2)

sqlda.Fill(DataSet11)
'test -= 1
'Loop
jmckown at 2007-11-10 3:32:51 >
# 4 Re: Desperate for help, not filling dataset
sqlda = New SqlClient.SqlDataAdapter("select * from ABCARD where card_id = [" + test + "]", SqlConnection2)

I think this has something to do with the string concatenation....

try this.....

Dim sqlQuery As String
sqlQuery = "SELECT * FROM ABCARD WHERE card_id =" & test

sqlda = New SqlClient.SqlDataAdapter(sqlQuery , SqlConnection2)
riscoh at 2007-11-10 3:33:52 >
# 5 Re: Desperate for help, not filling dataset
No errors, but still does not populate the dataset

MessageBox.Show(DataSet11.Tables(0).Rows.Count)

shows 0
tried it with test.tostring, no change

The problem is either with the dataset or dataadapter because if I do a normal query where card_id =2 (in the code) it still does not work, I know the connection is open. When I use the design view to generate an adapter and generate a sql query it succeeds doing the card_id =2 query and fills the dataset (go figure). But putting it in code and it does not work??? I would use the design view adapter but it won't except & test as part of the query.

Thanks alot for your help so far, I'll post if I figure it out.

Joel
jmckown at 2007-11-10 3:34:51 >
# 6 Re: Desperate for help, not filling dataset
Ok I figured it out, I can't believe I didn't try this before

sqlda.Fill(DataSet11.Tables(0))

instead

sqlda.fill (dataset11)

it fills the dataset and I can loop through my other dataset and fill each row that I query.

Thanks for the help :)

Joel
jmckown at 2007-11-10 3:35:48 >
.NET Programming
ADO.NET

.NET Programming

All Classified

.NET Programming New Topic

.NET Programming Hot Topic