How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Steve Your Own Question
Steve
Steve, Consultant
Category: Programming
Satisfied Customers: 289
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Programming Question Here...
Steve is online now
A new question is answered every 9 seconds

I need to write something in VB that will connect to a SQL

Customer Question

Hello, I need to write something in VB that will connect to a SQL server, run a query that could have more than one result, then run a predefined command for the result(s)
JA: What programs and versions are you using?
Customer: visual studio
JA: What is your ideal outcome? How can we solve this for you?
Customer: my ideal outcome is to provide the code I need so I can insert it into my solution
JA: Anything else you want the programmer to know before I connect you?
Customer: not at this time, other than I am not an experienced developer
Submitted: 9 months ago.
Category: Programming
Expert:  LogicPro replied 9 months ago.

Hi

welcome

please elaborate.

Customer: replied 9 months ago.
I already have a sqlconn but I have to rewrite for the purpose of performing a query, returning one or more results, then passing these results to a command that I want to run for each of the results
Customer: replied 9 months ago.
here is the sqlconn:
'SQLConn.ConnectionString = "Data Source=" & HostName & ";" & _
'"Initial Catalog=mvf;" & _
'"User ID=" & Session("db_name") & ";" & _
'"Password=" & Session("db_pass") & ";"
'SQLConn.Open()'For i As Integer = 0 To intLC - 1
'strCommand = "select convert (varchar, count (study_uid)) from mvf.dbo.dosr_study ms where study_uid = '" & arrResults(11, i) & "'"
strCommand = "Select series_uid from dosr_series where study_ref In (Select study_ref from dosr_study where study_uid = '" & arrResults(11, i) & "') and MODALITY !='SR'"
'Dim SQLCommand As New SqlClient.SqlCommand(strCommand, SQLConn)
'Dim reader As SqlDataReader = SQLCommand.ExecuteReader()
'While reader.Read()
'Response.Write(reader(0) & "<br />")
'If (Not reader(0) = 0) Then
'arrResults(12, i) = "1"
'End If
'Response.Write(i & " : " & arrResults(11, i) & " : " & arrResults(12, i) & "<br />")
'End While
'reader.Close()
'Next
'SQLConn.Close()
'End Sub
Customer: replied 9 months ago.
I want to use the following query:
"Select series_uid from dosr_series where study_ref In (Select study_ref from dosr_study where study_uid = '" & arrResults(11, i) & "') and MODALITY !='SR'"
Expert:  LogicPro replied 9 months ago.

Zip your project and provide

Format is not easier to read

Customer: replied 9 months ago.
ok
Expert:  LogicPro replied 9 months ago.

Ok

Customer: replied 9 months ago.
default.aspx.vb
Customer: replied 9 months ago.
I want to rewrite the sqlconn to my needs, and use this in line 291
Customer: replied 9 months ago.
the result of the query would be inserted here:
-k 0020,000E=<insert result here>
Customer: replied 9 months ago.
but there could be one or more results from the query, so it could have to be parsed 'x' number of times, 'x' being the result of the sql query
Expert:  LogicPro replied 9 months ago.

I will check and reply in few hours

Customer: replied 9 months ago.
right now the sqlconn is commented out because I do not want to use it for the purpose it was programmed and want to change it for my needs, I hope that makes sense
Expert:  LogicPro replied 9 months ago.

Will review

Customer: replied 9 months ago.
ok thanks!
Expert:  LogicPro replied 9 months ago.

welcome

Customer: replied 9 months ago.
hi there, any progress?
Expert:  LogicPro replied 9 months ago.

I tried it a lot but lost.

My apologies, I am out of ideas and opting out for other Professionals to answer. Please wait. You will be notified by email as soon as any Professional joins you.

I would be happy to help you with your other questions though.

Thank you for your patience.

Customer: replied 9 months ago.
ok thx
Expert:  Steve replied 9 months ago.

Hi there, my name is***** haven't tested this code, but give this a try:

Public Function SQLConnect() As String
Dim db As New SqlConnection
Dim reader As SqlDataReader
Dim results() As Variant
Dim count As Integer

Try
db.ConnectionString = "Data Source=" & Hostname & __
";Initial Catalog=mvf" &__
";Persist Security Info=True" &__
";User ID=" & Session("db_name") & __
";Password=" & Session("db_pass")
Dim query As New SqlCommand("Select series_uid from dosr_series where study_ref In (Select study_ref from dosr_study where study_uid = '" & arrResults(11, i) & "') and MODALITY !='SR", db)
db.Open()

reader = query.ExecuteReader()

count = 0
While reader.Read()
results(count) = reader("series_uid")
count = count + 1
End While

Catch ex As Exception
MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
Finally
db.Close()
End Try

Return "done"
End Function