Solved

sql data reader functinality

Posted on 2004-04-05
20
320 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
ID: 10760410
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
ID: 10760552
sex is just single datatype ...either 1 or 2
0
 

Author Comment

by:mahalakshmi_s
ID: 10760590
i checked with displaying the sql stt in msg box... it has no syntax error.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 28

Accepted Solution

by:
iboutchkine earned 25 total points
ID: 10760917
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
ID: 10761416
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
ID: 10768141
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
ID: 10768315
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
ID: 10768571
this:

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


should be:

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

Author Comment

by:mahalakshmi_s
ID: 10769077
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
ID: 10769105
did you add all the Imports on the top of the form?
0
 
LVL 12

Expert Comment

by:farsight
ID: 10771597
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
ID: 10774665
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
ID: 10775373
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
ID: 10775385
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
ID: 10775510
> 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
ID: 10775933
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
ID: 10776375
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
ID: 10776760
>>"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
ID: 10777219
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
ID: 10777424
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 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