Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql data reader functinality

Posted on 2004-04-05
20
Medium Priority
?
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 
LVL 28

Accepted Solution

by:
iboutchkine earned 100 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 100 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

660 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