Yet another VB Problem

Magnolia_Fan

This time I kind of know what I'm doing: VB.Net

If txtWspeed.Text = vbNullString Then
MessageBox.Show("There is no input for the Wind Speed." & vbCrLf & "Please amend this before saving details")
GoTo EndOf

ElseIf txtLocation.Text = vbNullString Then
MessageBox.Show("There is no input for the Location." & vbCrLf & "Please amend this before saving details")
GoTo EndOf

ElseIf txtFish.Text = vbNullString Then
MessageBox.Show("Please Don't alter the fish number, it is an automatic setting" & vbCrLf & "Please fix this before saving details")
GoTo EndOf

ElseIf txtWeight.Text = vbNullString Then
MessageBox.Show("There was no weight entered into the program." & vbCrLf & "Please amend this before saving details")
GoTo EndOf

ElseIf cbxDirect.Text = vbNullString Then
MessageBox.Show("Please remember to select a Wind Direction" & vbCrLf & "Please fix this before saving details")
GoTo EndOf

ElseIf cbxMethod.Text = vbNullString Then
MessageBox.Show("You Have forgotten to enter a Method" & vbCrLf & "Please fix this before saving details")
GoTo EndOf

ElseIf txtDate.Text = vbNullString Then
MessageBox.Show("Please make sure you've enetred a date" & vbCrLf & "Please fix this before saving details")
GoTo EndOf

ElseIf cbxSpecies.Text = vbNullString Then
MessageBox.Show("Please choose a Species from the species box" & vbCrLf & "Please choose one befor saving the record")
GoTo EndOf

If txtDate.Text >= today Then
MessageBox.Show("Error to insure accuracy we must advice you not to enter false information")
End If
End If
Try
OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO tblAdd(Date,Species,Time Of Day,fishno,Location,Method,Weather,Wind Speed,Wind Direction,Total Weight) VALUES ('" & txtDate.Text & "''" & cbxSpecies.Text & "','" & txtTime.Text & "' ,'" & txtFish.Text & "' ,'" & txtLocation.Text & "' ,'" & cbxMethod.Text & "' ,'" & rdoSunny.Text Or rdoOver.Text & "' ,'" & txtWspeed.Text & "' ,'" & cbxDirect.ValueMember & "','" & txtWeight.Text & "')"
OleDbConnection1.Open()

MessageBox.Show("sending insert query: " & OleDbDataAdapter1.InsertCommand.CommandText & vbCrLf & OleDbDataAdapter1.InsertCommand.ExecuteNonQuery())

MessageBox.Show("Query successful")

MessageBox.Show("The details of: " & txtFish.Text & " " & vbCrLf & "have been correctly added to the database")

Catch Exception As System.Data.OleDb.OleDbException
Console.WriteLine(Exception.StackTrace)
MessageBox.Show(" " & Exception.ToString)
End Try
OleDbConnection1.Close()

EndOf:
End Sub

damnyanks

And the problem is ?

Magnolia_Fan

Ohh sorry bout that, its coming up that it doesn't like a long int in the insert part...but I've checked out all my fields in the database and none are long int's

Duckjob

post up a cut and paste of all the error text or else a screen capture.

damnyanks

BTW its generally seen as terrible practice to use the GoTo keyword

Magnolia_Fan

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from string "INSERT INTO tblAdd(Date,Species," to type 'Long' is not valid.

Duckjob

Try using [] around all the field names in the INSERT.

Magnolia_Fan

I got the same error:

OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO tblAdd([Date],[Species],[Time Of Day],[fishno],[Location],[Method],[Weather],[Wind Speed],[Wind Direction],[Total Weight]) VALUES ('" & txtDate.Text & "''" & cbxSpecies.Text & "','" & txtTime.Text & "' ,'" & txtFish.Text & "' ,'" & txtLocation.Text & "' ,'" & cbxMethod.Text & "' ,'" & rdoSunny.Text Or rdoOver.Text & "' ,'" & txtWspeed.Text & "' ,'" & cbxDirect.ValueMember & "','" & txtWeight.Text & "')"


sorry if you find this annoying

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from string "INSERT INTO tblAdd([Date],[Speci" to type 'Long' is not valid.

Kernel32

Magnolia_Fan wrote:

This time I kind of know what I'm doing:

Based on the state of that code I would say no.

Magnolia_Fan wrote:

Try
OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO tblAdd(Date,Species,Time Of Day,fishno,Location,Method,Weather,Wind Speed,Wind Direction,Total Weight) VALUES ('" & txtDate.Text & "''" & cbxSpecies.Text & "','" & txtTime.Text & "' ,'" & txtFish.Text & "' ,'" & txtLocation.Text & "' ,'" & cbxMethod.Text & "' ,'" & rdoSunny.Text Or rdoOver.Text & "' ,'" & txtWspeed.Text & "' ,'" & cbxDirect.ValueMember & "','" & txtWeight.Text & "')"
OleDbConnection1.Open()

MessageBox.Show("sending insert query: " & OleDbDataAdapter1.InsertCommand.CommandText & vbCrLf & OleDbDataAdapter1.InsertCommand.ExecuteNonQuery())

ExecuteNonQuery returns an int which you are trying to append to a string, you will need to cast the return value if you want to do that.

Evil Phil

INSERT INTO tblAdd(Date, Species, Time Of Day, fishno, Location, Method, Weather, Wind Speed, Wind Direction, Total Weight) 
	VALUES ('" & txtDate.Text & [COLOR=Red]"','"[/COLOR] & cbxSpecies.Text & "','" & txtTime.Text & "' ,'" & txtFish.Text & "' ,'" & txtLocation.Text & "' ,'" & cbxMethod.Text & "' ,'" & rdoSunny.Text Or rdoOver.Text & "' ,'" & txtWspeed.Text & "' ,'" & cbxDirect.ValueMember & "','" & txtWeight.Text & "')"

Your missing a comma (in red above) which could the problem but I haven't tested it. Have a look at switches too, they're Select Case in Vb6, dunno about vb.net. A lot better than ElseIf with GoTo's.

bonkey

Evil Phil wrote:

[code]
A lot better than ElseIf with GoTo's.

The GoTos aren't even needed in that code. Move the try/catch block inside the if statement as an Else block, delete all the Gotos et voila.

The Close() statement is also just asking for trouble where it is. Consider that if the Open() statement fails and throws an error, this will be caught, and then the Close() will be attempted - with no error-handling wrapped around it.

Ouchies.

jc

Magnolia_Fan

Is there any easier way about this, I've got a project due on Thursday and I've been struggling with the datatbase for the last 4 weeks. I got my load fucntion to work, but since I've tried to use the add fucntion my project has totally screwed up. I was using Data Binding and that didn't work, I tried all coding that didn't work, so now I'm trying it this way and its still not working. Does anybody have an example of an easy insert function?

Magnolia_Fan

And now my search won't work....AHHHHHHHHHHHHHHHHHHHHHHHHHHHHH!, Too much stress, I don't think I want to be a PC Programmer anymore

Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
If txtWspeed.Text = vbNullString Then
MessageBox.Show("Cannot search if there is nothing in the textbox")
ElseIf txtWspeed.Text <> vbNullString Then
Try
DsSearch1.Clear()
daSearch.SelectCommand.CommandText = "SELECT [Date],[Wind Speed],[Wind Direction],[Weather] FROM Fish WHERE " & "txtWspeed.Text = '" & "'[Wind Speed]"
daSearch.Fill(DsSearch1)
' notify the user the query is being set
lblPrediction.Text &= vbCrLf & "Sending Select query: " & daSearch.UpdateCommand.CommandText & vbCrLf
' display information
lblPrediction.Text &= vbCrLf & "Query Successful " & vbCrLf ' prompt user for last name
' display verbose information with database exception
Catch exception As System.Data.OleDb.OleDbException
Console.WriteLine(exception.StackTrace)
lblPrediction.Text &= exception.ToString

' display message box when invalid operation
Catch invalidOperationException As InvalidOperationException
MessageBox.Show(invalidOperationException.Message)
End Try

End If

End Sub

bonkey

daSearch.SelectCommand.CommandText = "SELECT [Date],[Wind Speed],[Wind Direction],[Weather] FROM Fish WHERE " & "txtWspeed.Text = '" & "'[Wind Speed]"

If I might be so bold as to make a blunt suggestion - stop taking shortcuts like this.

You're performing concatenation, and assigning it directly to the .CommandText parameter, and then wondering why (why, oh why, oh why) its not working. Its not working because the generated SQL is wrong, and its a bitch to see it this way.

Instead:

String generatedSql =  "SELECT [Date],[Wind Speed],[Wind Direction],[Weather] FROM Fish WHERE " & "txtWspeed.Text = '" & "'[Wind Speed]"

daSearch.SelectCommand.CommandText = generatedSql

Then, put a breakpoint on the second line, and look at what you're assigning. If its not working, then copy and paste the entire SQL string into something you can run against your database (and Access QUery window? Query Analyzer for MSSQL? depends on your DB) and run it. If it doesn't run, your problem is that you're not generating your SQL right. If it does run, your problem is that you're not using the SQL correctly in the application. Thats half the problem - finding out where things aren't working. Then you could either post up the code without the SQL (i.e. "my SQL works...the code around it doesn't", or the SQL without the code "this SQL doesn't work...why not").

Once you know where the problem is, all thats left is why.

In an ideal world, I'd say you should have written a sample SQL statement first, and once you got it to work, implemented code to generate the SQL statement. You can then visually compare (at run-time) the generated string to the tested hand-written one. If they are not identical, you're generating it wrong.

At a guess, you'll find that the apostrophes wrapping the string-value to search for in the WHERE clause aren't where they should be. Look - your code says :

"... WHERE " & "txtWspeed.Text = '" & "'[Wind Speed]"

With a value of 100 in txtWSpeed, this will generate :

... WHERE 100 = ''[Wind Speed]

(Note: two apostrophes, not a shift-2 double-quote).

I know it will sound rude, but either you're not looking at the SQL string you generate (which makes fixing it pretty f**king impossible), or you don't know what valid SQL looks like. One of these two things is the root of your problems.

jc

bonkey

I've started going through your original question at the top, and the issue is the same.

The SQL you're generating is wrong. The rest of the code doesn't matter until you have that fixed. Look at the generated statement, and see whats wrong with it. I can see a couple of possible culprits, any of which could be the root of your problem :

0) No comma between the first two parameters
1) fishno is passed in as a string (i.e. you wrap it in apostrophes). I'm guessing its defined as a numeric of some description in the DB.
2) I have no idea what the rdoSunny.Text Or rdoOver.Text bit of your code is doing...from what I can see itwill generate the value "True" or something. Is this really what you want added to the Weather field?
3) Windspeed is passed in as a string (i.e. you wrap it in apostrophes). I'm guessing its defined as a numeric of some description in the DB.
4) Total Weight is passed in as a string (i.e. you wrap it in apostrophes). I'm guessing its defined as a numeric of some description in the DB.
5) You may need to wrap the field-names which contain spaces in square brackets in the "field list" part of the first half of the SQL statement.

Most DB tools have query-builders for building an INSERT statement. Why not use one of those to generate a sample, and then reverse-engineer your code from the (working) samlpe.

Incidentally - there is probably (definitely?) more than one error in your SQL, so don't be surprised if you fix the first one and get a different error.

jc

amen

just my 2 cents worth
but you should really seperate your input validation from your database access (different class)
and I might even go so far as to seperate the sql generation from the actual execution

rsynnott

Ooh, gotos, gotos are back! Yuch. Yep, separate code, and use the debugger; it's your friend.