insert into two tables with stored procedure

R

Richnl

Well-known member
Joined
Mar 20, 2007
Messages
93
Programming Experience
Beginner
I need to convert this routine to a storedprocedure that inserts this data and based on the generated autoincrement value I have to make another insert into another table.
VB.NET: 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2]dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DDataSet.Table1Row =[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet.Table1.NewTable1Row[/SIZE]
[SIZE=2]dr.Name = [/SIZE][SIZE=2][COLOR=#a31515]"asperigo"[/COLOR][/SIZE]
[SIZE=2]dr.Adress = route 66[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet.Table1.AddTable1Row(dr)[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Table1TableAdapter.Update([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet)[/SIZE]
I think I need to use a stored proc with the SCOPE_IDENTITY method
When I press the submitbutton the database needs to be updated with data for both tables from input textboxes

This did not work
the sqlcommand asks for the ID parameter, but I thought this was handeled internally?
The same thing could be set about the GetDate() method, it needs to set it automaticly.
What is wrong with the code?
VB.NET: 
Create Procedure InsertTwoTables
@Name As varchar(20),
@Adress As varchar(20),
@IDTable2 as int
@Field2Table2 As datetime= getDate()
@Field3Table2 As varchar(20),
@NewId As int Output
 
As
Insert Into Table1 (Name, Adress)
Values (@Name, @Adress)
Set @NewId = Scope_Identity 
Insert Into Table2 (ID, Field2Table2, Field3Table2)
Values (@NewId , @Field2Table2 , @Field3Table2)
VB.NET: 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] fn[/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TBox1.Text[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ln [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][SIZE=2]= [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TBox2.Text[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] str [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][SIZE=2]= [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TBox3.Text[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection(sconn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#a31515]"InsertTwoTables"[/COLOR][/SIZE][SIZE=2], conn)[/SIZE]
[SIZE=2]cmd.CommandType = CommandType.StoredProcedure[/SIZE]
[SIZE=2]conn.Open()[/SIZE]
[SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#a31515]"@FirstName"[/COLOR][/SIZE][SIZE=2], fn)[/SIZE]
[SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#a31515]"@LastName"[/COLOR][/SIZE][SIZE=2], ln)[/SIZE]
[SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#a31515]"@Field3Table2"[/COLOR][/SIZE][SIZE=2], "something")[/SIZE]
[SIZE=2]cmd.ExecuteNonQuery()[/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
Also
Does a stored proc bypass the tableadapter, and if so, does the disconnected dataset needs to be refreshed again somehow?

Thanks, Richard
 
Last edited:
Sort by date Sort by votes
Richnl said:
I need to convert this routine to a storedprocedure that inserts this data and based on the generated autoincrement value I have to make another insert into another table.
VB.NET: 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2]dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DDataSet.Table1Row =[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet.Table1.NewTable1Row[/SIZE]
[SIZE=2]dr.Name = [/SIZE][SIZE=2][COLOR=#a31515]"asperigo"[/COLOR][/SIZE]
[SIZE=2]dr.Adress = route 66[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet.Table1.AddTable1Row(dr)[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Table1TableAdapter.Update([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet)[/SIZE]
I think I need to use a stored proc with the SCOPE_IDENTITY method
When I press the submitbutton the database needs to be updated with data for both tables from input textboxes

This did not work
the sqlcommand asks for the ID parameter, but I thought this was handeled internally?
Click to expand...
It is. I think you have a slightly misconfigured tableadapter. Check the parameters collection of the relevant query

VB.NET: 
Create Procedure InsertTwoTables
[COLOR=red]@Name As varchar(20),[/COLOR]
[COLOR=red]@Adress As varchar(20),[/COLOR]
[COLOR=red]@IDTable2 as int[/COLOR]
[COLOR=red]@Field2Table2 As datetime= getDate()[/COLOR]
[B]@Field3Table2 As varchar(20),[/B]
[COLOR=red]@NewId As int Output[/COLOR]
VB.NET: 
[COLOR=red][SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2]"@FirstName"[/SIZE][SIZE=2], fn)[/SIZE][/COLOR]
[COLOR=red][SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2]"@LastName"[/SIZE][SIZE=2], ln)[/SIZE][/COLOR]
[B][SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#a31515]"@Field3Table2"[/COLOR][/SIZE][SIZE=2], "something")[/SIZE][/B]
Click to expand...

You dont seem to have noticed that your parameters to your sproc are completely different to those you add to the command. To assist you in noticing which ones are configured incorrectly, I have coloured them red. Correctly assigned ones are in bold..

This may provide valuable hints as to why the code doesnt work


Does a stored proc bypass the tableadapter
Click to expand...
Sprocs and tableadapters are for completely different things.

What purpose is it you think a tableadapter performs? (because I need to understand where the logical disconnect in your understanding lies, before launching into explanation)

and do I have to refresh the disconnected dataset again somehow?
Click to expand...
Why would you do this? After performing an Update() the idea is that the dataset and the database are in-sync;that's the purpose of Update(). Why would you refresh it? (After saving a document in MS word, do you close it then open it again? If so, why?)
 
Upvote 0 Downvote
oh no, I am sorry'

I did not type the same thing(sloppy), but the issue is still there
If I execute nonquerie it says it has not been given the NewId param?

VB.NET: 
Create Procedure InsertTwoTables
[COLOR=red]@FirstName As varchar(20),[/COLOR]
[COLOR=red]@LastName As varchar(20),[/COLOR]
[COLOR=red]@Field1 as int  [/COLOR][COLOR=black][B](is the ID column in the second table)[/B][/COLOR]
[COLOR=red]@Field2 As datetime= getDate()  [/COLOR][COLOR=black][B](this one, I don't call in the sqlparameter addwithvalue)[/B][/COLOR]
[B]@Field3 As varchar(20), (this one, I set in sqlparameter addwithvalue)[/B]
[COLOR=red]@NewId As int Output[/COLOR]
 
[COLOR=#ff0000]as[/COLOR]
Insert Into Table1 (FirstName, LastName)
Values (@FirstName, @LastName)
Set @NewId = Scope_Identity 
Insert Into Table2 (Field1, Field2, Field3)
Values (@NewId , @Field2 , @Field3)

Ok, I used more generic names for table2 to make it more readable (field1, field2, etc)
The dataset.datarow doesn't apply anymore. It was just to point out that I needed to go in another direction.

What purpose is it you think a tableadapter performs? (because I need to understand where the logical disconnect in your understanding lies, before launching into explanation)
Click to expand...
Well, in my very short experience in these matters was that handeling data from a database was separated into two category's.
connected and disconnected.
When I handle disconnected data I call the dataadapter to update to the database(it opens the connection for me)and also get's the updated data back again (or I already had it in the dataset!).Now, I am opening a connection without calling the adapter that was automaticly created when I dragged the table out off the datasource window. Therefore I was thinking that the disconnected dataset is not knowing what I am doing right now. It's like guarding the frontdoor and I am going threw the backdoor.

I ofcourse would not be asking this If I could get the sproc to work, then I would see for myself.
But anyway, that was my logical thinking
 
Last edited:
Upvote 0 Downvote
It is. I think you have a slightly misconfigured tableadapter. Check the parameters collection of the relevant query
Click to expand...
That's also why I don't understand this sentence, because I don't explicitly call an adapter.
Do I have to configure the individual tableadapters to work with scope_Identity?

I also excluded "@Field1 as int (is the ID column in the second table)"
giving only the name for the column in
Insert into table2 (ID, Field2 , Field3)
Values (@NewId , @Field2 , @Field3)

That's it completely, now it still asks me to supply @NewID param
A param set as Output does not have to be supplied in the commandObject, am I right?
Ok, I am doing it anyway and give it a default value, now it will work
I do need to fill the dataset again to have it contain all the data from the database, unless there is a better method
 
Last edited:
Upvote 0 Downvote
I'm a bit lost as to hat your thinking is, but if I reminded you of the following crucial point, it might help:

SQL Server is not MS Access.
The order of addition of parameter names is completely irrelevant
The name of parameter names is crucial


If you want to call an SProc with the following parameters:

@wibble, @wobble, @retval

Then you CANNOT, in VB, perform the following adds:

AddWithValue("@blahblah1", wibbleTextBox.Text)
AddWithValue("@mySillyName", wobbleTextBox.Text)
Add("@someValueIGetBack").Direction = Output



They HAVE to be:
AddWithValue("@wibble", wibbleTextBox.Text)
AddWithValue("@wobble", wobbleTextBox.Text)
Add("@retval").Direction = Output
 
Upvote 0 Downvote
Richnl said:
That's also why I don't understand this sentence, because I don't explicitly call an adapter.
Do I have to configure the individual tableadapters to work with scope_Identity?
Click to expand...
Eh.. um.. No, you jsut have to configure them to call a certain named procedure and supply parameters with all the correct names and values


I also excluded "@Field1 as int
Click to expand...


It really doesnt matter.. If you declare a SProc with 100 parameters but you only use 1 of them, VB is still going to ask oyu to supply them all. Itsjust like VB infact. If you wrote a Function with 100 params and then called it but only supplied one, you would see a message of "No overload of MyFunc accepts this number of parameters"

Maybe I'm misunderstanding your replies, but I'd have thought this was pretty obvious?

That's it completely, now it still asks me to supply @NewID param
Click to expand...
So, supply it?!

A param set as Output does not have to be supplied in the commandObject, am I right?
Click to expand...
It has to be declared and its .Direction property set to Output, otherwise how do you plan to retrieve the value?

you know, after the query runs, and you say:

MessageBox.Show("The sproc gave an output value of: " & cmd.Parameters("@myOutputParam").ToString() )

How will you expect the parameters collection to contain that parameter if you didnt add it in?



I do need to fill the dataset again to have it contain all the data from the database, unless there is a better method
Click to expand...
*thud

Dont you see that after you perform a successful Update(), your dataset and your database are in sync? You dont need to fill it!

Suppose your db has 10 rows
You fill all into the dataset
You delete 5
You edit the other 5
You add 5 new ones
You call Update()
5 rows disappear from the database using the DELETE SQL query in teh tableadapter
5 rows are updated using the UPDATE SQL
5 rows are created using the INSERT SQL

Exactly when did your database get out of sync with the dataset? Your dataset contains 10 rows, your database contains 10 rows. THe rows have the same data. What will you gain by clearing and refilling teh dataset?


I do have one more question
What do I do with nullable decimals in the database, conversionwhise
For example:
The database has a column defined as decimal, where nullvalues are excepted
The storedprocedure has a param @Price as dec
I get dim price as string=pricetextbox.text from the user input
I control the userinput with keypress event

IfNot price= vbNullString Then
Dim x AsDecimal = Decimal.Parse(price)
EndIf
cmd.Parameters.AddWithValue("@Price", IIf(price= vbNullString, 0, x))
how do I handle it in the sproc?
If @Price=0.......... then asign Decimal value or dbnull.value

Is this a good way of going about it?
Click to expand...
No. Nullables are supposed to be handled like:

Dim dn as Nullable(Of Decimal) = myDbCmd.Parameters("@someOutputDecimalOrNull")

Dim d as Decimal = dn.GetValueOrDefault(0)


If decimal is null, you get a 0 back. You can pick what you want to get back! The whole point for using a Nullable of decimal is because decimal itself cannot be null.

If you want to deal with the null, then call:

If dn.IsNull

...

Again, you havent really told of what you want to do with your decimal
 
Upvote 0 Downvote
Maybe I'm misunderstanding your replies, but I'd have thought this was pretty obvious?
Click to expand...
Yes, it was obvious so I removed it before I got another comment about it.
I already mentioned that my previous post was a bit sloppy.
I was trying to be accurate in that way.
But now I got the comment anyway:D

How will you expect the parameters collection to contain that parameter if you didnt add it in?
Click to expand...
Yes, it is getting clear to me now
All the parameters need to be supplyed(optional is not possible)
And if I want the param to use a default value @Something as string ="something"
I then would just use:
cmd.Parameters.Add("@Something", SqlDbType.String)
I hope I got the hang off it now!

Actually, I don't need an output anyway
I just want the sproc to handle it all by himself(scope_identity)
So, I have to assign the returnvalue to a variable then
dim i as int = scope_identity

Dont you see that after you perform a successful Update(), your dataset and your database are in sync? You dont need to fill it!
Click to expand...
I was navigating threw the gridview and I diddn't see the data that was added? What's that about then?
Again, you havent really told of what you want to do with your decimal
Click to expand...
Thought I deleted this question, because it was a little off subject?
Now there is another thread with the same question, sorry about that

A textbox returns an empty string if the user doesn't type a value
If a value is supplied, I can do decimal.parse(price), am I right -but it doesn't except "" -
the parameter is @Price as dec, so I have to supply a decimal value
the database column accepts no input also(in other words dbNull.value)
So, to continue the story
I have to give the @Price param something it can use (decimal value)
but if the user doesn't give input at all, I want to reflect that in the database (make it a dbnull.value and not make it a default 0, because that was what I was thinking about too)I don't want to see zero's in the gridview!

In short, how do I acomplish that the sproc does insert into table1(@Price....or if price = 0 -> insert into table1(dbnull.value....
It was easyer if I could assign a null value to a decimal type, but that's not possible, am I right (only c#)

I haven't read documentation on this "Dim d as Decimal = dn.GetValueOrDefault(0)", but it looks I need something
opposite if this setValueOrDefault if that would exist

Y hope the explanation was clear
 
Last edited:
Upvote 0 Downvote
Richnl said:
Yes, it is getting clear to me now
Supply all the parameters(optional is not possible)
Click to expand...
Such is the cas ein most languages, to the extent that you could call vb optional parameters something of a blight..

Actually, I don't need an output anyway
I just want the sproc to handle it all by himself(scope_identity)
So, I have to assign the returnvalue to a variable then
dim i as int = scope_identity
Click to expand...
Not necessarily.. If you dont want to use it, dont use it.. just leave it in the parameters collection, and er.. dont use it?!

I was navigating threw the gridview and I diddn't see the data that was added? What's that about then?
Click to expand...
Huh? How can you not? You add a row to the DGV, change the info, and that row gets added to the database. Sure there isnt a big flashing red sign saying "this is an added row" but you can query the rowstate and find out..

I jsut dont understand how you can have a DataGridView...
And you go to the bottom, to the *> row that is all blank
And you click in it, and you type stuff
And that is your new row
And you click save button
And this calls EndEdit(), followed by Update() (committing your new row to database)
And then you cant see your new row?

HUH?
You just added it?
Why can you not see it, when you just put it there? It's the row you were just typing on! Does it really vanish?
 
Upvote 0 Downvote
Why can you not see it, when you just put it there? It's the row you were just typing on! Does it really vanish?
Click to expand...
HaHa, I don't see where you make the assumption that I do something directly in the datagridview or do an update threw the adapter to get to the database since I did not supply the code for that.

I use textboxes and give the values to the sqlcommand object parameters
I don't do:
Me.Table1TableAdapter.Update(Me.DBDataSet)
Maybe, that's why we don't understand eachother so good?
maybe, I am doing something that is wrong from the beginning because I don't address dataadapters?
What am I supposed to do instead of what I am doing now?
Does it really vanish?
Click to expand...
No, I guess there's not really a magical thing going on here:D
 
Last edited:
Upvote 0 Downvote
Richnl said:
I use textboxes and give the values to the sqlcommand object parameters
Click to expand...
OK, well.. Dont

Youre making life hard for yourself if you do.

Instead, make a new row, add it to the datatable (it then appears in the grid) then update the table


I don't do:
Me.Table1TableAdapter.Update(Me.DBDataSet)
Maybe, that's why we don't understand eachother so good?
maybe, I am doing something that is wrong from the beginning because I don't address dataadapters?
Click to expand...
You mean you dont use tableadapters? Hmm.. then you have a strange program, because your SQL is scattered all over the place, and youre repeating code all over. Use tableadapters, put sql in them and then use the methods provided for you. Dont write sql in button handlers

What am I supposed to do instead of what I am doing now?
Click to expand...
Erm.. Take a read of the DW2 link in my signature, youll find a section on using stored procedures to read from and also write to databases

No, I guess there's not really a magical thing going on here:D
Click to expand...
Nop.. just my assumption that you were using TableAdapters from, this code you originally posted:
VB.NET: 
[COLOR=#0000ff]Dim[/COLOR][SIZE=2]dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DDataSet.Table1Row =[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet.Table1.NewTable1Row[/SIZE]
[SIZE=2]dr.Name = [/SIZE][SIZE=2][COLOR=#a31515]"asperigo"[/COLOR][/SIZE]
[SIZE=2]dr.Adress = route 66[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet.Table1.AddTable1Row(dr)[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Table1TableAdapter.Update([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DDataSet)[/SIZE]
 
Upvote 0 Downvote
Nop.. just my assumption that you were using TableAdapters from, this code you originally posted:
Click to expand...

Now, I am really back at the beginning

Do you then at least have some solution on inserting a dbnull.value or a decimal value depending on userinput or is that not relevant anymore if I have to start over differently?
 
Upvote 0 Downvote
I'm not sure what question is being asked.

Here is the code-behind that the IDE wrote for me when I linked a stored procedure to a tableadapter:

VB.NET: 
        Public Overloads Overridable Function BulkCustomerInsert( _
                    ByVal IN_CLIENT_REF As String,  _
                    ByVal IN_CLIENTS_NUMBER_FOR_CUST As String,  _
                    ByVal IN_TITLE_ID As String,  _
                    ByVal IN_FIRST_NAME As String,  _
                    ByVal IN_LAST_NAME As String,  _
                    ByVal IN_CUST_ADDRESS_1 As String,  _
                    ByVal IN_CUST_ADDRESS_2 As String,  _
                    ByVal IN_CUST_ADDRESS_3 As String,  _
                    ByVal IN_CUST_CITY As String,  _
                    ByVal IN_CUST_POSTCODE As String,  _
                    ByVal IN_LANDLINE_NUMBER As String,  _
                    ByVal IN_MOBILE_NUMBER As String,  _
                    ByVal IN_FAX_NUMBER As String,  _
                    ByVal IN_EMAIL_ADDRESS As String,  _
                    ByVal IN_BANK_ACCHOLD1 As String,  _
                    ByVal IN_BANK_ACCHOLD2 As String,  _
                    ByVal IN_BANK_SORT_CODE As String,  _
                    ByVal IN_BANK_ACC_NO As String,  _
                    ByVal IN_BANK_NAME As String,  _
                    ByVal IN_BANK_CONTACT_NAME As String,  _
                    ByVal IN_BANK_ADDRESS_1 As String,  _
                    ByVal IN_BANK_ADDRESS_2 As String,  _
                    ByVal IN_BANK_ADDRESS_3 As String,  _
                    ByVal IN_BANK_CITY As String,  _
                    ByVal IN_BANK_POSTCODE As String) As Integer
            Dim command As System.Data.OracleClient.OracleCommand = CType(Me.CommandCollection(0),System.Data.OracleClient.OracleCommand)
            If (IN_CLIENT_REF Is Nothing) Then
                command.Parameters(0).Value = System.DBNull.Value
            Else
                command.Parameters(0).Value = CType(IN_CLIENT_REF,String)
            End If
            If (IN_CLIENTS_NUMBER_FOR_CUST Is Nothing) Then
                command.Parameters(1).Value = System.DBNull.Value
            Else
                command.Parameters(1).Value = CType(IN_CLIENTS_NUMBER_FOR_CUST,String)
            End If
            If (IN_TITLE_ID Is Nothing) Then
                command.Parameters(2).Value = System.DBNull.Value
            Else
                command.Parameters(2).Value = CType(IN_TITLE_ID,String)
            End If
            If (IN_FIRST_NAME Is Nothing) Then
                command.Parameters(3).Value = System.DBNull.Value
            Else
                command.Parameters(3).Value = CType(IN_FIRST_NAME,String)
            End If
            If (IN_LAST_NAME Is Nothing) Then
                command.Parameters(4).Value = System.DBNull.Value
            Else
                command.Parameters(4).Value = CType(IN_LAST_NAME,String)
            End If
            If (IN_CUST_ADDRESS_1 Is Nothing) Then
                command.Parameters(5).Value = System.DBNull.Value
            Else
                command.Parameters(5).Value = CType(IN_CUST_ADDRESS_1,String)
            End If
            If (IN_CUST_ADDRESS_2 Is Nothing) Then
                command.Parameters(6).Value = System.DBNull.Value
            Else
                command.Parameters(6).Value = CType(IN_CUST_ADDRESS_2,String)
            End If
            If (IN_CUST_ADDRESS_3 Is Nothing) Then
                command.Parameters(7).Value = System.DBNull.Value
            Else
                command.Parameters(7).Value = CType(IN_CUST_ADDRESS_3,String)
            End If
            If (IN_CUST_CITY Is Nothing) Then
                command.Parameters(8).Value = System.DBNull.Value
            Else
                command.Parameters(8).Value = CType(IN_CUST_CITY,String)
            End If
            If (IN_CUST_POSTCODE Is Nothing) Then
                command.Parameters(9).Value = System.DBNull.Value
            Else
                command.Parameters(9).Value = CType(IN_CUST_POSTCODE,String)
            End If
            If (IN_LANDLINE_NUMBER Is Nothing) Then
                command.Parameters(10).Value = System.DBNull.Value
            Else
                command.Parameters(10).Value = CType(IN_LANDLINE_NUMBER,String)
            End If
            If (IN_MOBILE_NUMBER Is Nothing) Then
                command.Parameters(11).Value = System.DBNull.Value
            Else
                command.Parameters(11).Value = CType(IN_MOBILE_NUMBER,String)
            End If
            If (IN_FAX_NUMBER Is Nothing) Then
                command.Parameters(12).Value = System.DBNull.Value
            Else
                command.Parameters(12).Value = CType(IN_FAX_NUMBER,String)
            End If
            If (IN_EMAIL_ADDRESS Is Nothing) Then
                command.Parameters(13).Value = System.DBNull.Value
            Else
                command.Parameters(13).Value = CType(IN_EMAIL_ADDRESS,String)
            End If
            If (IN_BANK_ACCHOLD1 Is Nothing) Then
                command.Parameters(14).Value = System.DBNull.Value
            Else
                command.Parameters(14).Value = CType(IN_BANK_ACCHOLD1,String)
            End If
            If (IN_BANK_ACCHOLD2 Is Nothing) Then
                command.Parameters(15).Value = System.DBNull.Value
            Else
                command.Parameters(15).Value = CType(IN_BANK_ACCHOLD2,String)
            End If
            If (IN_BANK_SORT_CODE Is Nothing) Then
                command.Parameters(16).Value = System.DBNull.Value
            Else
                command.Parameters(16).Value = CType(IN_BANK_SORT_CODE,String)
            End If
            If (IN_BANK_ACC_NO Is Nothing) Then
                command.Parameters(17).Value = System.DBNull.Value
            Else
                command.Parameters(17).Value = CType(IN_BANK_ACC_NO,String)
            End If
            If (IN_BANK_NAME Is Nothing) Then
                command.Parameters(18).Value = System.DBNull.Value
            Else
                command.Parameters(18).Value = CType(IN_BANK_NAME,String)
            End If
            If (IN_BANK_CONTACT_NAME Is Nothing) Then
                command.Parameters(19).Value = System.DBNull.Value
            Else
                command.Parameters(19).Value = CType(IN_BANK_CONTACT_NAME,String)
            End If
            If (IN_BANK_ADDRESS_1 Is Nothing) Then
                command.Parameters(20).Value = System.DBNull.Value
            Else
                command.Parameters(20).Value = CType(IN_BANK_ADDRESS_1,String)
            End If
            If (IN_BANK_ADDRESS_2 Is Nothing) Then
                command.Parameters(21).Value = System.DBNull.Value
            Else
                command.Parameters(21).Value = CType(IN_BANK_ADDRESS_2,String)
            End If
            If (IN_BANK_ADDRESS_3 Is Nothing) Then
                command.Parameters(22).Value = System.DBNull.Value
            Else
                command.Parameters(22).Value = CType(IN_BANK_ADDRESS_3,String)
            End If
            If (IN_BANK_CITY Is Nothing) Then
                command.Parameters(23).Value = System.DBNull.Value
            Else
                command.Parameters(23).Value = CType(IN_BANK_CITY,String)
            End If
            If (IN_BANK_POSTCODE Is Nothing) Then
                command.Parameters(24).Value = System.DBNull.Value
            Else
                command.Parameters(24).Value = CType(IN_BANK_POSTCODE,String)
            End If
            Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
            If ((command.Connection.State And System.Data.ConnectionState.Open)  _
                        <> System.Data.ConnectionState.Open) Then
                command.Connection.Open
            End If
            Dim returnValue As Integer
            Try 
                returnValue = command.ExecuteNonQuery
            Finally
                If (previousConnectionState = System.Data.ConnectionState.Closed) Then
                    command.Connection.Close
                End If
            End Try
            Return returnValue
        End Function

To amke this, I just pointed and clicked a few times, and a new command was added and parameters were set up. Now to insert a customer, I just call this, passing whatever I feel like passing. If I want to pass nothing, I pass nothing..

What i'm trying to get across to you, is whatever problem youre having, I could solve it in about 2 seconds by getting the IDE to write the code. THe code would be 100% correct and you can just call it. Read DW2, sections on stored procedures
 
Upvote 0 Downvote
thank you, I will start reading the sections you mentioned
thanks anyway for pointing out, I had to use the adapter.

I will get back to you (or post back) if I don't understand something!
 
Last edited:
Upvote 0 Downvote

Similar threads

D
  • Question
Stored procedure to insert field value that have other fields from other table
Replies
1
Views
9K
jmcilhinney
jmcilhinney
D
  • Question
Create click event that use two different store procedures (insert/update)
Replies
0
Views
4K
DARK__FOXX
D
D
  • Question
Calling SQL stored proc from web app - SP runs but does not update
Replies
1
Views
3K
jmcilhinney
jmcilhinney
M
  • Question
Question How to append records in master detail tables.
Replies
0
Views
2K
Mazhar
M
D
  • Question
Question Stored Procedure/TableAdapter and Failed to enable contraints
Replies
0
Views
3K
dsk96m
D

Share this page

Latest posts

Back
Top