Visual Basic Discussion Boards

riced16-Aug-09 2:40

16-Aug-09 2:40

Two possible options are:
1. check that the record count is greater than 0.
2. use Integer.TryParse() to do the conversion.

1 will tell you that there are no records for customer.
2 will tell you that it could not convert to integer - there may be a record but the field does not contain something that can be converted.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Re: code to find max orderno

Zizo_nis16-Aug-09 2:45

Zizo_nis

16-Aug-09 2:45

hi friend try this code:
Public Function GetMaxVal(ByVal TableN As String, ByVal FieldN As String)
If ConSql.State = ConnectionState.Closed Then ConSql.Open()
Dim CmdSqlGetMax As New SqlCommand("Select Max(" & FieldN &") _
As MaxVal From " & TableN, ConSql)
If Not DBNull.Value.Equals(CmdSqlGetMax.ExecuteScalar) Then
GetMaxVal = CmdSqlGetMax.ExecuteScalar
Else
GetMaxVal = 0
End If
End Function

I used the If statement to escape from the DBNull Hell, If cannot do you may use try .. Catch .. End Try to do

Good Luck

ezzat oraby

Re: code to find max orderno

AjitMourya16-Aug-09 19:49

AjitMourya

16-Aug-09 19:49

Hi,
Thanks but i want the code for Ms-access database

Re: code to find max orderno

Eddy Vluggen16-Aug-09 3:02

Eddy Vluggen

16-Aug-09 3:02

Third option, add an ISNULL function to your query;

SELECT ISNULL(MAX(orderno), 0) AS ono 
  FROM measurementpant

Re: code to find max orderno

Zizo_nis16-Aug-09 3:50

Zizo_nis

16-Aug-09 3:50

Sorry bro

SELECT ISNULL(MAX(orderno), 0) AS ono

FROM measurementpant

I suggest it may be

<pre>SELECT NOT ISNULL(MAX(orderno), 0) AS ono </pre>
<pre> FROM measurementpant</pre>
Tell me if wrong

ezzat oraby

Re: code to find max orderno

Eddy Vluggen16-Aug-09 5:05

Eddy Vluggen

16-Aug-09 5:05

I just ran them on SQL Server, with these results;

SELECT ISNULL(MAX(orderno), 0) AS ono 
  FROM measurementpant

-- Result for an empty table: '0'

SELECT ISNULL(MAX(orderno), 0) AS ono 
  FROM measurementpant

-- Result for a table with two tuples: '2'

SELECT NOT ISNULL(MAX(orderno), 0) AS ono 
  FROM measurementpant

-- Result: Incorrect syntax near the keyword 'NOT'.

Zizo_nis wrote:
Tell me if wrong

Your SQL did not compute.

"Some mainframe users still wonder if SQL Server is reliable enough for them. We're a nuclear power plant—how much more reliable do they need it to be?"
-- Janice Hoerber[^]

Re: code to find max orderno

AjitMourya16-Aug-09 19:58

AjitMourya

16-Aug-09 19:58

Hi,
Thanks, But it gives syntax error

Re: code to find max orderno

Eddy Vluggen16-Aug-09 21:10

Eddy Vluggen

16-Aug-09 21:10

AjitMourya wrote:
But it gives syntax error

My apologies, didn't try it on MSAccess. Here is the Access version;

SELECT IIf(IsNull(Max([orderno])), 0, Max([orderno])) AS ono
  FROM measurementpant;

Re: code to find max orderno

AjitMourya16-Aug-09 19:30

AjitMourya

16-Aug-09 19:30

Hi ,

Thanks for sending the code but it gives an error

[Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'isnull (max(orderno),0)'.

Re: code to find max orderno

Johan Hakkesteegt16-Aug-09 21:01

Johan Hakkesteegt

16-Aug-09 21:01

The problem is that if your query does not return any results, there will be no rows, and therefore no value can be assigned to your integer.
Using ISNULL, will not necessarily work either. It also can return zero rows.

So you can either count the rows after

AjitMourya wrote:
rss.Open("select max(orderno) As ono from measurementpant where custno='" & txtcustno.Text & "' AND length<>0 ")

and before

AjitMourya wrote:
no = Convert.ToInt32(rss.Fields("ono").Value)

or you can force a result in your query, and then take that into account when you assign the value to your integer:

rss.Open("select (1 + ISNULL(max(orderno),0)) As ono from measurementpant where custno='" & txtcustno.Text & "' AND length<>0 ")
no = (Convert.ToInt32(rss.Fields("ono").Value) - 1)

My advice is free, and you may get what you paid for.

VB Sql check and insert if then command

Brav0116-Aug-09 0:56

Brav01

16-Aug-09 0:56

HI
I am new to coding and I was in need of a little help.

I have a contacts form and there is a textbox "tbNewContact.Text" and two buttons. When one buttone is pressed I was wanting the following to happen. An sql connection has been opened at form_load.

When the user enters a contacts name it does a check on the database table. If it returns any rows then it displays a message.
If not rows returned then it will submitted the contact to the SQL database.

But it does not work. I get the message "Contacted Added" but nothing has been.

If the code is cra then sorry like i said Im new to this. Laugh | :laugh:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddContact.Click
    'Check Contacts
    Dim SQLSearch As SqlCommand = sqlconn.CreateCommand
    Dim sqlinsert As SqlCommand = sqlconn.CreateCommand
    SQLSearch.CommandText = "SELECT * FROM Contacts WHERE ContactName = '%" & tbNewContact.Text & "%';"
    Dim SQLDR As SqlDataReader = SQLSearch.ExecuteReader

    If SQLDR.HasRows Then
        MsgBox("That contact name already exists." & tbNewContact.Text & _
                "If you wish to add this contact, please cancel and choose 'Add Existing Contact'", MsgBoxStyle.Information)
        SQLDR.Close()
    Else
        sqlinsert.CommandText = "insert into contacts (contactName) values '%" & tbNewContact.Text & "%';"
        MsgBox("Contact Added")

        Exit Sub
    End If
    SQLDR.Close()

End Sub

Thansk In advance

Re: VB Sql check and insert if then command

Moreno Airoldi16-Aug-09 1:12

Moreno Airoldi

16-Aug-09 1:12

The error is here:

sqlinsert.CommandText = "insert into contacts (contactName) values '%" & tbNewContact.Text & "%';"

You should change it to:

sqlinsert.CommandText = "insert into contacts (contactName) values ('" & tbNewContact.Text & "');"

Also note that opening the connection at form startup and keeping it open is not a good idea. It may time out or suffer from other connection problems.
You should open a connection each time you need to access the db, and then close it.

Do not worry about opening too many connections, since you will not: pooling and re-using of existing connections is done at driver level.

2+2=5 for very large amounts of 2
(always loved that one hehe!)

Re: VB Sql check and insert if then command

Brav0116-Aug-09 2:02

Brav01

16-Aug-09 2:02

Hi
Thanks for your responce but that does not seem to work either? OMG | :OMG:

Re: VB Sql check and insert if then command

Paramu197316-Aug-09 20:20

Paramu1973

16-Aug-09 20:20

As a try, you may check
dim My_Value as string=trim(tbNewContact.text.tostring)

And

sqlinsert.CommandText = "insert into contacts (contactName) values '%" & My_Value & "%'

Unchecking a checked box... Answered,thanks.

Laser Steve15-Aug-09 6:26

Laser Steve

15-Aug-09 6:26

After the user has checked a box and the program has done it's thing, I want to uncheck that box.

I thought this would work, but nothing happens.

CheckBox1.CheckState.Equals(False)

Visual Studio 8
Version 9

Thanks.

modified on Saturday, August 15, 2009 1:07 PM

Re: Unchecking a checked box

Dave Kreskowiak15-Aug-09 6:45

Dave Kreskowiak

15-Aug-09 6:45

Seriously, pickup a beginners book on VB.NET and work through it. The Equals method checks to see if the object that you called Equals on is equivilent to the object in the parenthesis. Essentially, you asked in the value of CheckState is equal to False. It returns a True/Value value (which you completely ignored.) It does NOT set the value of CheckState to False. To do that, you write this:

CheckBox1.CheckState = False

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Re: Unchecking a checked box... Answered,thanks.

Laser Steve15-Aug-09 7:15

Laser Steve

15-Aug-09 7:15

Thanks for the quick reply.

I have read, and worked though a few books.
I still don't comprehend some things.
I thought this was an easy task, but I kept going down the wrong path.

I have Step by step 2003, by JulianTempleman
Programers Refrence by Dan Rahmel
Step by step 2008 by Michael Halvorson

Thanks again.

ComboLost focus problem.?

mdrizwan_115-Aug-09 2:54

mdrizwan_1

15-Aug-09 2:54

Hi
I have set combo's Setfocus property on an button click.But when i click another exit button ,I have to click 2 times to exit the application .i wanna exit on just single So kindly help me anyone please????????
[Look the code is in VB6]

Thanks in advance

Re: ComboLost focus problem.?

riced15-Aug-09 3:04

riced

15-Aug-09 3:04

mdrizwan_1 wrote:
So kindly help me anyone please

Not without seeing some code. It could be anything and I'm no mid reader, nor is anyone else to the best of my knowledge. Smile | :)

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Outlook Contact list Read Only Problem

dcdhingra14-Aug-09 23:15

dcdhingra

14-Aug-09 23:15

Hi,

I am developing outlook add-in application in which i need to add new contacts from XML file into outlook and all updated contact should be Read Only. I added Contacts from XML successfully But not able to make Read only.

So my problem is that how to make Contact Read only so that no body can change Outlook Contacts.

Urgently

Thanks in advance.

Regards
Dharmchand Dhingra Smile | :)

Google Mapping...

mdrizwan_114-Aug-09 20:09

mdrizwan_1

14-Aug-09 20:09

Hello Everybody
As m new to Classic Asp kindly help me?
I m working on VB6 Environment.Here i have an Google mapping application been developed using Classic ASP and Java Script.The problems is that when i run this application it takes time in load the entire info on the page,So i need your help ,How can i increase its performs by reduce the delay,(If possible can i modify it using Ajax or something Else? Suggestion....)

Thanks In Advance

Rizwan

Re: Google Mapping...

Dave Kreskowiak15-Aug-09 6:42

Dave Kreskowiak

15-Aug-09 6:42

That depends on where the delay is. If it's with the Google server that your waiting for, there's nothing you can do to speed this up.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

How to Generate data on Crystal Report when Datagridview item is selected

Dambod14-Aug-09 7:50

Dambod

14-Aug-09 7:50

hi there,
i am using crystal report 10 to generate using VB 2008. I have a problem in generating data for crystal report when datagridview item is selected. my datagridview gets data from a dataset called ds. any help?

Re: How to Generate data on Crystal Report when Datagridview item is selected

Dave Kreskowiak14-Aug-09 8:10

Dave Kreskowiak

14-Aug-09 8:10

You haven't provided enough information to tell you anything useful. What's the problem you're having? What are the error messages, if any?? What does the code look like where the error was generated?? What does selecting an item in the DGV have to do with generating the report?? ...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Re: How to Generate data on Crystal Report when Datagridview item is selected

Dambod14-Aug-09 8:25

Dambod

14-Aug-09 8:25

For i As Integer = 0 To DataGridView1.Rows.GetRowCount(DataGridViewElementStates.None) - 1
If DataGridView1(2, i).Value Is DBNull.Value Then
MsgBox("you didn't Select Student ID", MsgBoxStyle.Exclamation, "Student ID Missing")
Else
'displaying selected student data on crystal report
If DataGridView1(3, i).Value.ToString = "A" Then
rpt.setdatasource(ds) 'ds is my dataset where student table is found
dim myparams as new new ParameterFields()
dim myparam as ParameterField = new ParameterField()
dim myDiscretevalue as new ParameterDiscreteValue()

'// Set the ParameterFieldName to the name of the parameter
'// created in the Field Explorer
myParam.ParameterFieldName = "StudFullName

'// Add first student
myDiscreteValue.Value = "Peter"
myParam.CurrentValues.Add(myDiscreteValue)

'// Reuse myDiscreteValue, and assign second student
myDiscreteValue = new ParameterDiscreteValue()
myDiscreteValue.Value = "Smith"
myParam.CurrentValues.Add(myDiscreteValue);

'// Add param object to params collection
myParams.Add(myParam)

'// Assign the params collection to the report viewer
myCrystalReportViewer.ParameterFieldInfo = myParams

'// Assign the Report to the report viewer.
'// This method uses a strongly typed report,
'// but other methods are possible as well.
myCrystalReportViewer.ReportSource = MyReportObject
end if
and the problem is it says no valid table. can u help me in correcting this code so that when datagridview row is selected, data for the selected student is displayed on the report

thanks

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.