Solved

sql data reader functinality

Posted on 2004-04-05
20
318 Views
Last Modified: 2008-02-26
Hi
I have

Dim sqlConn2 As New SqlConnection(connstr)
sqlConn2.Open()
Public rs1880 As SqlDataReader
Dim rs1880_sqlcomm As New SqlCommand("SELECT SERIALP, NAMEFRST, NAMELAST, AGE, SEX," & RACED from samp1920 where " & _
"(DIFFERENCE(NAMELAST,'" & LName1920 & "') > 2) " & _
 "AND (AGE Between " & AgeLower & " and " & AgeUpper & ") AND" & _
  "(SEX = " & Gender1920 & ") AND (RACED = " & RaceD1920 & ")", sqlConn2)
 
 
I get error as
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error

on
rs1880 = rs1880_sqlcomm.ExecuteReader()

Also, I want to use Delete and Insert statments also. What can I use sqldatareader/sqladapter ? Am confused. Example code will be helpful.

Thanks
Maha



0
Comment
Question by:mahalakshmi_s
  • 7
  • 6
  • 5
  • +1
20 Comments
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
Most possible you have the SQL error
For example
(SEX = " & Gender1920 & ")

SHould it be
(SEX = ' " & Gender1920 & " ')

I assume that SEX is a string and you need quotes . CHeck the rest of your SQL
0
 

Author Comment

by:mahalakshmi_s
Comment Utility
sex is just single datatype ...either 1 or 2
0
 

Author Comment

by:mahalakshmi_s
Comment Utility
i checked with displaying the sql stt in msg box... it has no syntax error.
0
 
LVL 28

Accepted Solution

by:
iboutchkine earned 25 total points
Comment Utility
For Delete and Insert use ExecuteNonQuery.

For executereader try this example. It works

Dim dr As OleDbDataReader = cmd.ExecuteReader()


'example

      Dim sFields As String
        Dim shIndex As Short
        Dim cmd As New OleDbCommand()
        Dim OleDbConn As New OleDbConnection(sConn)
        Dim reader As OleDbDataReader

        cmd.CommandText = sSQL
        cmd.CommandTimeout = 0
        OleDbConn.Open()
        cmd.Connection = OleDbConn
        reader = cmd.ExecuteReader

        While reader.Read()
            For shIndex = 0 To reader.FieldCount - 1
                sFields = sFields & reader(shIndex)
            Next
            sFields = sFields & vbCrLf
        End While

        reader.Close()
        reader = Nothing

        MsgBox(sFields, MsgBoxStyle.Information, "Read As400 Table - ADO.NET")
        OleDbConn.Close()
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
this

("SELECT SERIALP, NAMEFRST, NAMELAST, AGE, SEX," & RACED from samp1920 where " & _
"(DIFFERENCE(NAMELAST,'" & LName1920 & "') > 2) " & _
 "AND (AGE Between " & AgeLower & " and " & AgeUpper & ") AND" & _
  "(SEX = " & Gender1920 & ") AND (RACED = " & RaceD1920 & ")"

looks suspect.

Should it be:

("SELECT SERIALP, NAMEFRST, NAMELAST, AGE, SEX,RACED from samp1920 where" & _
"(DIFFERENCE(NAMELAST,'" & LName1920 & "') > 2) " & _
 "AND (AGE Between " & AgeLower & " and " & AgeUpper & ") AND" & _
  "(SEX = " & Gender1920 & ") AND (RACED = " & RaceD1920 & ")", sqlConn2

you seem to have a misplaced " & reight before RACED in the first line.

AW
0
 

Author Comment

by:mahalakshmi_s
Comment Utility
arthur_wood,
I tried to cut short the SQL into simplest one as

dim sql1880 as string = ("SELECT SERIALP, NAMEFRST, NAMELAST, AGE, SEX,RACED from samp1920 where (DIFFERENCE(NAMELAST,'" & LName1920 & "') > 2) " &_
  "(SEX = " & Gender1920 & ")"
Dim rs1880_sqlcomm As New SqlCommand(sql1880, sqlConn2)
rs1880 = rs1880_sqlcomm.ExecuteReader()

where rs1880 is declared as public
public rs1880 as sqldatareader

Still I get error. I tired the same query in sql server EM n QA ...I get answers there.
Please help,
M

0
 
LVL 12

Expert Comment

by:farsight
Comment Utility
Perhaps you have a keyword in the SQL, though I don't recognize one.
Put all the names in brackets;
  SELECT [SERIALP], [NAMEFRST], [NAMELAST], [AGE], [SEX]
Continue for all the names in the query.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
this:

(NAMELAST,'" & LName1920 & "') > 2) " &_
  "(SEX = " & Gender1920 & ")"


should be:

(NAMELAST,'" & LName1920 & "') > 2) AND " &_
  "(SEX = " & Gender1920 & ")"
0
 

Author Comment

by:mahalakshmi_s
Comment Utility
am sorry guys...still same error. SQL is just perfect. Same SQL gives answers in EM n QA of SQL Server.
0
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
did you add all the Imports on the top of the form?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:farsight
Comment Utility
Try this:

Private sql1800 As String
sql1880 = "SELECT ... ... ... "     ' <---- Insert whatever sql you're actually using now.
Debug.WriteLine("[[" & sql1880 & "]]")
Dim rs1880_sqlcomm As New SqlCommand(sql, sqlConn2)

Copy the sql that the WriteLine puts in the Output window, and paste it directly here, where we can read it.  Also post the "sql = ..." line that you are now using.  Use only copy/paste -- no typing.  That should let us get a better look at the problem.


 
 
0
 

Author Comment

by:mahalakshmi_s
Comment Utility
iboutchkine : These r my Imports. I havent used oledb yet !

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.DateTime

 farsight: this is my sql in debug.line ....as u said its just cut n paste !
      "SELECT SERIALP, FRSTNAME, LASTNAME, AGE, SEX, RACED from short1920 where (DIFFERENCE(LASTname,'AARON') > 2) AND (SEX = 1)"      

I changed no. of criterias now.Its just 2 now.When i posted question I had many. The COlumn names r correct for the table short1920 in sql server2000. The same yeilds answers.

Am migrating my program from vb6.0 to vb.net. also new bee for .net ....may be i lose some important backgrd work or i dont know.

Thanks a lot guys
Maha
0
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
SQL and Imports look good to me (and you don't need oledb)

try this

sSQL =  "SELECT SERIALP, FRSTNAME, LASTNAME, AGE, SEX, RACED from short1920 where (DIFFERENCE(LASTname,'AARON') > 2) AND (SEX = 1)"    

       rs1880_sqlcomm .CommandText = sSQL
       rs1880_sqlcomm .CommandTimeout = 0
       sqlConn2.Open()
        rs1880_sqlcomm .Connection = sqlConn2
        rs1880r = rs1880_sqlcomm .ExecuteReader

0
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
Wait a second.
What is this
DIFFERENCE(LASTname,'AARON') > 2

Can you calculate the difference between strings values????
0
 
LVL 12

Assisted Solution

by:farsight
farsight earned 25 total points
Comment Utility
> I havent used oledb yet !
You don't need it.  You're using System.Data.SqlClient instead


Do you have any database code that works?  I'm trying to determine if the problem is with THIS specific SQL, or if perhaps the problem is more general than that.

For example, can you do a simple "SELECT * FROM short1920"?

Dim sqlConn2 As New SqlConnection(connstr)
sqlConn2.Open()
Private sql1880 As String = "SELECT * FROM short1920"
Dim rs1880_sqlcomm As New SqlCommand(sql1880, sqlConn2)
Public rs1880 As SqlDataReader  = rs1880_sqlcomm.ExecuteReader()
'then close up everything


0
 
LVL 12

Expert Comment

by:farsight
Comment Utility
iboutchkine:
> Can you calculate the difference between strings values????
I had to look it up.  SQL Enterprise Manager help.
"Returns the difference between the SOUNDEX values of two character expressions as an integer."
4 means that they soundex the same, 0 means very, very different.
0
 

Author Comment

by:mahalakshmi_s
Comment Utility
iboutchkine
we can use DIFFERENCE fn. I used in another prj. It didnot give me error.
also am thinking of using into oledb, bcoz i have lots of insert n delete. let me know what shd I Import for it ?
Maha
0
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
>>"Returns the difference between the SOUNDEX values of two character expressions as an integer."

I believe you cannot do it with strings

For oledb you need

Imports system.data.oledb
0
 
LVL 12

Expert Comment

by:farsight
Comment Utility
Generally speaking, use:
  Imports System.Data.SqlClient       ' if you're working with SQL Server databases

OR, use
  Imports System.Data.OracleClient       ' if you're working with Oracle databases
(also must add a Reference to System.Data.OracleClient.dll)

OR, use:
  Imports System.Data.OleDb        ' if you're working with other databases
OR
  Imports System.Data.Odbc       ' if you're working with other databases

You don't need more that one unless you're getting or moving data among multiple databases.

> >>"Returns the difference between the SOUNDEX values of two character expressions as an integer."
Really, it works fine.  Check the manual.
http://download.sybase.com/pdfdocs/srg1100e/sqlref.pdf  (BIG)
Page 586 in the pdf (4-34).
0
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
Ok, I looked at it. I did not know that it was soundex. I thought that this is a string. My mistake
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now